MySQL Master never synchronous delay principle analysis and solution

1. Latency of the network
since MySQL master-slave replication is based on an asynchronous replication of Binlog, transmission of binlog files over the network, it is natural that the network delay is the main reason for the majority of the synchronization, especially across the computer room data synchronization occurs very large, so do read and write separation, Note the upfront 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.
 Solution: 

method One: After ignoring the error, continue syncingThis method is applicable to master-slave database data is not small, or require data can not be completely unified situation, the data requirements are not strict: stop slave; #表示跳过一步错误, the following number variable set global sql_slave_skip_counter =1;start slave; Then use mysql> show slave status\g view: Slave_IO_Running:YesSlave_SQL_Running:Yes OK, now the master-slave synchronization state is normal ... mode two: Re-master from, fully synchronizedThis method is suitable for the master-slave database data is large difference, or requires the data to be fully unified   the steps are as follows:  1. Advanced Library, lock table, prevent data write   use command: mysql> flush tables with Read lock;  Note: This is locked as a read-only state, and the statement is case-insensitive  2. Data backup    #把数据备份到mysql. bak.sql file [[email protected] MySQL] #mysqldump-uroot-p-hlocalhost > Mysql.bak.sql Note here: Database backups must be done on a regular basis, with shell scripts or Python scripts that are convenient to ensure data is foolproof 3. View master Status  mysql> show Master status;+-------------------+----------+--------------+-------------------------------+| File              | Position | binlog_do_db | binlog_ignore_db              |+-------------------+----------+--------------+---- ---------------------------+| mysqld-bin.000001 |     3260 |              | Mysql,test,information_schema |+-------------------+----------+--------------+-------------------------------+1 Row in Set (0.00 sec)  4. Upload MySQL backup files from the library machine for data recovery   #使用scp命令 [[email protected] mysql]# SCP mysqL.bak.sql [email protected]:/tmp/ 5. Stop mysql> stop slave;  6 from the library's state. Then to the MySQL command from the library, import the data backup  mysql> source/tmp/mysql.bak.sql 7. Settings to sync from the library, note that the synchronization point is in the main library show Master status information | file| Position two items  change master to Master_host = ' 192.168.128.100 ', master_user = ' rsync ', master_port=3306, Master_ Password= ', master_log_file = ' mysqld-bin.000001 ', master_log_pos=3260; 8. Re-open from Sync mysql> start slave;  9. View sync status mysql> show slave status\g   View:  slave_io_running:yesslave_sql_running:yes

MySQL Master never sync delay principle analysis and solution