当前位置：首页 > news >正文

MySQL 主从同步异常使用mysqldump修复

news 2026/5/12 5:46:37

MySQL 主从同步异常使用mysqldump修复

场景描述：
MySQL一主一从架构，使用GTID模式同步，无意中测试发现恢复过程中会丢失数据。
如下模拟整个恢复过程。

测试1 丢失数据

模拟从库宕机同步失败

mysqldump备份之前主库数据

主库备份：

[root@mongodb1 tmp]# mysqldump -uroot -p -S /usr/local/mysql/data/mysql.sock --source-data=2 --single-transaction --all-databases --triggers --routines --events > /tmp/alldb0303.sql Enter password:

拷贝备份到从库

[root@mongodb1 tmp]# scp alldb0303.sql root@192.168.56.101:/tmp root@192.168.56.101's password: alldb0303.sql 100% 2287KB 90.6MB/s 00:00 [root@mongodb1 tmp]#

从库恢复数据模拟主库插入数据

从库恢复数据

mysql> stop slave ; Query OK, 0 rows affected, 2 warnings (0.00 sec) mysql> reset slave all; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> source /tmp/alldb0303.sql 忽略 。。。。 mysql> change master to -> master_host='192.168.56.100', -> master_port=3306, -> master_user='root', -> master_password='Bai_yun123', -> master_auto_position=1; Query OK, 0 rows affected, 8 warnings (0.09 sec) mysql> start slave; Query OK, 0 rows affected, 1 warning (0.07 sec) mysql> show slave status\G *************************** 1. row *************************** Slave_IO_State: Waiting for source to send event Master_Host: 192.168.56.100 Master_User: root Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000004 Read_Master_Log_Pos: 1720 Relay_Log_File: mongodb2-relay-bin.000002 Relay_Log_Pos: 420 Relay_Master_Log_File: mysql-bin.000004 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 1720 Relay_Log_Space: 633 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 0 Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 100 Master_UUID: 27270078-0cd4-11f1-9b0c-0800274c0fb9 Master_Info_File: mysql.slave_master_info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Replica has read all relay log; waiting for more updates Master_Retry_Count: 86400 Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: <<<<<< Executed_Gtid_Set: 27270078-0cd4-11f1-9b0c-0800274c0fb9:1-14 Auto_Position: 1 Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: Master_public_key_path: Get_master_public_key: 0 Network_Namespace: 1 row in set, 1 warning (0.00 sec) mysql>

可以看到 Retrieved_Gtid_Set: 为空。

发现主库变更的数据1000没有同步到从库。

mysql> select * from demodb.tab22; +-----+ | tid | +-----+ | 100 | | 102 | | 103 | | 300 | | 332 | +-----+ 5 rows in set (0.00 sec) mysql>

主库再插入数据

mysql> insert into tab22 values(1002); Query OK, 1 row affected (0.04 sec) mysql> commit; Query OK, 0 rows affected (0.00 sec) mysql> select * from tab22; +------+ | tid | +------+ | 100 | | 102 | | 103 | | 300 | | 332 | | 1000 | | 1002 | +------+ 7 rows in set (0.00 sec) mysql>

从库查看数据，发现1000是丢失了没有同步过来。

测试2 无数据丢失

重新恢复一次数据，

mysql> stop slave; Query OK, 0 rows affected, 1 warning (0.06 sec) mysql> reset slave all; Query OK, 0 rows affected, 1 warning (0.09 sec) mysql> reset master; <<<<<<< Query OK, 0 rows affected (0.07 sec) mysql> source /tmp/alldb0303.sql

mysql> change master to -> master_host='192.168.56.100', -> master_port=3306, -> master_user='root', -> master_password='Bai_yun123', -> master_auto_position=1; Query OK, 0 rows affected, 8 warnings (0.14 sec) mysql> start slave; Query OK, 0 rows affected, 1 warning (0.06 sec) mysql> show slave status\G *************************** 1. row *************************** Slave_IO_State: Waiting for source to send event Master_Host: 192.168.56.100 Master_User: root Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000004 Read_Master_Log_Pos: 2000 Relay_Log_File: mongodb2-relay-bin.000002 Relay_Log_Pos: 980 Relay_Master_Log_File: mysql-bin.000004 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 2000 Relay_Log_Space: 1193 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 0 Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 100 Master_UUID: 27270078-0cd4-11f1-9b0c-0800274c0fb9 Master_Info_File: mysql.slave_master_info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Replica has read all relay log; waiting for more updates Master_Retry_Count: 86400 Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: 27270078-0cd4-11f1-9b0c-0800274c0fb9:14-15 Executed_Gtid_Set: 27270078-0cd4-11f1-9b0c-0800274c0fb9:1-15 Auto_Position: 1 Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: Master_public_key_path: Get_master_public_key: 0 Network_Namespace: 1 row in set, 1 warning (0.00 sec) mysql>

主库在恢复过程中新增的数据有同步过来