Flash back of MySQL

Time:2021-6-8

Flash back of MySQL

MySQL DBAs or developers sometimes delete or update data by mistake. If it is an online environment and has a great impact, they need to be able to roll back quickly. The traditional recovery method is to use the backup instance, and then use the binlog after removing the wrong SQL to recover the data. This method is time-consuming and laborious, even needs downtime maintenance, and is not suitable for fast rollback. Some teams also use LVM snapshot to shorten the recovery time, but the disadvantage of snapshot is that it will affect the performance of MySQL. MySQL flashback uses binlog to roll back directly, which can recover quickly without downtime.

The principle of flashback

MySQL binlog, in the form of event, records all the changes of MySQL server since binlog was enabled, which can help to reproduce all the changes. MySQL introduces binlog mainly for two purposes: one is for master-slave replication; Second, some backup and restore operations need to re apply binlog. There are three optional binlog formats, each with its own advantages and disadvantages

  • Statement: Based on the SQL statement mode, the amount of binlog data is small, but some statements and functions may cause data inconsistency or even errors in the copying process;
  • Row: a row based pattern that records the complete change of a row. It’s very safe, but binlog is much larger than the other two modes;
  • Mixed: mixed mode, select statement or row mode according to the statement;

To use binlog flashback, you need to set the binlog format to row. Use the following statement to view the current binlog mode.

show global variables like "%binlog_format%";

Flash back of MySQL

The actual combat of flashback

In the real flashback scenario, the key is to quickly filter out the SQL that really needs to be rolled back. We use the open source tool binlog2sql to carry out the actual combat drill. Binlog2sql is produced by meituan review DBA team (Shanghai) and has been rolled back online for many times.

① Install binlog2sql tool

First, install the python tool management table pip
yum -y install epel-release
yum -y install python-pip

Install binlog2sql
git clone https://github.com/danfengcao/binlog2sql.git && cd binlog2sql
pip install -r requirements.txt

② Flashback case

1. We use the previous employee table data to build a separate database

create database testflashback;
use testflashback;
source /root/tools/scott.sql

2. Error operation, execute the following transaction.

start transaction;
delete from emp where sal>3000;
update emp set sal=6000;
delete from emp where job='CLERK';
commit;

3. View the current binlog file

show master logs;

Flash back of MySQL

4. The latest binlog file is mysql-binlog.00000 1. Our goal is to filter out the SQL that needs to be rolled back. Because the wrong operator only knows the approximate time of the wrong operation, we first do a filter according to the time. You only need to parse the EMP table of the testflash back library( Note: if there are multiple SQL misoperations, the generated binlog may be distributed in multiple files, and multiple files need to be parsed.)

python binlog2sql/binlog2sql.py -uroot -pWelcome_1 \
--start-file='mysql-binlog.000001' > /root/tools/raw.sql

The above statement will display all binlog logs of the EMP table. If you can determine the approximate time range, you can use the parameters — start datetime and — stop datetime to filter. For example:

--start-datetime='2016-12-26 11:44:00' --stop-datetime='2016-12-26 11:50:00'

The binlog for parsing is as follows:

Flash back of MySQL

5. According to the location information, we determined that the misoperation SQL came from the same transaction, and the exact location was between 14956 and 16791 (binlog2sql will output the same start position for the same transaction). Then filter according to the location, use the – B option to generate the rollback SQL, and check whether the rollback SQL is correct( Note: in real scenarios, the generated rollback SQL often needs to be further filtered. Combined with grep, editor, etc.)

python binlog2sql/binlog2sql.py -uroot -pWelcome_1 --start-file='mysql-binlog.000001' \
--start-position=14956 --stop-position=16791 -B > /root/tools/rollback.sql

Here is the generated flashback statement:

Flash back of MySQL

6. Confirm with the business side that there is no problem in rolling back the SQL and execute the rolling back statement. Log in to MySQL and confirm that the rollback is successful.

mysql -uroot -pWelcome_1 < /root/tools/rollback.sql

7. Check whether the data is recovered

Flash back of MySQL