MySQL异常恢复之无主键情况下innodb数据恢复的方法

本文讲述了MySQL异常恢复之无主键情况下innodb数据恢复的方法。分享给大家供大家参考，具体如下： 在mysql的innodb引擎的数据库异常恢复中,一般都要求有主键或者唯一index,其实这个不是必须的,当没有index信息之时,可以在整个表级别的index_id进行恢复 创建模拟表—无主键 ? 1 0 Comments

本文讲述了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数据库计有所帮助。

本文链接：https://my.lmcjl.com/post/18001.html

展开阅读全文

相关文章

4 评论

留下您的评论.