In case of accidental deletion or update of data in production, the traditional recovery method is to use backup to rebuild the instance and binlog to recover the data. Sometimes, the number of data to be recovered is very small, but it takes time and effort to recover dozens or even hundreds of G of backups.

Is it convenient to recover data by flashback like Oracle? The answer is yes. MySQL has binlog, which records all changes of MySQL server since binlog was enabled in the form of event.

Don't go into too much detail about binlog. We can use the information recorded in binlog to quickly retrieve the misoperation data without backup and recovery.

Unlike Oracle, MySQL is done with a single command. MySQL needs tools. Today, let's test the 2500 star flashback gadget on git.

Frame Directory:
1. Experimental environment
2. Software download
3. Start test
4. Attached parameter description

01 experimental environment

Operating system: centos 7
Database version: MySQL 5.7.34
Software: binlog2sql

02 software download

https://github.com/danfengcao/binlog2sql

Unzip installation:
unzip binlog2sql-master.zip
cd binlog2sql-master
pip install -r requirements.txt

MySQL server must set the following parameters:
server_id = 1
log_bin = /var/log/mysql/mysql-bin.log
max_binlog_size = 1G
binlog_format = row
binlog_row_image = full

Minimum permission set required by user:
select
super/replication client
replication slave

Recommended authorization:
GRANT SELECT, REPLICATION SLAVE, REPLICATION CLIENT ON . TO

03 start test

Permission Description:
select: the server side information needs to be read_ Schema.columns table to obtain the meta information of the table structure and splice it into visual sql statements
super/replication client: both permissions are allowed. You need to execute 'SHOW MASTER STATUS' to obtain the binlog list on the server side
replication slave: through binlog_ The dump protocol obtains the permission of binlog content

Create user and create experimental data:
Use sysbench to generate test tables. This step is omitted

Authorized user:
GRANT SELECT, REPLICATION SLAVE, REPLICATION CLIENT ON . TO test_flash@'%' identified by 'test_flash';

Modify data:

INSERT INTO test_flash.test_flash_tab (`id`, `k`, `c`,`pad`,`test_col1`) VALUES (10001,'5014614','68487932199-96439406143-93774651418-41631865787-96406072701-20604855487-25459966574-28203206787-41238978918-19503783441','22195207048-70116052123-74140395089-76317954521-98694025897','');

update  test_flash.test_flash_tab set pad='22195207048-70116052123-74140395089-76317954521-98694025897' where  id=10000;

delete from  test_flash.test_flash_tab where  id=9998;

Retrieve data:

Copy the contents of binlog2sql master / binlog2sql to the directory where binlog is located

python binlog2sql.py --flashback -h192.168.176.130 -P5506 -utest_flash -p'test_flash' -dtest_flash -t test_flash_tab --start-file='mysql-bin.000001'

Output result: it can be directly warehoused after confirmation

INSERT INTO `test_flash`.`test_flash_tab`(`c`, `k`, `pad`, `id`, `test_col1`) VALUES ('26540399442-75345224560-23949007931-31007424094-85536370579-90557582232-52492275484-59891643786-99516288129-15346323350', 4994166, '09437973687-45231478323-39177308217-32926675721-14740893381', 9998, NULL); #start 1290 end 1731 time 2021-08-17 19:44:10

UPDATE `test_flash`.`test_flash_tab` SET `c`='34838736059-24362714610-75033330387-17863378665-80928638402-33892306210-78377564998-17324442332-39178876426-77334528413', `k`=4533452, `pad`='11946195857-63616115598-80208325001-42313633529-35180183845', `id`=10000, `test_col1`=NULL WHERE `c`='34838736059-24362714610-75033330387-17863378665-80928638402-33892306210-78377564998-17324442332-39178876426-77334528413' AND `k`=4533452 AND `pad`='22195207048-70116052123-74140395089-76317954521-98694025897' AND `id`=10000 AND `test_col1` IS NULL LIMIT 1; #start 627 end 1259 time 2021-08-17 19:43:59

DELETE FROM `test_flash`.`test_flash_tab` WHERE `c`='68487932199-96439406143-93774651418-41631865787-96406072701-20604855487-25459966574-28203206787-41238978918-19503783441' AND `k`=5014614 AND `pad`='22195207048-70116052123-74140395089-76317954521-98694025897' AND `id`=10001 AND `test_col1`='' LIMIT 1; #start 4 end 596 time 2021-08-17 19:43:47

04 attached parameter description

Options, mysql connection configuration
-h host; -P port; -u user; -p password

Parsing mode:

• – stop never continuously parses binlog. Optional. The default is False, which synchronizes to the latest binlog location when the command is executed.
• -K. -- no primary key removes the primary key from the INSERT statement. Optional. The default is False.
• -B. -- flashback generates rollback SQL, which can parse large files and is not limited by memory. Optional. The default is False. And stop never or no primary key cannot be added at the same time.
• – back interval - in mode B, the number of seconds to add a SLEEP sentence for every 1000 lines of rollback SQL printed. If you don't want to add SLEEP, set it to 0. Optional. The default is 1.0.

Resolution range control:

• – start file starts to parse the file. Only the file name is required, not the full path. It must be.
  – start position / – start POS start position. Optional. The default is the start location of the start file.
• – stop file / – end file terminates parsing the file. Optional. The default is start file, the same file. If the parsing mode is stop never, this option is disabled.
• – stop position / – end POS terminates the resolution position. Optional. The default is the last position of stop file; If the parsing mode is stop never, this option is disabled.
• – start datetime start parsing time, format '% Y -% m -% d% H:% m:% s'. Optional. The default is no filtering.
• – stop datetime terminate parsing time, format '% Y -% m -% d% H:% m:% s'. Optional. The default is no filtering.

Object filtering:

• -d. -- databases only parses the sql of the target dB, and multiple databases are separated by spaces, such as -d db1 db2. Optional. The default value is empty.
• -t. -- tables only parses the sql of the target table. Multiple tables are separated by spaces, such as - t tbl1 tbl2. Optional. The default value is empty.
• – only dml only parses dml, ignoring ddl. Optional. The default is False.
• – SQL type only resolves the specified type and supports insert, update and delete. Multiple types are separated by spaces, such as – SQL type insert delete. Optional. The default is to resolve all additions, deletions and modifications. If this parameter is used but no type is filled in, none of the three will be resolved.

Enjoy GreatSQL~