The Problem with MySQL Master Slave Databases Who triggers whom?

It seems that there is something called triggers in the DB, and there seems to be another thing called transactions.

Due to various reasons, data inconsistencies often occur in the MySQL master-slave architecture, which can be roughly summarized into the following categories:

1: Write data to the standby database.

2: Execute a non-deterministic query

3: Roll back transactions that are doped with transaction tables and non-transaction tables.

4: The data in the binary log or relay log is corrupted.

When there is a situation where the master-slave data is inconsistent, the common response is to take the slave database offline, and then find a time to stop the application in the middle of the night and re-execute the synchronization. This topic describes how to use the percona-toolkit tool to check and resynchronize the synchronization status of a MySQL master-slave database.

Part 1: Install percona-toolkit

2. Change the binlog format of MySQL to the row format.

MySQL binlog logs are available in three formats: statement, mixed, and row!

1.Latency of the network.

Since MySQL master-slave replication is an asynchronous replication based on binlog, and binlog files are transmitted over the network, it is natural that network latency is the vast majority of the reasons for master-slave non-synchronization, especially the probability of cross-computer room data synchronization is very large.

2.The load of the master and slave machines is inconsistent.

Since MySQL master-slave replication starts one I/O thread on the master database, and one SQL thread and one I/O thread are started from above, the load on any one of the machines is high and it is too busy, resulting in insufficient resources for any of the threads, and master-slave inconsistencies will occur.

The settings are inconsistent.

The max allowed packet set on the master database is larger than that of the slave database, and when a large SQL statement can be executed on the master database, the setting on the primary database is too small to be executed, resulting in master-slave inconsistency.

The master-slave inconsistency caused by the inconsistency between the key value at the beginning of the auto-increment key and the auto-increment step setting.

If sync binlog=1 or innodb flush log at trx commit=1 is not set, the binlog or relaylog file may be corrupted, resulting in inconsistencies between master and slave.

The master-slave is not synchronized due to the bug itself.

7.If the version is inconsistent, especially if the higher version is the master and the lower version is the slave, the function supported on the master database does not support the function on the slave database.

When the master of the main database is shut down, the data can only be read but not written, and after the master of the library is shut down, the data can still be read and written, which is not easy to do, if you know something about lua scripts, you may be able to modify the read/write splitting script of mysql-proxy. However, the primary database cannot be accessed when it is shut down, and the slave database can still be read and written, which does not need to be equipped with read/write separation.

The synchronization of mysql is not very stable, and I have done it before, and there is no problem with ordinary insertion and deletion, but if you add a crab hair device, it will fail. After the primary server is started, the secondary server will be synchronized again after a restart, and the main log-bin information will be read.