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

The following is about the main methods to solve the problem of MySQL master-slave replication inconsistency. the secret of the text is to be close to the topic. So, no gossip, let's go straight to the following, I believe you will benefit from reading this article on the main ways to solve the problem of MySQL master-slave replication inconsistency.

1. Network delay

Since mysql master-slave replication is an asynchronous replication based on binlog, and binlog files are transmitted through the network, it is only natural that network delay is the vast majority of reasons for master-slave non-synchronization, especially the probability of data synchronization across computer rooms is very high, so do read-write separation and pay attention to the pre-design from the business layer.

two。 The load of the master and slave machines is not consistent.

Because mysql master-slave replication starts 1 io thread above the master database, and starts 1 SQL thread and 1 io thread from above, any one of the machines has a high load and is too busy, resulting in a shortage of resources in any of these threads, and there will be master inconsistency.

Inconsistent 3.max_allowed_packet settings

The max_allowed_packet set above the master database is larger than that of the slave database. When a large sql statement can be executed on the master database, the setting from the master database is too small to be executed, resulting in the master never being consistent.

The key value of 4.key starting from the increment key is not consistent with the master caused by the inconsistency of the self-increment step size setting.

In the case of abnormal 5.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 corrupted, resulting in inconsistency of the master.

The master-slave out of sync caused by 6.mysql 's own bug.

7. The version is inconsistent, especially when the high version is the master and the low version is the slave, the function supported on the master database is not supported from the database.

These are some common cases of master-slave non-synchronization. There may be some other situations that are out of sync. Please tell me the situation in which you are inconsistent.

Based on the above, first ensure that the settings of max_allowed_packet, self-increment key starting point and growth point are consistent, and then open sync_binlog on the host at the expense of some performance. For libraries that use innodb, it is recommended to configure the following

1. Innodb_flush_logs_at_trx_commit = 1

2. Innodb-support_xa = 1 # Mysql or above

3 、 innodb_safe_binlog # Mysql 4.0

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

1 、 skip_slave_start

2 、 read_only

Is there anything you don't understand about the above main methods to solve the problem of MySQL master-slave replication inconsistency? Or if you want to know more about it, you can continue to follow our industry information section.

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.