如何使用mydumper 重建MySQL从库(slave)

by

当副本因损坏或漂移而失效时， 标准解决方案是从主数据库的全新副本重建副本。传统上，为了快速重建副本，我们会使用 物理备份，但在某些情况下，逻辑备份仍然必不可少。逻辑备份 正是在这种情况下发挥作用，它提供了可移植性和简易性，但前提是能够快速执行。MyDumper[1] 应运而生，成为一款必不可少的现代化解决方案，它兼具两者的优势：逻辑转储的跨平台、跨版本灵活性，以及以往只有物理方法才能实现的并行、多线程速度，使其成为快速重建一致性副本的理想之选。

什么是 MyDumper？

MyDumper is Open Source and maintained by the community, it is not a Percona, MariaDB or MySQL product.

MyDumper is a MySQL Logical Backup Tool. It has 2 tools:

  • mydumper which is responsible to export a consistent backup of MySQL databases
  • myloader reads the backup from mydumper, connects to the destination database and imports the backup.

github地址

https://github.com/mydumper/mydumper

wget https://github.com/mydumper/mydumper/releases/download/v0.21.3-1/mydumper-0.21.3-1.el7.x86_64.rpm

相比 MySQL/MariaDB 自带的 mysqldump(mariadump) 工具，mydumper 和 myloader 更加高效，且支持多线程备份和多线程恢复，可以大大缩短备份和迁移的时间。

安装测试MyDumper

# yum install https://github.com/maxbube/mydumper/releases/download/v0.10.7-2/mydumper-0.10.7-2.el7.x86_64.rpm

# wget  https://github.com/maxbube/mydumper/releases/download/v0.10.7-2/mydumper-0.10.7-2.el7.x86_64.rpm


[root@anbob ~]# mydumper  -u root -p "anbob.com"  -h localhost -P 3306 -t 4 -v 3  anbob  --regex='^(?!(mysql|sys|test))' -o ./backup
** Message: 11:58:32.004: Server version reported as: 5.7.26
** Message: 11:58:32.004: Connected to a MySQL server
** Message: 11:58:32.006: Started dump at: 2026-02-28 11:58:32
** Message: 11:58:32.006: Thread 1 connected using MySQL connection ID 82
** Message: 11:58:32.007: Thread 2 connected using MySQL connection ID 83
** Message: 11:58:32.008: Thread 3 connected using MySQL connection ID 84
** Message: 11:58:32.008: Thread 4 connected using MySQL connection ID 85
** Message: 11:58:32.009: Thread 1 dumping db information for `anbob`
** Message: 11:58:32.009: Thread 3 dumping schema create for `anbob`
** Message: 11:58:32.009: Thread 2 dumping db information for `mysql`
** Message: 11:58:32.009: Thread 4 dumping db information for `sys`
** Message: 11:58:32.028: Thread 1 dumping data for `anbob`.`MyTEST`
** Message: 11:58:32.028: Non-InnoDB dump complete, unlocking tables
** Message: 11:58:32.028: Thread 3 dumping data for `anbob`.`Mytest2`
** Message: 11:58:32.028: Thread 2 dumping schema for `anbob`.`MyTEST`
** Message: 11:58:32.028: Thread 4 dumping schema for `anbob`.`Mytest2`
** Message: 11:58:32.028: Empty table anbob.MyTEST
** Message: 11:58:32.028: Thread 1 shutting down
** Message: 11:58:32.028: Empty table anbob.Mytest2
** Message: 11:58:32.028: Thread 2 shutting down
** Message: 11:58:32.028: Thread 3 shutting down
** Message: 11:58:32.028: Thread 4 shutting down
** Message: 11:58:32.029: Finished dump at: 2026-02-28 11:58:32

[root@anbob ~]# ls -l backup
total 24
-rw-r--r-- 1 root root   1 Feb 28 11:58 anbob.Mytest2.metadata
-rw-r--r-- 1 root root 196 Feb 28 11:58 anbob.Mytest2-schema.sql
-rw-r--r-- 1 root root   1 Feb 28 11:58 anbob.MyTEST.metadata
-rw-r--r-- 1 root root 195 Feb 28 11:58 anbob.MyTEST-schema.sql
-rw-r--r-- 1 root root  91 Feb 28 11:58 anbob-schema-create.sql
-rw-r--r-- 1 root root  75 Feb 28 11:58 metadata

[root@anbob ~]# sh conn_mysql
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 86
Server version: 5.7.26 MySQL Community Server (GPL)

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> create database db1;
Query OK, 1 row affected (0.00 sec)

mysql> exit
Bye


[root@anbob ~]# myloader  -u root -p "anbob.com"  -h localhost -P 3306 -t 4 -v 3  -B db1  -d ./backup
** Message: 12:00:07.225: 4 threads created
** Message: 12:00:07.225: Creating table `db1`.`MyTEST`
** Message: 12:00:07.231: Creating table `db1`.`Mytest2`
** Message: 12:00:07.236: Thread 1 shutting down
** Message: 12:00:07.236: Thread 4 shutting down
** Message: 12:00:07.236: Thread 3 shutting down
** Message: 12:00:07.236: Thread 2 shutting down
** Message: 12:00:07.237: Starting table checksum verification


[root@anbob ~]# sh conn_mysql
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 92
Server version: 5.7.26 MySQL Community Server (GPL)

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use db1
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+---------------+
| Tables_in_db1 |
+---------------+
| MyTEST        |
| Mytest2       |
+---------------+
2 rows in set (0.00 sec)

恢复备库

常规操作流程

步骤1：确认主库状态
SHOW MASTER STATUS;
SHOW VARIABLES LIKE ‘gtid_mode’;
SHOW VARIABLES LIKE ‘server_uuid’;
确认：
• gtid_mode = ON
• 主库运行正常

步骤2：主库全量备份
mysqldump -uroot -p \
–single-transaction \
–master-data=2 \
–set-gtid-purged=ON \
–routines –triggers –events \
–all-databases > full_backup.sql
说明：
• 适用于 InnoDB 引擎
• 大库建议使用物理备份工具（如 xtrabackup）

这里可以使用mydumper多线程导出

步骤3：从库环境检查
在从库执行：
SHOW SLAVE STATUS\G — MySQL 5.7
SHOW REPLICA STATUS\G — MySQL 8.0
记录当前错误信息用于存档。
检查 server_uuid 不得与主库相同：
SHOW VARIABLES LIKE ‘server_uuid’;

步骤4：重置从库状态
STOP SLAVE; — 8.0 为 STOP REPLICA;
RESET SLAVE ALL;
RESET MASTER;
验证 GTID 已清空：
SELECT @@GLOBAL.GTID_EXECUTED,
@@GLOBAL.GTID_PURGED;
应为空。

步骤5：关闭只读模式
SET GLOBAL read_only=OFF;

步骤6：导入主库备份
mysql -uroot -p < full_backup.sql
导入完成后确认无报错。

这里可以使用myloader导入

步骤7：重新建立主从关系
CHANGE MASTER TO
MASTER_HOST=’主库IP’,
MASTER_PORT=3306,
MASTER_USER=’repl_user’,
MASTER_PASSWORD=”,
MASTER_AUTO_POSITION=1;

步骤8：启动复制
START SLAVE; — 8.0 为 START REPLICA;

步骤9：状态验证
SHOW SLAVE STATUS\G
关键检查项：
• Slave_IO_Running: Yes
• Slave_SQL_Running: Yes
• Seconds_Behind_Master 正常
• 无 Last_SQL_Error

传统数据库备份工具如mysqldump通过–source-data参数支持在备份文件中嵌入主从复制配置信息., MyDumper新版本也能记录binlog位置和GTID信息

使用mydumper备份

第一步是进行备份。mydumper 有多个参数可供使用，本例中我们将使用以下参数：

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

最后 –source-data 将指示 mydumper 将恢复后复制配置所需的所有信息保存到元数据文件中，位于 [source] 部分。

以下是输出示例：

[source]
# Channel_Name = '' # It can be used to setup replication FOR CHANNEL
# SOURCE_LOG_FILE = "binlog.000020"
# SOURCE_LOG_POS = 6803936
#SOURCE_HOST = "172.17.0.3"
#SOURCE_PORT =
#SOURCE_USER = ""
#SOURCE_PASSWORD = ""
#SOURCE_SSL = {0|1}
executed_gtid_set="941fdce6-47c4-11f0-87b2-0242ac110006:1-52"
SOURCE_LOG_FILE="binlog.000020"
SOURCE_LOG_POS=6803936
#SOURCE_AUTO_POSITION = {0|1}
myloader_exec_reset_replica=0
myloader_exec_change_source=0
myloader_exec_start_replica=0

配置复制参数

默认情况下将使用 SOURCE_LOG_FILE 和 SOURCE_LOG_POS，但如果您配置 SOURCE_AUTO_POSITION = 1，则可以设置 GTID 位置。

如果您想从头开始重建副本，则需要按如下方式配置：

[source]
SOURCE_HOST="172.17.0.3"
SOURCE_PORT=3306
SOURCE_USER="replica"
SOURCE_PASSWORD="r3pl1c4"
executed_gtid_set="941fdce6-47c4-11f0-87b2-0242ac110006:1-52"
SOURCE_LOG_FILE="binlog.000020"
SOURCE_LOG_POS=6803936
myloader_exec_reset_replica=1
myloader_exec_change_source=1
myloader_exec_start_replica=1

如果您已经建立了一个正在运行的复制系统，并且想要在不更改主机或凭据的情况下重建它，那么您可以按以下方式进行配置：

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

使用myloader恢复

配置好元数据文件后，即可执行 myloader，其界面如下所示：

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

在日志中，你会发现 myloader 发送了以下命令：

2025-12-18 16:57:09 [INFO] - Schema create checksum confirmed for sakila
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

注：mydumper 会发送命令，但不会检查输出，这意味着如果复制配置失败或无法启动，您需要手动检查并修复。

另外mydumper可以重建某个表的同步。

CHANGE REPLICATION FILTER REPLICATE_IGNORE_TABLE= (xxx.tab1xx);

STOP REPLICA;

mydumper -v 4 -o data --clear 
-T xxx.tab1xx
--source-data

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

32 是执行 START REPLICA UNTIL.

最后，我们移除忽略表选项并重新启动副本：

mysql-replica> CHANGE REPLICATION FILTER REPLICATE_IGNORE_TABLE= ();
 mysql-replica> START REPLICA;

myloader 在备份开始时执行的 START REPLICA UNTIL 将强制slave副本在备份表的位置停止，从而使我们能够在一致的场景中继续复制。

小结

从传统的数据转储方法转向 MyDumper 不仅仅意味着性能的提升，更代表着数据完整性和迁移性的现代化。如果继续自动化我们可以生成一套shell， 自动修复slave, 如同MySQL通过mydumper备份自动重做从库shell脚本

— over —

References

https://www.percona.com/blog/rebuilding-a-replica-with-mydumper

Leave a Comment