ITPub博客

首页 > 数据库 > MySQL > undrop-for-innodb恢复drop的表

undrop-for-innodb恢复drop的表

原创 MySQL 作者:pingdanorcale 时间:2020-07-31 14:23:43 0 删除 编辑

说明:

Undrop 恢复是通过扫描文件或磁盘设备,然后解析innodb 数据页进而恢复丢失的数据,对于drop truncate 进行恢复。目前mysql8.0 以后版本暂未提供支持。

准备

安装完之后,会在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/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2009-01-02

  • 博文量
    58
  • 访问量
    106380