Summary of restoring MySQL master-slave Data Consistency

Summary of restoring MySQL master-slave Data Consistency

This morning, my colleague told me that the data in the MySQL master-slave database was inconsistent and he guessed that the slave database encountered a problem during synchronization. So he boarded the slave database, use mysql> show slave status \ G to check whether the synchronization of the standby database stops because the primary key constraint is violated in the insert statement. The question is clearly how to restore the data consistency between the master and slave databases.

The options are as follows:

1. Check the latest Position of the Master and use it as the starting point for Slave replication.

This idea reflects the inconsistency of the past, and now it's time to keep it synchronized. It seems that this idea is contrary to the original intention of restoring the consistency of the master-slave database data. However, this method is simple and efficient and can be used in scenarios with low requirements on historical data in the test environment.

2. Master-slave database data consistency must be restored strictly.

There are also two ideas:

1. back up the data in the master database, restore the data from the database, and enable synchronization based on the consistency of historical data. However, this method is troublesome and you must perform the lock table operation on the master database, it prevents the client from updating table data. In case of large data volumes, backup is also a time-consuming project. In fact, this method is rarely used in the actual production environment.

2. Skip disconnection errors

In fact, this statement is not very rigorous. It is prepared to skip related transactions. In my current situation, it is the insert statement that the skip fails due to violation of the primary key constraint.

 

How to skip related transactions

1. Stop the slave Service

2. set global SQL _SLAVE_SKIP_COUNTER = 1;

3. Enable the slave service.

A transaction is skipped here. Of course, you can also skip multiple transactions, but be cautious. After all, you do not know what transactions are skipped.

Suggestion: You can perform the preceding steps repeatedly to carefully check the statements that cause the slave database to fail to be synchronized. Sometimes, blocking too many transactions from the slave database seems inefficient.

You can analyze the transactions in the master database logs to determine the appropriate values of SQL _SLAVE_SKIP_COUNTER. The procedure is as follows:

1. Execute show slave status \ G in the slave database to confirm the following two parameters:

Based on the values of the preceding two parameters, you can view the current transactions that impede slave database replication and subsequent transactions in the master database.

Mysql> show binlog events in 'mysql-bin.000217 'from 673146776;

This is to view all transactions in the log file mysql-bin.000217 after the transaction ID is 673146776.

Of course, the use of show binlog events is still quite flexible, either of the following methods.

Mysql> show binlog events in 'mysql-bin.000217 'from 673146776 \ G

Mysql> show binlog events in 'mysql-bin.000217 'from 673146776 limit 10;

You can also run the mysqlbinlog command in the host environment.

# Mysqlbinlog mysql-bin.000217 -- start-position = 673146776

 

How to query statement execution

After skipping related transactions from the Slave database and restarting Slave, Slave_IO_Running and Slave_ SQL _Running both show "YES", but Seconds_Behind_Master does not immediately drop, but slowly increases.

At this time, the show processlist statement is used to view the thread execution. It is found that the execution of the first statement is too long, and the "State" column displays "Sending data ". The official description of "Sending data" is as follows:

It can be seen that this statement involves a large number of disk reads.

To further analyze the time consumption distribution of the statement, you can set the profiling variable. The procedure is as follows:

1. set profiling = on before the query starts;

2. After the statement is executed, use show profiles to view the Query_ID of the statement.

3. view the statement execution status through show profile for query Query_ID.

It is also found that this statement takes too long in the Sending data phase.

Summary:

1. when running the stop slave command, the stop slave command is hang and related information is queried online. It may be related to long SQL or Locked SQL Execution in Slave. Here, except show processlist, it is recommended that you do not execute slave commands such as show slave status and slave stop. How can this problem be solved? Waiting to lock Slave SQLOr restart the database. I chose the latter.

2. when the slave database is restarted, the following ERROR occurs: ERROR 1872 (HY000 ): slave failed to initialize relay log info structure from the repository. It is recommended that you reconfigure the master-slave cluster for a lot of information on the Internet, and return to the solution selection section at the beginning. In a strange situation, I shut down the slave database and restarted it. The only difference between the two startup commands is that mysqld is used for the previous start, mysqld_safe is used for the next start, and a -- user parameter is added.

This article permanently updates the link address: