Introduction to Mysql database is restored in time
For any company, data is the most valuable asset.
How to protect the integrity of the data, how to protect the data from damage, how to keep the data in the event of a failure, and how to restore data based on our backups in the event of misoperation, hacking, data tampering, etc. is what every technician needs The key point of attention.
Alibaba Cloud is committed to serving customers, providing continuous data protection and low-cost backup services for customer databases. It can provide strong protection and strong recovery for data in a variety of environments. In extreme cases of data loss or data damage, the RDS management and control platform has a one-key restore function, and based on the time point set by the customer that needs to be restored, the data can be restored in an all-round way.
1. Technical realization of point-in-time recovery
If a customer loses data due to misoperation at a certain point in time, how does the RDS control service recover?
The overall idea of point-in-time recovery is as follows: A complete data recovery is composed of physical backup + binlog recovery + binlog cropping.
figure 1
First obtain the available backup set, apply the backup set to the target instance, and 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 recovery at a point in time
1. Create an instance for recovery purposes
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 the same network environment as the source instance.
Why do you want to do this?
Because backup recovery is a high-risk operation, if you restore directly to the source instance, once the backup set is unavailable, binlog is missing, etc., not only the lost data cannot be retrieved, but the original data cannot be kept intact. Therefore, it is strongly recommended to use a new instance. Recover!
2. Clarify the backup and recovery time point
After a customer has performed a series of database operations, such as accidental deletion, accidental modification, etc., they have no perception after the operation, and when the business is damaged or a failure occurs, how to locate the accurate time point of the operation for data recovery?
Method 1: You can find the corresponding misoperation time point through the log audit function.
Method 2: Binlog can be parsed into text, and the corresponding misoperation time point can be queried.
3. Get the available backup set through the backup history
Under normal circumstances, based on the importance of the business, customers will plan their own database backup cycles on the cloud, and RDS management and control 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 the top priority for high availability and disaster recovery of the database!
4. Get the binlog location corresponding to the backup set
Private cloud backups are generally based on the xtrabackup tool. Xtrabackup has the characteristics of hot backup and fast recovery. At the same time, the files and points of binlog applied at the end of the backup will be written into the corresponding files. RDS control will binlogfile and binlogpos into the database. When backup and restoration are needed, it will directly obtain this point for restoration.
As shown below:
figure 2
5. Restore the backup set to the destination instance
Steps 1-4 are preparatory work, and start the formal recovery of data below. The first step in restoring data is to download the available full physical backup set to the target instance, and use the xtrabackup tool to restore it.
// first stop mysql process on the destination instance
systemctl stop mysql
// then merge the data, assuming the backup decompress / root / backup / directory, you can specify the port need to restore example, the need to add --defaults-file is specified, the default 3306.
innobackupex --apply-log /root/backup/
// delete the original file directory
rm -rf /data/mysql
// restore data sets, which directory to restore data is based on the configuration file my.cnf datadir decision. This field must be checked for accuracy
innobackupex --copy-back /root/backup/
// directory empowerment
chown -R mysql:mysql /data/mysql
6. Verify that the restore was successful
The management and control service needs to verify whether the restore is successful, and then decide whether it needs to be operated downwards. The verification steps are also very simple and rude, directly check whether there is ERROR in the backup recovery log, and whether the last line is completed OK!
The following figure shows a successful backup and recovery.
7. Obtain the binlog log for recovery
This step is very important and is related to the success of the recovery and the integrity of the data.
So how does the RDS control 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, obtain the first binlog through the filename and pos of the binlog recorded by the physical backup, such as binlog004 in the figure above; and then restore the ones set by the customer. The timestamp at the time point is used to find the corresponding last binlog, such as binlog007 in the above figure; finally, the four binlog backups binlog004, binlog005, binlog006, and binlog007 are downloaded to the destination instance for restoration.
If the wrong binlog log is obtained for recovery, for example, binlog003/binlog005 is mistakenly set to the first binlog, then the dml statement executed on binlog003/binlog005 will be executed again on the new instance, and the recovered data will increase or Missing; for example, if binlog0006 or binlog0008 is mistakenly set as the last binlog, then the restored 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, and rename the binlog except the last binlog (the binlog covering the recovery time point) to relaylog , and then replay these relaylogs using the new instance.
// the binlog rename, relaylog file name can be executed show variables like '% relay%' view in mysql instance.
rename mysql-bin MySQL2-relay-bin mysql-bin*
// will relay information to initialize the index file
ls ./MySQL2-relay-bin.0000*>MySQL2-relay-bin.index
// copy these files to a data file
cp MySQL2-relay-bin.*/data/mysql/
// file empowerment
chown -R mysql:mysql /data/mysql
// start mysql examples
systemctl start mysql
//change master to instance does not exist, this example is an analog library equipment, designated a master library empty, creating SQL thread, and then set according to binlogfile binlogpos backup recording. 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 the relaylog is replayed successfully
Use show slave status\G to verify. This step is generally slower to recover, depending on the number of database binlogs and binlog size.
Verification 1: Check whether the value of the relay\_log\_file field is the largest value we maintain in the MySQL2-relay-bin.index file. If it is, it proves that all bilogs have been replayed successfully;
Verification 2: Check whether the Slave\_SQL\_Running field is YES.
As shown below:
Figure 5
10. Use the mysqlbinlog function to trim the binlog at the time of recovery and generate the sql file
So far, most of the data has been restored in steps 1-9, and there is a binlog that covers our recovery time point and has not been restored.
So how do we proceed?
As shown below:
Figure 6
According to the customer's time point (for example, the data needs to be restored to 15:00), RDS management and control needs to tailor the binlog covering our recovery time point according to the recovery time, that is, only apply the data from 12:00-15:00, 15: The data from 00 to 18:00 is a misoperation time and should not be used for application.
// use mysqlbinlog tool cropping function to crop 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 destination instance executes the data that needs 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. Validate the data
At this point, the overall backup and restoration has been completed, and the customer is required to verify the data, and the data of the destination instance has been restored 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, and high-availability engineer team of business systems; providing professional and systematic SRE services to help customers make better use of the cloud 、Build a more stable and reliable business system based on the cloud to improve business stability. We hope to share more technologies that help enterprise customers go to the cloud, make good use of the cloud, and make their business operations on the cloud more stable and reliable. You can scan the QR code below to join the Alibaba Cloud SRE Technical Institute Dingding circle, and more The multi-cloud master communicates about those things about the cloud platform.
Copyright Statement: content of this article is contributed spontaneously by Alibaba Cloud real-name registered users, and the copyright belongs to the original author. The Alibaba Cloud Developer Community does not own its copyright and does not assume corresponding legal responsibilities. 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 suspected plagiarism in this community, fill in the infringement complaint form to report it. Once verified, the community will immediately delete the suspected infringing content.
mysql
**粗体** _斜体_ [链接](http://example.com) `代码` - 列表 > 引用。你还可以使用@来通知其他用户。