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

Foreword:

At present, the most commonly used MySQL database is the master-slave architecture, and most of the highly available architectures are evolved through the master-slave architecture. However, the master-slave architecture is prone to data inconsistencies after a long running time, such as misoperation caused by writable slave database or copying bug. This article will explore in detail the occurrence of master inconsistency and how to solve this problem.

1. The reason for the inconsistency of the principal.

There are many possible reasons for the inconsistency of the principal. Here are a few simple examples:

The binlog format of the master library is Statement, and synchronization to the slave library may result in master inconsistency. Set sql_log_bin=0 is executed before the master database executes the change, so that the master database does not record binlog, and the slave database cannot change this part of the data. Read-only is not set from the slave node, misoperation to write data. The master library or slave library goes down unexpectedly, and the downtime may cause binlog or relaylog files to be corrupted, resulting in master inconsistency. The versions of master and slave instances are inconsistent, especially when the high version is the master and the lower version is the slave, the feature supported on the master database may not be supported on the slave database. MySQL itself is caused by bug. two。 Master inconsistent repair method

The following is a description of master inconsistency fixes. Note that this is about fixing master inconsistencies rather than master-slave synchronization errors.

If we want to repair the inconsistency of the master, we must first find that the master is inconsistent, and the appropriate repair methods will be given according to different situations.

The first case: for example, when executing a script, set sql_log_bin=0 is added to the script in order to finish it faster. Then all data changes in this script cannot be applied to the slave database, and the master-slave data is inconsistent at this time. The solution is to stop the master-slave replication, then manually execute the script in the slave library, and finally enable the master-slave replication.

The second situation: maybe your slave library is not set read-only, colleagues do not know the architecture, misoperation led to data writing from the library, this situation should be timely feedback and resolution. Solution: if these statements do need to be executed, you can execute set sql_log_bin=0 first in the master library, and then execute the statements; if you do not need to execute these statements, you need to roll back the previous misoperation on the slave library.

However, sometimes the situation is not so simple. There may be more cases in which the master and slave instances have been running for a long time. One day, the consistency check shows that the master is not consistent, so it is difficult to find the specific reason and time for the inconsistency. So what should we do at this time? some people say, redo it from the library, although this is also a solution, but the recovery time of this solution is relatively slow, and sometimes the database is responsible for part of the query operation, can not be rebuilt rashly. The following focuses on the repair methods in this case.

Use the percona-toolkit tool to assist.

The PT toolkit includes two tools, pt-table-checksum and pt-table-sync, which are mainly used to detect master-slave consistency and repair data inconsistencies. The advantage of this scheme is that the repair speed is fast, there is no need to stop master-slave assistance, and the disadvantage is that it requires knowledge accumulation. If you don't know how to use this tool, you may need time to learn and test, especially in a production environment. Use it carefully.

For instructions on how to use it, you can refer to the following link:

Https://www.cnblogs.com/feiren/p/7777218.html

Manually rebuild inconsistent tables.

For example, we find that some tables from the library are inconsistent with the data of the main database, and the amount of data of these tables is relatively large, it is not realistic to compare the data manually, and it is relatively slow to redo the whole library. At this time, you can only redo these tables to fix the master inconsistency. For example, if the master and slave data of the three tables A1 b1 C1 are inconsistent, then we can do this:

1. Stop Slave replication from the library

Mysql > stop slave

2. Dump the three tables on the main library, and record the synchronized binlog and POS points

Mysqldump-uroot-p123456-Q-single-transaction-- master-data=2 yourdb A1 b1 C1 >. / a1_b1_c1.sql

3. Check the a1_b1_c1.sql file to find out the recorded binlog and POS points

More a1_b1_c1.sql

For example, MASTER_LOG_FILE='mysql-bin.002974', MASTER_LOG_POS=55056952

4. Copy the a1_b1_c1.sql to the Slave machine and point to the Change master to

Mysql > start slave until MASTER_LOG_FILE='mysql-bin.002974', MASTER_LOG_POS=55056952

Note: let me explain what this step means. Ensure that the data of other tables will not be lost and keep synchronizing until the end of that point of synchronization. The data of a1MagneB1MagneC1 table has already generated a snapshot in the previous dump. We just need to import and enter it, and then turn on synchronization.

5. Import a1_b1_c1.sql on the Slave machine (if binlog is enabled from the library, to speed up the import, you can execute set sql_log_bin=0 first)

Mysql-uroot-p123456 yourdb

Start slave

In this way, we recovered three tables and fixed the synchronization. The disadvantage of this scenario is that you need to stop copying from the library during import, but it is acceptable.

There may be other fixes, such as comparison synchronization with tools such as Navicat, but such tools are only suitable for small amounts of data, and it is not realistic to use this method when there are tens of millions of data. Do you have any similar experience? you can also leave a message and share it.

3. How to avoid the inconsistency of the Lord

Through the above introduction, you may also know that repair is not easy, so we should avoid it from the source, so how can we avoid the inconsistency of the main body? here are some suggestions that I hope will be useful to you.

The main library binlog is in ROW format. The version of the master-slave instance database is consistent. The main database does a good job of controlling the account permissions and cannot execute set sql_log_bin=0. Read-only is enabled from the library, and artificial writing is not allowed. Check the master-slave consistency regularly.

Summary:

This article describes in detail the causes of principal inconsistencies, ways to repair inconsistencies and how to avoid principal inconsistencies. In particular, inconsistent repair methods, there may be other options, this should take into account the actual situation to choose the appropriate method to repair. Originality is not easy. I hope you will support it.

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.