MySQL实践——参数SQL_SLAVE_SKIP_COUNTER的奥秘

每次数据库复制冲突之后，经常使用的一个命令如下。

SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;

一般会认为，现在出现冲突错误，那就将上面参数值设置为1，跳过出错的这个event就可以解决了。重新启动复制，发现问题果然解决，我们以为这样理解是正确的。其实不然。

这需要从Binlog的内容说起。在Binlog中，所有的Binlog是按照组来分的，每一个组是其主库生成的一个事务Binlog，都以BEGIN开始并以COMMIT结束。还有一些特殊事件，比如用户变量的设置、随机数的设置等。

那么，设置参数SQL_SLAVE_SKIP_COUNTER之后，对复制的影响是什么呢?从库遇到这个参数时，它的skip算法又是什么呢?

这需要根据不同类型的事件，分别做介绍，如下。

• BEGIN语句：对于一个Binlog组，肯定会有一个BEGIN语句作为开始的标志。执行到BEGIN时，说明从这个位置开始，到后面出现的第一个提交事件结束，中间这一段Binlog是属于一个组的，那么此时会因为不同的SQL_SLAVE_SKIP_COUNTER有不同的处理方式。如果参数SQL_SLAVE_SKIP_COUNTER值为1，则此时这个组中的所有事件都会被算作不计数事件，也就是说这个1代表一个事务，而不是一个事件。只有遇到COMMIT语句时，才会将计数1减为0，那么下一个事务组会正常执行。如果参数SQL_SLAVE_SKIP_COUNTER的值大于1，那么这个组中的事件就会被认为是一个个的事件，处理一个事件，参数SQL_SLAVE_SKIP_COUNTER的值就减去1。当减到1的时候，如果这个事务组还没有结束，则回到上面，跳过值为1的情况；如果结束了，但还没有减为0，那么下一个事务组会继续重新处理。

• COMMIT或ROLLBACK语句：上面已经提到，遇到这个语句时，不管任何情况，参数SQL_SLAVE_SKIP_COUNTER的值都会减1。如果SQL_SLAVE_SKIP_COUNTER的值为
  1，就像上面说的，COMMIT之前所有的事件都会被算为不计数事件，这里减1之后，就完成了一个事务的skip。

• 其他Query语句：上面已经说过，当SQL_SLAVE_SKIP_COUNTER为1的时候，会将组内事件都跳过，否则它会被减1。

• Rows事件：这种类型是在行模式下，一个行的事件类型。针对一条sql语句产生的若干个事件，分别计数。如果设置的SQL_SLAVE_SKIP_COUNTER大于1，则针对每一个事
  件都会递减1，如果减到了1或设置为1了，则直到COMMIT事件之后才会计数，之前所有的操作都不会被计数。

• 不计数事件：这种类型的意思是，只要遇到这种事件，并且设置了参数SQL_SLAVE_SKIP_COUNTER为1或递减之后值为1，那么就跳过，并且不会影响SQL_SLAVE_SKIP_COUN-TER的值。而如果设置的SQL_SLAVE_SKIP_COUNTER值大于1，则计数递减1，直到递减到1时这类事件才不会算入计数。这种类型的事件包括Table_map、Intvar、Rand、User_var、BEGIN_load这五个事件，所以在Binlog中如果有这五个事件，则在计数中不做计数，直接忽略。

需要注意的是，在每次复制中断后，看上去中断的位置是出错的事件，但实际上，那只是一个执行错误的位置。因为在复制时是以组(事务)为单位的，事务中执行出错了，则这个事务会回滚，这个组就没有完成。实际上，此时Binlog停止的位置是这个组的开始位置，所以在设置好之后，开始的位置肯定是BEGIN或BEGIN之前的一些设置命令的位置，此时设置SQL_SLAVE_SKIP_COUNTER为1之后，后面跳过的就是一个完整的事务，而不是一个事件而已。

对于设置SQL_SLAVE_SKIP_COUNTER为其他值的情况，这是比较危险的。因为它的跳过算法在上面已经讲清楚了，它会把每一个query语句(包括BEGIN及COMMIT)都计入跳过计数的情况，也就是说，假设一个组至少存在3个事件，那么如果设置跳过为4、5、6，实际上结果有可能只跳过1个事务，也有可能跳过2个事务，但这是没有办法预期的。除非你自己已经很清楚当前点之后有多少个事件及对应事件的类型，否则不会知道具体跳过了几个事务。

此时可以再回到开头所说的问题上来。在skip的时候，看到的是在哪一个位置出错了，但实际上此时是停在了一个事务的开始位置，而出错的位置有可能是在事务中的某一个语句或者行上面，那么此时做skip，实际上是跳过了当前中断位置所在的整个事务。可想而知，事务中如果有其他操作，也就都跳过了。而从表象上看，好像是跳过了这个事件。所以说，看到的和真实发生的其实不太一样。

当然通过设置参数SQL_SLAVE_SKIP_COUNTER来跳过复制错误的操作，只有在MySQL 5.5版本，或者是5.6及以上的版本中没有开GTID的情况下才能使用。在处理问题时，有时候确实很方便，但也是比较危险的，最好确认清楚是不是可以跳过，跳过之后，是不是要处理数据丢失的问题等。最好在跳过之前，记录一下相关Binlog的位置，在恢复之后，看一下从出错位置开始的一个Binlog事务，有没有需要特殊处理的操作。

关于这个问题，其实很容易做一些测试，研究一下参数SQL_SLAVE_SKIP_COUNTER设置为不同的值时，复制是什么表现。不过，个人建议永远不要将这个参数的值设置为非1，否则会非常难控制。假设跳过的值太多，可以分开多次，每次跳过最多一个事务，这样也能做到心中有数，并且是只有出错的时候才去跳。

下面是一段每次跳过一个事务的脚本，只有在复制中断的情况下才会跳过，并且自动开始复制。

#!/usr/bin
MySQL_user=username
MySQL_password=password
MysoL_host=127.0.0.1
MysQL_port=3306
sleep_interval=100
while:
dodatemysql -u${MySQL_user} -p${MysQl_password} -h ${MysaL_host} -P ${MySQL_port} -e "set global sql_slave_skip_counter=1;start slave;"usleep ${sleep_interval}echo
done

当然，可以对这段代码稍微做一点改造，加上一行可以记录一下中断时的位置。每跳过一个事务，都打印一下这个事务的开始位置，这样可以了解跳过的事务量，并且方便事后查找跳过了哪些事务。但这样大批量的跳过，一般是在处理故障或是明知道影响不大时才这样做的。还是那句话，请谨慎使用。