编程宝库 - 技术改变世界

编程宝库

Search

Mysql知识

详解Mysql函数调用优化

MySQL pt-slave-restart工具的使用教程

MySQL复制问题的三个参数分析

今天星期二，早上居然起晚了，上班迟到了，简直是。。。废话不多说，在昨天的文章中，我们提到了三个参数，分别是：

slave_exec_mode参数；
sql_slave_skip_counter=N参数;
slave-skip-errors=N参数。

这三个参数都可以解决并行复制中的一些指定的错误，例如duplicate key 1062错误等，今天我们简单试验一下，这三个参数的区别：

01 sql_slave_skip_counter参数

这个参数的设置主要是为了跳过某些错误的"event",注意这里的用词是event而不是事务，是因为它的本质是跳过一个一个事件，需要注意的是，这个参数需要在偏移量复制模式中使用，如果使用的是gtid的复制模式，则不可以使用这个参数。我们来看例子，首先搭建一套复制关系：

master 10.30.124.68

slave 10.30.124.128

这俩实例互为主从。我们创建测试表test.yeyz，并插入一些数据，其中id为主键，具有唯一性，如下：

master上

mysql:(none)22:25:56>>select*fromtest.yeyz;
+----+------+
|id|age|
+----+------+
|1|1|
|2|2|
|3|3|
|4|4|
+----+------+
4rowsinset(0.00sec)

slave上

mysql:(none)22:25:38>>select*fromtest.yeyz;
+----+------+
|id|age|
+----+------+
|1|1|
|2|2|
|3|3|
|4|4|
|5|5|
+----+------+
5rowsinset(0.00sec)

我们可以发现，从节点的数据比主节点多一条，多了id=5的记录，然后我们在主节点上插入数据：

mysql:(none)22:26:06>>insertintotest.yeyzvalues(5,5),(6,6);
QueryOK,2rowsaffected(0.00sec)
Records:2Duplicates:0Warnings:0

此时观察从节点：

mysql:(none)22:26:34>>showslavestatus\G
Master_Host:10.30.124.68
Master_User:dba_repl
Master_Port:4306
Connect_Retry:60
Master_Log_File:mysqlbin.000002
Read_Master_Log_Pos:523
Relay_Log_File:slave-relay-bin.000002
Relay_Log_Pos:319
Relay_Master_Log_File:mysqlbin.000002
Slave_IO_Running:Yes
Slave_SQL_Running:No
Last_Errno:1062
Last_Error:Coordinatorstoppedbecausetherewereerror(s)
intheworker(s).Themostrecentfailurebeing:
Worker0failedexecutingtransaction'ANONYMOUS'at
masterlogmysqlbin.000002,end_log_pos492.
Seeerrorlogand/orperformance_schema.replication_applier_status_by_worker
tableformoredetailsaboutthisfailureorothers,ifany.
Skip_Counter:0

可以发现，从节点已经SQL线程断开了，这个时候，在主节点上查询这个错误position 492处的binlog，可以看到：

mysql:(none)22:30:28>>showbinlogeventsin'mysqlbin.000002'from194;
+-----------------+-----+----------------+-----------+-------------+--------------------------------------------+
|Log_name|Pos|Event_type|Server_id|End_log_pos|Info|
+-----------------+-----+----------------+-----------+-------------+--------------------------------------------+
|mysqlbin.000002|194|Anonymous_Gtid|192|259|SET@@SESSION.GTID_NEXT='ANONYMOUS'|
|mysqlbin.000002|259|Query|192|327|BEGIN|
|mysqlbin.000002|327|Rows_query|192|391|#insertintotest.yeyzvalues(5,5),(6,6)|
|mysqlbin.000002|391|Table_map|192|439|table_id:108(test.yeyz)|
|mysqlbin.000002|439|Write_rows|192|492|table_id:108flags:STMT_END_F|
|mysqlbin.000002|492|Xid|192|523|COMMIT/*xid=38*/|
+-----------------+-----+----------------+-----------+-------------+--------------------------------------------+
6rowsinset(0.00sec)

从上面的binlog可以看出来，我们的一个insert操作实际上生成了5个enent，分别对应的pos是从259~492，关于event，待会儿再说。

因为主节点上插入了id=5的记录，跟从节点上的记录冲突了，查看错误日志，可以发现：

Duplicateentry'5'forkey'PRIMARY',
Error_code:1062;handlererrorHA_ERR_FOUND_DUPP_KEY;
theevent'smasterlogFIRST,
end_log_pos492|2019-07-1622:26:25

我们通过sql_slave_skip_counter参数的设置来解决这个问题，步骤如下：

mysql:(none)22:29:32>>stopslave;
QueryOK,0rowsaffected,1warning(0.00sec)

mysql:(none)22:32:45>>setglobalsql_slave_skip_counter=1;
QueryOK,0rowsaffected(0.00sec)

mysql:(none)22:33:06>>startslave;

在昨天的文章中我们说过，sql_slave_skip_counter后面跟的值是event的个数，所以这里我们相当于跳过了一个event，mysql中规定，如果跳过一个event之后，还在某一个事务里面，那么会继续跳过这个事务。

使用这个参数跳过一个event之后，我们再来看从库表中的数据和复制情况，可以看到：

slave表：

mysql:(none)22:33:10>>showslavestatus\G
***************************1.row***************************
Slave_IO_State:Waitingformastertosendevent
Master_Host:10.30.124.68
Master_User:dba_repl
Master_Port:4306
Connect_Retry:60
Master_Log_File:mysqlbin.000002
Read_Master_Log_Pos:523
Relay_Log_File:slave-relay-bin.000003
Relay_Log_Pos:319
Relay_Master_Log_File:mysqlbin.000002
Slave_IO_Running:Yes
Slave_SQL_Running:Yes


mysql:(none)22:33:16>>select*fromtest.yeyz;
+----+------+
|id|age|
+----+------+
|1|1|
|2|2|
|3|3|
|4|4|
|5|5|
+----+------+
5rowsinset(0.00sec)

看看master表：

mysql:(none)22:33:36>>select*fromtest.yeyz;
+----+------+
|id|age|
+----+------+
|1|1|
|2|2|
|3|3|
|4|4|
|5|5|
|6|6|
+----+------+
6rowsinset(0.00sec)

可以发现，master中数据插入成功，而slave中数据插入失败，也就是说：

该参数跳过错误的时候，会导致主从的数据不一致。

02 slave_skip_errors参数

这个参数是跳过制定的错误，也就是说，需要我们设置对应的error_code，从下面的日志中的内容可以看出，error_code的值为1062

Duplicateentry'5'forkey'PRIMARY',
Error_code:1062;handlererrorHA_ERR_FOUND_DUPP_KEY;
theevent'smasterlogFIRST,
end_log_pos492|2019-07-1622:26:25

我们需要手动将这个参数的值也该为1062，需要注意的是，这个参数的改动需要重启mysql服务，因为这个参数是一个只读的参数。

修改后的情况如下：

mysql--dba_admin@127.0.0.1:(none)22:38:55>>showvariableslike'%errors%';
+--------------------+---------+
|Variable_name|Value|
+--------------------+---------+
|max_connect_errors|1000000|
|slave_skip_errors|1062|
+--------------------+---------+
2rowsinset(0.01sec)

此时我们更新master表和slave表的数据，更新后的情况如下：

master：

mysql:(none)22:39:15>>select*fromtest.yeyz;
+----+------+
|id|age|
+----+------+
|1|1||2|2|
|3|3||4|4|
|5|5||6|6|
+----+------+
6rowsinset(0.00sec)

slave上：

mysql:(none)22:40:15>>select*fromtest.yeyz;
+----+------+
|id|age|
+----+------+
|1|1|
|2|2|
|3|3|
|4|4|
|5|5|
|6|6|
|7|7|
+----+------+
7rowsinset(0.00sec)

我们发现，slave表比master表多一条数据，也就是id=7的记录，此时我们在master上执行：

mysql:(none)22:34:15>>insertintotest.yeyzvalues(7,7),(8,8);
QueryOK,2rowsaffected(0.00sec)
Records:2Duplicates:0Warnings:0

查看slave上面的复制情况和数据情况，如下：

mysql:(none)22:39:05>>showslavestatus\G
***************************1.row***************************
Slave_IO_State:Waitingformastertosendevent
Master_Host:10.30.124.68
Master_User:dba_repl
Master_Port:4306
Connect_Retry:60
Master_Log_File:mysqlbin.000002
Read_Master_Log_Pos:852
Relay_Log_File:slave-relay-bin.000005
Relay_Log_Pos:648
Relay_Master_Log_File:mysqlbin.000002
Slave_IO_Running:Yes
Slave_SQL_Running:Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:


mysql:(none)22:40:15>>select*fromtest.yeyz;
+----+------+
|id|age|
+----+------+
|1|1|
|2|2|
|3|3|
|4|4|
|5|5|
|6|6|
|7|7|
+----+------+
7rowsinset(0.00sec)

可以看到，复制没有出现错误，即使从库上已经有id=7的记录。而且发现，从库的数据跟之前保持一致，也就是说，主库插入的id=8的记录没有被同步过来。

总结一下：该参数在跳过复制错误的时候，需要重启mysql服务，然后可能导致主从数据不一致。

03 slave-skip-errors=N参数

再看最后一个参数，这个参数表示的是并行复制过程中的从库复制模式，默认值是strict严格模式，和上面一样，我们先看主库和从库的数据情况：

master数据：

mysql:(none)22:39:20>>select*fromtest.yeyz;
+----+------+
|id|age|
+----+------+
|1|1|
|2|2|
|3|3|
|4|4|
|5|5|
|6|6|
|7|7|
|8|8|
+----+------+
8rowsinset(0.00sec)

slave数据：

mysql:(none)22:42:46>>select*fromtest.yeyz;
+----+------+
|id|age|
+----+------+
|1|1|
|2|2|
|3|3|
|4|4|
|5|5|
|6|6|
|7|7|
|8|8|
|9|9|
+----+------+
9rowsinset(0.00sec)

此时我们在从库上修改参数如下：

mysql:(none)22:42:59>>showvariableslike'%exec%';
+----------------------------------+--------+
|Variable_name|Value|
+----------------------------------+--------+
|gtid_executed_compression_period|1000|
|max_execution_time|0|
|rbr_exec_mode|STRICT|
|slave_exec_mode|STRICT|
+----------------------------------+--------+
4rowsinset(0.00sec)

mysql:(none)22:44:05>>setglobalslave_exec_mode='IDEMPOTENT';
QueryOK,0rowsaffected(0.00sec)

mysql:(none)22:44:10>>showvariableslike'%exec%';
+----------------------------------+------------+
|Variable_name|Value|
+----------------------------------+------------+
|gtid_executed_compression_period|1000|
|max_execution_time|0|
|rbr_exec_mode|STRICT|
|slave_exec_mode|IDEMPOTENT|
+----------------------------------+------------+
4rowsinset(0.00sec)

修改完参数，我们在主库上进行insert操作：

insert into test.yeyz values (9,9),(10,10);

查看从库的复制状态和数据情况，如下：

mysql:(none)22:44:14>>showslavestatus\G
***************************1.row***************************
Slave_IO_State:Waitingformastertosendevent
Master_Host:10.30.124.68
Master_User:dba_repl
Master_Port:4306
Connect_Retry:60
Master_Log_File:mysqlbin.000002
Read_Master_Log_Pos:1183
Relay_Log_File:slave-relay-bin.000007
Relay_Log_Pos:650
Relay_Master_Log_File:mysqlbin.000002
Slave_IO_Running:Yes
Slave_SQL_Running:Yes

1rowinset(0.00sec)

mysql:(none)22:44:38>>select*fromtest.yeyz;
+----+------+
|id|age|
+----+------+
|1|1|
|2|2|
|3|3|
|4|4|
|5|5|
|6|6|
|7|7|
|8|8|
|9|9|
|10|10|
+----+------+
10rowsinset(0.00sec)

可以发现，既没有出现复制错误，主库上插入的数据也同步过来了。

总结一下：

slave_exec_mode参数；
sql_slave_skip_counter=N参数;
slave-skip-errors=N参数。

这三个参数都能解决复制过程中的不一致情况，区别如下：

slave_exec_mode参数可以保证主从数据一致，其他两个不可以。

slave-skip-errors参数可以跳过制定的错误，但是需要重启实例，不能保证数据一致。

sql_slave_skip_counter参数需要在偏移量的复制模式下使用，不能保证数据一致。

以上就是MySQL复制问题的三个参数分析的详细内容，更多关于MySQL复制问题的资料请关注编程宝库其它相关文章！

下一节：MySQL pt-slave-restart工具的使用教程

MySQL教程

MySQL pt-slave-restart工具的使用简介：在MySQL主从复制环境的搭建中，常常会遇到一种场景，主库和从库都要初始化用户的账号密码，正常的情况下，需要在主库和从库都关闭session级别的binlog，然后再进行用户信 ...

详解Mysql函数调用优化

MySQL pt-slave-restart工具的使用教程