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,
Hikvision - Embedded software written test questions C Language application 0 The length of array in memory and structure is 0 In depth analysis data structure --- The preorder of binary tree , Middle order , Subsequent traversal How to do it ipad Transfer of medium and super large files to computer elementui Shuttle box el-transfer Display list content text too long 2019 The 10th Blue Bridge Cup C/C++ A Summary after the National Games ( Beijing Tourism summary )unity Shooting games , Implementation of first person camera python of numpy Module detailed explanation and application case Study notes 【STM32】 Digital steering gear Horizontal and vertical linkage pan tilt Vue Used in Element Open for the first time el-dialog Solution for not getting element