# Running a standalone database instance

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:

```sql
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](https://docs.uibakery.io/on-premise/install-and-update/updating-environment-variables) the following environment variables and restart UI Bakery:

```bash
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:

```bash
# 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:

{% hint style="warning" %}
**Before Proceeding:** Ensure a backup has been created to prevent data loss.
{% endhint %}

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

```bash
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.

```bash
mysql -u root -p < backup.sql
```