MySQL What causes inconsistency in master-slave replication , How to prevent and solve ? one , The main reasons for the inconsistency are as follows ： Data inconsistency between slave database and master database caused by human factors （ Write from library ）
During master-slave replication , Abnormal outage of main database Set up ignore/do/rewrite etc. replication Equal rules binlog wrong row format
Asynchronous replication itself is not guaranteed , There is a problem of commit read in semi synchronization , Enhanced semi synchronization is perfect . But for abnormal restart （Replication Crash
Safe）, Write data from library （GTID） How to prevent , Strategy is also needed to ensure . A long break from the library ,binlog Application discontinuity , Monitor and repair master and slave in time
The slave library has stored procedures enabled , Disable stored procedures from libraries, etc Database size version / Branch version causes data inconsistency ? Master slave version unification No parameters were specified during backup for example mysqldump
--master-data=2 etc. Master and slave sql_mode atypism One master and two slaves , Secondary server id agreement MySQL Autoincrement column Inconsistency between master and slave
Master slave information is stored in a file , The refresh of the file itself is non transactional , As a result, the starting execution point after the restart of the library is larger than the actual execution point
use 5.6 Of after_commit Mode semi synchronous , When the master database crashes, it may cause inconsistency between master and slave , To see binlog Is it transferred to the slave library
Enhanced semi synchronization enabled （5.7 Of after_sync mode ）, However, the delay timeout from the library automatically switches to asynchronous replication two , Prevention and solutions are ：
master:innodb_flush_log_at_trx_commit=1&sync_binlog=1
slave:master_info_repository="TABLE"&relay_log_info_repository="TABLE"&relay_log_recovery=1
Set slave library to read only mode have access to 5.7 Enhance semi synchronization to avoid data loss binlog
Technology
Daily Recommendation