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