Shulou(Shulou.com)05/31 Report--

In this issue, the editor will bring you about how to ignore binlog events in MySQL. The article is rich in content and analyzes and narrates it from a professional point of view. I hope you can get something after reading this article.

1. When GTID mode is not enabled

Some replication events can be ignored simply by setting the value of SQL_SLAVE_SKIP_COUNTER. For example:

# you need to disable the SLAVE service first

Root@imysql.com [test] > STOP SLAVE

# ignore N events (event). Usually a SQL is an event

Root@imysql.com [test] > SET SQL_SLAVE_SKIP_COUNTER=N

# start the SLAVE service again

Root@imysql.com [test] > START SLAVE

2. When GTID mode is enabled

Enabling GTID makes it a little more troublesome to ignore some error events.

First, we need to check the progress of the current SLAVE replication:

Mysql > SHOW SLAVE STATUS\ G shows that the current GTID progress of SLAVE replication is as follows:

Slave_IO_Running: Yes

Slave_SQL_Running: No

Last_Errno: 1062

Last_Error: … Duplicate... Key 'PRIMARY', Error_code: 1062;...

Master_UUID: f2b6c829-9c87-11e4-84e8-deadeb54b599

Retrieved_Gtid_Set: 3a16ef7a-75f5-11e4-8960-deadeb54b599:1-283J f2b6c829-9c87-11e4-84e8-deadeb54b599:1-33

Executed_Gtid_Set: 3a16ef7a-75f5-11e4-8960-deadeb54b599:1-283J f2b6c829-9c87-11e4-84e8-deadeb54b599:1-31

Auto_Position: 1 from the above information, you can see that the transaction list of 1-33 has been fetched from MASTER, and has been executed (see Executed_Gtid_Set) to the transaction GTID location 31, where an error has occurred (32).

At this point, we need to manually adjust the GTID list GTID_PURGED cleared by SLAVE to artificially tell SLAVE which transactions have been cleared, which can be ignored later:

Root@imysql.com [test] > STOP SLAVE

Root@imysql.com [test] > RESET MASTER

Root@imysql.com [test] > SET @ @ GLOBAL.GTID_PURGED = "3a16ef7a-75f5-11e4-8960-deadeb54b599:1-283 under f2b6c829-9c87-11e4-84e8-deadeb54b599:1-32"

The intention of the above commands root@imysql.com [test] > START SLAVE; is to ignore the GTID transaction f2b6c829-9c87-11e4-84e8-deadeb54b599:32, and the next transaction can skip the above error by starting with the GTID of 33.

3. Use the pt-slave-restart tool whether GTID is enabled or not

First of all, I have to say that the percona toolkit toolset is too convenient for DBA. The role of the pt-slave-restart tool is to monitor for certain replication errors, then ignore them, and start the SLAVE process (Watch and restart MySQL replication after errors) again.

Simple usage example:

# ignore all 1062 errors and start the SLAVE process again

[yejr@imysql.com] # pt-slave-resetart-S./mysql.sock-error-numbers=1062

# ignore the error message as long as it contains test.yejr, and start the SLAVE process again

[yejr@imysql.com] # pt-slave-resetart-S./mysql.sock-error-text= "test.yejr" to sum up, although we can use tools to quickly ignore replication errors, we still need to know how to artificially ignore replication errors, even when we don't have the tools.

This is how to ignore the binlog event in the MySQL shared by the editor. If you happen to have similar doubts, you might as well refer to the above analysis to understand. If you want to know more about it, you are welcome to follow the industry information channel.

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.