如何解析sql_slave_skip_counter 參數(shù)的用法

如何解析sql_slave_skip_counter 參數(shù)的用法，很多新手對此不是很清楚，為了幫助大家解決這個難題，下面小編將為大家詳細(xì)講解，有這方面需求的人可以來學(xué)習(xí)下，希望你能有所收獲。

這里淺析一下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組成的一個組,即事務(wù)組。)
在備庫上設(shè)置 global sql_slave_skip_counter =N 會跳過當(dāng)前時間來自于master的之后N個事件,這對于恢復(fù)由某條SQL語句引起的從庫復(fù)制有效. 此語句只在當(dāng)slave threads是停止時才有效,否則將發(fā)生一條錯誤..每忽略一個事件，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.

有關(guān)"SET GLOBAL sql_slave_skip_counter"的語法可以參考官方文檔

測試環(huán)境：

rac3 主庫

rac4 備庫

測試之前保證主從無數(shù)據(jù)延時，保證數(shù)據(jù)一致！

1 使用含有 stop slave 的命令， 在主庫上創(chuàng)建測試表，并使用shell 插入數(shù)據(jù)！

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 -h227.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 status\G" | mysql -h227.0.0.1 -P3306 test ; 

   sleep 2;

done;

分別在主庫和備庫上進行驗證數(shù)據(jù)的完整性:

主庫上面:

[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條數(shù)據(jù)！因為正是執(zhí)行set global sql_slave_skip_counter=1;使備庫執(zhí)行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)

有網(wǎng)友測試的是在備庫上執(zhí)行沒有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 -h227.0.0.1 test ; sleep 2;done;   
1

....

100

在備庫上執(zhí)行，注："set global sql_slave_skip_counter=1; slave start;show slave status\G"  沒有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 status\G" | mysql -h227.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 

使用 該參數(shù)能夠解決從服務(wù)器sql 進程停止導(dǎo)致的數(shù)據(jù)庫不同步，但是也有一定的風(fēng)險，比如在高并發(fā)的數(shù)據(jù)庫環(huán)境下，可能會導(dǎo)致數(shù)據(jù)丟失！

另見另一位網(wǎng)友的測試實驗(多少有些出入，他的可以不使用stop slave)

看完上述內(nèi)容是否對您有幫助呢？如果還想對相關(guān)知識有進一步的了解或閱讀更多相關(guān)文章，請關(guān)注億速云行業(yè)資訊頻道，感謝您對億速云的支持。