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

本文讲述了MySQL异常恢复之无主键情况下innodb数据恢复的方法。分享给大家供大家参考，具体如下：

在mysql的innodb引擎的数据库异常恢复中,一般都要求有主键或者唯一index,其实这个不是必须的,当没有index信息之时,可以在整个表级别的index_id进行恢复

创建模拟表―无主键

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文件

[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
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

恢复数据字典

[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

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)

（编辑：安卓应用网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!