MySQL backup and recovery design ideas

background

First, let me explain the background. Due to certain restrictions, our company's current backup strategy adopts a full backup solution every other day, and the incremental backup uses the binlog server method. So how to quickly restore becomes a problem we need to think about.

Recovery needs

According to my previous experience, the scenarios where data needs to be restored from backup are usually as follows:

1. The library was deleted by mistake

2. The table was deleted by mistake, the type is TRUNCATE or DROP

3. The column was deleted by mistake, the type is ALTER ... DROP COLUMN

4. Data was accidentally deleted, the type is DELETE, UPDATE, or REPLACE

5. The table space is damaged or a bad block appears

According to the scenario, we can roughly divide it into two categories:

• The first type is irreversible recovery, which is the usual DDL, such as the above 1, 2, 3, 5 and other scenarios
• The second type is reversible recovery, which can usually be rolled back using binlog (requires binlog format to be ROW and binlog_image to be FULL), which corresponds to scenario 4 above.
  

The second type of recovery requirements are generally easier to handle. You can use binlog rollback tools, such as binlog2sql and MyFlash, which are well-known in the industry. We will not go into details here, and we will focus on the first type of requirements.

In order to achieve the goal of rapid recovery, the approach often adopted by DBAs in the industry is to deploy a delayed slave database to solve the problem. Currently, all core DBs of our company have deployed delayed slave databases. However, even with a delayed slave, if we miss the delayed time, or specify the wrong location when using the delayed slave to recover later, causing the accidentally deleted DDL to be applied to the slave as well, then we will not be able to use the delayed slave as a lifeline.

Full recovery (recovery on different machines)

At this point, we can only restore data through backup. First we need to restore the full backup, usually the physical backup backed up by xtrabackup. Assuming your backup is on a remote machine, you may need to do the following steps to perform a full backup recovery:

1. scp or rsync the backup to the target instance machine
2. If the backup file is compressed, you need to decompress it.
3. After decompression, you need to apply redo log
4. Changing File Permissions
5. Assuming you have copied the files directly to the datadir directory of the target instance, you can start mysqld directly at this step. If not, you also need to move-back or copy-back the data files to the datadir directory of the target instance.
6. Instance launch
   

Adding backup and recovery

At this point, the full backup has been restored, and the next step is incremental recovery. According to our previous backup plan, we need to use binlog to complete the recovery of incremental data. For binlog recovery, we usually need the following steps

1. Determine the binlog location corresponding to the full backup, which is the starting point that needs to be restored
2. Parse the binlog of the master database to determine the location of the accidentally deleted data as the end point of our recovery
3. Use mysqlbinlog —start-position —stop-position+pipeline to restore binlog to the target instance

There are many ways to restore binlog. You can use the binlog on the original master or the binlog on the binlogserver. All you need to do is find the end point of binlog recovery.

Backup and recovery optimization

At this point, you may think that using binlog recovery is a bit troublesome. This is indeed the case. There is no way to specify which GTID to restore to using the mysqlbinlog command. You can only parse the binlog to find the POS position corresponding to the GTID that needs to be restored, which is more troublesome to implement automatically. In addition, if you use the mysqlbinlog command to restore, it is a single-threaded recovery. If the amount of binlog that needs to be restored is relatively large, then the time for this incremental recovery can be imagined.

So is there any way to speed up binlog applications? Here we think of the parallel replication of MySQL 5.7. If we can use the parallel replication of sql thread, will this problem be solved?

Binlog recovery on master

We return to the point of full recovery, we make the new instance a slave of the original master, and then restore it to the specified GTID position? Yes, this is a very simple, easy and error-prone method, and it can also use the principle of parallel replication to speed up binlog applications. However, one requirement of this method is that the oldest binlog of the original master contains the starting recovery point we need. This is easy to think of, so this will become our preferred recovery method.

Binlog recovery on binlogserver

Assuming that the original binlog on the master has been purged, we need to restore it from the binlog. Some people may think of copying the binlog on the binlogserver to the original master, and then modifying the binlog index to achieve the purpose of registration. In fact, this is not advisable. For specific reasons, see "Manual registration of binlog files causes master-slave anomalies."

What approach can we take? It is to use binlogserver to pretend to be a master, and then change the slave library. The idea is to deceive the slave, let the slave's io_thread pull the missing binlog, and sql_thread apply the binlog event in parallel (we will demonstrate this method in detail in the next section).

Optimized recovery process

After optimization, our backup recovery process has become: first recover through the binlog on the master. If it is found that the binlog on the master has been purged, then recover through the binlog on the binlogserver. I think this is a more scientific and reasonable recovery process.

Comparison of the timeliness of various recovery methods

Business recovery

At this point, we have completed the full + incremental backup data recovery. At this time, we need to confirm the data with R&D. After confirmation, restore the corresponding table to the original master. The commonly used methods are:

1. mysqldump export + import target instance
2. Tablespace Transport
   

Summarize

This section mainly introduces the design process of backup and recovery. When we have no way to optimize full recovery, we can shorten the recovery time by optimizing the incremental backup method and process. One thing that needs to be explained is that I have not fully tested what is introduced in this section yet, and I cannot guarantee that every point is correct. Further verification is needed. After the verification is passed, I will notify you and combine it with the existing database operation and maintenance platform to achieve automatic recovery.

Finally, a few reminders:

1. Data is intangible property, so please be sure to back it up and verify it.
2. If conditions permit, try to deploy delayed slaves
3. Make a recovery plan to avoid being in a rush when recovering.
4. Choose appropriate recovery methods according to the scenario and try to shorten the recovery time

The above is the detailed content of MySQL backup and recovery design ideas. For more information about MySQL backup and recovery, please pay attention to other related articles on 123WORDPRESS.COM!