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

This article mainly introduces "how to restore data deleted by mysql". In daily operation, I believe many people have doubts about how to restore data deleted by mysql. The editor consulted all kinds of materials and sorted out simple and easy-to-use methods of operation. I hope it will be helpful to answer the doubt of "how to recover data deleted by mysql". Next, please follow the editor to study!

Erroneous deletion of data recovery:

Two tables have been deleted by mistake, with more than 30,000 data

Lock the file name of the binlog when the data is deleted

Parse the binlog into something you can understand to find the binlog position at the beginning of the deleted data and the binlog position at the end of the data

Mysqlbinlog mysql-bin.000819-base64-output=DECODE-ROWS-v-start-datetime= "2017-08-10 16:04:26"-- stop-datetime= "2017-08-10 16:04:30" > / tmp/819.sql

Resolution process:

According to the pos points found, the inverted sql file is generated:-h is the table that belongs to the database-t of ip-d of the database where the data was mistakenly deleted.

Python binlog2sql.py-flashback-h 2.1.1.1. -P3306-ubinlog-pendant 123456'-doms-tt_a-- start-file='mysql-bin.000819'-- start-position=13736449-- stop-position=47327695 > / tmp/roll.sql

Import the parsed sql into the test library to see if the data is correct:

Source roll.sql

When the data is correct: import into the library where the data was mistakenly deleted.

Binlog2sql uses:

Installation:

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

Shell > pip install-r requirements.txt

Please search and solve the installation problem of git and pip by yourself.

Use

MySQL server must set the following parameters:

[mysqld]

Server_id = 1

Log_bin = / var/log/mysql/mysql-bin.log

Max_binlog_size = 1G

Binlog_format = row

Binlog_row_image = full

The minimum set of permissions required by user:

Select, super/replication client, replication slave

Recommended authorization

GRANT SELECT, REPLICATION SLAVE, REPLICATION CLIENT ON *. * TO

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

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 13 end 0338,' english', 4); # start

UPDATE `test`.`test3` SET `addtime` = '2016-12-10 12 WHERE 0000 LIMIT, `data` =' Chinese', `id` = 3 Chinese `addtime` = '2016-12-10 13 AND 03purl 22' AND `data` = 'Chinese' AND `id` = 3 LIMIT 1;

DELETE FROM `test`.`test3` WHERE `addtime` = '2016-12-10 13 AND 0314 38' AND `data` = 'english' AND `id` = 4 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 13 english', 03RH,' english', 4); # start 981 end 1147

UPDATE `test`.`test3` SET `addtime` = '2016-12-10 13 AND 0315 22, `data` =' Chinese', `id` = 3 WHERE `addtime` = '2016-12-10 1200 WHERE 00' AND `data` =' Chinese 'AND `id` = 3 LIMIT 1;

Option

Mysql connection configuration

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

Analytic mode

-- stop-never continuously synchronizes binlog. Optional. Add or synchronize to the latest binlog location when the command is executed.

-K,-- no-primary-key removes the primary key from the INSERT statement. Optional.

-B,-- flashback generates a rollback statement that parses large files without memory restrictions, adding a SLEEP SELECT (1) for every thousand lines printed. Optional. Cannot be added at the same time as stop-never or no-primary-key.

Analytical range control

-- start-file starting parsing file. Must.

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

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

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

At which point in time does the start-datetime begin to be parsed by binlog, and the format must be datetime, such as' 2016-11-11-11-11-11-11-11-11-11-1-1-1-11-11-11 / 11 / 11 / 11 / 11 / 11 / 11 / 11 / 11 / 11 / 11 Optional. Does not filter by default.

At which point in time does the binlog of stop-datetime stop parsing? the format must be datetime, such as' 2016-11-11-11-11-11-11-11-11-11-11-11-1-1-11-11 / 11 / 11 / 11 / 11 / 11 / 11 / 11 / 11 / 11. Optional. Does not filter by default.

Object filtering

-d,-- databases outputs only the sql of the target db. Optional. The default is empty.

-t,-- tables outputs only the sql of the target tables. Optional. The default is empty.

Application case

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 | 2016-12-13 20:25:00 |

| | 4 | Xiao Li | 2016-12-12 00:00:00 |

+-- +

4 rows in set (0.00 sec)

Mysql > delete from tbl

Query OK, 4 rows affected (0.00 sec)

At 20: 28, the misoperation of the tbl table is cleared.

Mysql > select * from tbl

Empty set (0.00 sec)

Steps to restore data:

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 | |

+-+ +

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 2600, 4,' Xiao Li'); # start 317 end 487 time 2016-12-13 20:26:26

UPDATE `test`.`tbl` SET `addtime` = '2016-12-12 00 WHERE 0000, `id` = 4, `name` =' Xiao Li 'WHERE `addtime` =' 2016-12-13 2026 AND `id` = 4 AND `name` = 'Xiao Li' LIMIT 1; # start 514 end 701 time 2016-12-13

DELETE FROM `test`.`tbl` WHERE `addtime` = '2016-12-100 DELETE FROM 04AND 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-10 00 AND 04 AND 48' AND `id` = 2 end `name` = 'small money' LIMIT 1; # start 728 end 938 time 2016-12-13 20:28:05

DELETE FROM `test`.`tbl` WHERE `addtime` = '2016-12-13 20 AND 25 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-12 00 LIMIT 00' AND `id` = 4 AND `name` =' Xiao Li 'LIMIT 1; # start 728 end 938 time 2016-12-13 20:28:05

We get that the exact location of the misoperation sql is between 728 and 938, and then filter further according to the location, generate a rollback sql using flashback mode, and check whether the rollback sql is correct. (note: in the real world, this step often further filters 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 time 0012,' Xiao Li'); # start 728 end 938 time 2016-12-13 20:28:05

INSERT INTO `test`.`tbl` (`addtime`, `id`, `name`) VALUES ('2016-12-13 20 end 2500 time, 3,' Xiao Sun'); # start 728 938 time 2016-12-13 20:28:05

INSERT INTO `test`.`tbl` (`addtime`, `id`, `name`) VALUES ('2016-12-100 end 04 time 48 hours, 2,' penny'); # start 728 938 time 2016-12-13 20:28:05

INSERT INTO `test`.`tbl` (`addtime`, `id`, `name`) VALUES ('2016-12-10 00 time 0415 33, 1,' Xiao Zhao'); # start 728 end 938 time 2016-12-13 20:28:05

Verify 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 |

+-- +

Limit (compare 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

Advantages (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.

Parse to standard SQL for easy understanding and debugging

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

Question:

Some problems occurred during installation:

(Run "ez_setup.py-U setuptools" to reinstall or upgrade.)

[root@ces3 binlog2sql] # python binlog2sql.py

Traceback (most recent call last):

File "binlog2sql.py", line 5, in

Import pymysql

ImportError: No module named pymysq

# python setup.py install

Traceback (most recent call last):

File "setup.py", line 6, in

From setuptools import setup, find_packages

ImportError: No module named setuptools also seems to have something to do with a setuptools library, so let's continue with the installation.

# wget https://bootstrap.pypa.io/ez_setup.py-no-check-certificate and then use python ez_setup.py install to compile setuptools successfully

Try python setup.py install again to complete the installation of pip.

The plug-in PyMySQL can be installed in the following ways:

Git clone https://github.com/PyMySQL/PyMySQL can use the following ways for the plug-in mysql replication:

In this way, the preliminary work of git clone https://github.com/noplay/python-mysql-replication is done.

At this point, the study on "how to recover the data deleted by mysql" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

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.