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

This article introduces the knowledge of "the reason for the delay of MySQL replication from the library". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

I. delay in copying from the library

1. The possible reasons are as follows

(1) Master and slave servers are in different networks, which is caused by network delay

(2) the hardware configuration of the master-slave server is different, and the hardware configuration of the slave server (including memory, CPU, network card, etc.) is much lower than that of the master server.

(3) there are a large number of write operations on the master database, so that the slave database cannot replay the binlog on the master library in real time.

(4) there are large transaction operations or slow SQL on the master database, which leads to a delay in the process of applying binlog to the master library.

(5) the parameter configuration of the database instance causes problems, such as enabling binlog from the database, or configuring each transaction to perform flushing operations.

2. Judgment of master-slave synchronization delay

(1) judge according to the state parameters on the slave database

Mysql-server-3307 > SHOW SLAVE STATUS\ G

The Seconds_Behind_Master parameter is found in the output result, which represents the time difference between the IO thread and SQL thread on the library. According to the value of this parameter, this value is only a preliminary judgment, and the conclusion cannot be drawn from this value. There are several situations as follows:

A, 0: no delay, ideal state

B, NULL: indicates that there is a problem with one of the IO and SQL threads on the library. You can check again whether the values of Slave_IO_Running and Slave_SQL_Running are both Yes.

C, greater than 0: indicates that there has been a delay between the master and slave. The higher the value, the more serious the delay between the slave library and the master library.

D, less than 0: this value is not specified in the official documentation and usually does not appear. If so, congratulations on winning the lottery and bumping into MySQL's bug.

(2) judge according to the name of the binary log file currently applied above the master-slave library or the location of the replay log.

① simultaneously opens two command line windows of MySQL, the master library and the slave library, respectively, and executes the command to view the current status of the master library on the first window.

Mysql-server-3306 > SHOW MASTER STATUS\ gateway * 1. Row * * File: mysql-bin.000017 Position: 120 Binlog_Do_DB: Binlog_Ignore_DB: Executed_Gtid_Set: 1 row in set (0.00 sec)

② executes the following command in the second command line window of the slave library

Mysql-server-3307 > SHOW SLAVE STATUS\ gateway * 1. Row * * Slave_IO_State: Waiting for master to send event. Connect_Retry: 60 Master_Log_File: mysql-bin.000017 Read_Master_Log_Pos: 120 Relay_Log_File: relay-log.000016 Relay_Log_Pos: 283 Relay_Master_Log_File: mysql-bin.000017 Slave_IO_Running: Yes Slave_SQL_Running: Yes... Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 120 Relay_Log_Space: 613 Until_Condition: None Until_Log_File: Until_Log_Pos: 0... Seconds_Behind_Master: 0... Replicate_Ignore_Server_Ids: Master_Server_Id: 3 Master_UUID: 2dbbf79b-5d9f-11e8-8004-000c29e28409 Master_Info_File: / mysql_data/3307/data/master.info SQL_Delay: 0 SQL_Remaining_Delay: NULL

③ compares whether there are differences between the Master_Log_File and Relay_Master_Log_File files on the slave library

A. if there is a difference, it means that the master-slave delay is very serious.

B. If there is no difference, compare the difference between Read_Master_Log_Pos and Exec_Master_Log_Pos. These two parameters represent the location point of the binary log file currently read from the library and the location point that has been executed.

C, if there is no difference in the above output, you can compare the results of "show master status" on the main library and "show slave status" on the slave library. Mainly compare "File" of master library with "Master_Log_File" of slave library, "Position" on master library and "Read_Master_Log_Pos" on slave library.

3. Master-slave delay solution

(1) determine whether it is caused by the network.

Method: test the network latency between master and slave libraries, such as testing ping latency. At the same time, you can check whether master-slave synchronization uses the domain name of the master library for synchronization, and the speed of domain name resolution may be particularly slow. Or use other testing tools

(2) determine whether it is caused by the hardware environment.

Method: confirm whether the hardware configuration of the master-slave library is quite different. If the configuration parameters are different, you can check the utilization rate of CPU, memory and IO on the slave library to determine whether it is caused by the hardware configuration.

(3) determine whether there are a large number of DML operations on the main database.

Method: you can check the currently executing sql through the "show full processlist" command on the main library to see if there are a large number of executing SQL, or observe the CPU and memory utilization of the main library to determine whether there are high concurrency operations.

(4) to determine whether there is a slow SQl, you can temporarily open the slow SQL record on the main library. The temporary opening method is as follows.

# enable the slow SQL feature and check whether it takes effect mysql-server-3306 > SET @ @ GLOBAL.slow_query_log = ON;mysql-server-3306 > SHOW VARIABLES LIKE 'slow_query_log';# sets the time of slow SQL and checks whether it takes effect. In s, it indicates how many seconds of SQL will be recorded, mysql-server-3306 > SET @ @ GLOBAL.long_query_time = 5MySQL @ @ SHOW VARIABLES LIKE 3306 > SHOW VARIABLES LIKE' long_query_time' # set the slow SQL log path and check whether it takes effect. Note that this directory must have read and write access to the MySQL user mysql-server-3306 > SET @ @ GLOBAL.slow_query_log_file ='/ mysql_data/mysql-slow.log';mysql-server-3306 > SHOW VARIABLES LIKE 'slow_query_log_file'

(5) check whether the slave server parameter configuration is reasonable.

① to check whether the binlog log is enabled from the slave library, and execute the following command from the library to check

Mysql-server-3307 > SHOW VARIABLES LIKE 'log_bin'

If the binlog log is turned on, and the slave library does not act as the master library of other libraries, you can close the binlog on the slave library, otherwise it will increase the burden of the slave library. Every time you replay the binlog of the master library, you have to record its own binlog.

② looks at the value of the sync_binlog parameter on the slave library. This parameter indicates how many times the transaction has been committed, and then MySQL flushes the data in binlog_cache to disk. There are several values:

0: indicates that after the transaction is committed, MySQL does not refresh the binlog_cache to disk, but the operating system automatically completes the flushing operation on a regular basis. This operation has the least performance loss, but is also the least secure.

N: it means that after committing n transactions, MySQL will flush the data in binlog_cache to disk. If it is enabled, there will be a certain loss of performance. Therefore, if the delay is serious from the library, you can consider setting the value of this parameter to 0

Mysql-server-3307 > SET @ @ GLOBAL.sync_binlog = 0 *

③ if the database you want to synchronize from the library is using the InnoDB storage engine, you can view the innodb_flush_log_at_trx_commit parameter. This parameter indicates how often the log is refreshed to disk after the transaction is completed. The following values are available:

0: indicates that MySQL writes the data in the log buffer to the log file once a second, and the refresh operation of the log file occurs at the same time. In this mode, when the transaction commits, the operation of writing to disk will not be triggered actively, which is the most efficient, but the security is also relatively low, and data may be lost.

1: every transaction commit needs to write the log to disk, which is a very time-consuming operation

2: after each transaction commit, the operation of flushing the log will not be triggered automatically, but it is up to the operating system to decide when to refresh the log, and the data will be lost only when the operating system dies.

If the master-slave delay is very serious, you can set this parameter of the slave library to 0 to improve the efficiency of replaying the master library binary logs from the library.

Mysql-server-3307 > SET @ @ GLOBAL.innodb_flush_log_at_trx_commit = 0 * +-- +-+ | Variable_name | Value | +-+-+ | innodb_flush_log_at_trx_commit | 0 | +- -+-+ 1 row in set (0.00 sec)

Note: when the above design is used to modify the MySQL database instance, the modification will take effect immediately, but it will become invalid after restarting the instance. If you want to modify it permanently, you need to edit the mysql configuration file and then restart it.

This is the end of the content of "reasons for the delay in MySQL replication from the library". Thank you for reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

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.