Shulou(Shulou.com)05/31 Report--

This article is to share with you about the inconsistent situation of MySQL master-slave replication. The editor thinks it is very practical, so share it with you as a reference and follow the editor to have a look.

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 situation, 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 master at the expense of some performance. For libraries using innodb, it is recommended to configure the following:

1), innodb_flush_logs_at_trx_commit = 1

2), innodb-support_xa = 1 # Mysql 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

8. The master library has too many slaves, resulting in replication delay

The number of slave data should be 3-5. Too many slave nodes to be replicated will cause replication delay.

9. The hardware of the slave library is worse than the master library, resulting in replication delay

Viewing the system configuration of Master and Slave may cause replication delays due to improper machine configuration, including disk Imax O, CPU, memory and other factors. It usually occurs in the scenario of high concurrency and large amount of data writing.

10. Too many slow SQL statements

If the execution time of a SQL statement is 20 seconds, it will take at least 20 seconds from the completion of the execution to the time the data can be found from the database, which is a delay of 20 seconds.

Generally speaking, the optimization of SQL statements should be continuously monitored and optimized as a routine work. If the writing time of a single SQL is long, it can be modified and written multiple times. Find out the query statements or large transactions that have been executed for a long time by viewing the slow query log or show full processlist command

11. Design problems of master-slave replication

For example, master-slave replication single thread, if the write concurrency of the master library is too large to transfer to the slave library, it will cause delay. Later versions of Mysql can support multithreaded replication, while portals develop their own multithreaded synchronization capabilities.

twelve。 Network delay between master and slave libraries

Network devices such as network cards, network cables, switches and other network devices of the master-slave library may become the bottleneck of replication, resulting in replication delay. In addition, master-slave replication across public networks can easily lead to master-slave replication delays.

13. The main library is under great pressure to read and write, resulting in replication delay

Buffer and cache layer should be added to the front end of the architecture

Thank you for reading! This is the end of this article on "what are the inconsistencies in master-slave replication of MySQL?". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, you can share it out for more people to see!

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.