Rebuilding a MySQL Replica Fast with MyDumper and MyLoader

What is MyDumper?

When a replica becomes unusable due to corruption or drift, the standard approach is to rebuild it from a fresh source backup. While physical backups are common, logical backups are essential for migrations (e.g., MariaDB to MySQL, MyISAM to InnoDB, or moving to a new storage engine or cloud). MyDumper provides fast, parallel logical dumps that combine portability with multi‑threaded speed.

Backup with MyDumper

Run MyDumper with the following options to create a logical backup while excluding system schemas:

mydumper -v 4 -o data --clear 
--regex '^(?!(mysql.|sys.))' 
--source-data

The first three lines control logging and output directory, the --regex option skips mysql and sys schemas, and --source-data stores replication metadata in the [source] section of the generated file.

Sample metadata output:

[source]
#CHANNEL_NAME = ''
#SOURCE_LOG_FILE = "binlog.000020"
#SOURCE_LOG_POS = 6803936
executed_gtid_set = "941fdce6-47c4-11f0-87b2-0242ac110006:1-52"
myloader_exec_reset_replica = 0
myloader_exec_change_source = 0
myloader_exec_start_replica = 0

Enabled options (e.g., executed_gtid_set, SOURCE_LOG_FILE, SOURCE_LOG_POS) are required for automatic replica configuration; disabled options can be enabled by setting --source-data to the appropriate bitmask.

Configure Replication

By default MyLoader uses SOURCE_LOG_FILE and SOURCE_LOG_POS. Setting SOURCE_AUTO_POSITION = 1 allows GTID‑based positioning. Typical workflow:

Run CHANGE SOURCE to point the replica at the new source.

Optionally run RESET REPLICA if needed.

Execute START REPLICA after the metadata is in place.

MyLoader can apply these steps automatically when the metadata file contains the appropriate flags, e.g.:

myloader_exec_reset_replica = 1
myloader_exec_change_source = 1
myloader_exec_start_replica = 1

Restore the Replica

After the metadata file is prepared, run MyLoader to restore:

myloader -d data -v 4 
-o --max-threads-for-schema-creation=1 
-h replica_host

The log shows the sequence of commands sent to the replica:

2025-12-18 16:57:09 [INFO] - Sending reset replica
2025-12-18 16:57:09 [INFO] - Sending change replication source
2025-12-18 16:57:09 [INFO] - Sending start replica
2025-12-18 16:57:09 [INFO] - Restore completed

If a command fails (e.g., typo in SOURCE_USER), MyLoader reports the error, and manual correction is required.

Rebuilding a Faulty Replica

When a replica stops due to data drift, you can use START REPLICA UNTIL to stop at a specific point and then rebuild only the problematic tables with MyDumper:

mysql-replica> STOP REPLICA;
mysql-replica> CHANGE REPLICATION FILTER REPLICATE_IGNORE_TABLE = (test.test_table);
mysql-replica> START REPLICA;

Backup the affected table:

mydumper -v 4 -o data --clear 
-T test.test_table 
--source-data

Restore it using the appropriate --source-data bitmask (e.g., 32 for START REPLICA UNTIL), then remove the ignore filter and restart the replica.

Conclusion

Switching from traditional single‑threaded dumps to MyDumper provides significant performance gains, better data integrity, and flexibility for large‑scale migrations. Integrating MyDumper and MyLoader into standard operating procedures equips DBAs to handle emergency replica rebuilds and planned schema migrations efficiently.

References

[1] MyDumper documentation: https://mydumper.github.io/mydumper/docs/html/index.html

[2] MyLoader documentation: https://mydumper.github.io/mydumper/docs/html/myloader_usage.html