FestivalsApp 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

• Bash script friendly environment
• Visual Studio Code 1.84.2+
  • Plugin recommendations are managed via workspace recommendations.
• MySQL Community Edition Version 8+

Deployment

All of the deployment scripts require at least 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 Go binaries are able to run without system dependencies so there are not many requirements for the system to run the festivals-database-node binary. The config file needs to be placed at /etc/festivals-database-node.conf or the template config file needs to be present in the directory the binary runs in.

You need to provide certificates in the right format and location:

• The default path to the root CA certificate is /usr/local/festivals-database-node/ca.crt
• The default path to the server certificate is /usr/local/festivals-database-node/server.crt
• The default path to the corresponding key is /usr/local/festivals-database-node/server.key

The database alswo needs certificates in the right format and location:

• The default path to the root CA certificate is /var/lib/mysql/ca.pem
• The default path to the database certificate is /var/lib/mysql/database.pem
• The default path to the corresponding key is /var/lib/mysql/database-key.pem

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 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

Installing 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