From the following aspects, we will deal with the problem of inconsistent MySQL master-slave replication.

When setting up a Mysql master-slave configuration, it often happens that the master and slave are synchronized, or there are errors or delays. Below we can troubleshoot errors based on these aspects.

Slightly larger websites will basically configure mysql master-slave replication. On the one hand, mysql master-slave is used to separate the reading and writing of the database. On the other hand, The stand-alone backup of MySQL itself is not very strong. It generally adopts a master-slave architecture and performs data backup on the slave.

In the MySQL master-slave replication process, there are more or less master-slave synchronization situations. This article will briefly summarize the situation of data master-slave synchronization. Please note that this article mainly discusses it from the database level. The master-slave database is inconsistent.
1. Network delay

Since mysql master-slave replication is an asynchronous replication based on binlog, binlog files are transmitted through the network. Of course, network delay is the absolute reason for master-slave synchronization. For most reasons, especially cross-computer room data synchronization, the probability of this happening is very high, so when separating reading and writing, pay attention to the early design from the business layer.

2. The loads of the master and slave machines are inconsistent

Because mysql master-slave replication starts an io thread on the master database and starts 1 sql from above Threads and 1 io thread, any one of the machines has a high load and is too busy, resulting in insufficient resources for any one of the threads, and master-slave inconsistency will occur.

3. Max_allowed_packet setting is inconsistent

The max_allowed_packet set on the master database is larger than that of the slave database. When a large SQL statement can be executed on the master database, the slave database The settings on the database are too small and cannot be executed, resulting in master-slave inconsistency.

4. Master-slave inconsistency caused by inconsistency between the key value starting from the key auto-increment key and the auto-increment step setting.

5. In the case of abnormal mysql downtime, if sync_binlog=1 or innodb_flush_log_at_trx_commit=1 is not set, it is very likely that the binlog or relaylog file will be damaged, resulting in master-slave inconsistency.

6. The master-slave synchronization is caused by a bug in mysql itself.

7. The versions are inconsistent, especially when the higher version is the master and the lower version is the slave, the function supported by the master database is not supported by the slave database.

The above are some common master-slave synchronization situations. There may be other out-of-synchronization situations. Please tell us about the master-slave inconsistency situation you encountered.

Based on the above situation, first ensure that max_allowed_packet, auto-increment key starting point and growth point settings are consistent, and then sacrifice part of the performance to enable sync_binlog on the main server. For libraries using innodb, it is recommended to configure the following content

1. innodb_flush_logs_at_trx_commit = 1
2. innodb-support_xa = 1 # Mysql 5.0 and above
3. innodb_safe_binlog # Mysql 4.0

At the same time, it is recommended to add the following two parameters to the slave database

1, skip_slave_start
2, read_only

The above is the solution to the problem in the master-slave environment built by Mysql. I hope it will be helpful to everyone.

Related articles:

#Example of Mycat read-write separation based on MySQL master-slave replication

Detailed introduction on how to use docker to quickly build a MySQL master-slave replication environment

The above is the detailed content of From the following aspects, we will deal with the problem of inconsistent MySQL master-slave replication.. For more information, please follow other related articles on the PHP Chinese website!