Summary of the benefits of deploying MySQL delayed slaves

Preface

The master-slave replication relationship of MySQL is less rigorously called "synchronization" or "master-slave synchronization". In fact, in the early days, the master and slave of MySQL could not achieve true "synchronization" (sync), but "asynchronous" (async).

MySQL master-slave replication can have multiple modes. The most classic and earliest one is asynchronous replication (async replication). Since version 5.5, semi-synchronous replication (semi-sync replication) has been available, and in 5.7, enhanced semi-synchronous replication has been available. The delayed slave library discussed in this article is also a feature available after 5.6. Before that, it needs to be implemented in disguise using the percona pt-slave-delay tool.

In addition, parallel replication has been added since version 5.6, but it is still based on the schema-based parallel mode (slave-parallel-type=DATABASE), which is very inefficient and meaningless. In 5.7, true parallel replication (slave-parallel-type=LOGICAL_CLOCK) was implemented, which greatly improved the replication efficiency; multi-source replication was also added, which made it very convenient to implement a multi-master and one-slave architecture.

After understanding the brief history of MySQL replication, let's get to the point.

The benefits of MySQL delayed slaves are as follows:

1. When data is deleted by mistake, it can be restored faster.

Sometimes, due to hand shaking, online data is deleted by mistake, or the database, table, or other objects are deleted by mistake, or updates or deletions are performed without adding WHERE conditions. All of these can make the delayed slave database stop at the time before the wrong operation, and then recover.

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

3. You can also use the delay library as the object of some problems and case studies. Sometimes, some binlog events may cause problems on ordinary slaves (for example, the classic problem in early versions where no primary key causes very slow updates on slaves). In this case, you have time to slowly study and research on delayed slaves.

The method of enabling delayed slave is also very simple. Here is how to do it in MySQL 8.0:

#Directly use CHANGE MASTER TO to set, the following N unit is seconds CHANGE MASTER TO MASTER_DELAY = N

When an error occurs and the delayed slave library needs to be stopped at a certain position, use the following command:

START SLAVE
UNTIL {
#1. Stop until the specified GTID position {SQL_BEFORE_GTIDS | SQL_AFTER_GTIDS} = gtid_set

#2. Stop until 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 between multiple parallel threads on the slave. #Because of multi-threaded replication, the replication progress of different threads is different, so there is a gap | SQL_AFTER_MTS_GAPS }

PS, starting from 5.7, modifying the MASTER_DELAY option can take effect immediately online without restarting the slave thread.

As for the specific MASTER_DELAY setting, you need to estimate the average time it takes the DBA to arrive on site if an error occurs. Generally, about 1 hour is recommended.

Summarize

The above is the full content of this article. I hope that the content of this article will have certain reference learning value for your study or work. Thank you for your support of 123WORDPRESS.COM.