MYSQL 数据库还原 drop-for-innodb_undrop-for-innodb-优快云博客

安装和准备数据

cd /tmp
yum install -y git  make gcc flex bison
git clone https://github.com/twindb/undrop-for-innodb.git
cd undrop-for-innodb
make
#/u01/app/mysql/db/binmysql_config MySQL安装目录
make sys_parser /u01/app/mysql/db/binmysql_config cc -o sys_parser sys_parser.c `mysql_config --cflags` `mysql_config --libs`
ls

mysql> CREATE TABLE t1 (
    ->   id  int(11) NOT NULL,
    ->   name varchar(10) DEFAULT NULL,
    ->   PRIMARY KEY (id),
    ->   KEY name (name)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.01 sec)

mysql> insert into t1(id,name) values(1,"1"),(2,"2"),(3,"3");
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> drop table t1;
Query OK, 0 rows affected (0.01 sec);

stream_parser

cd /tmp/undrop-for-innodb
[root@k8s-master01 undrop-for-innodb]# ./stream_parser -f /data/mysql/engine/innodata/ibdata1
Opening file: /data/mysql/engine/innodata/ibdata1
File information:

ID of device containing file:        64768
inode number:                    106469449
protection:                         100640 (regular file)
number of hard links:                    1
user ID of owner:                     1000
group ID of owner:                    1000
device ID (if special file):             0
blocksize for filesystem I/O:         4096
number of blocks allocated:         262144
time of last access:            1575623631 Fri Dec  6 17:13:51 2019
time of last modification:      1576120124 Thu Dec 12 11:08:44 2019
time of last status change:     1576120124 Thu Dec 12 11:08:44 2019
total size, in bytes:            134217728 (128.000 MiB)

Size to process:                 134217728 (128.000 MiB)
Worker(0): 56.25% done. 2019-12-12 11:10:52 ETA(in 00:00:00). Processing speed: 64.000 MiB/sec
All workers finished in 1 sec

解析页内容

#dumps/default 不能修改

cd /tmp/undrop-for-innodb

mkdir -p dumps/default
./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/0000000000000003.page -t dictionary/SYS_INDEXES.sql >./dumps/default/SYS_INDEXES 2>./dumps/default/SYS_INDEXES.sql
./c_parser -4Df pages-ibdata1/FIL_PAGE_INDEX/0000000000000002.page -t dictionary/SYS_COLUMNS.sql >./dumps/default/SYS_COLUMNS 2>./dumps/default/SYS_COLUMNS.sql
./c_parser -4Df pages-ibdata1/FIL_PAGE_INDEX/0000000000000004.page -t dictionary/SYS_FIELDS.sql >./dumps/default/SYS_FIELDS 2>./dumps/default/SYS_FIELDS.sql

导入

mysql> create database testback;
Query OK, 1 row affected (0.01 sec)

mysql> use testback;

#导入表结构

source /tmp/undrop-for-innodb/dictionary/SYS_TABLES.sql;
source /tmp/undrop-for-innodb/dictionary/SYS_INDEXES.sql;
source /tmp/undrop-for-innodb/dictionary/SYS_FIELDS.sql;
source /tmp/undrop-for-innodb/dictionary/SYS_COLUMNS.sql;

#导入表数据
source /tmp/undrop-for-innodb/dumps/default/SYS_TABLES.sql;
source /tmp/undrop-for-innodb/dumps/default/SYS_INDEXES.sql;
source /tmp/undrop-for-innodb/dumps/default/SYS_FIELDS.sql;
source /tmp/undrop-for-innodb/dumps/default/SYS_COLUMNS.sql;

[root@k8s-master01 undrop-for-innodb]# ./sys_parser -h127.0.0.1 -uroot -p123.com -d dictionary test_1/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;

#参考

http://mysql.taobao.org/monthly/2017/11/01/

https://blog.youkuaiyun.com/cpongo1/article/details/89534185