主从数据不一致导出同步错误(从库记录存在导致写入报主键重复)
mysql> show slave status \G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.1.22
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: binlog.000083
Read_Master_Log_Pos: 130645397
Relay_Log_File: relay-bin.000003
Relay_Log_Pos: 125560703
Relay_Master_Log_File: binlog.000083
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 db_message.app_uid_dev_token; Duplicate entry '508452' for key 'PRIMARY', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's master log binlog.000083, end_log_pos 125560887
Skip_Counter: 0
Exec_Master_Log_Pos: 125560539
Relay_Log_Space: 130645963
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 db_message.app_uid_dev_token; Duplicate entry '508452' for key 'PRIMARY', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's master log binlog.000083, end_log_pos 125560887
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
Master_UUID: c95fcd3a-98ae-11e5-b124-5820b106a87c
Master_Info_File: /home/mysql/data/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: 200707 18:30:51
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set: c95fcd3a-98ae-11e5-b124-5820b106a87c:26487093-26666024
Executed_Gtid_Set: c95fcd3a-98ae-11e5-b124-5820b106a87c:1-26487091:26487093-26655522
Auto_Position: 0
1 row in set (0.00 sec)
解析日志(主库操作):
[root@dev-env-22 logs]# mysqlbinlog --base64-output=decode-rows -v --start-position=125560539 /home/mysql/binlog/binlog.000083>/tmp/bb.txt
[root@dev-env-22 logs]# more /tmp/bb.txt
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 125560539
#200707 18:30:51 server id 1 end_log_pos 125560587 CRC32 0x243015c5 GTID [commit=yes]
SET @@SESSION.GTID_NEXT= 'c95fcd3a-98ae-11e5-b124-5820b106a87c:26655523'/*!*/;
# at 125560587
#200707 18:30:51 server id 1 end_log_pos 125560665 CRC32 0xac84d714 Query thread_id=409830 exec_time=0 error_code=0
SET TIMESTAMP=1594117851/*!*/;
SET @@session.pseudo_thread_id=409830/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1075838976/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8mb4 *//*!*/;
SET @@session.character_set_client=45,@@session.collation_connection=45,@@session.collation_server=45/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
BEGIN
/*!*/;
# at 125560665
#200707 18:30:51 server id 1 end_log_pos 125560743 CRC32 0xe7758f1b Table_map: `db_message`.`app_uid_dev_token` mapped to number 13563
# at 125560743
#200707 18:30:51 server id 1 end_log_pos 125560887 CRC32 0x53621834 Write_rows: table id 13563 flags: STMT_END_F
### INSERT INTO `db_message`.`app_uid_dev_token`
### SET
### @1=508452
### @2=2
### @3='40532cbd7f185a99ebedcbdd94a7d8b1d1d4ab31730255cafae198cb244abb7e'
### @4='1a1018970a2b899e6c7'
### @5='6.0.0'
### @6=1594117851
# at 125560887
#200707 18:30:51 server id 1 end_log_pos 125560966 CRC32 0x50183d32 Query thread_id=409830 exec_time=0 error_code=0
SET TIMESTAMP=1594117851/*!*/;
原因是app_uid_dev_token存在主键为508452的记录(之前数据不一致导致),导致写入失败
解决办法:
在从库将重复的记录删除掉
select * from db_message.app_uid_dev_token where user_id=508452
【推荐】国内首个AI IDE,深度理解中文开发场景,立即下载体验Trae
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· AI与.NET技术实操系列(二):开始使用ML.NET
· 记一次.NET内存居高不下排查解决与启示
· 探究高空视频全景AR技术的实现原理
· 理解Rust引用及其生命周期标识(上)
· 浏览器原生「磁吸」效果!Anchor Positioning 锚点定位神器解析
· DeepSeek 开源周回顾「GitHub 热点速览」
· 物流快递公司核心技术能力-地址解析分单基础技术分享
· .NET 10首个预览版发布:重大改进与新特性概览!
· AI与.NET技术实操系列(二):开始使用ML.NET
· 单线程的Redis速度为什么快?