"Go" to resolve MySQL master-slave replication inconsistencies

Troubleshoot MySQL master-slave replication inconsistencies October (NO COMMENTS)

Slightly some of the size of the site, basically will be configured MySQL master-slave replication, on the one hand, MySQL master and slave to do the database read and write separation, on the other hand MySQL itself is not very strong single-machine backup, generally use the master-slave architecture, in the data backup from the.

In the MySQL master-slave replication process more or less in the case of some master never synchronized, this article will be the data master from the situation of the synchronization of a simple summary, please note that this article mainly from the database level to explore the database of the main never consistent situation.

1. Latency of the network

Since MySQL master-slave replication is based on a binlog asynchronous replication, transmission of Binlog files over the network, it is natural that network delay is the main reason for the majority of the synchronization, especially across the computer room data synchronization is very large, so do read and write separation, pay attention to the early design from the business layer.

2. Load inconsistency between master and slave machines

Since MySQL master-slave replication is the primary database that starts 1 IO threads, and starts 1 SQL threads and an IO thread from above, any one of the machines has a high load and is not busy, causing any one of these threads to be out of resources, and there will be a situation where the master is never consistent.

3.max_allowed_packet setting inconsistencies

The main database set above the Max_allowed_packet than from the database, when a large SQL statement, can be executed on the main database above, set too small from the database, can not be executed, resulting in the Lord never consistent.

The key value starting with the 4.key self-increment key is inconsistent with the self-increment step setting.

5.mysql abnormal downtime, if the sync_binlog=1 is not set or innodb_flush_log_at_trx_commit=1 is likely to occur binlog or relaylog file corruption, causing the master to never agree.

A bug in the 6.mysql itself causes the master to never sync.

7. Inconsistent version, especially the high version is the main, the lower version is from the case of the primary database above the supported features, from the database above does not support this feature.

These are some of the common situations where the master never synchronizes. Perhaps there are other situations that are out of sync, please tell us what you are experiencing that the Lord is never consistent.

Based on the above situation, the first to ensure that the max_allowed_packet, self-increment key start point and growth points set consistent, and the sacrifice of some performance in the main above the Sync_binlog, for the use of InnoDB library, the recommended configuration of the following content

1, Innodb_flush_logs_at_trx_commit = 1
2, Innodb-support_xa = 1 # Mysql 5.0 or more
3, Innodb_safe_binlog # Mysql 4.0

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

1, Skip_slave_start
2, Read_Only

Original source http://www.frostsky.com/2011/10/mysql-master-slave-tongbu/

"Go" to resolve MySQL master-slave replication inconsistencies