关于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…。

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 筹备数据

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

-- 敞开 GTID
mysql> 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_only
mysql> 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()
begin
declare i int;
set i = 2;
while i < 6 do
insert 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: Yes
Slave_SQL_Running: No
Last_SQL_Errno: 1032
Last_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: 1
msg: innodb_001
1 row in set (0.01 sec)

-- 查看复制状态为 Yes，示意曾经跳过抵触的 event 事件
mysql> show slave status\G
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Last_Errno: 0
Last_Error:
Skip_Counter: 9955
Exec_Master_Log_Pos: 4493
Relay_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 事件作用：

(5) 模仿主库追加数据

-- 主库追加数据
begin;
insert into replica_innodb values(6,'innodb_006');
commit;
select * from replica_innodb where id=6\G
*************************** 1. row ***************************
 id: 6
msg: innodb_006

-- 从库察看到 IO 及 SQL 线程状态为 yes，复制失常且 Skip_Counter 值递加。show slave status\G
*************************** 1. row ***************************
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Last_Errno: 0
Last_Error:
Skip_Counter: 9949
Exec_Master_Log_Pos: 4849
Relay_Log_Space: 5210

-- 从库查看事务表数据，未发现 id= 6 的记录 
select * from replica_innodb\G
*************************** 1. row ***************************
 id: 1
msg: 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 相干参数配置。