Database MariaDB to MySQL Migration

Resources used
Migrating to a new server

Migrating to a new server

If we've set up a new server for a site with the plans of migrating the site over to it, we'll need to migrate the database over too.

Notes for production environments

When migrating the database of a production environment, we need to implement a content freeze for the website so that we can ensure that we don't miss anything when copying the data across.

However, this isn't always possible. A potentially better solution would be to have some way to turn on maintenance mode instead so that we can guarantee that we're not losing any data. verbb/patrol offers something like this.

Migration steps

SSH into the original server

Dump the .env config so that it's quick to reference the credentials:

> cat <site host>/.env | grep DB_

DB_DRIVER="mysql"
DB_SERVER="localhost"
DB_USER="forge"
DB_PASSWORD="<redacted>"
DB_DATABASE="<redacted>"
DB_TABLE_PREFIX=""
DB_PORT="3306"

Run mysqldump with the creds above and gzip it when it's done:

mysqldump -uforge -p dbname > site-environment-20250303.sql
gzip site-environment-20250303.sql

exit from the SSH session and scp the file from the original server to the new one:
```
scp original-server:./mysite-staging-20250303.sql.gz new-server:./
```
ssh into the new server and decompress the gz file:
```
gzip -d mysite-staging-20250303.sql.gz
```

Then restore the sql using a similar approach to before:

cat site-domain/.env | grep DB_
mysql -uforge -p dbname < mysite-staging-20250303.sql

Converting the database

Sometimes it's not possible to import a MariaDB database dump into a MySQL instance. Follow these steps to perform the migration and conversion to MySQL.

Follow steps 1 - 3 in Migration steps
exit from the SSH session and scp the file from the original server to the new one:
```
scp original-server:./mysite-staging-20250303.sql.gz /path/to/local/dir/ 
```
In your project dir ensure that you're using a MariaDB database for ddev, and then import the database
```
ddev import-db --file /path/to/local/dir/mysite-staging-20250303.sql.gz
```
Let ddev convert the database
```
ddev debug migrate-database mysql:8.0 
```

Export the database again

ddev export-db --file /path/to/local/dir/mysite-staging-20250303-mysql.sql.gz

Copy the file onto the server

scp /path/to/local/dir/mysite-staging-20250303-mysql.sql.gz new-server:./

ssh into the new server and decompress the gz file:
```
gzip -d mysite-staging-20250303-mysql.sql.gz
```