Shulou(Shulou.com)06/01 Report--

How to analyze the use of sql_slave_skip_counter parameters, many novices are not very clear about this, in order to help you solve this problem, the following editor will explain for you in detail, people with this need can come to learn, I hope you can gain something.

Here is a brief analysis of the specific usage and meaning of sql_slave_skip_counter!

Set global sql_slave_skip_counter = N

This statement skips the next N events from the master.

(that is, skip N events, the most important thing here is to understand the meaning of event! In mysql, the binary log for sql is actually a group made up of a series of event, the transaction group.)

Setting global sql_slave_skip_counter = N on the slave database skips N events after the current time from master, which is effective for resuming replication from the library caused by a SQL statement. This statement is valid only when slave threads is stopped, otherwise an error will occur.. For each event ignored, N is reduced by one until N is reduced to 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.

The syntax of "SET GLOBAL sql_slave_skip_counter" can be found in the official documentation.

Test environment:

Rac3 main library

Rac4 Reserve Library

Ensure master-slave data delay and data consistency before testing!

Use the command containing stop slave to create a test table on the main library and insert data using 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-h227.0.0.1 test

Sleep 1

Done

Use the set global sql_slave_skip_counter=1; command to test in the standby library

[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

Verify the integrity of the data on the main database and the standby database, respectively:

Above the main library:

[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)

There are 10 pieces of data missing from the database. Because it is the execution of set global sql_slave_skip_counter=1; that causes the library to ignore the event when it executes 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)

Some netizens tested to execute commands without stop slave statements on the repository, but it is not allowed in version 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 2done

one

....

one hundred

Execute on the standby database, note: "set global sql_slave_skip_counter=1; slave start;show slave status\ G" has no stop slave statement, report an error!

[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

one

ERROR 1198 (HY000) at line 1: This operation cannot be performed with a running slave; run STOP SLAVE first

two

ERROR 1198 (HY000) at line 1: This operation cannot be performed with a running slave; run STOP SLAVE first

three

ERROR 1198 (HY000) at line 1: This operation cannot be performed with a running slave; run STOP SLAVE first

four

ERROR 1198 (HY000) at line 1: This operation cannot be performed with a running slave; run STOP SLAVE first

five

Using this parameter can solve the database out-of-synchronization caused by stopping the sql process from the server, but there is also a certain risk, such as in a highly concurrent database environment, it may lead to data loss!

See also another netizen's test experiment (somewhat different, his can not use stop slave)

Is it helpful for you to read the above content? If you want to know more about the relevant knowledge or read more related articles, please follow the industry information channel, thank you for your support.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.