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

This article mainly explains "how to use mysql to reduce master-slave delay". The content in the article is simple and clear, easy to learn and understand. Please follow the editor's train of thought to study and learn "how to use mysql to reduce master-slave delay".

1. Optimize SQL.

Avoid slow SQL and reduce batch operations. It is recommended to write scripts in the form of update-sleep.

2. Reduce the probability of concurrency of large multithreaded transactions.

Optimize business logic

3. Add servers.

The aim is to spread the reading pressure so as to reduce the load on the server

4. Improve the configuration of the slave machine.

Reduce the poor efficiency of writing binlog and reading binlog from the master library

5. Try to use short links.

That is, the distance between the master library and the slave server is as short as possible, so as to increase the port bandwidth and reduce the network delay of binlog transmission.

6. The business read required in real time is forced to go to the master database, and the slave database only makes disaster backup.

Example

Mysql > stop slave; Query OK, 0 rows affected (0.03 sec) mysql > change master to master_delay=600; Query OK, 0 rows affected (0.01 sec) mysql > start slave Query OK 0 rows affected (0.01 sec) mysql > show slave status\ G * * 1. Row * * Slave_IO_State: Waiting for master to send event Master_Host: 172.17.61.131 Master_User: repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql_bin.000037 Read_Master_Log_Pos: 154 Relay_Log_File: slave_relay_bin.000002 Relay_Log_Pos: 320 Relay_Master_Log_File: mysql_bin.000037 Slave_IO_Running: Yes Slave_SQL_Running: Yes . Exec_Master_Log_Pos: 154 Relay_Log_Space: 527 . Master_Server_Id: 10000 Master_UUID: 8d8746fb-2cc6-11e8-b1b6-000c295c63e0 Master_Info_File: / u01/mysql/master.info SQL_Delay: 10000 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates Master_Retry_Count: 86400. 1 row in set (0.00 sec) Thank you for your reading, the above is the content of "how to use mysql to reduce master-slave delay". After the study of this article, I believe you have a deeper understanding of how to use mysql to reduce master-slave delay. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

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.