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

Today we mainly look at several ways to skip errors, skip transactions, or skip event in master-slave mode. In fact, we have always ignored this before, which can easily lead to greater inconsistency between master and slave data in the process of maintaining master and slave data.

Test machine 5.7.18 Master / Slave gtid

Main database data

Slave database data

It is obvious that there is a discrepancy in the master-slave data. The slave database is missing a piece of data (28Phone2). At this time, the main database opens the following transactions:

This will inevitably result in an error of 1032 reported from the slave library, as shown below:

Mysql > show slave status\ G

1. Row

Slave_IO_State: Waiting for master to send event

Master_Host: 192.168.1.56

Master_User: repl

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000023

Read_Master_Log_Pos: 1928

Relay_Log_File: hadoop2-relay-bin.000012

Relay_Log_Pos: 1595

Relay_Master_Log_File: mysql-bin.000023

Slave_IO_Running: Yes

Slave_SQL_Running: No

Replicate_Do_DB:

Replicate_Ignore_DB:

Replicate_Do_Table:

Replicate_Ignore_Table:

Replicate_Wild_Do_Table:

Replicate_Wild_Ignore_Table:

Last_Errno: 1032

Last_Error: Could not execute Delete_rows event on table yhtest1.yhtest; Can't find record in 'yhtest', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's master log mysql-bin.000023, end_log_pos 1812

Skip_Counter: 0

Exec_Master_Log_Pos: 1502

Relay_Log_Space: 2384

Until_Condition: None

Until_Log_File:

Until_Log_Pos: 0

Master_SSL_Allowed: No

Master_SSL_CA_File:

Master_SSL_CA_Path:

Master_SSL_Cert:

Master_SSL_Cipher:

Master_SSL_Key:

Seconds_Behind_Master: NULL

Master_SSL_Verify_Server_Cert: No

Last_IO_Errno: 0

Last_IO_Error:

Last_SQL_Errno: 1032

Last_SQL_Error: Could not execute Delete_rows event on table yhtest1.yhtest; Can't find record in 'yhtest', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's master log mysql-bin.000023, end_log_pos 1812

Replicate_Ignore_Server_Ids:

Master_Server_Id: 1

Master_UUID: ab8c3ec3-b588-11e7-a769-000c29c57be6

Master_Info_File: mysql.slave_master_info

SQL_Delay: 0

SQL_Remaining_Delay: NULL

Slave_SQL_Running_State:

Master_Retry_Count: 86400

Master_Bind:

Last_IO_Error_Timestamp:

Last_SQL_Error_Timestamp: 171130 23:55:18

Master_SSL_Crl:

Master_SSL_Crlpath:

Retrieved_Gtid_Set: ab8c3ec3-b588-11e7-a769-000c29c57be6:96-101,

Executed_Gtid_Set: ab8c3ec3-b588-11e7-a769-000c29c57be6:1:77-100

B6ddfda0-d8bc-4272-a58f-4ea75acbbc79:1-22 1000012-1000013 2000012-2000013

D24c1c76-b4ef-11e7-969a-000c29a75f68:1-17

Auto_Position: 0

Replicate_Rewrite_DB:

Channel_Name:

Master_TLS_Version:

1 row in set (0.01 sec)

Solution:

Method 1

5.7. since GTID is enabled, we cannot skip the error through the parameter sql_slave_skip_counter=N, but we can skip the error by executing an empty thing from the library, but note that it is a thing to be skipped.

From the above error information, we can easily see that the current execution position is: ab8c3ec3-b588-11e7-a769-000c29c57be6:1:77-100. that is, the error location is: ab8c3ec3-b588-11e7-a769-000c29c57be6:1:77-101,

Do the following:

At this time, we show slave status\ G again to see that the master and slave have returned to normal, but when we compare the data, we find that the three event of our master database were all skipped in the same event, that is, the two inserted data are not executed in the slave database!

Master database data:

From the library data:

Method 2

If we think there are many steps in the above methods, we can also skip master-slave errors by using pt-slave-restart. The method is as follows:

This can also achieve the goal of skipping master-slave errors! But the unit it skips is also a transaction, which will also cause the two inserts made by the master library not to be performed in the slave library!

Method 3

Use the slave_exec_mode parameter to handle the errors encountered by the master, as follows:

Stop slave

Set global slave_exec_mode='IDEMPOTENT'; idempotent mode (default is STRICT strict mode)

Start slave

View master and slave data

It is found that it is consistent, that is, after adjusting the parameter slave_exec_mode, we skipped the delete error, but the two insert operations were still performed. At the same time, looking at the error log, we found that the record is as follows:

Method 4

Use the parameter slave_skip_errors to skip the error as follows, add slave_skip_errors=1062,1032 to the configuration file, restart the database, start slave, and you will also find that the test results are consistent with method 3. But slave_skip_errors does not support dynamic modification!

Method 5

In slave 1032, we go directly to the master database to find the corresponding records and insert them into the slave database. In restart salve, in 1062, we can directly delete the conflict records in the backup from the slave database.

A little mention here: we can skip the Gtid error of mariadb directly with the parameter sql_slave_skip_counter=N, but we do not support the use of pt-slave-restart to skip the error. Moreover, the Gtid of mariadb is not in common with the official, and the implementation principle is the same, but the implementation method is different, so we cannot build gtid master and slave between the two versions.

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.