Technology sharing | test 2500 star flashback gadgets on git

  • The original content of GreatSQL community cannot be used without authorization. Please contact Xiaobian and indicate the source for reprint.
  • 1. Experimental environment
  • 2. Software download
  • 3. Start test
  • 4. Attached parameter description

In case of accidental deletion or update of data in production, the traditional recovery method is to use backup to rebuild the instance, and then use 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 there a flashback way like oracle to recover data? The answer is yes. MySQL has binlog, which records all changes of MySQL server since binlog was enabled in the form of event.

We won't repeat too much about binlog. We can use the information recorded in binlog to quickly retrieve the misoperation data without backup and recovery.

Unlike oracle, MySQL needs tools to complete it. Today, let's test the 2500 star flashback gadget on git. The test process is as follows:

1. Experimental environment

Operating system: centos 7

Database version: MySQL 5.7.34

Software: binlog2sql

2. Software download

Unzip installation:

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:

super/replication client
replication slave

Recommended authorization:


3. 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 --flashback -h192.168.176.130 -P5506 -utest_flash -p'test_flash' -dtest_flash -t test_flash_tab --start-file='mysql-bin.000001'

Output results:

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

4. Attached parameter description

Options, mysql connection configuration

-h host; -P port; -u user; -p password

Parsing mode:

--stop-never Continuous resolution binlog. Optional. default False,Synchronize to the latest at the time of command execution binlog Location.

-K, --no-primary-key yes INSERT Statement to remove the primary key. Optional. default False . 

-B, --flashback Generate rollback SQL,Large files can be parsed and are not limited by memory. Optional. default False. And stop-never or no-primary-key Cannot be added at the same time.

--back-interval -B In mode, roll back every 1000 lines printed SQL,Add a sentence SLEEP How many seconds, if you don't want to add SLEEP,Please set to 0. Optional. Default 1.0. 

Analytical range control:

--start-file Starting to parse the file, you only need the file name, not the full path,must.

--start-position/--start-pos Start resolution location. Optional. Default to start-file The starting position of the.

--stop-file/--end-file Terminate parsing file. Optional. Default to start-file Same file. If the parsing mode is stop-never,This option is disabled.

--stop-position/--end-pos End resolution location. Optional. Default to stop-file The last position of the; 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 End parsing time, format'%Y-%m-%d %H:%M:%S'. Optional. The default is no filtering.

Object filtering:

-d, --databases Resolve targets only db of sql,Multiple libraries are separated by spaces, such as-d db1 db2. Optional. The default value is empty.

-t, --tables Resolve targets only table of sql,Multiple tables are separated by spaces, such as-t tbl1 tbl2. Optional. The default value is empty.

--only-dml Parse only dml,ignore ddl. Optional. default False. 

--sql-type Only the specified type can be resolved. Support INSERT, UPDATE, 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 :)

Article recommendation:


Wan Da #12, how can the MGR cluster automatically select the master without manual intervention

"2021 data technology Carnival · ON LINE": evolution and practice of MySQL high availability architecture

Packet capture analysis of where an sql statement is slow

Wan Da #15, what are the conditions that may cause the MGR service to fail to start

Technology sharing | why MGR consistency mode does not recommend AFTER

About GreatSQL

GreatSQL is a MySQL branch maintained by Wanli database. It focuses on improving the reliability and performance of MGR and supports the parallel query feature of InnoDB. It is a branch version of MySQL suitable for financial applications.




Wechat & QQ group:
You can search and add GreatSQL community assistant wechat friends, send verification information "add group" to join GreatSQL/MGR communication wechat group

QQ group: 533341697
Wechat assistant: wanlidbc

This article is composed of blog one article multi posting platform OpenWrite release!

Keywords: Database MySQL SQL

Added by eddieblunt on Wed, 05 Jan 2022 20:43:15 +0200