MySQL主从同步异常之从库误写数据
原创MySQL主从同步异常之从库误写数据
原创
Power
发布于 2025-03-01 12:54:03
发布于 2025-03-01 12:54:03
代码可运行
运行总次数：0
代码可运行
一、主从同步异常现象分析
（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切换到最后一行
MySQL主从同步异常之从库误写数据(图1)
【注】由于出现主从同步报错，所以从库SQL Thread回放终止，最后记录binlog的时间，一般就可以用来验证场景二的情况。
MySQL主从同步异常之从库误写数据(图1)
【注】经过对binlog文件的分析，可以看出，主从同步异常时间点，从库有单独插入数据（判断方法server id发生了变化）。到此即可确定是从库单独写数据导致了主从同步异常。
原创声明：本文系作者授权腾讯云开发者社区发表，未经许可，不得转载。
如有侵权，请联系 cloudcommunity@tencent.com 删除。
原创声明：本文系作者授权腾讯云开发者社区发表，未经许可，不得转载。
如有侵权，请联系 cloudcommunity@tencent.com 删除。
评论
登录后参与评论
推荐阅读
目录
腾讯云开发者
