Festivals App Database
This is the project repository of the MySQL database used by the festivals-server for persistently storing festival data
and a lightweight go sidecar app, called festivals-database-node. The festivals-database-node will register with the festivals-gateway discovery service and exposes other
functions including backing up the database.
Development •
Deployment •
Usage •
Architecture •
Engage
Development
The database development is currently a little bit under-organized as at the moment there are really just database scripts storing the schema and the test data.
Beside that there are bash scripts to install, backup, restore and uninstall the database but it all depends on manually running the script rather than having a
build or test procedure. To test whether the database is correct i'm currently relying on downstream tests of the webserver
or API framework and on the ability to rollback the database to a backup known to work.
Requirements
Deployment
All of the deployment scripts require Ubuntu 20 LTS as the operating system, so you have to do the general VM setup first and than use the install script to get the database and database-node running.
The project folders are located at /usr/local/festivals-database
and /usr/local/festivals-database-node
.
The backup folder is located at /srv/festivals-database/backups
.
The database
You need to convert the root and server certificate and server key to PEM for MYSQL being able to use the files:
openssl x509 -in mycert.crt -out mycert.pem -outform PEM
openssl rsa -in my.key -text > mykey.pem
Installing a new instance of the database.
curl -o install_database.sh https://raw.githubusercontent.com/Festivals-App/festivals-database/main/operation/install_database.sh
chmod +x install_database.sh
sudo ./install_database.sh <mysql_root_pw> <mysql_backup_pw> <read_only_pw> <read_write_pw>
// Configure ssl certificates, see [festivals-pki repository](https://github.com/Festivals-App/festivals-pki) on how to obtain them.
// If your system enforces AppArmor profiles, the certificates must be located in the mysql data dir at /var/lib/mysql
sudo nano /etc/mysql/mysql.conf.d/festivals-database.cnf
// configure bind-address=<private-ip>
sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf
Restoring a backup created by the backup script
curl -o restore_database.sh https://raw.githubusercontent.com/Festivals-App/festivals-database/main/operation/restore_database.sh
chmod +x restore_database.sh
sudo ./restore_database.sh <url_to_zipped_backup>
Setup test database
curl -L -o insert_testdata.sql https://raw.githubusercontent.com/Festivals-App/festivals-database/main/database_scripts/insert_testdata.sql
sudo mysql -uroot -p -e "source ./insert_testdata.sql"
MySQL CheatSheet
brew services restart mysql
> SHOW DATABASES;
> USE festivals_api_database;
> SHOW TABLES;
> SELECT * FROM ;
> EXIT;
The database node
curl -o install_node.sh https://raw.githubusercontent.com/Festivals-App/festivals-database/main/operation/install_node.sh
chmod +x install_node.sh
sudo ./install_node.sh
Usage
The database festivals_api_database
has two users who can access it from a remote machine:
festivals.api.reader
This user is only able to read from the database.
festivals.api.writer
This user can read and write to the database.
The port is the default MySQL port 3306
Documentation & Architecture
The FestivalsApp database is tightly coupled with the festivals-server which provides the implementation of the FestivalsAPI as the database functions as its persistent storage. To find out more about architecture and technical information see the ARCHITECTURE document.
The general documentation for the Festivals App is in the festivals-documentation repository.
The documentation repository contains architecture information, general deployment documentation, templates and other helpful documents.
Engage
I welcome every contribution, whether it is a pull request or a fixed typo. The best place to discuss questions and suggestions regarding the database is the issues section. More general information and a good starting point if you want to get involved is the festival-documentation repository.
The following channels are available for discussions, feedback, and support requests:
Type |
Channel |
General Discussion |
|
Other Requests |
|
Licensing
Copyright (c) 2017-2024 Simon Gaus. Licensed under the GNU Lesser General Public License v3.0