MySQL误DROP表恢复,innodb_file_per_table=OFF

MySQL误DROP表恢复,innodb_file_per_table=OFF

原文https://twindb.com/recover-after-drop-table-innodb_file_per_table-is-off/

Undrop-for-innodb安装

详见https://github.com/chhabhaiya/undrop-for-innodb

介绍

误操作是不可避免的.错误的DROP DATABASE或DROP TABLE会摧毁重要的数据,更悲剧的是备份又不可用.
恢复方法取决于InnoDB是将所有数据保存在单个ibdata1中还是每个表都有自己的表空间.在这篇文章中,我们将考虑innodb_file_per_table = OFF的情况.此选项假定所有表都存储在通用文件中,通常位于/var/lib/mysql/ibdata1.

误删表恢复

这里使用sakila库,假设我们误删除了actor表

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
mysql> SELECT * FROM actor LIMIT 10;
+----------+------------+--------------+---------------------+
| actor_id | first_name | last_name | last_update |
+----------+------------+--------------+---------------------+
| 1 | PENELOPE | GUINESS | 2006-02-15 04:34:33 |
| 2 | NICK | WAHLBERG | 2006-02-15 04:34:33 |
| 3 | ED | CHASE | 2006-02-15 04:34:33 |
| 4 | JENNIFER | DAVIS | 2006-02-15 04:34:33 |
| 5 | JOHNNY | LOLLOBRIGIDA | 2006-02-15 04:34:33 |
| 6 | BETTE | NICHOLSON | 2006-02-15 04:34:33 |
| 7 | GRACE | MOSTEL | 2006-02-15 04:34:33 |
| 8 | MATTHEW | JOHANSSON | 2006-02-15 04:34:33 |
| 9 | JOE | SWANK | 2006-02-15 04:34:33 |
| 10 | CHRISTIAN | GABLE | 2006-02-15 04:34:33 |
+----------+------------+--------------+---------------------+
10 rows in set (0.02 sec)

查看一下checksum
mysql> CHECKSUM TABLE actor;
+--------------+------------+
| Table | Checksum |
+--------------+------------+
| sakila.actor | 1702520518 |
+--------------+------------+
1 row in set (0.01 sec)

mysql> select count(*) from actor;
+----------+
| count(*) |
+----------+
| 200 |
+----------+
1 row in set (0.01 sec)

mysql> DROP TABLE actor;
Query OK, 0 rows affected (0.03 sec)

从ibdata1恢复误删除表

虽然表被删了,但是数据可能依然存在于ibdata1中,直到这部分数据被覆盖.所以要赶紧停止MySQL

1
2
3
4
[mysql@master 3307]$ mysqladmin -umysql -p -S /data/mysqldata/3307/mysql.sock shutdown
Enter password:
161227 14:13:59 mysqld_safe mysqld from pid file /data/mysqldata/3307/mysql.pid ended
[1]+ Done mysqld_safe --defaults-file=/data/mysqldata/3307/my.cnf

解析InnoDB tablespace

InnoDB通过B+树存储数据.每个表都通过主键聚簇,所有字段数据都存储在索引叶块中.如果表有secondary index,则每个键具有索引.每个索引通过index_id标识.
如果我们想要恢复一个表,就要找出属于特定index_id的所有page
stream_parser读取InnoDB tablespace,并按照type和index_id排序InnoDB pages

stream_parser reads InnoDB tablespace and sorts InnoDB pages per type and per index_id.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
[mysql@master undrop-for-innodb]$ ./stream_parser -f /data/mysqldata/3307/data/ibdata1 
Opening file: /data/mysqldata/3307/data/ibdata1
File information:

ID of device containing file: 64768
inode number: 104900755
protection: 100660 (regular file)
number of hard links: 1
user ID of owner: 27
group ID of owner: 27
device ID (if special file): 0
blocksize for filesystem I/O: 4096
number of blocks allocated: 4194304
time of last access: 1482819237 Tue Dec 27 14:13:57 2016
time of last modification: 1482819237 Tue Dec 27 14:13:57 2016
time of last status change: 1482819237 Tue Dec 27 14:13:57 2016
total size, in bytes: 2147483648 (2.000 GiB)

Size to process: 2147483648 (2.000 GiB)
Worker(0): 5.47% done. 2016-12-27 14:24:21 ETA(in 00:00:18). Processing speed: 104.000 MiB/sec
Worker(0): 13.67% done. 2016-12-27 14:24:14 ETA(in 00:00:10). Processing speed: 168.000 MiB/sec
Worker(0): 22.66% done. 2016-12-27 14:24:13 ETA(in 00:00:08). Processing speed: 184.000 MiB/sec
Worker(0): 28.52% done. 2016-12-27 14:24:18 ETA(in 00:00:12). Processing speed: 120.000 MiB/sec
Worker(0): 35.94% done. 2016-12-27 14:24:15 ETA(in 00:00:08). Processing speed: 152.000 MiB/sec
Worker(0): 43.75% done. 2016-12-27 14:24:15 ETA(in 00:00:07). Processing speed: 160.000 MiB/sec
Worker(0): 51.17% done. 2016-12-27 14:24:15 ETA(in 00:00:06). Processing speed: 152.000 MiB/sec
Worker(0): 57.81% done. 2016-12-27 14:24:16 ETA(in 00:00:06). Processing speed: 136.000 MiB/sec
Worker(0): 64.45% done. 2016-12-27 14:24:16 ETA(in 00:00:05). Processing speed: 136.000 MiB/sec
Worker(0): 71.48% done. 2016-12-27 14:24:16 ETA(in 00:00:04). Processing speed: 144.000 MiB/sec
Worker(0): 78.91% done. 2016-12-27 14:24:15 ETA(in 00:00:02). Processing speed: 152.000 MiB/sec
Worker(0): 85.16% done. 2016-12-27 14:24:16 ETA(in 00:00:02). Processing speed: 128.000 MiB/sec
Worker(0): 92.19% done. 2016-12-27 14:24:16 ETA(in 00:00:01). Processing speed: 144.000 MiB/sec
All workers finished in 13 sec

Data from database pages is saved by the stream_parser to folder pages-ibdata1:
[mysql@master undrop-for-innodb]$ tree pages-ibdata1/
pages-ibdata1/
├── FIL_PAGE_INDEX
│ ├── 0000000000000001.page
│ ├── 0000000000000002.page
│ ├── 0000000000000003.page
│ ├── 0000000000000004.page
│ ├── 0000000000000005.page
│ ├── 0000000000000011.page
│ ├── 0000000000000012.page
│ ├── 0000000000000013.page
│ ├── 0000000000000014.page
│ ├── 0000000000000015.page
│ ├── 0000000000000016.page
│ ├── 0000000000000017.page
│ ├── 0000000000000018.page
│ ├── 0000000000000019.page
│ ├── 0000000000000020.page
│ ├── 0000000000000021.page
│ ├── 0000000000000022.page
│ ├── 0000000000000023.page
│ └── 18446744069414584320.page
└── FIL_PAGE_TYPE_BLOB

2 directories, 19 files

现在从InnoDB tablespace取出的每个index_id都被保存在单独的文件中.我们可以使用c_parser从page中取回数据,但是我们需要知道actor表的index_id是多少.我们可以通过字典表SYS_TABLES和SYS_INDEXES中查出actor表的信息

SYS_TABLES字典表总是存储在index_id为1的文件中,即pages-ibdata1/FIL_PAGE_INDEX./0000000000000001.page

If MySQL had enough time to flush changes to disk then add -D option which means “find deleted records”. The dictionary is always in REDUNDANT format, so we specify option -4:

1
2
3
4
5
[mysql@master undrop-for-innodb]$ ./c_parser -4Df pages-ibdata1/FIL_PAGE_INDEX/0000000000000001.page -t dictionary/SYS_TABLES.sql | grep sakila/actor
SET FOREIGN_KEY_CHECKS=0;
LOAD DATA LOCAL INFILE '/var/lib/mysql/undrop-for-innodb/dumps/default/SYS_TABLES' REPLACE INTO TABLE `SYS_TABLES` 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`);
000000000712 0F0000014802C8 SYS_TABLES "sakila/actor" 20 4 1 0 64 "" 0
000000000712 0F0000014802C8 SYS_TABLES "sakila/actor" 20 4 1 0 64 "" 0

在表名”sakila/actor”后面的列就是table_id列,这里查出actor表的table_id是20
接着要查出actor表的主键索引的index_id,通过解析0000000000000003.page文件可以取出SYS_INDEXES字典表的数据(这个表包含index_id和table_id信息).

1
2
3
4
5
6
7
[mysql@master undrop-for-innodb]$ ./c_parser -4Df pages-ibdata1/FIL_PAGE_INDEX/0000000000000003.page -t dictionary/SYS_INDEXES.sql | grep 20
SET FOREIGN_KEY_CHECKS=0;
LOAD DATA LOCAL INFILE '/var/lib/mysql/undrop-for-innodb/dumps/default/SYS_INDEXES' REPLACE INTO TABLE `SYS_INDEXES` FIELDS TERMINATED BY '\t' OPTIONALLY ENCLOSED BY '"' LINES STARTING BY 'SYS_INDEXES\t' (`TABLE_ID`, `ID`, `NAME`, `N_FIELDS`, `TYPE`, `SPACE`, `PAGE_NO`);
000000000712 0F000001480145 SYS_INDEXES 20 22 "PRIMARY" 1 3 0 4294967295
000000000712 0F0000014801B7 SYS_INDEXES 20 23 "idx\_actor\_last\_name" 1 0 0 4294967295
000000000712 0F000001480145 SYS_INDEXES 20 22 "PRIMARY" 1 3 0 4294967295
000000000712 0F0000014801B7 SYS_INDEXES 20 23 "idx\_actor\_last\_name" 1 0 0 4294967295

从结果可以看出,主键的index_id是22.因此我们将从0000000000000022.page文件中取回actor表的数据,需要actor表的建表语句
1
2
3
4
5
6
[mysql@master undrop-for-innodb]$ ./c_parser -6f pages-ibdata1/FIL_PAGE_INDEX/0000000000000022.page -t sakila/actor.sql |  head -5
-- Page id: 320, Format: COMPACT, Records list: Valid, Expected records: (200 200)
000000000709 89000001430110 actor 1 "PENELOPE" "GUINESS" "2006-02-15 04:34:33"
000000000709 8900000143011A actor 2 "NICK" "WAHLBERG" "2006-02-15 04:34:33"
000000000709 89000001430124 actor 3 "ED" "CHASE" "2006-02-15 04:34:33"
000000000709 8900000143012E actor 4 "JENNIFER" "DAVIS" "2006-02-15 04:34:33"

从结果来看我们找到了数据,下面通过c_parser生成load data infile语句和数据
1
2
3
4
[mysql@master undrop-for-innodb]$ mkdir -p dumps/default
[mysql@master undrop-for-innodb]$ ./c_parser -6f pages-ibdata1/FIL_PAGE_INDEX/0000000000000022.page -t sakila/actor.sql > dumps/default/actor 2> dumps/default/actor_load.sql
[mysql@master undrop-for-innodb]$ ls dumps/default/
actor actor_load.sql

.sql文件包含load data语句
1
2
3
[mysql@master undrop-for-innodb]$ cat dumps/default/actor_load.sql
SET FOREIGN_KEY_CHECKS=0;
LOAD DATA LOCAL INFILE '/var/lib/mysql/undrop-for-innodb/dumps/default/actor' REPLACE INTO TABLE `actor` FIELDS TERMINATED BY '\t' OPTIONALLY ENCLOSED BY '"' LINES STARTING BY 'actor\t' (`actor_id`, `first_name`, `last_name`, `last_update`);

另一个文件包含数据
1
2
3
4
5
6
7
8
9
10
11
12
13
[mysql@master undrop-for-innodb]$ more dumps/default/actor
-- Page id: 320, Format: COMPACT, Records list: Valid, Expected records: (200 200)
000000000709 89000001430110 actor 1 "PENELOPE" "GUINESS" "2006-02-15 04:34:33"
000000000709 8900000143011A actor 2 "NICK" "WAHLBERG" "2006-02-15 04:34:33"
000000000709 89000001430124 actor 3 "ED" "CHASE" "2006-02-15 04:34:33"
000000000709 8900000143012E actor 4 "JENNIFER" "DAVIS" "2006-02-15 04:34:33"
000000000709 89000001430138 actor 5 "JOHNNY" "LOLLOBRIGIDA" "2006-02-15 04:34:33"
000000000709 89000001430142 actor 6 "BETTE" "NICHOLSON" "2006-02-15 04:34:33"
000000000709 8900000143014C actor 7 "GRACE" "MOSTEL" "2006-02-15 04:34:33"
000000000709 89000001430156 actor 8 "MATTHEW" "JOHANSSON" "2006-02-15 04:34:33"
000000000709 89000001430160 actor 9 "JOE" "SWANK" "2006-02-15 04:34:33"
000000000709 8900000143016A actor 10 "CHRISTIAN" "GABLE" "2006-02-15 04:34:33"
000000000709 89000001430174 actor 11 "ZERO" "CAGE" "2006-02-15 04:34:33"

##恢复数据
启动数据库

1
mysqld_safe --defaults-file=/data/mysqldata/3307/my.cnf &

创建表
1
2
3
mysql> use sakila
Database changed
mysql> source sakila/actor.sql

load回数据
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
mysql> source dumps/default/actor_load.sql
Query OK, 0 rows affected (0.00 sec)

Query OK, 400 rows affected (0.01 sec)
Records: 400 Deleted: 0 Skipped: 0 Warnings: 0

mysql> select count(*) from actor;
+----------+
| count(*) |
+----------+
| 200 |
+----------+
1 row in set (0.00 sec)

checksum和之前也是一样的
mysql> CHECKSUM TABLE actor;
+--------------+------------+
| Table | Checksum |
+--------------+------------+
| sakila.actor | 1702520518 |
+--------------+------------+
1 row in set (0.00 sec)

Powered by Hexo and Hexo-theme-hiker

Copyright © 2013 - 2022 Fan() All Rights Reserved.

访客数 : | 访问量 :