MySQL quick recovery solution based on time point

The reason for writing such an article is that one night a few days ago, when I was about to get off work, the business side suddenly had a requirement to restore the data in a table. I asked about the situation at that time, and it was roughly like this: the business side accidentally performed an update operation in a table. Maybe the where condition was not written correctly, resulting in the data in the table being corrupted. However, the data has not yet been written to the disk, only the value in the memory has been modified, and now it is required to restore to the previous data. Fortunately, this data is the price of certain products on the platform. Basically, there are a limited number of products, and the price values ​​are all fixed. This price list has been backed up before, so the data of a new copy of the price list was directly imported for him, and this problem was solved.

At that time, I was thinking, if I don’t have a backup, but only binlog, and if I have to recover this problem, is there any better way? Create a new instance, restore the entire database, and then apply the backed-up binlog, tracing back to the time when the data was corrupted.

Use the mysqlbinlog tool to replay transactions. This method has many pitfalls, such as:

1. You can only run one mysqlbinlog command at a time to replay one binlog file at a time. You cannot run multiple commands in parallel because a temporary table will be generated when replaying, which will cause conflicts and failures.

2. It is an atomic operation. If it fails in the middle of its run, it will be difficult to know where it failed and to restart from a previous point in time. There are many reasons for failure: Innodb lock wait timeout caused by some concurrent transactions, different max_allowed_packet settings of server and client, loss of connection with MySQL server during query, etc.

So I flipped through Percona's blog, found a method, read the essence, and roughly recorded it. I haven't implemented this method myself yet, but just recorded it here. When I have time in the future, I can try it myself to see if I can solve this problem more efficiently.

The general idea is as follows:

Two additional machines, the first one is used to restore the backup result data, and the other one is used to copy the original master's binlog to the instance and then simulate the original master. Then the first machine and the second machine establish a master-slave relationship, change master to the second machine, position the backup result (binlog name and pos in xtrabackup_binlog_info), then synchronize to the error operation point, export the restored table, and then restore it to the production original master.

The specific steps are as follows:

1. Prepare a machine to back up and restore the latest backup result data of the instance.

2. Prepare another machine, a new instance, copy the original master's binlog file to the instance's data directory, start an empty instance (server-id is the same as the original master, --log_bin=master-bin binlog file name remains the same as the original master;), then stop it, delete all the binlogs it automatically created, decompress and copy all the required binlogs (from the original production instance) to its data directory, and then restart it.

The location of the latest backup data:

If the startup is normal, connect to MySQL and view binlog related information:

3. Establish a synchronization relationship and stop before the position of the wrong operation

 CHANGE MASTER TO 

MASTER_HOST='127.0.0.1',

MASTER_PORT=3307,

MASTER_USER='root',

MASTER_PASSWORD='secret',

MASTER_LOG_FILE='master-bin.000007', MASTER_LOG_POS=1518932;

START SLAVE UNTIL 

MASTER_LOG_FILE = 'log_name', 

MASTER_LOG_POS = log_pos

or

START SLAVE SQL_THREAD UNTIL

 SQL_AFTER_GTIDS =

 3E11FA47-71CA-11E1-9E33-C80AA9429562:11-56

SHOW SLAVE STATUSG;

This is equivalent to using one more instance, which increases the utilization rate of binary logs and the success rate of binary log utilization. Whether this method is feasible remains to be verified. According to the idea described by the author in the article, it is better than the method of applying binlog on a single instance, because once an error occurs in the process of applying binlog, it can quickly determine the point at which the error occurred, which helps us quickly solve the problem.

The above is the details of MySQL's time-based quick recovery solution. For more information about MySQL quick recovery, please pay attention to other related articles on 123WORDPRESS.COM!