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
©2019-2020 Toolsou All rights reserved,
c Language implementation 《 Student management system 》 No hole is the future of mobile phone ? There are still many problems to be solved Junior , A little sense , Just for mutual encouragement How to use Vue Used in Echarts Visual Library The 11th Blue Bridge Cup Java The second provincial competition B Group part Python- be based on OpenCV Contour fill for flooding algorithm hole filling 【C#】 The realization of student achievement information management system List Common interview questions in the collection and simple ideas China Mobile Science Popularization : Why do mobile networks call “ Cellular mobile network ”【Golang Basic series 10 】Go language On conditional sentences if