MySQL 主从同步异常使用mysqldump修复场景描述MySQL一主一从架构使用GTID模式同步无意中测试发现恢复过程中会丢失数据。如下模拟整个恢复过程。测试1 丢失数据模拟从库宕机同步失败mysqldump备份之前主库数据主库备份[rootmongodb1 tmp]# mysqldump -uroot -p -S /usr/local/mysql/data/mysql.sock --source-data2 --single-transaction --all-databases --triggers --routines --events /tmp/alldb0303.sql Enter password:拷贝备份到从库[rootmongodb1 tmp]# scp alldb0303.sql root192.168.56.101:/tmp root192.168.56.101s password: alldb0303.sql 100% 2287KB 90.6MB/s 00:00 [rootmongodb1 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_host192.168.56.100, - master_port3306, - master_userroot, - master_passwordBai_yun123, - master_auto_position1; 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.sqlmysql change master to - master_host192.168.56.100, - master_port3306, - master_userroot, - master_passwordBai_yun123, - master_auto_position1; 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主库在恢复过程中新增的数据有同步过来总结第一次和第二次的区别是在从库恢复数据之前多了一个步骤reset master;主库再次插入数据从库同步正常