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

Editor to share with you how to use undrop in MySQL to restore drop table, I believe that most people do not know much about it, so share this article for your reference, I hope you will learn a lot after reading this article, let's go to know it!

In MySQL, you can use a programming language (such as Python) to parse the reverse operation of DML in binlog to achieve the effect of flashback, or manual parsing if there is not much data. This is also a basic principle of many DML Flashback that we encounter now. And if it's a DDL, such as DROP, you'll have to sigh.

A very good project on GitHub is undrop, which is based on InnoDB, which means that if your environment has drop operation, it is still possible to recover. Of course, this process needs to be cautious. It is recommended that you practice the test demonstration before making a decision in the test environment. Of course, we hope this tool will never be useful.

It seems that there are not many people following this project right now, which has also been interpreted in Ali's core monthly report recently. Http://mysql.taobao.org/monthly/2017/11/01/, so it caught my attention and tested it myself with curiosity.

Installation and deployment is simple.

Gitclone https://github.com/twindb/undrop-for-innodb.git

Make

It is important to note that your environment probably does not have a flex package, so you can install it before make.

Yum install flex

Then let's try to compile a file, sys_parser.c, and configure basedir first

Basedir=/usr/local/mysql_5.7

Gcc `$ basedir/bin/mysql_config-- crooms`` $basedir/bin/mysql_config-- libs`-o sys_parser sys_parser.c

One of the basic principles of this tool is to parse ibdata, from which we parse the information of the page we need, that is, the corresponding data dictionary.

Mysql > show tables like'% SYS%'

+-+

| | Tables_in_information_schema (% SYS%) |

+-+

| | INNODB_SYS_DATAFILES |

| | INNODB_SYS_VIRTUAL |

| | INNODB_SYS_INDEXES |

| | INNODB_SYS_TABLES |

| | INNODB_SYS_FIELDS |

| | INNODB_SYS_TABLESPACES |

| | INNODB_SYS_FOREIGN_COLS |

| | INNODB_SYS_COLUMNS |

| | INNODB_SYS_FOREIGN |

| | INNODB_SYS_TABLESTATS |

+-+

10 rows in set (0.00 sec)

These four data dictionaries need to be paid more attention to.

SYS_COLUMNS | SYS_FIELDS | SYS_INDEXES | SYS_TABLES

This tool is quite functional, there is a sakila folder in the directory, is some sample scripts, we can create some seemingly business-related tables, but in fact we can do a subtraction, just create a table that we need to focus on, and then import the data.

-rw-r--r--. 1 jeanron jeanron 3231472 Dec 16 2011 sakila-data.sql

-rw-r--r--. 1 jeanron jeanron 50019 Dec 16 2011 sakila.mwb

-rw-r--r--. 1 jeanron jeanron 23099 Dec 16 2011 sakila-schema.sql

Otherwise, this set of table structure is quite complex, there are foreign key associations, and it will take some extra time to process.

For the whole initialization work, we take the table actor as an example, manually extract the table-building statement of actor, and then run the sakila-data.sql script.

Mysql > checksum table actor

+-+ +

| | Table | Checksum |

+-+ +

| | sakila.actor | 1702520518 | |

+-+ +

1 row in set (0.00 sec)

Query the amount of data in table actor.

Mysql > select count (*) from actor

+-+

| | count (*) |

+-+

| | 200 |

+-+

1 row in set (0.00 sec)

To parse ibdata, you can do it using stream_parser. We specify the path to the ibdata.

#. / stream_parser-f / U01/mysql_5.7_test/n1/ibdata1

Opening file: / U01/mysql_5.7_test/n1/ibdata1

File information:

ID of device containing file: 64512

Inode number: 152382

Protection: 100640 (regular file)

Number of hard links: 1

User ID of owner: 501

Group ID of owner: 501

Device ID (if special file): 0

Blocksize for filesystem I/O: 4096

Number of blocks allocated: 155648

Time of last access: 1509475823 Wed Nov 1 02:50:23 2017

Time of last modification: 1509476206 Wed Nov 1 02:56:46 2017

Time of last status change: 1509476206 Wed Nov 1 02:56:46 2017

Total size, in bytes: 79691776 (76.000 MiB)

Size to process: 79691776 (76.000 MiB)

All workers finished in 1 sec

The whole operation is very fast, and a folder of pages-ibdata1 is generated under the current directory, with two folders below.

Drwxr-xr-x. 2 root root 4096 Nov 1 02:57 FIL_PAGE_INDEX

Drwxr-xr-x. 2 root root 4096 Nov 1 02:57 FIL_PAGE_TYPE_BLOB

For example, under the INDEX directory, there are a large number of pages, such as the following output.

-rw-r--r--. 1 root root 32768 Nov 1 02:57 0000000000000001.page

-rw-r--r--. 1 root root 32768 Nov 1 02:57 0000000000000002.page

-rw-r--r--. 1 root root 32768 Nov 1 02:57 0000000000000003.page

-rw-r--r--. 1 root root 32768 Nov 1 02:57 0000000000000004.page

-rw-r--r--. 1 root root 32768 Nov 1 02:57 0000000000000005.page

-rw-r--r--. 1 root root 16384 Nov 1 02:57 0000000000000011.page

-rw-r--r--. 1 root root 16384 Nov 1 02:57 0000000000000012.page

-rw-r--r--. 1 root root 16384 Nov 1 02:57 0000000000000013.page

-rw-r--r--. 1 root root 16384 Nov 1 02:57 0000000000000014.page

-rw-r--r--. 1 root root 32768 Nov 1 02:57 0000000000000015.page

-rw-r--r--. 1 root root 32768 Nov 1 02:57 0000000000000016.page

-rw-r--r--. 1 root root 16384 Nov 1 02:57 0000000000000017.page

-rw-r--r--. 1 root root 16384 Nov 1 02:57 0000000000000032.page

-rw-r--r--. 1 root root 32768 Nov 1 02:57 0000000000000033.page

-rw-r--r--. 1 root root 16384 Nov 1 02:57 18446744069414584320.page

To parse the contents, we can use another command, c_parser, to filter based on the keyword sakila/actor.

. / c_parser-4Df pages-ibdata1/FIL_PAGE_INDEX/0000000000000001.page-t dictionary/SYS_TABLES.sql | grep 'sakila/actor'

Parameter 4DF represents the file format (4 represents the file format is REDUNDANT), D (D means only deleted records are restored), and f generates files

Then use c_parser in turn to parse the page to get the information of several other data dictionaries, such as (SYS_INDEXES,SYS_COLUMNS,SYS_FIELDS)

The above is all the contents of the article "how to use undrop to restore drop tables in MySQL". 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.