[MySQL异常恢复]使用工具直接抽取MySQL数据字典

熟悉Oracle的朋友都知道,当Oracle数据库无法正常启动之时,可以通过dul或者其他三方工具直接读取数据文件中数据,从而来抢救数据,减少损失,在mysql中如果使用了innodb引擎也可以在mysql数据库不启动启动下抽取相关记录.本文为抽取数据字典篇章,后续将继续提供drop恢复,truncate 恢复,delete恢复等mysql非常规恢复篇章.
创建一张get_dict测试表

mysql> use xifenfei;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
±-------------------+
| Tables_in_xifenfei |
±-------------------+
| t_delete |
±-------------------+
1 row in set (0.00 sec)

mysql> create table get_dict(id int not null primary key,name varchar(100));
Query OK, 0 rows affected (0.01 sec)

mysql> insert into get_dict value(1,‘www.xifenfei.com’);
Query OK, 1 row affected (0.00 sec)

mysql> insert into get_dict value(2,‘www.xifenfei.com-xifenfei’);
Query OK, 1 row affected (0.00 sec)

mysql> insert into get_dict value(3,‘xifenfei-www.xifenfei.com’);
Query OK, 1 row affected (0.00 sec)

mysql> show tables;
±-------------------+
| Tables_in_xifenfei |
±-------------------+
| get_dict |
| t_delete |
±-------------------+
2 rows in set (0.00 sec

mysql> select TABLE_NAME,TABLE_SCHEMA,TABLE_TYPE from information_schema.tables
-> where table_name=‘get_dict’;
±-----------±-------------±-----------+
| TABLE_NAME | TABLE_SCHEMA | TABLE_TYPE |
±-----------±-------------±-----------+
| get_dict | xifenfei | BASE TABLE |
±-----------±-------------±-----------+
1 row in set (0.01 sec)

mysql> select TABLE_NAME,NON_UNIQUE,TABLE_SCHEMA,INDEX_SCHEMA,INDEX_NAME,COLUMN_NAME from
-> INFORMATION_SCHEMA.STATISTICS where TABLE_NAME=‘get_dict’;
±-----------±-----------±-------------±-------------±-----------±------------+
| TABLE_NAME | NON_UNIQUE | TABLE_SCHEMA | INDEX_SCHEMA | INDEX_NAME | COLUMN_NAME |
±-----------±-----------±-------------±-------------±-----------±------------+
| get_dict | 0 | xifenfei | xifenfei | PRIMARY | id |
±-----------±-----------±-------------±-------------±-----------±------------+
1 row in set (0.00 sec)

mysql> select TABLE_SCHEMA,TABLE_NAME,COLUMN_NAME,ORDINAL_POSITION from
-> information_schema.COLUMNS where table_name=‘get_dict’;
±-------------±-----------±------------±-----------------+
| TABLE_SCHEMA | TABLE_NAME | COLUMN_NAME | ORDINAL_POSITION |
±-------------±-----------±------------±-----------------+
| xifenfei | get_dict | id | 1 |
| xifenfei | get_dict | name | 2 |
±-------------±-----------±------------±-----------------+
2 rows in set (0.01 sec)
关闭mysql数据库

[root@localhost recovery_mysql]# service mysql stop
Shutting down MySQL…[ OK ]
[root@localhost recovery_mysql]# ps -ef|grep mysql
root 18876 15827 0 18:05 pts/1 00:00:00 grep mysql
使用工具解析innodb文件

[root@localhost recovery_mysql]# ./stream_parser -f /var/lib/mysql/ibdata1
Opening file: /var/lib/mysql/ibdata1
File information:

Opening file: /var/lib/mysql/ibdata1
File information:

ID of device containing file: 2054
inode number: 1782889
ID of device containing file: 2054
protection: 100660 inode number: 1782889
(regular file)
protection: 100660 number of hard links: 1
(regular file)
user ID of owner: 101
number of hard links: 1
group ID of owner: 102
user ID of owner: 101
device ID (if special file): 0
group ID of owner: 102
blocksize for filesystem I/O: 4096
device ID (if special file): 0
number of blocks allocated: 24616
blocksize for filesystem I/O: 4096
Opening file: /var/lib/mysql/ibdata1
number of blocks allocated: 24616
File information:

………………
user ID of owner: 101
group ID of owner: 102
device ID (if special file): 0
blocksize for filesystem I/O: 4096
ID of device containing file: 2054
number of blocks allocated: 24616
inode number: 1782889
protection: 100660 (regular file)
number of hard links: 1
user ID of owner: 101
group ID of owner: 102
device ID (if special file): 0
blocksize for filesystem I/O: 4096
number of blocks allocated: 24616
time of last access: 1417922668 Sun Dec 7 11:24:28 2014
time of last modification: 1418294104 Thu Dec 11 18:35:04 2014
time of last status change: 1418294104 Thu Dec 11 18:35:04 2014
time of last access: 1417922668 Sun Dec 7 11:24:28 2014
total size, in bytes: 12582912 (12.000 MiB)

time of last modification: 1418294104 Thu Dec 11 18:35:04 2014
time of last status change: 1418294104 Thu Dec 11 18:35:04 2014
Size to process: 12582912 (12.000 MiB)
total size, in bytes: 12582912 (12.000 MiB)

Size to process: 12582912 (12.000 MiB)
All workers finished in 0 sec
主要文件介绍

[root@localhost recovery_mysql]# ls -l pages-ibdata1/FIL_PAGE_INDEX/
total 1388
-rw-r–r-- 1 root root 16384 Dec 11 18:51 0000000000000001.page
-rw-r–r-- 1 root root 16384 Dec 11 18:51 0000000000000002.page
-rw-r–r-- 1 root root 49152 Dec 11 18:51 0000000000000003.page
-rw-r–r-- 1 root root 49152 Dec 11 18:51 0000000000000004.page
-rw-r–r-- 1 root root 16384 Dec 11 18:51 0000000000000005.page
-rw-r–r-- 1 root root 114688 Dec 11 18:51 0000000000000011.page
-rw-r–r-- 1 root root 114688 Dec 11 18:51 0000000000000012.page
-rw-r–r-- 1 root root 114688 Dec 11 18:51 0000000000000013.page
-rw-r–r-- 1 root root 114688 Dec 11 18:51 0000000000000014.page
-rw-r–r-- 1 root root 114688 Dec 11 18:51 0000000000000015.page
-rw-r–r-- 1 root root 147456 Dec 11 18:51 0000000000000016.page
-rw-r–r-- 1 root root 98304 Dec 11 18:51 0000000000000017.page
-rw-r–r-- 1 root root 114688 Dec 11 18:51 0000000000000018.page
-rw-r–r-- 1 root root 49152 Dec 11 18:51 0000000000000019.page
-rw-r–r-- 1 root root 49152 Dec 11 18:51 0000000000000020.page
-rw-r–r-- 1 root root 49152 Dec 11 18:51 0000000000000021.page
-rw-r–r-- 1 root root 65536 Dec 11 18:51 0000000000000025.page
-rw-r–r-- 1 root root 16384 Dec 11 18:51 18446744069414584320.page
0000000000000001.page主要是记录mysql中表信息文件
0000000000000002.page主要是记录mysql中的表的列的信息文件
0000000000000003.page主要是记录mysql中表的index信息文件

抽取table数据

[root@localhost recovery_mysql]# ./c_parser -4f pages-ibdata1/FIL_PAGE_INDEX/0000000000000001.page -t
dictionary/SYS_TABLES.sql > dumps/default/SYS_TABLES 2> dumps/default/SYS_TABLES.sql
[root@localhost recovery_mysql]# grep get dumps/default/SYS_TABLES | head -5
000000000D1D 95000001510110 SYS_TABLES “xifenfei/get_dict” 23 2 1 0 80 “” 9

[root@localhost recovery_mysql]# cat dumps/default/SYS_TABLES.sql
SET FOREIGN_KEY_CHECKS=0;
LOAD DATA LOCAL INFILE ‘/tmp/recovery_mysql/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);
抽取column数据

[root@localhost recovery_mysql]# ./c_parser -4f pages-ibdata1/FIL_PAGE_INDEX/0000000000000002.page
-t dictionary/SYS_COLUMNS.sql > dumps/default/SYS_COLUMNS 2> dumps/default/SYS_COLUMNS.sql
[root@localhost recovery_mysql]# cat dumps/default/SYS_COLUMNS
– Page id: 10, Format: REDUNDANT, Records list: Valid, Expected records: (115 115)
000000000300 800000012D0123 SYS_COLUMNS 11 0 “ID” 1 4 0 0
000000000300 800000012D0138 SYS_COLUMNS 11 1 “FOR_NAME” 1 4 0 0
…………
000000000D1D 95000001510129 SYS_COLUMNS 23 0 “id” 6 1283 4 0
000000000D1D 9500000151013E SYS_COLUMNS 23 1 “name” 1 524303 100 0
– Page id: 10, Found records: 115, Lost records: NO, Leaf page: YES
[root@localhost recovery_mysql]# more dumps/default/SYS_COLUMNS.sql
SET FOREIGN_KEY_CHECKS=0;
LOAD DATA LOCAL INFILE ‘/tmp/recovery_mysql/dumps/default/SYS_COLUMNS’ REPLACE INTO TABLE
SYS_COLUMNS FIELDS TERMINATED BY ‘\t’ OPTIONALLY ENCLOSED BY
‘"’ LINES STARTING BY ‘SYS_COLUMNS\t’ (TABLE_ID, POS, NAME, MTYPE, PRTYPE, LEN, PREC);
抽取index数据

[root@localhost recovery_mysql]# ./c_parser -4f pages-ibdata1/FIL_PAGE_INDEX/0000000000000003.page
-t dictionary/SYS_INDEXES.sql > dumps/default/SYS_INDEXES 2> dumps/default/SYS_INDEXES.sql
[root@localhost recovery_mysql]# more dumps/default/SYS_INDEXES.sql
SET FOREIGN_KEY_CHECKS=0;
LOAD DATA LOCAL INFILE ‘/tmp/recovery_mysql/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);
[root@localhost recovery_mysql]# more dumps/default/SYS_INDEXES
– Page id: 11, Format: REDUNDANT, Records list: Valid, Expected records: (13 13)
000000000300 800000012D0177 SYS_INDEXES 11 11 “ID_IND” 1 3 0 302
…………
000000000B02 820000013504C8 SYS_INDEXES 20 22 “GEN_CLUST_INDEX” 0 1 6 3
000000000D1D 9500000151016B SYS_INDEXES 23 25 “PRIMARY” 1 3 9 3
启动mysql数据库

[root@localhost recovery_mysql]# service mysql start
Starting MySQL…[ OK ]
[root@localhost recovery_mysql]# ps -ef|grep mysql
root 18948 1 0 19:57 pts/1 00:00:00 /bin/sh /usr/bin/mysqld_safe --datadir=/var/lib/mysql
–pid-file=/var/lib/mysql/localhost.localdomain.pid
mysql 19049 18948 14 19:57 pts/1 00:00:00 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql
–plugin-dir=/usr/lib64/mysql/plugin --user=mysql
–log-error=/var/lib/mysql/localhost.localdomain.err --pid-file=/var/lib/mysql/localhost.localdomain.pid
root 19078 15827 0 19:58 pts/1 00:00:00 grep mysql
创建抽取数据字典表

mysql> source dictionary/SYS_TABLES.sql
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)
…………

mysql> source dictionary/SYS_INDEXES.sql
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)
…………

mysql> source dictionary/SYS_COLUMNS.sql
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)
…………

mysql> show tables;
±---------------+
| Tables_in_test |
±---------------+
| SYS_COLUMNS |
| SYS_INDEXES |
| SYS_TABLES |
±---------------+
3 rows in set (0.00 sec)
加载抽取数据字典数据

mysql> source dumps/default/SYS_TABLES.sql
Query OK, 0 rows affected (0.00 sec)

Query OK, 11 rows affected (0.03 sec)
Records: 11 Deleted: 0 Skipped: 0 Warnings: 0

mysql> source dumps/default/SYS_INDEXES.sql
Query OK, 0 rows affected (0.00 sec)

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

mysql> source dumps/default/SYS_COLUMNS.sql
Query OK, 0 rows affected (0.00 sec)

Query OK, 115 rows affected (0.00 sec)
Records: 115 Deleted: 0 Skipped: 0 Warnings: 0
验证抽取数据字典数据

mysql> desc SYS_TABLES
-> ;
±-------------±--------------------±-----±----±--------±------+
| Field | Type | Null | Key | Default | Extra |
±-------------±--------------------±-----±----±--------±------+
| NAME | varchar(255) | NO | PRI | | |
| ID | bigint(20) unsigned | NO | | 0 | |
| N_COLS | int(10) | YES | | NULL | |
| TYPE | int(10) unsigned | YES | | NULL | |
| MIX_ID | bigint(20) unsigned | YES | | NULL | |
| MIX_LEN | int(10) unsigned | YES | | NULL | |
| CLUSTER_NAME | varchar(255) | YES | | NULL | |
| SPACE | int(10) unsigned | YES | | NULL | |
±-------------±--------------------±-----±----±--------±------+
8 rows in set (0.00 sec)

mysql> SELECT NAME,ID from SYS_TABLES WHERE NAME=‘xifenfei/get_dict’;
±------------------±—+
| NAME | ID |
±------------------±—+
| xifenfei/get_dict | 23 |
±------------------±—+
1 row in set (0.00 sec)

mysql> desc SYS_COLUMNS
-> ;
±---------±--------------------±-----±----±--------±------+
| Field | Type | Null | Key | Default | Extra |
±---------±--------------------±-----±----±--------±------+
| TABLE_ID | bigint(20) unsigned | NO | PRI | NULL | |
| POS | int(10) unsigned | NO | PRI | NULL | |
| NAME | varchar(255) | YES | | NULL | |
| MTYPE | int(10) unsigned | YES | | NULL | |
| PRTYPE | int(10) unsigned | YES | | NULL | |
| LEN | int(10) unsigned | YES | | NULL | |
| PREC | int(10) unsigned | YES | | NULL | |
±---------±--------------------±-----±----±--------±------+
7 rows in set (0.00 sec)

mysql> SELECT TABLE_ID,NAME,MTYPE FROM SYS_COLUMNS WHERE TABLE_ID=23;
±---------±-----±------+
| TABLE_ID | NAME | MTYPE |
±---------±-----±------+
| 23 | id | 6 |
| 23 | name | 1 |
±---------±-----±------+
2 rows in set (0.01 sec)

mysql> SELECT TABLE_ID,ID,NAME,TYPE FROM SYS_INDEXES WHERE TABLE_ID=23;
±---------±—±--------±-----+
| TABLE_ID | ID | NAME | TYPE |
±---------±—±--------±-----+
| 23 | 25 | PRIMARY | 3 |
±---------±—±--------±-----+
1 row in set (0.00 sec)
这里基本上可以看出来,在mysql数据库未启动情况下,使用工具可以正常抽取mysql数据字典信息

发布了0 篇原创文章 · 获赞 3 · 访问量 1980
展开阅读全文

没有更多推荐了,返回首页

©️2019 CSDN 皮肤主题: 大白 设计师: CSDN官方博客

分享到微信朋友圈

×

扫一扫,手机浏览