分类目录归档：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数据字典信息