Running a standalone database instance

UI Bakery incorporates a MySQL database to manage crucial data such as applications, users, roles, among others, essential for its operation.

The standard UI Bakery configuration includes a database running in Docker. For production environments, we recommend using a standalone database.

Step 1. Install MySQL

UI Bakery requires MySQL 8+ version.

Step 2. Create database and user

Use the following script to create database and user:

CREATE DATABASE bakery;

CREATE USER 'bakery'@'host' IDENTIFIED BY 'password';

GRANT
  SELECT, INSERT, UPDATE, DELETE, CREATE, DROP,
  REFERENCES, INDEX, ALTER, LOCK TABLES, EXECUTE,
  CREATE ROUTINE, ALTER ROUTINE 
ON bakery.* TO 'bakery'@'host';

Ensure you have updated the password and host details. If the database should be accessible from external networks, you need to modify the bind-address setting in the MySQL configuration file.

Step 3. Update UI Bakery to use a new database

Provide the following environment variables and restart UI Bakery:

UI_BAKERY_DB_HOST=mysql-instance-address
UI_BAKERY_DB_PORT=3306
UI_BAKERY_DB_DATABASE=bakery
UI_BAKERY_DB_USERNAME=bakery
UI_BAKERY_DB_PASSWORD=password

To stop running the embedded database, remove the db service from the docker-compose.yml file.

How to migrate data from the default db to a standalone

If you need to move data from an embedded database to a standalone one, follow these steps:

Step 0. Reduce the database size (Optional)

If you have been using UI Bakery for an extended period, you might notice that your database requires a significant amount of space.

To clear old audit logs and apps model backups connect to db container:

# list runing containers
docker ps
# connect to db container
docker exec -it DB_CONTAINER_ID /bin/bash
# login into MySQL
mysql -u bakery -p bakery 

Then run the following script:

Before Proceeding: Ensure a backup has been created to prevent data loss.

-- delete audit logs that older than 7 days
DELETE FROM audit_log WHERE created_at < NOW() - INTERVAL 7 DAY

-- delete app backups that older than 7 days
DELETE `release`, project_snapshot FROM project_snapshot
 JOIN `release` ON `release`.project_snapshot_id=project_snapshot.id 
 WHERE `release`.automatic_backup=1 AND `release`.created_at <  NOW() - INTERVAL 7 DAY AND project_snapshot.id>0 AND `release`.id>0;

Step 1. Create the database dump

docker exec DB_CONTAINER_ID /usr/bin/mysqldump -u root --password=root bakery > backup.sql

Step 2. Apply the dump to the standalone database

Ensure you first create an empty database, then proceed to run the script.

mysql -u root -p < backup.sql