MySQL主从同步异常之从库误写数据

一、主从同步异常现象分析

（1）查看主从同步报错信息

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.105.182
                  Master_User: ucloudbackup
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000124
          Read_Master_Log_Pos: 391411904
               Relay_Log_File: mysql-relay.000022
                Relay_Log_Pos: 363854657
        Relay_Master_Log_File: mysql-bin.000124
             Slave_IO_Running: Yes
            Slave_SQL_Running: No
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 1062
                   Last_Error: Could not execute Write_rows event on table a.b; Duplicate entry '184' for key 'PRIMARY', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's master log mysql-bin.000124, end_log_pos 363854899
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 363854487
              Relay_Log_Space: 391412361
              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: NULL
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 1062
               Last_SQL_Error: Could not execute Write_rows event on table a.b; Duplicate entry '184' for key 'PRIMARY', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's master log mysql-bin.000124, end_log_pos 363854899
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 3232262476
                  Master_UUID: 74553187-3722-11e8-850c-5254008f51bf
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: 
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 210721 15:41:45
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 74553187-3722-11e8-850c-5254008f51bf:37888074-47378724
            Executed_Gtid_Set: 648630e3-3722-11e8-850c-525400ab8db8:1-85681179,
74553187-3722-11e8-850c-5254008f51bf:1-47357209,
879ff10d-e650-11eb-8b5e-5254004294cb:1-3
                Auto_Position: 1
1 row in set (0.00 sec)

【主】报错信息输出“Duplicate entry '184' for key 'PRIMARY'”，由此可见，出现了主键冲突。那什么场景会导致这个问题呢？根据经验可以猜测：场景一：MySQL出现了事务回滚；场景二：从库没有设置read-only，业务误写数据到从库。

（2）查询主库和从库的server-id

# 主库Server_id

MySQL [(none)]> SELECT @@server_id;
+-------------+
| @@server_id |
+-------------+
|  3232262476 |
+-------------+
1 row in set (0.00 sec)

#从库server-id

mysql> SELECT @@server_id;
+-------------+
| @@server_id |
+-------------+
|  3232262449 |
+-------------+
1 row in set (0.00 sec)

【注】为验证场景二做准备。

二、猜测验证

（1）查看从库binlog列表或查询从库当前正在写的binlog文件

mysql> show binary logs;
+------------------+------------+
| Log_name         | File_size  |
+------------------+------------+
| mysql-bin.000001 |        198 |
| mysql-bin.000002 | 1073744226 |
| mysql-bin.000003 | 1073741917 |
| mysql-bin.000004 | 1073742256 |
| mysql-bin.000005 | 1073742372 |
| mysql-bin.000006 | 1073742169 |
| mysql-bin.000007 | 1073742129 |
| mysql-bin.000008 |  762871737 |
+------------------+------------+
8 rows in set (0.00 sec)

（2）解析从库最新的binlog文件

mysqlbinlog -v --base64-output=decode-row mysql-bin.000008 | less 
# 按G切换到最后一行

【注】由于出现主从同步报错，所以从库SQL Thread回放终止，最后记录binlog的时间，一般就可以用来验证场景二的情况。

【注】经过对binlog文件的分析，可以看出，主从同步异常时间点，从库有单独插入数据（判断方法server id发生了变化）。到此即可确定是从库单独写数据导致了主从同步异常。

作者：UStarGao
链接：https://www.starcto.com/mysql/191.html
来源：STARCTO
著作权归作者所有。商业转载请联系作者获得授权，非商业转载请注明出处。

UCloud新用户专属注册连接

UCloud CDN超值特惠专场

UCloud全球云主机（UHost/VPS）大促页面

UCloud快杰云主机大促页面