Migrate data via mydumper

mydumper tool is a powerful MySQL logical data-migration tool, including:

  • mydumper - responsible to export a consistent of MySQL databases

  • myloader - reads the dump from mydumper, connects the to destination database and imports the data

Both tools use multi-threading capabilities and support S3 storage to write/read dumps.

Note

For data stored in legacy charms, see How to migrate data via mysqldump

Prepare

Before migrating data:

Install mydumper

Install the latest version from GitHub:

wget https://github.com/mydumper/mydumper/releases/download/v0.15.1-3/mydumper_0.15.1-3.jammy_amd64.deb && \
sudo apt install ./mydumper_0.15.1-3.jammy_amd64.deb

Dump database

Dump database using Charmed MySQL operator user serverconfig:

# Collect credentials
DB_NAME=<your_db_name>
OLD_DB_IP=$(juju show-unit mysql/0 | yq '.[] | .public-address')
OLD_DB_USER=serverconfig
OLD_DB_PASS=$(juju run mysql/leader get-password username=${OLD_DB_USER}| yq '.password')

# Test connection
mysql -h ${OLD_DB_IP} -u ${OLD_DB_USER} -p${OLD_DB_PASS} ${DB_NAME}

# Dump database using mydumper
mydumper -h ${OLD_DB_IP} -u ${OLD_DB_USER} -p ${OLD_DB_PASS} -B ${DB_NAME}

The content of the database dump is stored in a newly created folder, e.g. export-20230927-123337 (which can be stored on S3-compatible storage):

> ls -la export-20230927-123337
drwxr-x---  2 ubuntu ubuntu   4096 Sep 27 12:33 .
drwxr-x--- 18 ubuntu ubuntu   4096 Sep 27 12:34 ..
-rw-rw-r--  1 ubuntu ubuntu    175 Sep 27 12:33 your_db_name-schema-create.sql
-rw-rw-r--  1 ubuntu ubuntu      0 Sep 27 12:33 your_db_name-schema-triggers.sql
-rw-rw-r--  1 ubuntu ubuntu    298 Sep 27 12:33 your_db_name.data-schema.sql
-rw-rw-r--  1 ubuntu ubuntu 124131 Sep 27 12:33 your_db_name.data.00000.sql
-rw-rw-r--  1 ubuntu ubuntu    314 Sep 27 12:33 your_db_name.random_data-schema.sql
-rw-rw-r--  1 ubuntu ubuntu    153 Sep 27 12:33 your_db_name.random_data.00000.sql
-rw-rw-r--  1 ubuntu ubuntu    499 Sep 27 12:33 metadata

Restore

NEW_DB_IP=...
NEW_DB_USER=serverconfig
NEW_DB_PASS=...

myloader -h ${NEW_DB_IP} -u ${NEW_DB_USER} -p ${NEW_DB_PASS} --directory=export-20230927-123337 --overwrite-tables