Shulou(Shulou.com)06/01 Report--

This article mainly introduces the relevant knowledge of what the concept of mysql master-slave delay is, the content is detailed and easy to understand, the operation is simple and fast, and has a certain reference value. I believe you will gain something after reading this mysql master-slave delay concept. Let's take a look at it.

MySQL's built-in replication capabilities are the basis for building large, high-performance applications. To distribute the data of MySQL to multiple systems, this distributed mechanism is realized by copying the data of one host of MySQL to another host slaves and re-executing it.

During replication, one server acts as the master server, while one or more other servers act as the slave server. The primary server writes updates to the binary log file and maintains an index of the file to track the log cycle. These logs can record updates sent to the slave server. When a slave server connects to the master server, it notifies the master server of the location of the last successful update read by the slave server in the log. Receive any updates that have occurred since then from the server, then block and wait for the master server to notify the update. Problems with mysql master-slave replication:

After the master database goes down, the data may be lost. There is only one sql Thread in the slave database. The master database is under great pressure to write, and replication is likely to be delayed.

Here are four convenient discussions on how to solve the mysql master-slave delay

The principle of master-slave synchronization delay in 1.MySQL database.

A: when it comes to the principle of master-slave synchronization delay in MySQL database, we should start with the master-slave replication principle of mysql. The master-slave replication of mysql is a single-thread operation, and the master database generates binlog,binlog sequentially for all DDL and DML, so it is very efficient; the Slave_IO_Running thread of slave will go to the master database to get logs, which is more efficient. Slave's Slave_SQL_ running thread implements both DDL and DML operations of the master database in slave. The IO operations of DML and DDL are random, not sequential, so the cost will be very high, and it is also possible that other queries on slave cause lock contention. Because Slave_SQL_Running is also single-threaded, a DDL card owner needs to execute for 10 minutes, so all subsequent DDL will wait for the DDL to finish execution before continuing to execute, which leads to the delay. A friend will ask: "the same DDL on the main library also needs to execute 10 points. Why is the slave delayed?" The answer is that master can be concurrent, but Slave_SQL_ running threads cannot.

How the master-slave synchronization delay of 2.MySQL database is generated.

A: when the TPS concurrency of the main library is high, the number of DDL generated is more than a sql thread of slave can bear, then the delay occurs, and of course, lock waiting may occur with the large query statements of slave.

Master-Slave synchronization delay solution for 3.MySQL Database

A: the simplest way to reduce slave synchronization latency is to optimize the architecture so that the DDL of the main library can be executed as quickly as possible. In addition, the main library is write, which is highly secure for data, such as sync_binlog=1,innodb_flush_log_at_trx_commit = 1, while slave does not need such high data security. You can set sync_binlog to 0 or close binlog,innodb_flushlog to 0 to improve the efficiency of sql execution. The other thing is to use a better hardware device than the main library as the slave.

The factors caused by the master-slave synchronization delay of 4.MySQL database.

1. Network delay

2. Master load

3. The general practice of slave load is to use multiple slave to share read requests, and then take a dedicated server from these slave for backup only, without any other operation, you can achieve the real-time requirements to the maximum extent.

In addition, two more parameters that can reduce latency are introduced:

Slave-net-timeout=seconds parameter meaning: when slave fails to read log data from the master database, how long to wait to re-establish the connection and obtain the data? the slave_net_timeout unit is set to 3600 seconds by default. | slave_net_timeout | 3600

Master-connect-retry=seconds parameter meaning: when the master-slave connection is re-established, if the connection establishment fails, how long will the interval be before retrying. The master-connect-retry unit is set to 60 seconds by default. Generally, configuring the above two parameters can reduce the master-slave data synchronization delay caused by network problems.

This is the end of the article on "what is the concept of master-slave delay in mysql". Thank you for reading! I believe you all have a certain understanding of "what is the concept of master-slave delay in mysql". If you want to learn more, you are welcome to follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.