Shulou(Shulou.com)06/01 Report--

A few days ago, a colleague encountered a problem with MySQL data recovery. He ran a update statement and forgot to add the where condition. As a result, it was too late. I simply confirmed whether there is a backup, no, whether the log is turned on, no. So this recovery is out of the question.

Of course, later he also spent some effort to repair the data one by one, things are over, the importance of data recovery, the importance of human operation is self-evident, but some time the job responsibilities still need to be moved down. I think we still need to sum up the problem of data recovery. I will talk about it from the following aspects.

Catalogue

A simple example of manual data recovery by ⊙

⊙ uses open source tools to restore the configuration of data

The practice of ⊙ using Open Source tools to recover data

Summary of ⊙

First of all, manual recovery of data, in fact, there are some ideas, one is through the full + binlog time, offset to restore. The other is to recover by parsing binlog, as long as the log format is row. Let's simply simulate and analyze the recovery mode of binlog.

A simple example of manually recovering data

If you take a look at the binlog, you can see that the current binlog is a log file with serial number 15.

> show binary logs

+-+ +

| | Log_name | File_size |

+-+ +

| | mysql-bin.000014 | 1073742219 | |

| | mysql-bin.000015 | 998953054 | |

+-+ +

2 rows in set (0.00 sec) to facilitate simulation, we can switch logs. The logs obtained after flush logs are as follows:

> show binary logs

+-+ +

| | Log_name | File_size |

+-+ +

| | mysql-bin.000015 | 999120424 | |

| | mysql-bin.000016 | 6722 | |

+-+ +

2 rows in set (0.00 sec) create table test

Create table test (id int not null primary key,name varchar (20), memo varchar (50)) ENGINE=InnoDB auto_increment=100 default charset=utf8; inserts several pieces of data

> insert into test values (1), (2) (2), (2), (2), (3), (3), (4), (4), (5), (5), (5), (5)

Query OK, 5 rows affected (0.00 sec)

Records: 5 Duplicates: 0 Warnings: 0 check the basic situation of the data:

> select * from test

+-- +

| | id | name | memo | |

+-- +

| | 1 | name1 | memo1 |

| | 2 | name2 | memo2 |

| | 3 | name3 | memo3 |

| | 4 | name4 | memo4 |

| | 5 | name5 | memo5 |

+-for testing convenience, mark a timestamp > select current_timestamp ()

+-+

| | current_timestamp () |

+-+

| | 2017-02-06 04:14:33 |

+-+ We begin to simulate the operation of DML.

> delete from test where id in (1pr 3)

Query OK, 2 rows affected (0.01sec)

> update test set memo='new' where id in (2pr 4)

Query OK, 2 rows affected (0.01sec)

Rows matched: 2 Changed: 2 Warnings: 0

> insert into test values (6 recording name 6 recording memo 6')

Query OK, 1 row affected (0. 00 sec) after doing the above three DML operations, let's mark the time.

> select current_timestamp ()

+-+

| | current_timestamp () |

+-+

| | 2017-02-06 04:15:44 |

Let's interpret the binlog and get a basic readable log according to the timestamp, in which there are still some data changes, but there are still some differences between the statement and the execution. Let's directly copy a copy of the binlog to the / tmp directory for parsing.

Mysqlbinlog-- no-defaults-v-- start-datetime= "2017-02-06 04:14:33"-- stop-datetime= "2017-02-06 04:15:44" / tmp/mysql-bin.000016-- the contents of the file result.sql generated by result-file=/tmp/result.sql are as follows, you can see that these operations are marked in detail in binlog, the data is basically clear at a glance, and part of the update before and after changes are at a glance. In fact, the more difficult thing in DML is update, and insert,delete is an addition and subtraction.

Delete operation corresponds to SQL in binlog log

# DELETE FROM `test`.`test`

# WHERE

# @ 1room1

# @ 2roomname 1'

# @ 3roommemo1'

# DELETE FROM `test`.`test`

# WHERE

# @ 1y3

# @ 2roomname 3'

# @ 3roommemo3'

# at 998969666update operation corresponds to SQL in binlog log

# UPDATE `test`.`test`

# WHERE

# @ 1room2

# @ 2roomname 2'

# @ 3roommemo2'

# SET

# @ 1room2

# @ 2roomname 2'

# @ 3percent new'

# UPDATE `test`.`test`

# WHERE

# @ 1mm 4

# @ 2roomname 4'

# @ 3roommemo4'

# SET

# @ 1mm 4

# @ 2roomname 4'

# @ 3percent new'

# at 998971422

Insert operation corresponds to SQL in binlog log

# INSERT INTO `test`.`test`

# SET

# @ 1: 6

# @ 2roomname 6'

# @ 3roommemo6'

# at 998973859 is worth mentioning that the-v (--verbose) option reconstructs line events into commented-out pseudo-SQL statements, and you can use the-vv option if you want to see more detailed information, so you can include comments for some data types and meta-information.

For example:

-results of vv:

# DELETE FROM `test`.`test`

# WHERE

# @ 1room1 / * INT meta=0 nullable=0 is_null=0 * /

# @ 2roomname 1'/ * VARSTRING (60) meta=60 nullable=1 is_null=0 * /

Back to the problem of data recovery, if you need to recover data manually, you need to do several things. One is to splice the runnable SQL statements according to the field marks, and then execute them in reverse order.

Restore the configuration of data using open source tools

As you can see from the above steps, if manual repair can actually be achieved, but there are a lot of manual operations, and it is a good tool to simplify your work at this time. I tried binglog2sql, an open source tool, which was also launched by Dianping's DBA team.

This tool is developed by Python, and of course there are some dependent libraries and environments that need to be configured. If your server is in a networked environment, it will be much easier.

It can be done in two steps.

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

Pip install-r requirements.txt-additional plug-ins will be installed and if not, there will be some extra work for you to do. For example, my environment does not even have pip. You can download the corresponding script on another server and deploy it.

# wget "https://pypi.python.org/packages/source/p/pip/pip-1.5.4.tar.gz#md5=834b2904f92d46aaa333267fb1c922bb"-some no-check-certificate components may have the following errors when deploying pip.

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

The practice of using open source tools to recover data

After completing the configuration of the environment, there are still some similarities between the tools and mysqlbinlog. The good thing is that there are some auxiliary functions.

We create a user admin to parse.

GRANT SELECT, REPLICATION SLAVE, REPLICATION CLIENT ON *. * TO 'admin'@'127.0.0.1' IDENTIFIED BY' admin';, for example, we use the following command to parse binlog to get the SQL within the specified timestamp range, where we limit the data to test

The contents of the file obtained by python binlog2sql/binlog2sql.py-h227.0.0.1-P3306-uadmin-padmin-dtest-- start-file='mysql-bin.000016'-- start-datetime='2017-02-06 04 padmin 14 padmin 33'--stop-datetime='2017-02-06 04 purse 15 15 charge 44'> / tmp/tmp.log are as follows:

# cat / tmp/tmp.log

DELETE FROM `test`.`test`WHERE `memo` = 'memo1' AND `id` = 1 AND `name` =' name1' LIMIT 1; # start 11127 end 11321 time 2017-02-06 04:15:23

DELETE FROM `test`.`test`WHERE `memo` = 'memo3' AND `id` = 3 AND `name` =' name3' LIMIT 1; # start 11127 end 11321 time 2017-02-06 04:15:23

UPDATE `test`.`test`SET `memo` = 'new', `id` = 2, `name` =' name2' WHERE `memo` = 'memo2' AND `id` = 2 AND `name` =' name2' LIMIT 1; # start 11400 end 11625 time 2017-02-06 04:15:29

UPDATE `test`.`test`SET `memo` = 'new', `id` = 4, `name` =' name4' WHERE `memo` = 'memo4' AND `id` = 4 AND `name` =' name4' LIMIT 1; # start 11400 end 11625 time 2017-02-06 04:15:29

INSERT INTO `test`.`test` (`memo`, `id`, `name`) VALUES ('memo6', 6,' name6'); # start 12062 end 12239 time 2017-02-06 04:15:37 actually looks easy.

If you want to get flashback statements, there is an option for flashback that parses and adjusts the order based on the original.

The contents obtained by python binlog2sql/binlog2sql.py-h227.0.0.1-P3306-uadmin-padmin-dtest-- flashback-- start-file='mysql-bin.000016'-- start-datetime='2017-02-06 04 are as follows:

# cat / tmp/tmp.log

DELETE FROM `test`.`test`WHERE `memo` = 'memo6' AND `id` = 6 AND `name` =' name6' LIMIT 1; # start 12062 end 12239 time 2017-02-06 04:15:37

UPDATE `test`.`test`SET `memo` = 'memo4', `id` = 4, `name` =' name4' WHERE `memo` = 'new' AND `id` = 4 AND `name` =' name4' LIMIT 1; # start 11400 end 11625 time 2017-02-06 04:15:29

UPDATE `test`.`test`SET `memo` = 'memo2', `id` = 2, `name` =' name2' WHERE `memo` = 'new' AND `id` = 2 AND `name` =' name2' LIMIT 1; # start 11400 end 11625 time 2017-02-06 04:15:29

INSERT INTO `test`.`test` (`memo`, `id`, `name`) VALUES ('memo3', 3,' name3'); # start 11127 end 11321 time 2017-02-06 04:15:23

INSERT INTO `test`.`test` (`memo`, `id`, `name`) VALUES ('memo1', 1,' name1'); # start 11127 end 11321 time 2017-02-06 04:15:23 after running the above statement, check the data again, and the data will return to normal.

> select * from test

+-- +

| | id | name | memo | |

+-- +

| | 1 | name1 | memo1 |

| | 2 | name2 | memo2 |

| | 3 | name3 | memo3 |

| | 4 | name4 | memo4 |

| | 5 | name5 | memo5 |

+-- +

5 rows in set (0.00 sec)

Summary

In fact, there are some tips for DML flashback, but for DDL flashback, it is relatively troublesome. We will also follow up the work in this area in the future. But there are relatively more flashback scenarios in DML, and we need to pay particular attention to it.

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.