本文讲述了MySQL异常恢复之无主键情况下innodb数据恢复的方法。分享给大家供大家参考，具体如下：
在mysql的innodb引擎的数据库异常恢复中,一般都要求有主键或者唯一index,其实这个不是必须的,当没有index信息之时,可以在整个表级别的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
|
mysql> CREATE TABLE `t1` (
-> `messageId` varchar(30) character set utf8 NOT NULL,
-> `tokenId` varchar(20) character set utf8 NOT NULL,
-> `mobile` varchar(14) character set utf8 default NULL,
-> `msgFormat` int(1) NOT NULL,
-> `msgContent` varchar(1000) character set utf8 default NULL,
-> `scheduleDate` timestamp NOT NULL default '0000-00-00 00:00:00',
-> `deliverState` int(1) default NULL,
-> `deliverdTime` timestamp NOT NULL default '0000-00-00 00:00:00'
-> ) ENGINE=INnodb DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into t1 select * from sms_service.sms_send_record;
Query OK, 11 rows affected (0.00 sec)
Records: 11 Duplicates: 0 Warnings: 0
…………
mysql> insert into t1 select * from t1;
Query OK, 81664 rows affected (2.86 sec)
Records: 81664 Duplicates: 0 Warnings: 0
mysql> insert into t1 select * from t1;
Query OK, 163328 rows affected (2.74 sec)
Records: 163328 Duplicates: 0 Warnings: 0
mysql> select count(*) from t1;
+----------+
| count(*) |
+----------+
| 326656 |
+----------+
1 row in set (0.15 sec)
|
解析innodb文件
|
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
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
|
[root@web103 mysql_recovery]# rm -rf pages-ibdata1/
[root@web103 mysql_recovery]# ./stream_parser -f /var/lib/mysql/ibdata1
Opening file: /var/lib/mysql/ibdata1
File information:
ID of device containing file: 2049
inode number: 1344553
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: 463312
time of last access: 1440819443 Sat Aug 29 11:37:23 2015
time of last modification: 1440819463 Sat Aug 29 11:37:43 2015
time of last status change: 1440819463 Sat Aug 29 11:37:43 2015
total size, in bytes: 236978176 (226.000 MiB)
Size to process: 236978176 (226.000 MiB)
Opening file: /var/lib/mysql/ibdata1
File information:
ID of device containing file: 2049
inode number: 1344553
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: 463312
Opening file: /var/lib/mysql/ibdata1
File information:
time of last access: 1440819443 Sat Aug 29 11:37:23 2015
time of last modification: 1440819463 Sat Aug 29 11:37:43 2015
ID of device containing file: 2049
inode number: 1344553
protection: 100660 time of last status change: 1440819463 Sat Aug 29 11:37:43 2015
total size, in bytes: 236978176 (226.000 MiB)
Size to process: 236978176 (226.000 MiB)
Opening file: /var/lib/mysql/ibdata1
File information:
ID of device containing file: 2049
inode number: 1344553
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: 463312
time of last access: 1440819443 Sat Aug 29 11:37:23 2015
time of last modification: 1440819463 Sat Aug 29 11:37:43 2015
time of last status change: 1440819463 Sat Aug 29 11:37:43 2015
total size, in bytes: 236978176 (226.000 MiB)
Size to process: 236978176 (226.000 MiB)
(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: 463312
time of last access: 1440819443 Sat Aug 29 11:37:23 2015
time of last modification: 1440819463 Sat Aug 29 11:37:43 2015
time of last status change: 1440819463 Sat Aug 29 11:37:43 2015
total size, in bytes: 236978176 (226.000 MiB)
Size to process: 236978176 (226.000 MiB)
Opening file: /var/lib/mysql/ibdata1
File information:
ID of device containing file: 2049
inode number: 1344553
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: 463312
time of last access: 1440819443 Sat Aug 29 11:37:23 2015
time of last modification: 1440819463 Sat Aug 29 11:37:43 2015
time of last status change: 1440819463 Sat Aug 29 11:37:43 2015
total size, in bytes: 236978176 (226.000 MiB)
Size to process: 236978176 (226.000 MiB)
Opening file: /var/lib/mysql/ibdata1
File information:
ID of device containing file: 2049
inode number: 1344553
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: 463312
time of last access: 1440819443 Sat Aug 29 11:37:23 2015
time of last modification: 1440819463 Sat Aug 29 11:37:43 2015
time of last status change: 1440819463 Sat Aug 29 11:37:43 2015
Opening file: /var/lib/mysql/ibdata1
File information:
ID of device containing file: 2049
inode number: 1344553
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: 463312
total size, in bytes: 236978176 (226.000 MiB)
Size to process: 236978176 (226.000 MiB)
time of last access: 1440819443 Sat Aug 29 11:37:23 2015
time of last modification: 1440819463 Sat Aug 29 11:37:43 2015
time of last status change: 1440819463 Sat Aug 29 11:37:43 2015
total size, in bytes: 236978176 (226.000 MiB)
Size to process: 236978176 (226.000 MiB)
Opening file: /var/lib/mysql/ibdata1
File information:
ID of device containing file: 2049
inode number: 1344553
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: 463312
time of last access: 1440819465 Sat Aug 29 11:37:45 2015
time of last modification: 1440819463 Sat Aug 29 11:37:43 2015
time of last status change: 1440819463 Sat Aug 29 11:37:43 2015
total size, in bytes: 236978176 (226.000 MiB)
Size to process: 236978176 (226.000 MiB)
All workers finished in 0 sec
|
恢复数据字典
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
[root@web103 mysql_recovery]# ./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 ... 48 recs OK
SYS_COLUMNS ... 397 recs OK
SYS_INDEXES ... 67 recs OK
SYS_FIELDS ... 89 recs OK
All OK
|
分析数据字典,找出来index_id
这里需要注意对于没有主键的表恢复,我们对应的类型是GEN_CLUST_INDEX
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
mysql> select * from SYS_TABLES where name='test/t1';
+----------------------------------------+-----+-------------+------+--------+---------+--------------+-------+
| NAME | ID | N_COLS | TYPE | MIX_ID | MIX_LEN | CLUSTER_NAME | SPACE |
+----------------------------------------+-----+-------------+------+--------+---------+--------------+-------+
| test/t1 | 100 | 8 | 1 | 0 | 0 | | 0 |
+----------------------------------------+-----+-------------+------+--------+---------+--------------+-------+
40 rows in set (0.00 sec)
mysql> SELECT * FROM SYS_INDEXES where table_id=100;
+----------+-----+------------------------------+----------+------+-------+------------+
| TABLE_ID | ID | NAME | N_FIELDS | TYPE | SPACE | PAGE_NO |
+----------+-----+------------------------------+----------+------+-------+------------+
| 100 | 119 | GEN_CLUST_INDEX | 0 | 1 | 0 | 2951 |
+----------+-----+------------------------------+----------+------+-------+------------+
67 rows in set (0.00 sec)
|
恢复数据
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
|
root@web103 mysql_recovery]# ./c_parser -5f pages-ibdata1/FIL_PAGE_INDEX/0000000000000119.page -t dictionary/t1.sql >/tmp/2.txt 2>2.sql
[root@web103 mysql_recovery]# more /tmp/2.txt
-- Page id: 10848, Format: COMPACT, Records list: Valid, Expected records: (73 73)
00000002141B 0000009924F2 80000027133548 t1 "82334502212106951" "SDK-BBX-010-18681" "13718311436" 8 "尊敬的用户您好：您的手机验证码为916515如非本人操作，请拨打奥
斯卡客服：400-620-7575。" "2010-01-01 00:00:00" 0 "1970-01-01 07:00:00"
00000002141C 0000009924F2 80000027133558 t1 "82339012756833423" "SDK-BBX-010-18681" "13718311436" 8 "尊敬的用户您好：您的手机验证码为396108如非本人操作，请拨打奥
斯卡客服：400-620-7575。" "2010-01-01 00:00:00" 0 "1970-01-01 07:00:00"
00000002141D 0000009924F2 80000027133568 t1 "8234322198577796" "SDK-BBX-010-18681" "13718311436" 8 "尊敬的用户您好：您的手机验证码为935297如非本人操作，请拨打奥
斯卡客服：400-620-7575。" "2010-01-01 00:00:00" 0 "1970-01-01 07:00:00"
00000002141E 0000009924F2 80000027133578 t1 "10235259536125650" "SDK-BBX-010-18681" "13718311436" 8 "尊敬的用户您好：您的手机验证码为474851如非本人操作，请拨打奥
斯卡客服：400-620-7575。" "2010-01-01 00:00:00" 0 "1970-01-01 07:00:00"
00000002141F 0000009924F2 80000027133588 t1 "10235353811295807" "SDK-BBX-010-18681" "13718311436" 8 "尊敬的用户您好：您的手机验证码为444632如非本人操作，请拨打奥
斯卡客服：400-620-7575。" "2010-01-01 00:00:00" 0 "1970-01-01 07:00:00"
000000021420 0000009924F2 80000027133598 t1 "102354211240398235" "SDK-BBX-010-18681" "13718311436" 8 "尊敬的用户您好：您的手机验证码为478503如非本人操作，请拨打奥
斯卡客服：400-620-7575。" "2010-01-01 00:00:00" 0 "1970-01-01 07:00:00"
000000021421 0000009924F2 800000271335A8 t1 "102354554052884567" "SDK-BBX-010-18681" "13718311436" 8 "尊敬的用户您好：您的手机验证码为216825如非本人操作，请拨打奥
斯卡客服：400-620-7575。" "2010-01-01 00:00:00" 0 "1970-01-01 07:00:00"
000000021422 0000009924F2 800000271335B8 t1 "132213454294519126" "SDK-BBX-010-18681" "13718311436" 8 "尊敬的用户您好：您的手机验证码为854812如非本人操作，请拨打奥
斯卡客服：400-620-7575。" "2010-01-01 00:00:00" 0 "1970-01-01 07:00:00"
000000021423 0000009924F2 800000271335C8 t1 "82329022242584577" "SDK-BBX-010-18681" "13718311436" 8 "尊敬的用户您好：您的手机验证码为253127如非本人操作，请拨打奥
斯卡客服：400-620-7575。" "2010-01-01 00:00:00" 0 "2015-08-26 22:02:17"
…………
[root@web103 mysql_recovery]# cat /tmp/2.txt|grep -v "Page id:"|wc -l
380731
|
因为没有主键,使得恢复出来记录可能有一些重复,整体而言,可以较为完美的恢复数据
希望本文所述对大家MySQL数据库计有所帮助。