How to solve mysql master-slave delay

MySQL's built-in replication capabilities are the foundation for building large, high-performance applications. Distribute MySQL data to multiple systems. This distributed mechanism is achieved by copying the data of a certain MySQL host to other host slaves and re-executing it.
During replication, one server acts as the master and one or more other servers act as slaves. The master writes updates to binary log files and maintains an index of the files to track log rotation. These logs record updates sent to slave servers. When a slave connects to the master, it notifies the master of the location of the last successful update the slave read in the log. The slave server receives any updates that have occurred since then, then blocks and waits for the master server to notify them of updates.
Mysql master-slave replication problems:

• After the main database goes down, data may be lost
• There is only one sql Thread in the slave library, and the main library is under great writing pressure, so replication is likely to be delayed.

The following is a discussion on how to solve the mysql master-slave delay from four aspects

1. MySQL database master-slave synchronization delay principle.

Answer: When talking about the principle of master-slave synchronization delay in MySQL database, we have to start with the principle of mysql database master-slave replication. The master-slave replication of mysql is a single-threaded operation. The main database generates binlog and binlog for all DDL and DML. It is written sequentially, so the efficiency is very high; the slave's Slave_IO_Running thread will go to the main library to get the log, and the efficiency will be relatively high. The slave's Slave_SQL_Running thread will implement all the DDL and DML operations of the main library on the slave. The IO operations of DML and DDL are random, not sequential, so the cost will be very high. Other queries on the slave may also cause lock contention. Since Slave_SQL_Running is also single-threaded, a DDL card master needs to execute 10 times. minutes, then all subsequent DDL will wait for this DDL to finish executing before continuing, which leads to delays. Some friends will ask: "The same DDL on the main library also needs to be executed for 10 minutes. Why is the slave delayed?" The answer is that the master can run concurrently, but the Slave_SQL_Running thread cannot.

2. How does the master-slave synchronization delay in the MySQL database occur?

Answer: When the TPS concurrency of the main library is high, the number of DDL generated exceeds the range that one SQL thread of the slave can bear, and then delay occurs. Of course, there may also be a lock with the slave's large query statement. wait.

3.MySQL database master-slave synchronization delay solution

Answer: The simplest solution to reduce slave synchronization delay is to optimize the architecture and try to make the DDL of the main library execute quickly. There is also the fact that the main library is written, which has high data security, such as sync_binlog=1, innodb_flush_log_at_trx_commit = 1 and other settings. However, the slave does not need such high data security. You can set sync_binlog to 0 or turn off binlog. innodb_flushlog can also be set to 0 to improve SQL execution efficiency. The other is to use a better hardware device than the main library as a slave.

4. Factors causing delay in master-slave synchronization of MySQL database.

1. Network delay
2. master load
3. slave load
The general approach is to use multiple slaves to distribute read requests, and then use a dedicated server from these slaves only for backup without any other operations, so that the 'real-time' requirement can be achieved to the maximum extent.

In addition, introduce two more parameters that can reduce delay
–slave-net-timeout=seconds
Parameter meaning: When the slave fails to read log data from the main database, how long to wait to re-establish the connection and obtain the data
The unit of slave_net_timeout is seconds. The default setting is 3600 seconds
| slave_net_timeout | 3600
–master-connect-retry=seconds
Parameter meaning: When re-establishing the master-slave connection, if the connection fails to be established, how long will it take to retry.
The unit of master-connect-retry is seconds. The default setting is 60 seconds
Usually configuring the above two parameters can reduce the master-slave data synchronization delay caused by network problems

The above is the detailed content of How to solve mysql master-slave delay. For more information, please follow other related articles on the PHP Chinese website!