关于mysql:故障分析-Sqlslaveskipcounter-使用不规范对复制的影响

作者：唐龙哲
爱可生交付服务团队上海DBA，负责 MySQL 数据库故障解决及日常保护。善于故障诊断
本文起源：原创投稿
*爱可生开源社区出品，原创内容未经受权不得随便应用，转载请分割小编并注明起源。

一、背景形容

最近在客户环境遇到一个 sql_slave_skip_counter 应用不标准引起的主从故障问题。

咱们晓得 mysql 的主从复制是逻辑复制，所以 slave 在利用 relay log 的过程中，常常会遇到谬误，而参数 sql_slave_skip_counter 能够设置跳过多少个 event ，让从库 sql 持续回放。然而如果 sql_slave_skip_counter 使用不当，也会造成主从数据不统一。

以下为抓取的局部谬误日志截图：

能够看到为了跳过报错的 SQL ，现场有手动设置过 SQL_SLAVE_SKIP_COUNTER=100000 ，示意跳过10万的 events ，而 binlog event 事件的多少，与事务表/非事务表、及 binlog 日志模式无关。

常见谬误阐明：

write_rows ：Duplicate entry（1062谬误）主键抵触，次要体现为反复插入曾经存在的记录;

update_rows ：Can't find record（1032谬误），无奈发现须要更新的行记录。

sql_slave_skip_counter 参数阐明：

从官网解释晓得，sql_slave_skip_counter以event 为单位 skip ，直到 skip 完第N个 event 所在的 event group 才进行。对于事务表，一个 event group 对应一个事务; 对于非事务表，一个 event group 对应一条 SQL 语句。并且一个 event group 能够蕴含多个 event 事件。

具体解释，能够参考官网文档：

https://dev.mysql.com/doc/ref...。

二、测试 sql_slave_skip_counter 对复制的影响

应用显示开启事务形式(begin/commit)，模仿 delete/update 遇到 Can't find record (1032) 谬误，insert 中的 Duplicate entry(1062) 谬误能够应用相似的办法去剖析。此处应用 MySQL8.0.23 版本，基于 Row 日志模式 + Position 形式搭建异步复制架构。

2.1 筹备数据

前提条件(主从库利用如下配置)：

-- 敞开GTIDmysql> show global variables like '%gtid%';+----------------------------------------+----------+| Variable_name               | Value     |+----------------------------------------+----------+| binlog_gtid_simple_recovery    | OFF    || enforce_gtid_consistency        | OFF    || gtid_executed                |         || gtid_mode                    | OFF      || gtid_owned                    |          || gtid_purged                    |          || simplified_binlog_gtid_recovery    | OFF      |+----------------------------------------+----------+-- 禁用read_onlymysql> show global variables like 'read_only';+------------------+-------+| Variable_name | Value |+------------------+-------+| read_only    | OFF  |+------------------+-------+-- 设置row模式mysql> show global variables like 'binlog_format';+---------------+----------+| Variable_name | Value |+---------------+----------+| binlog_format | ROW |+---------------+----------+

在主库创立两张表，一张是事务表，另一张为非事务表，而后往各表中写入id=1的记录。

-- 主库创立测试表create table replica_innodb (id int, msg char(32)) engine=innodb;create table replica_myisam (id int, msg char(32)) engine=myisam;-- 往测试表中增加数据 insert into replica_innodb values(1,'innodb_001');insert into replica_myisam values(1,'myisam_001');

2.2 模仿 sql_slave_skip_counter = N 跳过复制故障的场景

阐明：该场景将模仿主库更新 replica_myisam 非事务表的记录，导致从库更新失败的操作。

(1)从库执行 delete 语句，删除非事务表id=1的记录

set sql_log_bin=0;delete from replica_myisam where id=1;set sql_log_bin=1;

(2)主库更新 replica_myisam 非事务表id=1的记录，并新增数据到两张表中

-- 编写insert存储过程delimiter $$create procedure p_insert()begindeclare i int;set i = 2;while i < 6 doinsert into replica_innodb values (i, concat('innodb_', repeat(0, 2), i));insert into replica_myisam values (i, concat('myisam_', repeat(0, 2), i));set i = i + 1;end while;end$$delimiter ;-- 更新数据begin;update replica_myisam set msg='my01' where id=1;call p_insert;commit;

（3）查看复制状态

Slave_IO_Running: YesSlave_SQL_Running: NoLast_SQL_Errno: 1032Last_SQL_Error: Could not execute Update_rows event on table testdb.replica_myisam; Can't find record in 'replica_myisam', Error_code: 1032; handler error HA_ERR_END_OF_FILE; the event's master log mysql-bin.000001, end_log_pos 1715

上述操作先在从库先删除非事务表数据id=1的状况下，主库再更新数据，而后通过复制将主库id=1的记录传递到从库，在 SQL 线程回放时造成从库无奈更新不存的记录 (报1032谬误)，导致 SQL 线程故障（此时 SQL 线程曾经进行）。

（4）尝试应用 sql_slave_skip_counter 跳过谬误，并重启复制

-- 从库跳过"10000"个event，并重启SQL线程set global sql_slave_skip_counter=10000;start slave sql_thread;-- 查看数据，能够发现replica_myisam以及replica_innodb表id>=2的记录并未同步到从库mysql> select * from replica_myisam;Empty set (0.00 sec)mysql> select * from replica_innodb\G*************************** 1. row *************************** id: 1msg: innodb_0011 row in set (0.01 sec)-- 查看复制状态为Yes，示意曾经跳过抵触的event事件mysql> show slave status\GSlave_IO_Running: YesSlave_SQL_Running: YesLast_Errno: 0Last_Error:Skip_Counter: 9955Exec_Master_Log_Pos: 4493Relay_Log_Space: 4854

依据察看到的 Skip_Counter 及查问结果显示，所有抵触的 sql 语句及失常的 sql 语句产生的 event 都被跳过了。被跳过的操作以及对应的事件类型，能够通过谬误日志中 SQL 线程复原时记录的 Relay_Log_File和Relay_Log_Pos 两个值，确认执行过了哪些操作。

show relaylog events in "mysql-relay.000002" from 1533 limit 50;

能够看到，基于 ROW 日志模式下，事务表及非事务表至多会产生对应的4个 event 事件：

rows_query (binlog_rows_query_log_events=ON 状况下才会记录到 binlog 日志中)、table_map 、update_rows/write_rows 以及 query(begin)/xid(commit)。实际上所有对事务表的操作是在同一个显式事务中，所有对非事务表的操作，每条 SQL 语句独自对应一个事务。

依照 binlog 记录的提交程序，能够看到非事务表会先提交，innodb 事务表在 commit 执行之后提交，所以1032报的是 replica_myisam 表相干的错误操作。

各个 event 事件作用：

Event	作用
Query	以文本的模式记录事务，包含DDL操作（ROW格局），begin等
Rows_query	记录DML操作（insert、delete、update）
Table_map	记录事件的库表信息
Write_rows	针对insert操作，蕴含了要插入的数据
Update_rows	针对update操作，蕴含了，批改前后的数据
Xid	在事务的开端调配一个Xid（事务id，惟一），标记事务的完结

(5) 模仿主库追加数据

-- 主库追加数据begin;insert into replica_innodb values(6,'innodb_006');commit;select * from replica_innodb where id=6\G*************************** 1. row *************************** id: 6msg: innodb_006-- 从库察看到IO及SQL线程状态为yes，复制失常且Skip_Counter值递加。show slave status\G*************************** 1. row ***************************Slave_IO_Running: YesSlave_SQL_Running: YesLast_Errno: 0Last_Error:Skip_Counter: 9949Exec_Master_Log_Pos: 4849Relay_Log_Space: 5210-- 从库查看事务表数据，未发现id=6的记录 select * from replica_innodb\G*************************** 1. row *************************** id: 1msg: innodb_001

阐明：上述操作并未将主库提交的事务利用到从库。

景象阐明：

(1)通过上述操作，sql_slave_skip_counter=N(N>0) 重启 slave 复制线程后，从库开始跳过 event ，每跳过一个 event ，N就减去1，直到N变成0。在此期间，所有抵触的 sql 语句及失常的 sql 语句产生的 event 都被记录到该变量中，当N变成0后从库才开始执行以后的语句。
并且当 Skip_Counter 值等于1，在对事务表操作时，并不是只跳过一个 event ，而是跳过一个残缺事务，该事务如果存在多条 sql ，那么当报错产生时，会将残余的 sql 操作一并跳过，直到遇到 xid event 为止。例如: 报错产生在第一个 sql 语句，如果第二条 sql 语句影响1000行数据，那么主从就有1000行数据不统一。
(2)察看从库复制失常，IO 及 SQL 线程状态为 yes 、Skip_Counter 值递加，且 binlog 无新的日志产生，谬误日志无报错信息。

2.3 sql_slave_skip_counter 清零

为了避免 sql_slave_skip_counter 对复制的影响，须要在谬误跳过之后及时的复原，将参数置为零。

形式一：重启复制

留神：MySQL5.7、8.0 重启复制后，Skip_Counter 计数器清零，MySQL 5.6 此办法有效。

stop slave;start slave;show global variables like 'sql_slave_skip_counter'\G*************************** 1. row ***************************Variable_name: sql_slave_skip_counter        Value: 0

形式二：手工设置为零，立刻失效

stop slave;set global sql_slave_skip_counter=0;start slave;show variables like 'sql_slave_skip_counter'\G*************************** 1. row ***************************Variable_name: sql_slave_skip_counter        Value: 0

形式三：重启从库

三、总结

标准应用 sql_slave_skip_counter ，如果操作不当，极有可能跳过其余 events ，并造成主从数据不统一。

如果数据库数据量较小，大概60G左右，倡议重做复制。数据量超过60G大小，能够在 sql_slave_skip_counter 跳过之前，看一下以后 binlog event group 执行了哪些操作。对于1032、1062谬误尽量修补数据，让复制过程在从库利用变更。

倡议 MySQL 复制开启 GTID（全局惟一事务ID），该形式能够依据以后事务的变动疾速判断主从数据一致性，也强化故障复原以及容错能力。须要留神：此形式在5.6不反对在线开启 GTID ，须要先停库再进行 GTID 相干参数配置。