Mysql database recovery actual record by time point

Introduction: MySQL database recovery by time point

For any enterprise, data is the most valuable asset.

How to protect data integrity, prevent data from being damaged, how to keep data in the event of a failure, and how to restore data based on our backups in the event of misoperation, hacker intrusion, data tampering, etc. are key points that every technician needs to pay attention to.

Alibaba Cloud is committed to serving its customers and providing continuous data protection and low-cost backup services for customer databases. It can provide strong protection and powerful recovery for data in various environments. In extreme cases of data loss or data corruption, the RDS management and control platform has a one-click restore function, which can perform comprehensive data recovery based on the time point set by the customer.

​​1. Technical implementation of point-in-time recovery​

If a customer loses data due to an erroneous operation at a certain point in time, how does the RDS management service recover it?

The overall idea of ​​point-in-time recovery is as follows: a complete data recovery consists of physical backup + binlog recovery + binlog pruning.

Figure 1

First, obtain the available backup set, apply the backup set to the target instance, then replay the binlog file that needs to be restored on the target instance, and finally apply the SQL file in the form of binlog trimming to achieve overall recovery.

2. Management and control process for point-in-time recovery

1. Create an instance for recovery

When we need to restore the source database data as a whole, we first need to create a target instance with the same specifications and network environment as the source instance.

Why do this?

Because backup and recovery are high-risk operations, if you restore directly to the source instance, once problems such as the backup set being unavailable or binlog missing occur, not only will the lost data be irrecoverable, but even the original data may not be preserved intact. Therefore, it is strongly recommended to use a new instance for recovery!

2. Specify the backup and recovery time points

When a customer performs a series of database operations, such as accidental deletion or modification, without any awareness of the operation, and when the business is damaged or a failure occurs, how can the exact time of the operation be located for data recovery?

Method 1: You can use the log audit function to find the corresponding erroneous operation time point.

Method 2: You can parse the binlog into text and query the corresponding error operation time point.

3. Get available backup sets through backup history

Generally speaking, based on the importance of the business, customers will plan their own database backup cycles on the cloud, and RDS management will automatically search for available physical backup sets based on the recovery time point selected by the user.

It can be seen that backup is of paramount importance for high availability and disaster recovery of databases!

4. Get the binlog point corresponding to the backup set

The backup of private clouds is generally based on the xtrabackup tool. Xtrabackup has the characteristics of hot backup and fast recovery. At the same time, it will write the files and points of the binlog applied at the end of the backup into the corresponding files. RDS control will write the binlogfile , binlogpos and other information into the database. When backup and recovery are required, the point will be directly obtained for recovery.

As shown in the following figure:

Figure 2

5. Restore the backup set to the destination instance

Steps 1-4 are preparations. Now we will start to recover the data. The first step in restoring data is to download the available full physical backup set to the destination instance and restore it using the xtrabackup tool.

//​​First, stop the mysql process on the target instance​

​systemctl stop mysql​

//​​Then merge the data. Assuming that the backup is unzipped in the /root/backup/ directory, you can specify the instance port to be restored. You need to add the --defaults-file parameter to specify it. The default is 3306.​

innobackupex --apply-log /root/backup/

​//​​Delete the original directory file​

rm -rf /data/mysql

//Restore the data set. The directory to which the data is restored is determined by the datadir in the configuration file my.cnf. This field must be checked for accuracy.

innobackupex --copy-back /root/backup/

​//​​Directory authorization​

​chown ​​-​​R mysql:mysql ​​/​​data​​/​​mysql

6. Verify that the restore was successful

The management service needs to verify whether the restoration is successful before deciding whether to proceed. The verification steps are also very simple and crude. Just check whether there is an ERROR in the backup recovery log and whether the last line is completed OK!

The following figure shows a successful backup and recovery.

Figure 3

7. Get binlog log for recovery

This step is critical to the success of the recovery and the integrity of the data.

So how does the RDS management service obtain the correct binlog for recovery? Let’s look at the picture below.

Figure 4

For example, there are a total of 8 binlog backups (000-008) in our current backup. First, get the first binlog through the filename and pos of the binlog recorded in the physical backup, such as binlog004 in the above figure; then find the corresponding last binlog through the timestamp of the time point set by the customer to be restored, such as binlog007 in the above figure; finally, download the four binlog backups binlog004, binlog005, binlog006, and binlog007 to the destination instance for recovery.

If the wrong binlog is obtained for recovery, for example, if binlog003/binlog005 is mistakenly set as the first binlog, the DML statements executed on binlog003/binlog005 will be re-executed on the new instance, and the recovered data will increase or be missing. For example, if binlog0006 or binlog0008 is mistakenly set as the last binlog, the recovered data will be missing and the expected effect cannot be achieved.

8. Replay relaylog

Copy the downloaded binlog to the logdir of the new instance, rename the binlogs except the last binlog (the binlog covering the recovery time point) to relaylog , and then replay these relaylogs using the new instance.

​//​​Rename binlog, the relaylog file name can be viewed by executing show variables like '%relay%' in the mysql instance.​

​rename mysql​​-​​bin MySQL2​​-​​relay​​-​​bin mysql​​-​​bin​​*​

​//​​Initialize relay information into index file​

​ls .​​/​​MySQL2​​-​​relay​​-​​bin.​​0000​​*​​​​>​​MySQL2​​-​​relay​​-​​bin.index​

​//​​Copy these files into the data file​

​cp MySQL2-relay-bin.*/data/mysql/​

​//​​File authorization​

​chown ​​-​​R mysql:mysql ​​/​​data​​/​​mysql​

​//​​Start the mysql instance​

​systemctl start mysql​

//Change master to a non-existent instance, simulate this instance as a standby database, specify an empty master database, create an SQL thread, and then set it according to the binlogfile and binlogpos of the backup record. And start the slave's sql_thread​

​CHANGE MASTER TO MASTER_HOST​​=​​'1.1.1.1'​​,RELAY_LOG_FILE​​=​​'MySQL2-relay-bin.000011'​​,RELAY_LOG_POS​​=​​160338​​;​

​START SLAVE SQL_THREAD;​

​show slave status\G

9. Verify that relaylog replay is successful

Verify by running show slave status\G. This step is usually slow to recover, depending on the number and size of database binlogs.

Verification 1: Check whether the value of the relay_log_file field is the largest value maintained in the MySQL2-relay-bin.index file. If so, it proves that all bilogs have been replayed successfully;

Verification 2: Check whether the Slave_SQL_Running field is YES.

As shown in the following figure:

Figure 5

10. Use the mysqlbinlog function to trim the binlog at the recovery time point and generate a sql file

At this point, steps 1-9 have recovered most of the data, and there is one binlog covering our recovery time point that has not been recovered.

So how do we do it?

As shown in the following figure:

Figure 6

According to the customer's time point (such as data that needs to be restored to 15:00), RDS management needs to trim the binlog covering our recovery time point according to the recovery time, that is, only the data from 12:00-15:00 is applied. The data from 15:00 to 18:00 belongs to the error operation time and should not be used.

//​​Use the pruning function of the mysqlbinlog tool to prune the binlog

mysqlbinlog ​​--​​start​​-​​position​​=​​4​​​​--​​stop​​-​​datetime​​=​​'2021-04-23 15:00:00'​​​​-​​R ​​-​​h127.​​0.0​​.​​1​​​​-​​uroot ​​-​​pxxxx ​​-​​P3306 mysql​​-​​bin.​​007​​​​>​​​​/​​tmp​​/​​mysql​​-​​bin.​​007.​​sql

11. The target instance executes the data to be restored through the SQL file

Execute the SQL file on the target instance.

//​​Empowerment​

​chown mysql:mysql ​​/​​tmp​​/​​mysql​​-​​bin.​​007.​​sql​

​//​​Recover data​
mysql -uroot -pxxxx -h127.0.0.1-P3306 -f --max_allowed_packet=1073741824 </root/mysql-bin.007.sql

12. Verify Data

At this point, the overall backup and recovery has been completed. Now the customer needs to verify the data and restore the data of the destination instance to the source instance.

We are the Alibaba Cloud Intelligent Global Technical Service-SRE team. We are committed to becoming a technology-based, service-oriented engineering team that ensures high availability of business systems. We provide professional and systematic SRE services to help our customers better use the cloud, build more stable and reliable business systems based on the cloud, and improve business stability. We hope to share more technologies that help enterprise customers move to the cloud, make good use of the cloud, and make customers' cloud business operations more stable and reliable. You can use DingTalk to scan the QR code below to join the Alibaba Cloud SRE Technology Academy DingTalk circle and communicate with more people on the cloud about cloud platforms.

Original link: https://developer.aliyun.com/article/784887?

Copyright Statement: The content of this article is contributed by real-name registered users of Alibaba Cloud. The copyright belongs to the original author. Alibaba Cloud Developer Community does not own the copyright and does not bear the corresponding legal responsibility. For specific rules, please refer to the "Alibaba Cloud Developer Community User Service Agreement" and the "Alibaba Cloud Developer Community Intellectual Property Protection Guidelines". If you find any content suspected of plagiarism in this community, please fill out the infringement complaint form to report it. Once verified, this community will immediately delete the suspected infringing content.

This is the end of this article about the practical recovery of MySQL database by time point. For more relevant MySQL database recovery content, please search for previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope everyone will support 123WORDPRESS.COM in the future!