Solution to inconsistent master-slave replication of MySQL

For websites of a slightly larger scale, mysql master-slave replication is basically configured. On the one hand, mysql master-slave is used for database read/write splitting, and on the other hand, mysql's standalone backup is not very strong. Generally, the master-slave architecture is used, back up data from the top.

For websites of a slightly larger scale, mysql master-slave replication is basically configured. On the one hand, mysql master-slave is used for database read/write splitting, and on the other hand, mysql's standalone backup is not very strong. Generally, the master-slave architecture is used, back up data from the top.

In the master-slave replication process of MySQL, there may be some situations where the master node is not synchronized. This article will briefly summarize the situations where the master node is not synchronized, please note that this article mainly discusses the inconsistency between the master and slave databases at the database level.
1. network latency

Because mysql master-slave replication is an asynchronous replication based on binlog, The binlog file is transmitted over the network. Of course, the network latency is the majority of the reasons for master-slave synchronization, especially for cross-data-center data synchronization, this probability is very high. Therefore, read/write splitting should be performed and the preliminary design should be conducted from the business layer.

2. the load on the master and slave machines is inconsistent.

Since mysql master-slave replication starts one I/O thread on the master database, and one SQL thread and one I/O thread are started on the master database, the load on any machine is very high, as a result, any thread may experience insufficient resources, and the master may be inconsistent.

3. Inconsistent settings of max_allowed_packet

The max_allowed_packet set on the master database is larger than that on the slave database. When a large SQL statement can be executed on the master database, it is too small to be executed, as a result, the primary nodes are inconsistent.

4. The primary key is inconsistent with the auto-increment step.

5. If sync_binlog = 1 or innodb_flush_log_at_trx_commit = 1 is not set, binlog or relaylog files may be damaged, resulting in inconsistent master data.

6. The master node is not synchronized due to a bug in mysql.

7. Version inconsistency, especially when the master version is the master version and the slave version is the slave version, the features supported by the master database are not supported from the database.

These are common scenarios where the master node is not synchronized. There may be some other non-synchronization situations. Please tell us the situations where you encounter different masters.

Based on the above situation, first ensure that the max_allowed_packet, auto-incrementing key start point and Growth Point settings are consistent, and sacrifice some performance to enable sync_binlog on the master. For databases using innodb, we recommend that you configure the following content.

The Code is as follows:
1. innodb_flush_logs_at_trx_commit = 1 2. innodb-support_xa = 1 # Mysql 5.0 or above 3. innodb_safe_binlog # Mysql 4.0

We recommend that you add the following two parameters to the database.

1. skip_slave_start
2. read_only