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

In this issue, the editor will bring you about the installation and use of mysql flashback tool binlog2sql. The article is rich in content and analyzed and described from a professional point of view. I hope you can get something after reading this article.

Download address: link: https://pan.baidu.com/s/15dDeGufVWOgVrfATGWBzaA password: gnz8

DBA or developers sometimes delete or update data mistakenly. If the online environment has a great impact, it needs to be able to roll back quickly, while MySQL flashback (flashback) can be rolled back directly using binlog and can quickly recover data (MySQL flashback now only supports DML statements for flashback).

Conditions required to take advantage of binlog flashback:

Mysql parameter settings:

Log_bin = / directory / mysql-bin.log (on)

Binlog_format = row

Binlog_row_image = full (default is full)

The working principle is that for delete operation, the delete information is extracted from binlog and the rollback statement of insert is generated. For insert operations, the rollback SQL is delete. For update operations, the rollback sql should exchange the values of SET and WHERE.

Installation:

The environment is ready to install a list of various dependent toolkits

Replace python with python3.6 version (python version upgrade reference blog: https://blog.csdn.net/wwwdaan5com/article/details/78218277)

Python-pip

PyMySQL

Python-mysql-replication

Wheel argparse

1. Download binlog2sql

Https://github.com/danfengcao/binlog2sql

2. Binlog2sql dependency package installation

(1) PyMySQL-0.8.0 installation

Https://pypi.python.org/pypi/PyMySQL/

[root@node1 binlogsql] # tar-xzvf PyMySQL-0.8.0.tar.gz

[root@node1 binlogsql] # cd PyMySQL-0.8.0

[root@node1 PyMySQL-0.8.0] # python setup.py install

(2) wheel-0.31.0 installation

Https://pypi.python.org/pypi/wheel/

[root@node1 binlogsql] # tar-xzvf wheel-0.31.0.tar.gz

[root@node1 binlogsql] # cd wheel-0.31.0

[root@node1 wheel-0.31.0] # python setup.py install

(3) python-mysql-replication installation

Https://github.com/noplay/python-mysql-replication

[root@node1 binlogsql] # unzip python-mysql-replication-master.zip

[root@node1 binlogsql] # cd python-mysql-replication-master

[root@node1 python-mysql-replication-master] # python setup.py install

(4) you can install the corresponding dependency package through pip

Https://pypi.python.org/pypi/pip

[root@node1 tools] # tar-xzvf pip-10.0.1.tar.gz

[root@node1 tools] # cd pip-10.0.1

[root@node1 pip-10.0.1] # python setup.py install

[root@node1 binlog2sql-master] # pip install-r requirements.txt

Something could go wrong.

FileNotFoundError: [Errno 2] No such file or directory:'/ usr/local/lib/python3.6/site-packages/mysql_replication-0.18-py3.6.egg'

Dependent package version problem.

Carry out. Pip install mysql-replication

Then execute pip install-r requirements.txt

Shows that the installation is complete.

Actual combat exercise:

1. View test data:

MariaDB [test] > select * from t

+-+ +

| | id | name |

+-+ +

| | 1 | ga |

| | 31 | ga |

| | 38 | ga |

| | 45 | ga |

| | 52 | hg |

| | 59 | hh |

| | 61 | Planning if |

| | 68 | what to do |

| | 73 | ww |

| | 80 | ww |

| | 87 | gg |

| | 94 | gg |

+-+ +

12 rows in set (0.00 sec)

two。 Delete some of the data:

MariaDB [test] > delete from t where id > 50

Query OK, 8 rows affected (0.18 sec)

MariaDB [test] > select * from t

+-+ +

| | id | name |

+-+ +

| | 1 | ga |

| | 31 | ga |

| | 38 | ga |

| | 45 | ga |

+-+ +

4 rows in set (0.00 sec)

Eight records were deleted.

3. View the log location of master.

MariaDB [test] > show master status

+-+

| | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | |

+-+

| | mysql-bin.000029 | 468829331 | |

+-+

1 row in set (0.01 sec)

4. Find out the corresponding misoperation statement according to the approximate time of misoperation.

[root@localhost binlog2sql] # python binlog2sql.py-h272.168.1.201-P3306-uroot-p123456-dtest-tt-- start-file='mysql-bin.000029'-- start-datetime='2018-05-10 11V 00L 00'-stop-datetime='2018-05-10 11V 10V 00' > a.sql

[root@localhost binlog2sql] # cat a.sql

DELETE FROM `test`.`t`WHERE `id` = 52 AND `name` = 'hg' LIMIT 1; # start 468829113 end 468829304 time 2018-05-10 11:06:52

DELETE FROM `test`.`t`WHERE `id` = 59 AND `name` = 'hh' LIMIT 1; # start 468829113 end 468829304 time 2018-05-10 11:06:52

DELETE FROM `test`.`t`WHERE `id` = 61 AND `name` = 'Planning if' LIMIT 1; # start 468829113 end 468829304 time 2018-05-10 11:06:52

DELETE FROM `test`.`t`WHERE `id` = 68 AND `name` = 'what to do' LIMIT 1; # start 468829113 end 468829304 time 2018-05-10 11:06:52

DELETE FROM `test`.`t`WHERE `id` = 73 AND `name` = 'ww' LIMIT 1; # start 468829113 end 468829304 time 2018-05-10 11:06:52

DELETE FROM `test`.`t`WHERE `id` = 80 AND `name` = 'ww' LIMIT 1; # start 468829113 end 468829304 time 2018-05-10 11:06:52

DELETE FROM `test`.`t`WHERE `id` = 87 AND `name` = 'gg' LIMIT 1; # start 468829113 end 468829304 time 2018-05-10 11:06:52

DELETE FROM `test`.`t`WHERE `id` = 94 AND `name` = 'gg' LIMIT 1; # start 468829113 end 468829304 time 2018-05-10 11:06:52

You can find the start and end of this thing in binlog from a.sql (binlog2sql outputs the same start position for the same transaction)

5. Generate the corresponding insert recovery statement based on the location point in the previous step

Use the-B option to generate a rollback sql and check that the rollback sql is correct. (note: in real scenarios, the generated rollback SQL often needs to be further filtered to see if there are dml statements for other tables and statements for non-delete operations in this table, combined with grep, editor, etc.)

[root@localhost binlog2sql] # python binlog2sql.py-h272.168.1.201-P3306-uroot-p123456-dtest-tt-- start-file='mysql-bin.000029'-- start-position=468829113-- stop-position=468829304-B > b.sql

[root@localhost binlog2sql] # cat b.sql

INSERT INTO `test`.`t` (`id`, `name`) VALUES (94, 'gg'); # start 468829113 end 468829304 time 2018-05-10 11:06:52

INSERT INTO `test`.`t` (`id`, `name`) VALUES (87, 'gg'); # start 468829113 end 468829304 time 2018-05-10 11:06:52

INSERT INTO `test`.`t` (`id`, `name`) VALUES (80, 'ww'); # start 468829113 end 468829304 time 2018-05-10 11:06:52

INSERT INTO `test`.`t` (`id`, `name`) VALUES (73, 'ww'); # start 468829113 end 468829304 time 2018-05-10 11:06:52

INSERT INTO `test`.`t` (`id`, `name`) VALUES (68, 'what to do'); # start 468829113 end 468829304 time 2018-05-10 11:06:52

INSERT INTO `test`.`t` (`id`, `name`) VALUES (61, 'Planning if'); # start 468829113 end 468829304 time 2018-05-10 11:06:52

INSERT INTO `test`.`t` (`id`, `name`) VALUES (59, 'hh'); # start 468829113 end 468829304 time 2018-05-10 11:06:52

INSERT INTO `test`.`t` (`id`, `name`) VALUES (52, 'hg'); # start 468829113 end 468829304 time 2018-05-10 11:06:52

There are exactly 8 items compared with the delete statement.

6. After confirming with the developer that there is no problem with rolling back sql, execute the rollback statement and confirm that the rollback is successful.

MariaDB [test] > source / home/binlog2sql-master/binlog2sql/b.sql

Query OK, 1 row affected (0.06 sec)

Query OK, 1 row affected (0.06 sec)

Query OK, 1 row affected (0.09 sec)

Query OK, 1 row affected (0.05sec)

Query OK, 1 row affected (0.05sec)

Query OK, 1 row affected (0.05sec)

Query OK, 1 row affected (0.05sec)

Query OK, 1 row affected (0.05sec)

MariaDB [test] > select * from t

+-+ +

| | id | name |

+-+ +

| | 1 | ga |

| | 31 | ga |

| | 38 | ga |

| | 45 | ga |

| | 52 | hg |

| | 59 | hh |

| | 61 | Planning if |

| | 68 | what to do |

| | 73 | ww |

| | 80 | ww |

| | 87 | gg |

| | 94 | gg |

+-+ +

12 rows in set (0.00 sec)

seven.

(1) the key to flashback is to quickly filter out the SQL that really needs to be rolled back.

(2) first filter according to the database, table and time, and then filter more accurately according to the location.

(3) since the data is being written all the time, make sure that the rollback sql does not contain other data. It can be judged based on whether it is the same transaction, the number of lines of misoperation, the characteristics of the field value, and so on.

(4) if there is an error when performing a rollback sql, you need to find out the specific reason, usually because the corresponding data has changed. Because of the strict row mode, as long as there is a unique key (including the primary key), it will only report an error in which a piece of data does not exist, and there is no need to worry about updating the data that should not be operated.

(5) if the table to be rolled back is associated with other tables, it should be related to the respective side effects of rollback and non-rollback before determining the solution.

(6) the two most important points: screen out the correct SQL! Communicate clearly with the developer!

This is how the installation and use of the mysql flashback tool binlog2sql shared by Xiaobian is like. If you happen to have similar doubts, you might as well refer to the above analysis to understand. If you want to know more about it, you are 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.