Shulou(Shulou.com)05/31 Report--

Editor to share with you how to delete MySQL data by mistake, I believe most people do not know much about it, so share this article for your reference. I hope you will gain a lot after reading this article. Let's learn about it together.

Overview

Binlog2sql is an open source MySQL Binlog parsing tool developed by Python, which can parse Binlog to original SQL, and also support parsing Binlog to rollback SQL, removing primary key INSERT SQL, which is a good helper for data recovery of DBA and operation and maintenance personnel.

I. installation and configuration

1.1 purpose

Quick data rollback (flashback)

Repair of lost data in New master after Master-Slave switching

The derivative function of generating standard SQL from binlog

Support for MySQL5.6,5.7

1.2 installation

Shell > git clone https://github.com/danfengcao/binlog2sql.git & & cd binlog2sql

Shell > pip install-r requirements.txt

II. Usage

2.1 pre-use configuration

2.1.1 Parameter configuration

[mysqld] server_id = 1 log_bin = / var/log/mysql/mysql-bin.log max_binlog_size = 1G binlog_format = row binlog_row_image = full

2.1.2 minimum set of permissions required by user

Select, super/replication client, replication slave

Recommended authorization

Select, super/replication client, replication slave

Permission description

Select: need to read the server information _ schema.COLUMNS table, get the meta-information of the table structure, and splice it into a visual sql statement

Super/replication client: both permissions are allowed. You need to execute 'SHOW MASTER STATUS', to get the binlog list on the server side.

Replication slave: access to binlog content through BINLOG_DUMP protocol

2.2 basic usage

2.2.1 basic usage

Parse out the standard SQL

Shell > python binlog2sql.py-h227.0.0.1-P3306-uadmin-pendant admin'- dtest-t test3 test4-- start-file='mysql-bin.000002'

Output:

INSERT INTO `test`.`test3` (`addtime`, `data`, `id`) VALUES ('2016-12-10 1313 DELETE FROM `test`.`test3` SET `addtime` =' 2016-12-10 120000ln UPDATE, `data` = 'Chinese', `id` = 3 WHERE `addtime` = '2016-12-10 133english',' = 'Chinese' AND `data` = 3 LIMIT 1; # start 763 end 954 DELETE FROM `test`.`test3` WHERE `addtime` = '2016-12-10 133end 338' AND `data` = 4 LIMIT 1 LIMIT 1) # start 981 end 1147

Parse out rollback SQL

Shell > python binlog2sql.py-- flashback-h227.0.0.1-P3306-uadmin-pawnadmin'- dtest-ttest3-- start-file='mysql-bin.000002'-- start-position=763-- stop-position=1147

Output:

INSERT INTO `test`.`test3` (`addtime`, `data`, `id`) VALUES ('2016-12-10 13english', 03end 38mm,' LIMIT 4); # start 981 end 1147 UPDATE `test`.`test3`SET `addtime` = '2016-12-10 1313english', 0322mm, `data` =' Chinese', `id` = 3 LIMIT `addtime` = '2016-12-10 1200english', 00' AND `data` =' Chinese 'AND `id` = 3 LIMIT 1

2.2.2 options

Mysql connection configuration

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

Analytic mode

-- stop-never continuously parses binlog. Optional. The default 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. Default False

-B,-- flashback generates rollback SQL, which can parse large files without memory restrictions. Optional. Default False. Cannot be added at the same time as stop-never or no-primary-key.

In back-interval-B mode, roll back SQL for every thousand lines printed, add a word SLEEP for how many seconds. If you do not want to add SLEEP, please set it to 0. Optional. The default is 1.0.

Analytical range control

-- start-file starts parsing the file. You only need the file name, not the full path. Must.

-- the starting resolution position of start-position/--start-pos. Optional. The default is the starting position of start-file.

-- stop-file/--end-file terminates the resolution file. Optional. The default is start-file the same file. If the parsing mode is stop-never, this option has no effect.

-- stop-position/--end-pos terminates the resolution location. Optional. The default is the last location of stop-file; if the parsing mode is stop-never, this option has no effect.

-- the starting parsing time of start-datetime, in the format of'% Y-%m-%d% HRV% MRV% S'. Optional. Does not filter by default.

-- stop-datetime terminates the resolution time, in the format'% Y-%m-%d% HRV% MRV% S'. Optional. Does not filter by default.

Object filtering

-d,-- databases only parses the sql of the target db, and multiple libraries are separated by spaces, such as-d db1 db2. Optional. The default is empty.

-t,-- tables only parses the sql of the target table, and multiple tables are separated by spaces, such as-t tbl1 tbl2. Optional. The default is empty.

-- only-dml parses only dml and ignores ddl. Optional. Default False.

-- sql-type parses only specified types, 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 changes. If this parameter is used but no type is filled in, none of the three will be parsed.

2.3 Application cases

2.3.1 accidentally delete the entire table data, which needs to be rolled back urgently

For more information on flashback, please see "flashback principle and practice" example/mysql-flashback-priciple-and-practice.md in the example directory.

Test library tbl table original data

Mysql > select * from tbl +-+ | id | name | addtime | +-+ | 1 | Xiaozhao | 2016-12-10 00:04:33 | | 2 | small coin | 2016-12-10 00:04:48 | 3 | Xiao Sun | | 0-12-13 20:25:00 | | 4 | Xiao Li | 2016-12 00:00:00 | +-+ 4 rows in set (2016 sec) mysql > delete from tbl | Query OK, 4 rows affected (0.00 sec) 20: 28, tbl table misoperation is cleared mysql > select * from tbl; Empty set (0.00 sec)

Steps to restore data:

1. Log in to mysql to view the current binlog file

Mysql > show master status; +-+-+ | Log_name | File_size | +-+-+ | mysql-bin.000051 | 967 | mysql-bin.000052 | 965 | +-+-+

2. The latest binlog file is mysql-bin.000052, and we relocate the binlog location of the misoperation SQL. The misoperator can only know the approximate misoperation time, and we filter the data according to the approximate time.

Shell > python binlog2sql/binlog2sql.py-h227.0.0.1-P3306-uadmin-pawnadmin'- dtest-ttbl-- start-file='mysql-bin.000052'-- start-datetime='2016-12-13 20purl 25ve00'-stop-datetime='2016-12-1320purl 3000'

Output:

INSERT INTO `test`.`tbl` (`addtime`, `id`, `name`) VALUES ('2016-12-13 20 end 2600V, 4,' Xiao Li'); # start 317 end 487 time 2016-12-13 20:26:26 UPDATE `test`.`tbl` SET `addtime` = '2016-12-120000 LIMIT', `id` = 4, `name` = 'Xiao Li' WHERE `addtime` = '2016-12-13 2020 start 2600' AND `id` = 4 Xiao Li' LIMIT 1 # start 514 end 701 time 2016-12-13 20:27:07 DELETE FROM `test`.`tbl` WHERE `addtime` = '2016-12-10 00 time 04purl 33' AND `id` = 1 AND `name` =' Xiaozhao 'LIMIT 1; # start 728 end 938 time 2016-12-13 20:28:05 DELETE FROM `test`.`tbl`WHERE `addtime` =' 2016-12-100 00 DELETE FROM 0449 48' AND `id` = 2 AND `name` = 'penny' LIMIT 1 # start 728 end 938 time 2016-12-13 20:28:05 DELETE FROM `test`.`tbl` WHERE `addtime` = '2016-12-13 20 time 25V 00' AND `id` = 3 AND `name` =' Xiao Sun 'LIMIT 1; # start 728 end 938 time 2016-12-13 20:28:05 DELETE FROM `test`.`tbl`WHERE `addtime` =' 2016-12-1200 time 0000' AND `id` = 4 AND `name` = 'Xiao Li' LIMIT 1; # start 728 end 938 time 2016-12-13 20:28:05

3. We get the exact location of the misoperation sql between 728 and 938, and then filter it further according to the location, and use flashback mode to generate a rollback sql to check whether the rollback sql is correct. (note: in real environment, this step will often further filter out the required sql. Combine grep, editor, etc.)

Shell > python binlog2sql/binlog2sql.py-h227.0.0.1-P3306-uadmin-pendant admin'- dtest-ttbl-- start-file='mysql-bin.000052'-- start-position=3346-- stop-position=3556-B > rollback.sql | cat

Output:

INSERT INTO `test`.`tbl` (`addtime`, `id`, `name`) VALUES ('2016-12-12 00 end 938 time 2016-12-13 20:28:05 INSERT INTO `test`.`tbl` (`addtime`, `id`, `name`) VALUES (' 2016-12-13 20 20 end 2500, 3, 'Xiao Sun') # start 728 end 938 time 2016-12-13 20:28:05 INSERT INTO `test`.`tbl` (`addtime`, `id`, `name`) VALUES ('2016-12-100 00 end 04time 48A, 2,' penny'); # start 728 end 938 time 2016-12-13 20:28:05 INSERT INTO `test`.`tbl` (`addtime`, `id`, `name`) VALUES ('2016-12-1000j04range 3333, 1,' Xiao Zhao'); # start 728 end 938 time 2016-12-13 20:28:05

4. Confirm that the rollback sql is correct and execute the rollback statement. Log in to mysql to confirm that the data was rolled back successfully.

Shell > mysql-h227.0.0.1-P3306-uadmin-pendant admin'

Select * from tbl +-+ | id | name | addtime | +-+ | 1 | Xiaozhao | 2016-12-10 00:04:33 | | 2 | small coin | 2016-12-10 00:04:48 | 3 | Xiao Sun | | 2016-12-13 20:25:00 | | 4 | Xiao Li | 2016-12-12 00:00:00 | +-+ |

III. Summary

3.1restrictions (compared to mysqlbinlog)

Mysql server must be enabled and cannot be parsed in offline mode

Parameter binlog_row_image must be FULL. MINIMAL is not supported.

The parsing speed is not as fast as mysqlbinlog

3.2 benefits (compared to mysqlbinlog)

Pure Python development, easy to install and use

Comes with flashback and no-primary-key parsing mode, no need to install patches

Flashback mode is more suitable for flashback combat.

Parsed into standard SQL for easy understanding and screening

The code is easy to modify and can support more personalized parsing

The above is all the contents of the article "how to delete MySQL data by mistake". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.