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

The following mainly brings you the delay and solutions of mysql master-slave synchronization. I hope these contents can bring you practical use, which is also the main purpose of my article on why mysql master-slave synchronization is delayed and solved. All right, don't talk too much nonsense, let's just read the following.

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

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

(1) 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.

(2) the other thing is to use a better hardware device than the main library as the slave.

That is, a server from the CVM is used as a backup instead of providing a query. When its load comes down there, it is naturally more efficient to execute the SQL in relay log.

(3) to increase the load from the CVM, the purpose is to disperse the reading pressure, so as to reduce the load on the CVM.

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 CVM from these slave for backup only, without any other operation, you can relatively maximize the requirements of 'real-time'.

In addition, introduce two parameters that can reduce latency-slave-net-timeout=seconds parameter meaning: when slave failed to read log data from the master database, how long to wait to re-establish the connection and obtain data slave_net_timeout unit is set to 3600 seconds by default slave_net_timeout 3600-master-connect-retry=seconds parameter meaning: when re-establishing a master-slave connection, if the connection establishment fails, how long will the interval be before retry. 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.

For the above about the mysql master-slave synchronization why the delay and solutions, we do not find it very helpful. If you need to know more, please continue to follow our industry information. I'm sure you'll like it.

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.