【MySql】sql_slave_skip_counter引數的用法解析

北在南方發表於2016-04-13
MySql
   前一篇文章介紹了當mysql的複製出現slave sql 程式終止時的解決辦法，其中之一使用了sql_slave_skip_counter 來使sql 執行緒跳過遇到錯誤的事件記錄！本文淺析一下sql_slave_skip_counter的具體用法和意義！
set global sql_slave_skip_counter = N
This statement skips the next N events from the master. 
(即是跳過N個events，這裡最重要的是理解event的含義!在mysql中,對於sql的 binary log 實際上是由一連串的event組成的一個組,即事務組。)
在備庫上設定 global sql_slave_skip_counter =N 會跳過當前時間來自於master的之後N個事件,這對於恢復由某條SQL語句引起的從庫複製有效. 此語句只在當slave threads是停止時才有效,否則將發生一條錯誤..每忽略一個事件，N 減一，直到N減為0！
When using this statement, it is important to understand that the binary log is actually organized as a sequence of groups known as event groups. Each event group consists of a sequence of events.
For transactional tables, an event group corresponds to a transaction.
For nontransactional tables, an event group corresponds to a single SQL statement.
Note
   A single transaction can contain changes to both transactional and nontransactional tables.When you use SET GLOBAL sql_slave_skip_counter to skip events and the result is in the middle of a group, the slave continues to skip events until it reaches the end of the group. Execution then starts with the next event group
### comment ###
   Setting this variable isn`t like setting other server variables: you can`t read the variable back again as @@sql_slave_skip_counter, and it isn`t really a “global variable.” Rather, it`s a variable that only the slave thread reads.
  When you restart the slave threads again with START SLAVE, the slave skips statements and decrements the variable until it reaches 0, at which point it begins executing statements again. You can watch this happening by executing SHOW SLAVE STATUS, where the variable`s value appears in the Skip_Counter column. This is the only place you can see its value.
  The effect is that the setting isn`t persistent. If you set it to 1, start the slave, and the slave has an error in replication sometime later, the variable won`t still be set to 1. It`ll be 0. At that point, if you want the slave to skip the statement that caused the error, you`ll have to set it to 1 again.
有關”SET GLOBAL sql_slave_skip_counter”的語法可以參考官方文件
測試環境：
rac3 主庫
rac4 備庫
測試之前保證主從無資料延時，保證資料一致！
1 使用含有 stop slave 的命令， 在主庫上建立測試表，並使用shell 插入資料！
mysql> create table tab_skip(id int);
Query OK, 0 rows affected (0.80 sec)
[root@rac3 mysql]#
 for i in {1..100}; 
   do 
    echo $i; 
    echo “insert into tab_skip(id) values($i)” | mysql -h127.0.0.1 test ; 
    sleep 1;
done;
在備庫 使用 set global sql_slave_skip_counter=1;命令做測試
[root@rac4 mysql]# 
for i in {1..10}; 
do 
   echo $i; 
   echo “slave stop;set global sql_slave_skip_counter=1; slave start;show slave statusG” | mysql -h127.0.0.1 -P3306 test ; 
   sleep 2;
done;
分別在主庫和備庫上進行驗證資料的完整性:
主庫上面:
[root@rac3 mysql]# mysql
mysql> use test;                 
Database changed
mysql> select count(1) from tab_1;
+———-+
| count(1) |
+———-+
|      100 |
+———-+
1 row in set (0.00 sec)
備庫上面,少了 10條資料！因為正是執行set global sql_slave_skip_counter=1;使備庫執行sql replay的時候忽略了事件！
[root@rac4 mysql]# mysql
mysql> use test;
Database changed
mysql> select count(1) from tab_1;  
+———-+
| count(1) |
+———-+
|       90 |
+———-+
1 row in set (0.00 sec)

有網友測試的是在備庫上執行沒有stop slave 語句的命令，但是在5.5.18版本上面是不允許的！
[root@rac3 mysql]# for i in {1..100}; do echo $i; echo “insert into tab_2(id) values($i)” | mysql -h127.0.0.1 test ; sleep 2;done;   
1
….
100

在備庫上執行，注：”set global sql_slave_skip_counter=1; slave start;show slave statusG”  沒有stop slave 語句，報錯！

[root@rac4 mysql]# for i in {1..10}; do echo $i; echo “set global sql_slave_skip_counter=1; slave start;show slave statusG” | mysql -h127.0.0.1 -P3306 test ; sleep 2;done;          
1
ERROR 1198 (HY000) at line 1: This operation cannot be performed with a running slave; run STOP SLAVE first
2
ERROR 1198 (HY000) at line 1: This operation cannot be performed with a running slave; run STOP SLAVE first
3
ERROR 1198 (HY000) at line 1: This operation cannot be performed with a running slave; run STOP SLAVE first
4
ERROR 1198 (HY000) at line 1: This operation cannot be performed with a running slave; run STOP SLAVE first
5 


使用 該引數能夠解決從伺服器sql 程式停止導致的資料庫不同步，但是也有一定的風險，比如在高併發的資料庫環境下，可能會導致資料丟失！
另見另一位網友的測試實驗(多少有些出入，他的可以不使用stop slave)


相關文章