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.

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:

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:

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:

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:

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:

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

Step 5: Restore into the target#

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

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:

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:

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:

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.
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: set up the recovery layers on the new target
• Back up to Quake AI object storage: copy dumps off the data volume
• Database networking and access: security group scope and account host patterns after the cutover