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.

访客数 : | 访问量 :