mysql恢复drop的表_MySQL删库不跑路-无备份Drop表恢复

MySQL删库不跑路-无备份Drop恢复

说在前头

库一定要备份！！！

库一定要备份！！！

库一定要备份！！！

由于当前环境基本上都会开启 innodb_file_per_table ，因此测试 innodb_file_per_table = 1情况下的数据恢复，innodb_file_per_table = 0 的操作差不多不要太指望工具能恢复户多少数据，会有多种情况花式丢页数据，导致无法恢复

现象1：drop table 后，有DDL操作 + insert ..select 操作，丢失页

现象2：使用命令行shutdown库或kill 进程，丢失页

各种花式丢页，所以备份一定要做好，不要太指望这个

环境

MySQL 5.7.27-log

系统： CentOS Linux release 7.6.1810

恢复工具: undrop-for-innodb

恢复工具安装

git clone twindb/undrop-for-innodb

yum install -y make gcc flex bison

cd undrop-for-innodb/

make完成后当前目录下出现可执行文件 c_parser 和 stream_parser

表结构恢复工具安装

cd undrop-for-innodb/

[root@testinit undrop-for-innodb]# gcc `/usr/local/mysql/bin/mysql_config --cflags` `/usr/local/mysql/bin/mysql_config --libs` -o sys_parser sys_parser.c/usr/local/mysql/bin 是当前mysql安装的 basedir,完成后当前目录下会出现可执行文件 sys_parser

模拟数据表误删

root@mysqldb 04:27: [none]> create database dtest;

Query OK, 1 row affected (0.00 sec)

root@mysqldb 04:27: [(none)]> use dtest

Database changed

root@mysqldb 04:27: [(dtest)]>

CREATE TABLE `dtest1` ( `id` int(11) NOT NULL AUTO_INCREMENT,

`a` varchar(10) DEFAULT NULL,

`b` varchar(10) DEFAULT NULL,

PRIMARY KEY (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4;

root@mysqldb 22:38: [dtest]> insert into dtest1 values(1,1,1),(2,2,2),(3,3,3),(4,4,4),(5,5,5);

root@mysqldb 04:27: [dtest]> update dtest1 set b = '222' where id = 2;

Query OK, 1 row affected (0.01 sec)

Rows matched: 1 Changed: 1 Warnings: 0

root@mysqldb 04:27: [dtest]> checksum table dtest1;

+--------------+------------+

| Table | Checksum |

+--------------+------------+

| dtest.dtest1 | 3175768141 |

+--------------+------------+

1 row in set (0.00 sec)

root@mysqldb 04:28: [dtest]> select * from dtest1;

+----+------+------+

| id | a | b |

+----+------+------+

| 1 | 1 | 1 |

| 2 | 2 | 222 |

| 3 | 3 | 3 |

| 4 | 4 | 4 |

| 5 | 5 | 5 |

+----+------+------+

5 rows in set (0.00 sec)

root@mysqldb 04:30: [dtest]> create table dtest2 like dtest1;

Query OK, 0 rows affected (0.02 sec)

root@mysqldb 04:30: [dtest]> create table dtest3 like dtest1;

Query OK, 0 rows affected (0.01 sec)

root@mysqldb 04:30: [dtest]> insert into dtest2 select * from dtest1;

Query OK, 5 rows affected (0.00 sec)

Records: 5 Duplicates: 0 Warnings: 0

root@mysqldb 04:30: [dtest]> alter table dtest3 add c varchar(10);

Query OK, 0 rows affected (0.02 sec)

Records: 0 Duplicates: 0 Warnings: 0

root@mysqldb 04:32: [dtest]> insert into dtest3 select t.*,'123456' from dtest1 t;

Query OK, 5 rows affected (0.00 sec)

Records: 5 Duplicates: 0 Warnings: 0

# 模拟误删表

root@mysqldb 04:32: [dtest]> drop table dtest1;

Query OK, 0 rows affected (0.00 sec)

# 误删后肯定还会有其他业务会在操作其他表,模拟还有其他操作

root@mysqldb 04:34: [dtest]> update dtest2 set b = 'bbbbbbbb' where id = 2;

Query OK, 1 row affected (0.00 sec)

Rows matched: 1 Changed: 1 Warnings: 0

备份复制一份进行恢复【也可以不操作这一步，直接分析源库的】可以不停止源MySQL,但若出现DDL操作，可能会导致数据丢失恢复不出来

停机并拷贝备份，业务无法停机也能直接操作下一步，停机是为了降低莫名丢页问题

不要 kill 进程，不要在mysql命令项内执行 shutdown来关闭Mysql

mysqladmin -uroot -S /tmp/mysql.sock shutdown 或 service mysqld stop

cp -R /data/mysql_data/ /data/mysql_data2

获取表对应页

当前操作在源库上操作，mysql已关闭

1-解析ibdata1

[root@testinit undrop-for-innodb]# ./stream_parser -f /data/mysql_data/ibdata1

Opening file: /data/mysql_data/ibdata1

File information:

ID of device containing file: 64768

inode number: 16935187

protection: 100640 (regular file)

number of hard links: 1

user ID of owner: 998

group ID of owner: 1000

device ID (if special file): 0

blocksize for filesystem I/O: 4096

number of blocks allocated: 2097152

time of last access: 1589574685 Sat May 16 04:31:25 2020

time of last modification: 1589574876 Sat May 16 04:34:36 2020

time of last status change: 1589574876 Sat May 16 04:34:36 2020

total size, in bytes: 1073741824 (1.000 GiB)

Size to process: 1073741824 (1.000 GiB)

Worker(0): 3.12% done. 2020-05-16 04:37:21 ETA(in 00:00:41). Processing speed: 24.000 MiB/sec

...省略

All workers finished in 11 sec

2-解析数据文件所在磁盘页

[root@testinit undrop-for-innodb]# df -k

Filesystem 1K-blocks Used Available Use% Mounted on

/dev/mapper/centos-root 15767552 8275312 7492240 53% /

devtmpfs 485780 0 485780 0% /dev

tmpfs 497948 0 497948 0% /dev/shm

tmpfs 497948 7728 490220 2% /run

tmpfs 497948 0 497948 0% /sys/fs/cgroup

/dev/sda1 1038336 135380 902956 14% /boot

tmpfs 99592 0 99592 0% /run/user/0

[root@testinit undrop-for-innodb]# ./stream_parser -f /dev/mapper/centos-root -t 15767552k

Opening file: /dev/mapper/centos-root

File information:

ID of device containing file: 5

inode number: 18278

protection: 60660 (block device)

number of hard links: 1

user ID of owner: 0

group ID of owner: 6

device ID (if special file): 64768

blocksize for filesystem I/O: 4096

number of blocks allocated: 0

time of last access: 1589538693 Fri May 15 05:31:33 2020

time of last modification: 1589538693 Fri May 15 05:31:33 2020

time of last status change: 1589538693 Fri May 15 05:31:33 2020

total size, in bytes: 0 (0.000 exp(+0))

Size to process: 16145973248 (15.037 GiB)

Worker(0): 1.09% done. 2020-05-15 05:52:55 ETA(in 00:11:07). Processing speed: 22.813 MiB/sec

Worker(0): 2.13% done. 2020-05-15 05:46:34 ETA(in 00:04:43). Processing speed: 53.234 MiB/sec

...省略

All workers finished in 85 sec完成后当前目录下会生成俩个page开头的文件

pages-centos-root

pages-ibdata1

解析页获取表结构与表对应页ID

1-找到表的ID

[root@testinit undrop-for-innodb]# ./c_parser -6Df ./pages-ibdata1/FIL_PAGE_INDEX/0000000000000001.page -t ./dictionary/SYS_TABLES.sql | grep 'dtest/dtest1'

SET FOREIGN_KEY_CHECKS=0;

LOAD DATA LOCAL INFILE '/root/undrop-for-innodb/dumps/default/SYS_TABLES' REPLACE INTO TABLE `SYS_TABLES` CHARACTER SET UTF8 FIELDS TERMINATED BY '\t' OPTIONALLY ENCLOSED BY '"' LINES STARTING BY 'SYS_TABLES\t' (`NAME`, `ID`, `N_COLS`, `TYPE`, `MIX_ID`, `MIX_LEN`, `CLUSTER_NAME`, `SPACE`);

-- STATUS {"records_expected": 54, "records_dumped": 6, "records_lost": true} STATUS END

000000000B06260000002F0221SYS_TABLES"dtest/dtest1"40333080""28

000000000B06260000002F0221SYS_TABLES"dtest/dtest1"40333080""28表ID为表名后面的那个数字，这里为 40

2-利用找出来的表ID，查找数据所在的磁盘页ID

[root@testinit undrop-for-innodb]# ./c_parser -6Df ./pages-ibdata1/FIL_PAGE_INDEX/0000000000000003.page -t ./dictionary/SYS_INDEXES.sql | grep 40

SET FOREIGN_KEY_CHECKS=0;

LOAD DATA LOCAL INFILE '/root/undrop-for-innodb/dumps/default/SYS_INDEXES' REPLACE INTO TABLE `SYS_INDEXES` CHARACTER SET UTF8 FIELDS TERMINATED BY '\t' OPTIONALLY ENCLOSED BY '"' LINES STARTING BY 'SYS_INDEXES\t' (`TABLE_ID`, `ID`, `NAME`, `N_FIELDS`, `TYPE`, `SPACE`, `PAGE_NO`);

-- STATUS {"records_expected": 64, "records_dumped": 8, "records_lost": true} STATUS END

000000000B06260000002F0145SYS_INDEXES4041"PRIMARY"13284294967295

000000000B06260000002F0145SYS_INDEXES4041"PRIMARY"13284294967295一般会在 0000000000000003.page 中找到，若不存在，则使用下面的语句循环查一下看看

for i in `ls ./pages-ibdata1/FIL_PAGE_INDEX/`; do echo "$i"; ./c_parser -6Df ./pages-ibdata1/FIL_PAGE_INDEX/$i -t ./dictionary/SYS_INDEXES.sql | grep -w 40; done这里可以看到，ID 40 对应的page页为 41，41就是表数据所在磁盘页

3-查看对应的页是否存在

查看恢复页存不存在，不存在则凉凉，当前示例需要查看对应的页 41 是否存在

ls ./pages-centos-root/FIL_PAGE_INDEX/

4-获取表结构(若已知被删除表的表结构，这一步跳过)

上面的页已分析完毕，可以启动mysql了【之前没关的话，忽略】

mysqld_safe --user=mysql &

4.1-执行recover_dictionary.sh

执行undrop-for-innodb目录下的recover_dictionary.sh，会创建一个`test`库，并生成SYS开头的4张表，若test库已存在，则直接生成在这个库下

[root@testinit undrop-for-innodb]# ./recover_dictionary.sh

Generating dictionary tables dumps... OK

Creating test database ... OK

Creating dictionary tables in database test:

SYS_TABLES ... OK

SYS_COLUMNS ... OK

SYS_INDEXES ... OK

SYS_FIELDS ... OK

All OK

Loading dictionary tables data:

SYS_TABLES ... 30 recs OK

SYS_COLUMNS ... 146 recs OK

SYS_INDEXES ... 36 recs OK

SYS_FIELDS ... 48 recs OK

All OK

生成库表如下：

root@mysqldb 06:21: [(none)]> use test;

Database changed

root@mysqldb 06:21: [test]> show tables;

+----------------+

| Tables_in_test |

+----------------+

| SYS_COLUMNS |

| SYS_FIELDS |

| SYS_INDEXES |

| SYS_TABLES |

+----------------+

4 rows in set (0.00 sec)

可以查看字典信息同样得知表ID与对应页ID

root@mysqldb 06:22: [(none)]> use test

Database changed

# 获得表ID 40

root@mysqldb 06:22: [test]> select * from SYS_TABLES where name like 'dtest/dtest1';

+--------------+----+--------+------+--------+---------+--------------+-------+

| NAME | ID | N_COLS | TYPE | MIX_ID | MIX_LEN | CLUSTER_NAME | SPACE |

+--------------+----+--------+------+--------+---------+--------------+-------+

| dtest/dtest1 | 40 | 3 | 33 | 0 | 80 | | 28 |

+--------------+----+--------+------+--------+---------+--------------+-------+

1 row in set (0.00 sec)

# 获得页ID 41

root@mysqldb 06:22: [test]> select * from SYS_INDEXES where table_id=40;

+----------+----+---------+----------+------+-------+------------+

| TABLE_ID | ID | NAME | N_FIELDS | TYPE | SPACE | PAGE_NO |

+----------+----+---------+----------+------+-------+------------+

| 40 | 41 | PRIMARY | 1 | 3 | 28 | 4294967295 |

+----------+----+---------+----------+------+-------+------------+

1 row in set (0.00 sec)

4.2-获得结构

# 创建libmysqlclient.so.20软链，否则可能报错

[root@testinit undrop-for-innodb]# ln -s /usr/local/mysql-5.7.27-linux-glibc2.12-x86_64/lib/libmysqlclient.so.20 /usr/lib64/libmysqlclient.so.20

# 密码要写上去，可以创建个权限高一点的，用完删除这个用户

[root@testinit undrop-for-innodb]# ./sys_parser -h 127.0.0.1 -u root -p123456 -d test dtest/dtest1

CREATE TABLE `dtest1`(

`id` INT NOT NULL,

`a` VARCHAR(10) CHARACTER SET 'utf8mb4' COLLATE 'utf8mb4_general_ci',

`b` VARCHAR(10) CHARACTER SET 'utf8mb4' COLLATE 'utf8mb4_general_ci',

PRIMARY KEY (`id`)

) ENGINE=InnoDB;

解析页数据导出

1-创建需恢复表的表结构SQL

[root@testinit undrop-for-innodb]# cat /tmp/dtest1.sql

CREATE TABLE `dtest1` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`a` varchar(10) DEFAULT NULL,

`b` varchar(10) DEFAULT NULL,

PRIMARY KEY (`id`)

) ENGINE=InnoDB;

2 查看数据是否存在【可略】

# 查看数据

[root@testinit undrop-for-innodb]# ./c_parser -6f pages-centos-root/FIL_PAGE_INDEX/0000000000000041.page -t /tmp/dtest1.sql |head -5

SET FOREIGN_KEY_CHECKS=0;

LOAD DATA LOCAL INFILE '/root/undrop-for-innodb/dumps/default/dtest1' REPLACE INTO TABLE `dtest1` CHARACTER SET UTF8 FIELDS TERMINATED BY '\t' OPTIONALLY ENCLOSED BY '"' LINES STARTING BY 'dtest1\t' (`id`, `a`, `b`);

-- STATUS {"records_expected": 5, "records_dumped": 5, "records_lost": false} STATUS END

-- Page id: 3, Format: COMPACT, Records list: Valid, Expected records: (5 5)

000000000907A7000000250110dtest11"1""1"

00000000090C2A000000300110dtest12"2""222"

000000000907A7000000250128dtest13"3""3"

000000000907A7000000250134dtest14"4""4"

00000B18 B3000000290134 da 4 "4" "4"

3-创建指定的恢复目录

mkdir -p /root/undrop-for-innodb/dumps/default

4-数据导出

./c_parser -6f pages-centos-root/FIL_PAGE_INDEX/0000000000000041.page -t /tmp/dtest1.sql > dumps/default/dtest1 2> dumps/default/dtest1.sqldumps/default 这个目录不要更改

恢复数据到mysql

# 登陆mysql

mysql -uroot -p

# 切换到指定库

root@mysqldb 06:25: [(none)]> use dtest

# 生成对应表，或手动创建

root@mysqldb 06:25: [dtest]> source /tmp/dtest1.sql

# 导入数据

root@mysqldb 06:26: [dtest]> source /root/undrop-for-innodb/dumps/default/dtest1.sql

Query OK, 0 rows affected (0.00 sec)

Query OK, 5 rows affected (0.00 sec)

Records: 5 Deleted: 0 Skipped: 0 Warnings: 0

数据验证

root@mysqldb 06:28: [dtest]> show tables;

+-----------------+

| Tables_in_dtest |

+-----------------+

| dtest1 |

| dtest2 |

| dtest3 |

+-----------------+

3 rows in set (0.00 sec)

root@mysqldb 06:28: [dtest]> select * from dtest1;

+----+------+------+

| id | a | b |

+----+------+------+

| 1 | 1 | 1 |

| 2 | 2 | 222 |

| 3 | 3 | 3 |

| 4 | 4 | 4 |

| 5 | 5 | 5 |

+----+------+------+

5 rows in set (0.00 sec)