Migrate database data via `mysqldump`¶

This guide describes how to copy data:

from a legacy MySQL charm to a modern MySQL charm
from a modern MySQL charm to a different installation of the same modern MySQL charm.

Note that this guide describes how to migrate database data only.

For information about integrating your charm with a MySQL database, see How to integrate a database with my charm.

Do you need to migrate?¶

Legacy MariaDB/MySQL charms for machines:

Legacy MariaDB/MySQL charms for Kubernetes:

OSM MariaDB K8s

See the mysqldump guide for Charmed MySQL K8s

To check if a database migration is required, run the following commands, where DB_CHARM is the name of your legacy database application:

DB_CHARM= < mariadb | percona-cluster | mysql-innodb-cluster >
juju show-application ${DB_CHARM} | yq '.[] | .charm'

No migration is necessary if the output above is mysql.

Prepare¶

Before migrating data:

check all limitations of the modern Charmed MySQL charm
check your application’s compatibility with Charmed MySQL

Caution

Always perform the migration in a test environment before performing it in production!

Prerequisites¶

Client machine with access to deployed legacy charm
Juju 2.9 or later
- See the Juju explanation for more details
Enough storage in the cluster to support backup/restore of the databases
mysql-client on client machine (install by running sudo apt install mysql-client)

Caution

Most legacy database charms support old Ubuntu series only, while Juju 3.x does NOT support Ubuntu Bionic.

It is recommended to use the latest stable revision of the charm on Ubuntu Jammy and Juju 3.x

Obtain existing database credentials¶

Set DB_APP to the name of the desired unit:

DB_APP= < mariadb/0 | percona-cluster/leader | mysql-innodb-cluster/1 >

Get username and password of the existing legacy database from the database relation. The username is usually root, and the password is specified in the mysql relation by root_password:

OLD_DB_RELATION_ID=$(juju show-unit ${DB_APP} | yq '.[] | .relation-info | select(.[].endpoint == "mysql") | .[0] | .relation-id')

OLD_DB_USER=root

OLD_DB_PASS=$(bash -c "juju run --unit ${DB_APP} 'relation-get -r ${OLD_DB_RELATION_ID} - ${DB_APP}' | grep root_password" | awk '{print $2}')

OLD_DB_IP=$(juju show-unit ${DB_APP} | yq '.[] | .address')

Deploy new MySQL databases and obtain credentials¶

Deploy new MySQL databases. In this example, 3 units are deployed:

juju deploy mysql --channel 8.0/stable -n 3

Obtain credentials for each new database by executing the following commands, once per database:

NEW_DB_USER=$(juju run mysql/leader get-password | yq '.username')
NEW_DB_PASS=$(juju run mysql/leader get-password | yq '.password')
NEW_DB_IP=$(juju show-unit mysql/0 | yq '.[] | .address')

Migrate database¶

The next step is to use the credentials and information obtained in previous steps to perform the database migration.

First, ensure that there are no new connections are made and that database is not altered.

Remove the relation between your charm and the legacy MySQL charm:

juju remove-relation <your_application> <mariadb | percona-cluster | mysql-innodb-cluster>

Connect to the legacy database to verify the connection:

mysql \
  --host=${OLD_DB_IP} \
  --user=${OLD_DB_USER} \
  --password=${OLD_DB_PASS} \
  -e "show databases"

Choose which databases to dump/migrate to the new charm (one by one!)

DB_NAME=< e.g. wordpress >

Create a backup of each database file using the mysqldump utility, username, password, and unit’s IP address, obtained earlier. This will create a dump that can be used to restore the database.

OLD_DB_DUMP="legacy-mysql-${DB_NAME}.sql"

mysqldump \
  --host=${OLD_DB_IP} \
  --user=${OLD_DB_USER} \
  --password=${OLD_DB_PASS} \
  --column-statistics=0 \
  --databases ${OLD_DB_NAME} \
  > "${OLD_DB_DUMP}"

Connect to the new database using username, password, and unit’s IP address, and restore database from backup:

mysql \
  --host=${NEW_DB_IP} \
  --user=${NEW_DB_USER} \
  --password=${NEW_DB_PASS} \
  < "${OLD_DB_DUMP}"

Integrate with modern charm¶

Integrate your application and new MySQL database charm (using the database or mysql endpoint):

juju integrate <your_application> mysql:database

If the mysql_client interface is not yet supported, use the legacy mysql interface:

juju integrate <your_application> mysql:mysql

Verify database migration¶

Create a dump for the new MySQL database and compare it to the backup created earlier:

NEW_DB_DUMP="new-mysql-${DB_NAME}.sql"
mysqldump \
--host=${NEW_DB_IP} \
--user=${NEW_DB_USER} \
--password=${NEW_DB_PASS} \
--column-statistics=0 \
--databases ${DB_NAME} \

> "${NEW_DB_DUMP}"
diff "${OLD_DB_DUMP}" "${NEW_DB_DUMP}"

Note

Some variables will vary between legacy and modern charms, namely: ${NEW_DB_PASS} and ${NEW_DB_IP}. These must be adjusted for the correct database, accordingly.

The difference between two SQL backup files should be limited to server versions, IP addresses, timestamps and other non data related information.

Remove old databases¶

Test your application and if you are happy with a data migration, do not forget to remove legacy charms to keep the house clean:

juju remove-application --destroy-storage < mariadb | percona-cluster | mysql-innodb-cluster >

Migrate database data via mysqldump¶

Do you need to migrate?¶

Prepare¶

Prerequisites¶

Obtain existing database credentials¶

Deploy new MySQL databases and obtain credentials¶

Migrate database¶

Integrate with modern charm¶

Verify database migration¶

Remove old databases¶

Migrate database data via `mysqldump`¶