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

Let me tell you a little bit about how mysql tries to avoid master-slave replication delay. Have you ever heard of similar topics before? If you are interested, let's take a look at this article. I believe it will be helpful for everyone to read how to avoid master-slave replication delay as much as possible after reading mysql.

If the delay is large, identify the following factors first:

1. The hardware of the slave library is worse than the master library, resulting in replication delay

two。 Master-slave replication single thread, if the master library write concurrency is too large, too late to transfer to the slave library, it will cause delay. Later versions of mysql can support multithreaded replication

3. Too many slow SQL statements

4. Network delay

5. Master load

The main library has great read and write pressure, which leads to replication delay. Buffer and cache layer should be added to the front end of the architecture.

6. Slave load

In general, multiple slave are used to apportion read requests, and then a dedicated CVM is taken from these slave for backup purposes only, without any other operations.

In addition, there are two parameters that can reduce latency:

-slave-net-timeout=seconds unit is seconds. Default is 3600 seconds.

# Parameter meaning: when slave fails to read log data from the master database, how long does it take to re-establish the connection and get the data?

-master-connect-retry=seconds unit is set to 60 seconds by default

# Parameter meaning: when the master-slave connection is re-established, how long will the interval be before retrying if the connection establishment fails.

Usually, configuring the above two parameters can reduce the master-slave data synchronization delay caused by network problems.

Master-Slave synchronization delay solution for MySQL Database

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.

What do you think of mysql how to avoid master-slave replication delay as far as possible? what do you think of this article and whether it has gained anything? If you want to know more about it, you can continue to follow our industry information section.

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.