- 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
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
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 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 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:474. 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:
GreatSQL MGR FAQ
https://mp.weixin.qq.com/s/J6...
Wan Da #12, how can the MGR cluster automatically select the master without manual intervention
https://mp.weixin.qq.com/s/07...
"2021 data technology Carnival · ON LINE": evolution and practice of MySQL high availability architecture
https://mp.weixin.qq.com/s/u7...
Packet capture analysis of where an sql statement is slow
https://mp.weixin.qq.com/s/AY...
Wan Da #15, what are the conditions that may cause the MGR service to fail to start
https://mp.weixin.qq.com/s/in...
Technology sharing | why MGR consistency mode does not recommend AFTER
https://mp.weixin.qq.com/s/rN...
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.
Gitee:
https://gitee.com/GreatSQL/Gr...
GitHub:
https://github.com/GreatSQL/G...
Bilibili:
https://space.bilibili.com/13...
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!