1、主从复制从服务器报错

从服务器查询主从复制状态，查看Last_Error和Last_SQL_Error字段。从服务器在回放主服务器10285151位置操作时发生了错误，导致主从复制停止。

mysql> show slave status\G;
                   Last_Error: Coordinator stopped because there were error(s) in the worker(s). The most recent failure being: Worker 1 failed executing transaction 'ANONYMOUS' at source log mysql-bin.000001, end_log_pos 10285151. 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: 10284876
              Relay_Log_Space: 12342179
              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: Coordinator stopped because there were error(s) in the worker(s). The most recent failure being: Worker 1 failed executing transaction 'ANONYMOUS' at source log mysql-bin.000001, end_log_pos 10285151. See error log and/or performance_schema.replication_applier_status_by_worker table for more details about this failure or others, if any.

从服务器查询performance_schema表，该表会提供错误详细信息。查询到的信息是saleable_products.history表主键42263-1737270983-614678073重复。

mysql> SELECT * FROM performance_schema.replication_applier_status_by_worker\G;
                 LAST_ERROR_MESSAGE: Worker 1 failed executing transaction 'ANONYMOUS' at source log mysql-bin.000001, end_log_pos 10285151; Could not execute Write_rows event on table saleable_products.history; Duplicate entry '42263-1737270983-614678073' for key 'history.PRIMARY', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's source log mysql-bin.000001, end_log_pos 10285151

从服务器核实此条数据和主服务器数据一致，可以采取跳过此错误事务方法。

mysql> stop slave;
mysql> set global sql_slave_skip_counter = 1;
mysql> start slave;

1.1 终极方法

如果从服务器和主服务数据差异过大，停止主服务器数据写入，备份主服务器数据在从服务器导入

a. 查询主服务器最新binlog文件和位置

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000003 |  3285322 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+

b. 从服务器重新配置主从复制配置信息，从最新binlog文件和位置开始同步

mysql> stop slave;
mysql> CHANGE MASTER TO
  MASTER_LOG_FILE = 'mysql-bin.000003',
  MASTER_LOG_POS = 3285322;
mysql> start slave;

1.2 主服务器查询事务

主服务器也可以查询详细的事务信息，这里举个完整事务例子。

mysql> SHOW BINLOG EVENTS IN 'mysql-bin.000001';
| mysql-bin.000001 | 11665014 | Query          |         1 |    11665091 | BEGIN                                    |
| mysql-bin.000001 | 11665091 | Table_map      |         1 |    11665154 | table_id: 104 (zabbix.history_uint)      |
| mysql-bin.000001 | 11665154 | Write_rows     |         1 |    11665239 | table_id: 104 flags: STMT_END_F          |
| mysql-bin.000001 | 11665239 | Xid            |         1 |    11665270 | COMMIT /* xid=343758 */                  |

查询binlog，binlog是base64编码的文件，通过mysqlbinlog解码为可阅读文本，start-position和stop-position指定位置事务。

[root@128 ~]# mysqlbinlog --base64-output=DECODE-ROWS -v --start-position=11665014 --stop-position=11665240 /var/lib/mysql/mysql-bin.000001
# The proper term is pseudo_replica_mode, but we use this compatibility alias
# to make the statement usable on server versions 8.0.24 and older.
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 157
#250119 12:12:44 server id 1  end_log_pos 126 CRC32 0xe16af235 	Start: binlog v 4, server v 8.1.0 created 250119 12:12:44 at startup
ROLLBACK/*!*/;
# at 11665014   ------11665014事务
#250119 15:53:00 server id 1  end_log_pos 11665091 CRC32 0xc473820f 	Query	thread_id=12	exec_time=0	error_code=0
SET TIMESTAMP=1737273180/*!*/;
SET @@session.pseudo_thread_id=12/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1168113696/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8mb4 *//*!*/;
SET @@session.character_set_client=255,@@session.collation_connection=255,@@session.collation_server=255/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
/*!80011 SET @@session.default_collation_for_utf8mb4=255*//*!*/;
BEGIN   ------11665014事务操作-开始事务
/*!*/;
# at 11665091   ------11665091事务-指定库表
#250119 15:53:00 server id 1  end_log_pos 11665154 CRC32 0xf750b6ec 	Table_map: `zabbix`.`history_uint` mapped to number 104
# at 11665154   ------11665154事务
#250119 15:53:00 server id 1  end_log_pos 11665239 CRC32 0xe0a2bcbf 	Write_rows: table id 104 flags: STMT_END_F
### INSERT INTO `zabbix`.`history_uint`   ------11665154事务操作-INSERT操作
### SET
###   @1=47279
###   @2=1737273179
###   @3=6
###   @4=943094739
### INSERT INTO `zabbix`.`history_uint`   ------11665154事务操作-INSERT操作
### SET
###   @1=48239
###   @2=1737273179
###   @3=9154789376
###   @4=944581849
# at 11665239   -----11665239事务
#250119 15:53:00 server id 1  end_log_pos 11665270 CRC32 0x4f297bcc 	Xid = 343758
COMMIT/*!*/;   ------11665239事务操作-结束事务
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;