A simple example of data recovery using Binlog2sql

Sometimes we encounter operators mistakenly deleted or mistakenly update the data, then we are eager to restore the original data back, today we introduce a simple tool to facilitate the implementation of this feature.

Pre-conditions

Before implementing data recovery, our MySQL needs to meet the following configuration conditions:

1=== Full

The above configuration is the general configuration of MySQL, there is no special configuration situation, so you don't have to worry too much.

Constructing experimental data

Next we build a simple user table and insert the sample data.

CREATE TABLE`User' (' ID 'int( One) not NULLauto_increment, ' username 'varchar(255)DEFAULT NULL, ' password 'varchar(255)DEFAULT NULL, ' create_date 'datetime DEFAULT NULL,  PRIMARY KEY(' id ')) ENGINE=InnoDB auto_increment=4 DEFAULTCHARSET=UTF8;-- ------------------------------Records of User-- ----------------------------INSERT  into`User`VALUES('1','liping','ADFASDFASDF','2017-10-17 10:57:42');INSERT  into`User`VALUES('2','xiaoming','SDDSFGSDFGSDG','2017-10-17 10:58:06');INSERT  into`User`VALUES('3','Xiaohong','QEWFGFGDSFGSDG','2017-10-17 10:58:29');

Performing a mis-operation

Next, if the operator performs a misoperation, attempts to modify the password for liping to 123456, but forgets to add the where condition, that is, SQL is executed:

Update User set ' password '='123456';

Causes all user passwords to be set to 123456.

Data recovery

Next we will try to restore the original data, first because we opened the binary log, so in fact, each of our operations are recorded in the binary log, we can use the binary view command to query the operation traces we have just done.

First locate the current MySQL write to which binary file, go to MySQL execute command:

Show master status;

You can see that the current binary file is written to the bin.000002. So we use the binary file to view the command, to find the traces of the operation just now. (Note the path to the binary file, modify the path to your binary file)

-- no-defaults-v-V--base64-output=decode-rows/mdata/mysql_data/bin.000002 | grep-b ' 123456 '

As you can see, the binaries record our traces of each row of data modifications, where @1 @2 @3 @4 represents 4 of the fields in our user table, and you should think that if we reverse these records, the data can be restored. So here is a thought, that is, you can use the text operation, the update set before and after the string exchange, save to SQL, you can achieve data restoration, but the real implementation is still quite troublesome. Here we introduce the Binlog2sql tool, you do not have to be so troublesome to implement this function.

Binlog2sql Tools

Binlog2sql is a popular review of open source for the analysis of Binlog tools, see: Https://github.com/danfengcao/binlog2sql

To convert a binary file to SQL using Binlog2sql:

-H10. 89.1. 143 - - -P'123456'--user--  Start-file= ' bin.000002 '

Using Binlog2sql to parse a binary file into SQL format, this file includes the SQL that we did before, as well as inserting the sample data, and, of course, the UPDATE statement we mistakenly manipulated.

Here we focus on the contents of the yellow box, it is our UPDATE statement recorded in the beginning and end of the binary file, we can see that three update statements are recorded: #start 2616 end 3028, so we will recover, only need to restore the location of the data on it.

Using BINLOG2SQL to generate rollback SQL

-- flashback-h10.89.1.143-p3306-udev-p ' 123456 '-dtest-tuser--start-file= ' bin.000002 '--start-position=2616-- stop-position=3028

You can see that Binlog2sql generated: #start 2616 end 3,028-bit rollback SQL. We can restore the data by copying this SQL to MySQL.

A simple example of data recovery using Binlog2sql