DB data migration using ‘mydumper’

Tip: use ‘mysqldump’ manual to migrate legacy charms data.

The 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. MyDumper is Open Source and maintained by the community, it is not a Percona, MariaDB, MySQL or Canonical product.

Before the data migration check all limitations of the modern Charmed MySQL charm! Please check your application compatibility with Charmed MySQL before migrating production data from legacy charm!

Installation:

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

Dumping:

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 DB dump content 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

Restoring:

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