# How to migrate MySQL or MariaDB to Quake AI

Source: https://docs.quake.ai/docs/databases/migration/migrate-mysql-mariadb
Markdown: https://docs.quake.ai/docs/databases/migration/migrate-mysql-mariadb.md
> Move an existing MySQL or MariaDB database onto a Quake AI instance: check engine compatibility, dump the source, transfer through a bastion, restore, verify, and cut over.

---

# How to migrate MySQL or MariaDB to Quake AI

Move a MySQL or MariaDB database onto a Quake AI instance using a logical dump. The procedure works from any source that exposes a MySQL-protocol endpoint you can reach with `mysqldump`, including a managed database elsewhere, a VM you run, or a container.

<PrerequisiteBlock methods={["cli"]}>

- A MySQL or MariaDB target on Quake AI, deployed with [the MySQL/MariaDB database template](/resources/deployments/deploy-mysql-database-template)
- Credentials on the source database for an account that can read every object you plan to move
- A bastion, VPN, or jump instance with a route into the target's private subnet. See [SSH bastion access into a private subnet](/docs/network/how-to/ssh-bastion-access)
- Disk space on the workstation or transfer host for the compressed dump

</PrerequisiteBlock>

Replace `SOURCE_HOST`, `SOURCE_USER`, and `SOURCE_DB` with the source connection values, `DB_PRIVATE_IP` with the target's private address, `JUMP_HOST` with the bastion address, and `YOUR_PRIVATE_KEY_PATH` with the key matching the deployment's `key_name`.

## Step 1: Compare the engines

The template installs the package named by `db_engine_package`, which is `mariadb-server` by default. MariaDB and MySQL share the wire protocol and most of the SQL surface, and they diverge on newer features: MySQL 8 roles, some JSON functions, and certain authentication plugins have different behavior or no MariaDB equivalent. Read both versions before you plan the cutover:

```bash
mysql -h SOURCE_HOST -u SOURCE_USER -p -e "SELECT VERSION();"
ssh -i YOUR_PRIVATE_KEY_PATH -J ubuntu@JUMP_HOST ubuntu@DB_PRIVATE_IP \
  'mysql --version'
```

To land on MySQL rather than MariaDB, set `db_engine_package` to the MySQL server package and redeploy the target before you migrate.

## Step 2: Inventory what has to move

A single-database dump carries tables, data, and indexes. Accounts, grants, stored routines, triggers, and events need explicit handling:

```bash
mysql -h SOURCE_HOST -u SOURCE_USER -p -e "SELECT user, host FROM mysql.user;"
mysql -h SOURCE_HOST -u SOURCE_USER -p -e \
  "SELECT routine_schema, routine_name, routine_type FROM information_schema.routines WHERE routine_schema = 'SOURCE_DB';"
mysql -h SOURCE_HOST -u SOURCE_USER -p -e \
  "SELECT table_name, engine FROM information_schema.tables WHERE table_schema = 'SOURCE_DB';"
```

Check the storage engine column: `--single-transaction` produces a consistent dump for InnoDB tables and does not lock them, while MyISAM tables need `--lock-tables` or a write pause to dump consistently.

## Step 3: Dump the source

Dump the database with routines and triggers, and compress on the way out:

```bash
mysqldump -h SOURCE_HOST -u SOURCE_USER -p \
  --single-transaction --quick --routines --triggers --events \
  SOURCE_DB | gzip > source-db.sql.gz
```

Record row counts for the largest tables so Step 6 has a comparison point:

```bash
mysql -h SOURCE_HOST -u SOURCE_USER -p -e \
  "SELECT table_name, table_rows FROM information_schema.tables WHERE table_schema = 'SOURCE_DB' ORDER BY table_rows DESC LIMIT 10;"
```

## Step 4: Transfer the dump into the subnet

Copy the dump to the target through the bastion:

```bash
scp -i YOUR_PRIVATE_KEY_PATH -J ubuntu@JUMP_HOST \
  source-db.sql.gz ubuntu@DB_PRIVATE_IP:/tmp/
```

The target's data volume holds the database, the dump, and the template's scheduled backups. Check free space before a large transfer, and extend the volume first if the dump would crowd it (see [How to extend volume storage capacity](/docs/block/how-to/extend-volume)).

## Step 5: Restore into the target

SSH to the target instance, create the destination database, and load the dump:

```bash
ssh -i YOUR_PRIVATE_KEY_PATH -J ubuntu@JUMP_HOST ubuntu@DB_PRIVATE_IP
sudo mysql -e "CREATE DATABASE SOURCE_DB CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;"
gunzip -c /tmp/source-db.sql.gz | sudo mysql SOURCE_DB
```

Match the character set and collation to the source. A mismatch restores without error and changes how the database sorts and compares text.

Grant the application account access to the restored database:

```bash
sudo mysql -e "GRANT ALL PRIVILEGES ON SOURCE_DB.* TO 'appuser'@'%'; FLUSH PRIVILEGES;"
```

Recreate any other source account explicitly with its own `CREATE USER` and `GRANT` statements. A dump of one database does not carry the `mysql` system tables.

## Step 6: Verify the restore

Compare the table inventory and row counts against the numbers from Step 3:

```bash
sudo mysql -e "SELECT table_name, table_rows FROM information_schema.tables WHERE table_schema = 'SOURCE_DB' ORDER BY table_rows DESC LIMIT 10;"
sudo mysql -e "SELECT routine_name FROM information_schema.routines WHERE routine_schema = 'SOURCE_DB';"
```

`table_rows` is an estimate for InnoDB. Run `ANALYZE TABLE` first, and confirm exact counts with `SELECT count(*)` on the tables that matter most. Then confirm the application account authenticates and reads:

```bash
mysql -h 127.0.0.1 -u appuser -p'YOUR_DB_PASSWORD' SOURCE_DB -e "SELECT 1;"
```

## Step 7: Cut the application over

1. Stop writes at the source, or put the application in maintenance mode.
2. Dump and restore any tables that changed since Step 3.
3. Update the application's connection string to the target's private address and port `3306`. See [How to connect to a database on a private network](/docs/databases/how-to/connect-to-a-private-database).
4. Start the application and confirm it reads and writes against the new target.
5. Confirm the template's nightly `mysqldump` job covers the restored database, since the job dumps all databases on the instance.

Keep the source database readable until the target has run through a full business cycle. The rollback path is the connection string you changed in step 3.

## Next steps

- [Database backup and recovery](/docs/databases/concepts/database-backup-and-recovery): set up the recovery layers on the new target
- [Back up to Quake AI object storage](/docs/object/migration/backup-to-quake-ai): copy dumps off the data volume
- [Database networking and access](/docs/databases/concepts/database-networking-and-access): security group scope and account host patterns after the cutover