undrop-for-innodb恢復drop的表

pingdanorcale發表於2020-07-31

說明：

Undrop 恢復是通過掃描檔案或磁碟裝置，然後解析innodb 資料頁進而恢復丟失的資料，對於drop 、truncate 進行恢復。目前mysql8.0 以後版本暫未提供支援。

準備

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

安裝完之後，會在undrop-for-innodb 目錄下生成stream_parser 和c_parser 檔案

 

 

gcc `/mysql/mysql5.7/bin/mysql_config --cflags` `/mysql/mysql5.7/bin/mysql_config --libs` -o sys_parser sys_parser.c

./sys_parser  -uroot -pmysql5.7  -S  /mysql/mysql5.7/run/mysql.sock -d dictionary ttdb/t1 （用這個）

用make 是會報錯的

 

二、 模擬 mysql 表誤刪操作

mysql>

mysql>

mysql> create database  ttdb;

Query OK, 1 row affected (0.07 sec)

 

mysql>

mysql> use ttdb;

Database changed

mysql> create table t1(id int);

mysql>

mysql> insert into  t1 (id) values (1);

Query OK, 1 row affected (0.35 sec)

 

mysql>

mysql> select * from t1;

+------+

| id   |

+------+

|    1 |

+------+

1 row in set (0.00 sec)

 

mysql>

mysql>

mysql>  insert into  t1 (id) values (2),(3),(4);

Query OK, 3 rows affected (0.36 sec)

Records: 3  Duplicates: 0  Warnings: 0

 

mysql>

mysql>

mysql> select  * from t1;

+------+

| id   |

+------+

|    1 |

|    2 |

|    3 |

|    4 |

+------+

4 rows in set (0.00 sec)

 

解析四張關鍵字典表的資料，獲取已刪表的資料結構。

三、恢復

mkdir -p dumps/default

mysql> drop table t1;

Query OK, 0 rows affected (0.02 sec)

 

[root@kbase data]# cd ttdb/

[root@kbase ttdb]# ls

t1.ibd

[root@kbase ttdb]#

[root@kbase ttdb]# ls

[root@kbase ttdb]# pwd

/mysql/mysql8.21/data/ttdb

[root@kbase undrop-for-innodb-develop]# ./stream_parser -f /mysql/mysql8.21/data/ibdata1

Opening file: /mysql/mysql8.21/data/ibdata1

File information:

 

ID of device containing file:        64768

inode number:                     70038496

protection:                         100640 (regular file)

number of hard links:                    1

user ID of owner:                     1002

group ID of owner:                    1002

device ID (if special file):             0

blocksize for filesystem I/O:         4096

number of blocks allocated:          24576

time of last access:            1594864392 Thu Jul 16 09:53:12 2020

time of last modification:      1594957088 Fri Jul 17 11:38:08 2020

time of last status change:     1594957088 Fri Jul 17 11:38:08 2020

total size, in bytes:             12582912 (12.000 MiB)

 

Size to process:                  12582912 (12.000 MiB)

掃描完之後，會在當前目錄下生成相關的目錄：

[root@kbase pages-ibdata1]# ls

FIL_PAGE_INDEX  FIL_PAGE_TYPE_BLOB

[root@kbase pages-ibdata1]# cd FIL_PAGE_INDEX

[root@kbase FIL_PAGE_INDEX]# ls

0000000000000001.page  0000000000000005.page  0000000000000014.page  0000000000000020.page  0000000000000026.page

0000000000000002.page  0000000000000011.page  0000000000000015.page  0000000000000021.page  0000000000000033.page

0000000000000003.page  0000000000000012.page  0000000000000016.page  0000000000000024.page  0000000000000040.page

0000000000000004.page  0000000000000013.page  0000000000000017.page  0000000000000025.page  18446744069414584320.page

[root@kbase FIL_PAGE_INDEX]# ls

0000000000000001.page  0000000000000005.page  0000000000000014.page  0000000000000020.page  0000000000000026.page

0000000000000002.page  0000000000000011.page  0000000000000015.page  0000000000000021.page  0000000000000033.page

0000000000000003.page  0000000000000012.page  0000000000000016.page  0000000000000024.page  0000000000000040.page

0000000000000004.page  0000000000000013.page  0000000000000017.page  0000000000000025.page  18446744069414584320.page

  ./c_parser  -4Df  pages-ibdata1/FIL_PAGE_INDEX/0000000000000001.page -t  dictionary/SYS_TABLES.sql >./dumps/default/SYS_TABLES  2>./dumps/default/SYS_

TABLES.sql

 

./c_parser  -4Df  pages-ibdata1/FIL_PAGE_INDEX/0000000000000002.page  -t  dictionary/SYS_COLUMNS.sql >./dumps/default/SYS_COLUMNS  2>./dumps/default/S

YS_COLUMNS.sql

  475  ./c_parser  -4Df  pages-ibdata1/FIL_PAGE_INDEX/0000000000000003.page  -t  dictionary/SYS_INDEXES.sql >./dumps/default/SYS_INDEXES  2>./dumps/default/S

YS_INDEXES.sql

  476   ./c_parser  -4Df  pages-ibdata1/FIL_PAGE_INDEX/0000000000000004.page  -t  dictionary/SYS_FIELDS.sql >./dumps/default/SYS_FIELDS  2>./dumps/default/SY

S_FIELDS.sql

 

create database dictionary;

 

./sys_parser  -uroot -pmysql5.7   -d dictionary ttdb/t1

CREATE TABLE `t1`(

        `id` INT NOT NULL,

        `name` VARCHAR(20) CHARACTER SET 'utf8' COLLATE 'utf8_general_ci',

        `age` INT,

        PRIMARY KEY (`id`)

) ENGINE=InnoDB;


來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/10201716/viewspace-2708352/，如需轉載，請註明出處，否則將追究法律責任。

相關文章