暂无图片
mysql 主从同步异常
我来答
返回问答列表
分享
暂无图片 匿名用户
mysql 主从同步异常
暂无图片 50M
mysql

架构：一主一从的mysql 8.0.33 

现象： 有一个表，同步报错：

 mysql> select * from performance_schema.replication_applier_status_by_worker \G;
*************************** 1. row ***************************
CHANNEL_NAME:
WORKER_ID: 1
THREAD_ID: NULL
SERVICE_STATE: OFF
LAST_ERROR_NUMBER: 1032
LAST_ERROR_MESSAGE: Worker 1 failed executing transaction '7ba22a59-4aca-11ee-b668-00505691bdbc:1928663' at source log mysql-bin.000420, end_log_pos 339465170; Could not execute Delete_rows event on table DT_BOM; Can't find record in 'DT_BOM', Error_code: 1032; Can't find record in 'DT_BOM', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's source log mysql-bin.000420, end_log_pos 339465170
LAST_ERROR_TIMESTAMP: 2024-03-10 21:03:44.638081
LAST_APPLIED_TRANSACTION: 7ba22a59-4aca-11ee-b668-00505691bdbc:1928662
LAST_APPLIED_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP: 2024-03-10 21:01:35.749365
LAST_APPLIED_TRANSACTION_IMMEDIATE_COMMIT_TIMESTAMP: 2024-03-10 21:01:35.749365
LAST_APPLIED_TRANSACTION_START_APPLY_TIMESTAMP: 2024-03-10 21:03:44.473680
LAST_APPLIED_TRANSACTION_END_APPLY_TIMESTAMP: 2024-03-10 21:03:44.576723
APPLYING_TRANSACTION: 7ba22a59-4aca-11ee-b668-00505691bdbc:1928663
APPLYING_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP: 2024-03-10 21:01:40.284088
APPLYING_TRANSACTION_IMMEDIATE_COMMIT_TIMESTAMP: 2024-03-10 21:01:40.284088
APPLYING_TRANSACTION_START_APPLY_TIMESTAMP: 2024-03-10 21:03:44.576777
LAST_APPLIED_TRANSACTION_RETRIES_COUNT: 0
LAST_APPLIED_TRANSACTION_LAST_TRANSIENT_ERROR_NUMBER: 0
LAST_APPLIED_TRANSACTION_LAST_TRANSIENT_ERROR_MESSAGE:
LAST_APPLIED_TRANSACTION_LAST_TRANSIENT_ERROR_TIMESTAMP: 0000-00-00 00:00:00.000000
APPLYING_TRANSACTION_RETRIES_COUNT: 0
APPLYING_TRANSACTION_LAST_TRANSIENT_ERROR_NUMBER: 0
APPLYING_TRANSACTION_LAST_TRANSIENT_ERROR_MESSAGE:
APPLYING_TRANSACTION_LAST_TRANSIENT_ERROR_TIMESTAMP: 0000-00-00 00:00:00.000000
*************************** 2. row ***************************
CHANNEL_NAME:
WORKER_ID: 2
THREAD_ID: NULL
SERVICE_STATE: OFF
LAST_ERROR_NUMBER: 0
LAST_ERROR_MESSAGE:
LAST_ERROR_TIMESTAMP: 0000-00-00 00:00:00.000000
LAST_APPLIED_TRANSACTION: 7ba22a59-4aca-11ee-b668-00505691bdbc:1928336
LAST_APPLIED_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP: 2024-03-10 19:00:01.400985
LAST_APPLIED_TRANSACTION_IMMEDIATE_COMMIT_TIMESTAMP: 2024-03-10 19:00:01.400985
LAST_APPLIED_TRANSACTION_START_APPLY_TIMESTAMP: 2024-03-10 19:00:01.401455
LAST_APPLIED_TRANSACTION_END_APPLY_TIMESTAMP: 2024-03-10 19:00:01.402354
APPLYING_TRANSACTION:
APPLYING_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP: 0000-00-00 00:00:00.000000
APPLYING_TRANSACTION_IMMEDIATE_COMMIT_TIMESTAMP: 0000-00-00 00:00:00.000000
APPLYING_TRANSACTION_START_APPLY_TIMESTAMP: 0000-00-00 00:00:00.000000
LAST_APPLIED_TRANSACTION_RETRIES_COUNT: 0
LAST_APPLIED_TRANSACTION_LAST_TRANSIENT_ERROR_NUMBER: 0
LAST_APPLIED_TRANSACTION_LAST_TRANSIENT_ERROR_MESSAGE:
LAST_APPLIED_TRANSACTION_LAST_TRANSIENT_ERROR_TIMESTAMP: 0000-00-00 00:00:00.000000
APPLYING_TRANSACTION_RETRIES_COUNT: 0
APPLYING_TRANSACTION_LAST_TRANSIENT_ERROR_NUMBER: 0
APPLYING_TRANSACTION_LAST_TRANSIENT_ERROR_MESSAGE:
APPLYING_TRANSACTION_LAST_TRANSIENT_ERROR_TIMESTAMP: 0000-00-00 00:00:00.000000
*************************** 3. row ***************************
CHANNEL_NAME:
WORKER_ID: 3
THREAD_ID: NULL
SERVICE_STATE: OFF
LAST_ERROR_NUMBER: 0
LAST_ERROR_MESSAGE:
LAST_ERROR_TIMESTAMP: 0000-00-00 00:00:00.000000
LAST_APPLIED_TRANSACTION: 7ba22a59-4aca-11ee-b668-00505691bdbc:1926078
LAST_APPLIED_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP: 2024-03-10 08:00:00.928970
LAST_APPLIED_TRANSACTION_IMMEDIATE_COMMIT_TIMESTAMP: 2024-03-10 08:00:00.928970
LAST_APPLIED_TRANSACTION_START_APPLY_TIMESTAMP: 2024-03-10 11:21:22.425080
LAST_APPLIED_TRANSACTION_END_APPLY_TIMESTAMP: 2024-03-10 11:21:22.429151
APPLYING_TRANSACTION:
APPLYING_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP: 0000-00-00 00:00:00.000000
APPLYING_TRANSACTION_IMMEDIATE_COMMIT_TIMESTAMP: 0000-00-00 00:00:00.000000
APPLYING_TRANSACTION_START_APPLY_TIMESTAMP: 0000-00-00 00:00:00.000000
LAST_APPLIED_TRANSACTION_RETRIES_COUNT: 0
LAST_APPLIED_TRANSACTION_LAST_TRANSIENT_ERROR_NUMBER: 0
LAST_APPLIED_TRANSACTION_LAST_TRANSIENT_ERROR_MESSAGE:
LAST_APPLIED_TRANSACTION_LAST_TRANSIENT_ERROR_TIMESTAMP: 0000-00-00 00:00:00.000000
APPLYING_TRANSACTION_RETRIES_COUNT: 0
APPLYING_TRANSACTION_LAST_TRANSIENT_ERROR_NUMBER: 0
APPLYING_TRANSACTION_LAST_TRANSIENT_ERROR_MESSAGE:
APPLYING_TRANSACTION_LAST_TRANSIENT_ERROR_TIMESTAMP: 0000-00-00 00:00:00.000000
*************************** 4. row ***************************
CHANNEL_NAME:
WORKER_ID: 4
THREAD_ID: NULL
SERVICE_STATE: OFF
LAST_ERROR_NUMBER: 0
LAST_ERROR_MESSAGE:
LAST_ERROR_TIMESTAMP: 0000-00-00 00:00:00.000000
LAST_APPLIED_TRANSACTION:
LAST_APPLIED_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP: 0000-00-00 00:00:00.000000
LAST_APPLIED_TRANSACTION_IMMEDIATE_COMMIT_TIMESTAMP: 0000-00-00 00:00:00.000000
LAST_APPLIED_TRANSACTION_START_APPLY_TIMESTAMP: 0000-00-00 00:00:00.000000
LAST_APPLIED_TRANSACTION_END_APPLY_TIMESTAMP: 0000-00-00 00:00:00.000000
APPLYING_TRANSACTION:
APPLYING_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP: 0000-00-00 00:00:00.000000
APPLYING_TRANSACTION_IMMEDIATE_COMMIT_TIMESTAMP: 0000-00-00 00:00:00.000000
APPLYING_TRANSACTION_START_APPLY_TIMESTAMP: 0000-00-00 00:00:00.000000
LAST_APPLIED_TRANSACTION_RETRIES_COUNT: 0
LAST_APPLIED_TRANSACTION_LAST_TRANSIENT_ERROR_NUMBER: 0
LAST_APPLIED_TRANSACTION_LAST_TRANSIENT_ERROR_MESSAGE:
LAST_APPLIED_TRANSACTION_LAST_TRANSIENT_ERROR_TIMESTAMP: 0000-00-00 00:00:00.000000
APPLYING_TRANSACTION_RETRIES_COUNT: 0
APPLYING_TRANSACTION_LAST_TRANSIENT_ERROR_NUMBER: 0
APPLYING_TRANSACTION_LAST_TRANSIENT_ERROR_MESSAGE:
APPLYING_TRANSACTION_LAST_TRANSIENT_ERROR_TIMESTAMP: 0000-00-00 00:00:00.000000
4 rows in set (0.01 sec)

ERROR:
No query specified



表结构如下：

+------------------+----------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------------+----------------+------+-----+---------+-------+
| ID | bigint | NO | MUL | NULL | |
| PROJECT_NAME | varchar(100) | YES | MUL | NULL | |
| PURCHASE_LEVEL | varchar(10) | YES | | NULL | |
| KEY_PART | varchar(200) | YES | | NULL | |
| BYD_PN | varchar(200) | YES | MUL | NULL | |
| CHINESE_NAME | varchar(200) | YES | | NULL | |
| ENGLISH_NAME | varchar(200) | YES | | NULL | |
| DESCRIPTION | varchar(1000) | YES | | NULL | |
| VALUE | varchar(200) | YES | | NULL | |
| QTY | decimal(16,6) | YES | | NULL | |
| UNIT | varchar(20) | YES | | NULL | |
| DESIGNATOR | varchar(10000) | YES | | NULL | |
| PART_REV | varchar(200) | YES | | NULL | |
| DWG_NO | varchar(200) | YES | | NULL | |
| DWG_REV | varchar(200) | YES | | NULL | |
| MATERIAL_REV | varchar(200) | YES | | NULL | |
| CUSTOMER_PN | varchar(200) | YES | | NULL | |
| CUSTOMER_REV | varchar(20) | YES | | NULL | |
| SUPPLIER_PN | varchar(200) | YES | | NULL | |
| SUPPLIER | varchar(200) | YES | | NULL | |
| RO_HS_MATERIAL | varchar(20) | YES | | NULL | |
| CREATE_NAME | varchar(200) | YES | | NULL | |
| CREATE_TIME | datetime | YES | | NULL | |
| UPDATE_NAME | varchar(200) | YES | | NULL | |
| UPDATE_TIME | datetime | YES | | NULL | |
| FACTORY | varchar(100) | YES | MUL | NULL | |
| RE_MAT_GROUP | varchar(100) | YES | MUL | NULL | |
| USE_CAN | varchar(100) | YES | | NULL | |
| RE_MAT_PRIORITY | varchar(100) | YES | | NULL | |
| LEVEL | int | YES | | NULL | |
| PATH | varchar(1000) | YES | | NULL | |
| MAT_TYPE | varchar(100) | YES | | NULL | |
| SOURCE | varchar(100) | YES | | NULL | |
| GROUP_NAME | varchar(100) | YES | | NULL | |
| FINISHED_MAT_DES | varchar(100) | YES | | NULL | |
| MPN | varchar(100) | YES | | NULL | |
| COLOR | varchar(100) | YES | | NULL | |
| COUNTRY | varchar(100) | YES | | NULL | |
| MEMORY | varchar(100) | YES | | NULL | |
| VERSION | varchar(100) | YES | | NULL | |
| PACK_METHOD | varchar(100) | YES | | NULL | |
| ITEM | varchar(100) | YES | | NULL | |
| OPTIONAL_BOM | int | YES | | NULL | |
| pre_material | varchar(100) | YES | | NULL | |
+------------------+----------------+------+-----+---------+-------+

mysql> show slave status \G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for source to send event
Master_Host: 10.xx.xx.xx
Master_User: repuser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000421
Read_Master_Log_Pos: 500942750
Relay_Log_File: relay-bin.000131
Relay_Log_Pos: 17668697
Relay_Master_Log_File: mysql-bin.000420
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: 1032
Last_Error: Coordinator stopped because there were error(s) in the worker(s). The most recent failure being: Worker 1 failed executing transaction '7ba22a59-4aca-11ee-b668-00505691bdbc:1928663' at source log mysql-bin.000420, end_log_pos 339465170. See error log and/or performance_schema.replication_applier_status_by_worker table for more details about this failure or others, if any.
Skip_Counter: 0
Exec_Master_Log_Pos: 339400221
Relay_Log_Space: 1359839956
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: 1032
Last_SQL_Error: Coordinator stopped because there were error(s) in the worker(s). The most recent failure being: Worker 1 failed executing transaction '7ba22a59-4aca-11ee-b668-00505691bdbc:1928663' at source log mysql-bin.000420, end_log_pos 339465170. See error log and/or performance_schema.replication_applier_status_by_worker table for more details about this failure or others, if any.
Replicate_Ignore_Server_Ids:
Master_Server_Id: 127
Master_UUID: 7ba22a59-4aca-11ee-b668-00505691bdbc
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: 240310 21:03:44
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set: 7ba22a59-4aca-11ee-b668-00505691bdbc:1907898-1932290
Executed_Gtid_Set: 7ba22a59-4aca-11ee-b668-00505691bdbc:1-1928662
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)

ERROR:
No query specified

我来答
添加附件
收藏
分享
问题补充
5条回答
默认
最新
test

mysql-bin.000420, end_log_pos 339465170;

'7ba22a59-4aca-11ee-b668-00505691bdbc:1928663' 

Could not execute Delete_rows event on table DT_BOM; Can't find record in 'DT_BOM', Error_code: 1032; 

Can't find record in 'DT_BOM'

可以通过分析mysql-bin.000420事务，查看这个事务要删除DT_BOM表哪条记录，从主库找到该条数据，

（建议该报错处理后用pt-table-checksum工具，校验主从该表的数据）

方案1：补数据

备库开启session级别nolog，手动补上，然后启动主从

方案二：跳过错误（确保备库已无该条数据）

stop slave;

set gtid_next='备库当前gtid';

begin;

commit;

set gtid_next=automatic;

start slave;

暂无图片 评论
暂无图片 有用 0
打赏 0
www
2024-03-11
我一般过几天就要遇到一次这个报错， 每次遇到之后， 我就直接使用clone的方式把备库全表重新同步，然后就能用几天，再过几天还是会出现这个问题。 我都clone了， 理论没有数据异常才对。 至于跳过错误倒是不用， 因为这个备库只是做备份用的。重新同步也费不了多久，想知道具体是什么原因导致的。 我看了binlog日志， 是一个批量的删除动作。删除了265条记录。
test
答主
2024-03-11
你需要分析下这几天从库的日志，看下这些数据是什么时候产生的，在这个删除动作之前，有没有相关的删除操作（可能是人为误操作） 另外，数据恢复后，这个备库设置只读，观察下后续会不会发生相同的问题
test
答主
2024-03-11
clone后，用mysql的工具也检查下吧，最好在业务低峰期做个全库的数据检查
lianR

这个错误信息是MySQL复制过程中的一个常见错误，表示协调器因为工作进程中的错误而停止。具体的错误在工作进程1执行事务'7ba22a59-4aca-11ee-b668-00505691bdbc:1928663'时发生，源日志位置是mysql-bin.000420，结束日志位置是339465170。

要解决这个问题，你需要查看错误日志和/或performance_schema.replication_applier_status_by_worker表以获取更多关于这个失败或其他可能的失败的详细信息。

以下是一些可能的解决步骤：

1.  查看错误日志：错误日志通常会提供更多关于错误的详细信息。你可以通过查看MySQL的错误日志来获取更多信息。在Linux系统中，你可以使用如下命令来查看错误日志：
```
tail  -f  /var/log/mysql/error.log
```
2.  检查performance_schema.replication_applier_status_by_worker表：这个表提供了关于复制应用程序的状态信息。你可以通过运行以下SQL命令来查看这个表的内容：
```
SELECT  *  FROM  performance_schema.replication_applier_status_by_worker;
```
3.  根据错误日志和replication_applier_status_by_worker表的信息，你可能需要修复数据库，调整复制配置，或者执行其他操作来解决问题。

4.  如果错误是由于某个特定的事务导致的，你可能需要跳过这个事务。你可以使用以下命令来跳过一个事务：
```
SET  GLOBAL  sql_slave_skip_counter  =  1;  START  SLAVE;
```
请注意，这个命令只应在你确定跳过事务不会导致数据不一致的情况下使用。

5.  一旦问题解决，你需要重新启动复制进程。你可以使用以下命令来重新启动复制进程：
```
START  SLAVE;
```

暂无图片 评论
暂无图片 有用 0
打赏 0
张sir

1、备库设置下readonly看看是不是有人在备库删了数据

2、表要设置主键，对于mysql8.0，如果表没有主键，在备库上可能会出现重放的时候找不到数据的情况。这还是备库重放算法决定的。

暂无图片 评论
暂无图片 有用 0
打赏 0
Evan

遇到过类似问题，原因是人为在备库删除了数据

暂无图片 评论
暂无图片 有用 0
打赏 0
荒

Could not execute Delete_rows event on table DT_BOM; Can't find record in 'DT_BOM', Error_code: 1032; Can't find record in 'DT_BOM'

从报错看，就是主库的删除语句找不到对应的删除数据。

mysqlbinlog --base64-output=decode-rows -v --start-position=339400221 --stop-position=339465170 mysql-bin.000420 > fix.sql

 先用这个找出缺失的数据，然后解析从库的binlog，用grep查找出这条数据是如何被删除的。根据主从删除的时间点，如果有较大差距，就可以确认是人为删除的。

暂无图片 评论
暂无图片 有用 1
打赏 0
回答交流
Markdown


请输入正文
提交
问题信息
请登录之后查看
邀请回答
暂无人订阅该标签，敬请期待~~
暂无图片墨值悬赏