Shulou(Shulou.com)05/31 Report--

In this issue, Xiaobian will bring you the benefits of deploying delayed slave libraries in MySQL. The article is rich in content and analyzed and described from a professional perspective. After reading this article, I hope you can gain something.

The main benefits of MySQL deferred from the library are:

1. When deleted by mistake, data can be recovered faster.

Sometimes the hand shakes, the online data is deleted by mistake, or the library, table, other objects are deleted by mistake, or the update and deletion without the WHERE condition can stop the delay from the library at the time point before the wrong operation, and then recover.

2. Take the delayed slave library as a dedicated backup node. Although there is some delay, it does not affect the utilization of the node as a backup role, nor does it affect the production node database.

3. You can also regard delayed slave libraries as objects of some problems and case studies. Occasionally, there may be binlog events that have problems with regular slaves (e.g., classic problems where no primary key in earlier versions causes slaves to update very slowly), and there will be time to slowly study them on delayed slaves.

The method to enable deferred slave libraries is also quite simple. Here is how to do it in MySQL 8.0:

Change MASTER TO_DELAY = N

When a malfunction occurs and the delay needs to be stopped at a certain position from the library, use the following command:

START SLAVEUNTIL {#1, Stop until specified GTID position {SQL_BEFORE_GTIDS| SQL_AFTER_GTIDS} = gtid_set#2. Stop at the specified binlog position| MASTER_LOG_FILE = 'log_name', MASTER_LOG_POS = log_pos#3. Stop until the specified relay log position| RELAY_LOG_FILE = 'log_name', RELAY_LOG_POS = log_pos#4. Stop until there is no delay gap before multiple parallel threads on slave #Because multithreaded replication, the replication progress of different threads is different, so there is a gap| SQL_AFTER_MTS_GAPS }

P.S. From 5.7, modifying the MASTER_DELAY option can take effect immediately online without restarting the slave thread.

As for how appropriate the specific MASTER_DELAY setting is, it is generally recommended to estimate the average time that DBA can arrive at the site in case of misoperation.

The above is what are the benefits of deploying delayed slave libraries in MySQL shared by Xiaobian. If you happen to have similar doubts, you may wish to refer to the above analysis for understanding. If you want to know more about it, please pay attention to 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.