熟悉oracle的朋友都知道,当oracle数据库无法正常启动之时,可以通过dul或者其他三方工具直接读取数据文件中数据,从而来抢救数据,减少损失,在mysql中如果使用了innodb引擎也可以在mysql数据库不启动启动下抽取相关记录.本文为抽取数据字典篇章,后续将继续提供d
熟悉oracle的朋友都知道,当oracle数据库无法正常启动之时,可以通过dul或者其他三方工具直接读取数据文件中数据,从而来抢救数据,减少损失,在mysql中如果使用了innodb引擎也可以在mysql数据库不启动启动下抽取相关记录.本文为抽取数据字典篇章,后续将继续提供drop恢复,truncate 恢复,delete恢复等mysql非常规恢复篇章.
创建一张get_dict测试表
mysql> use xifenfei;reading table information for completion of table and column namesyou can turn off this feature to get a quicker startup with -adatabase changedmysql> 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 secmysql> 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 stopshutting down mysql..[ ok ][root@localhost recovery_mysql]# ps -ef|grep mysqlroot 18876 15827 0 18:05 pts/1 00:00:00 grep mysql
使用工具解析innodb文件
[root@localhost recovery_mysql]# ./stream_parser -f /var/lib/mysql/ibdata1opening file: /var/lib/mysql/ibdata1file information:opening file: /var/lib/mysql/ibdata1file information:id of device containing file: 2054inode number: 1782889id of device containing file: 2054protection: 100660 inode number: 1782889(regular file)protection: 100660 number of hard links: 1(regular file)user id of owner: 101number of hard links: 1group id of owner: 102user id of owner: 101device id (if special file): 0group id of owner: 102blocksize for filesystem i/o: 4096device id (if special file): 0number of blocks allocated: 24616blocksize for filesystem i/o: 4096opening file: /var/lib/mysql/ibdata1number of blocks allocated: 24616file information:………………user id of owner: 101group id of owner: 102device id (if special file): 0blocksize for filesystem i/o: 4096id of device containing file: 2054number of blocks allocated: 24616inode number: 1782889protection: 100660 (regular file)number of hard links: 1user id of owner: 101group id of owner: 102device id (if special file): 0blocksize for filesystem i/o: 4096number of blocks allocated: 24616time of last access: 1417922668 sun dec 7 11:24:28 2014time of last modification: 1418294104 thu dec 11 18:35:04 2014time of last status change: 1418294104 thu dec 11 18:35:04 2014time of last access: 1417922668 sun dec 7 11:24:28 2014total size, in bytes: 12582912 (12.000 mib)time of last modification: 1418294104 thu dec 11 18:35:04 2014time of last status change: 1418294104 thu dec 11 18:35:04 2014size 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 -5000000000d1d 95000001510110 sys_tables xifenfei/get\_dict 23 2 1 0 80 9[root@localhost recovery_mysql]# cat dumps/default/sys_tables.sqlset 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 0000000000300 800000012d0138 sys_columns 11 1 for\_name 1 4 0 0…………000000000d1d 95000001510129 sys_columns 23 0 id 6 1283 4 0000000000d1d 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.sqlset 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.sqlset 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 3000000000d1d 9500000151016b sys_indexes 23 25 primary 1 3 9 3
启动mysql数据库
[root@localhost recovery_mysql]# service mysql startstarting mysql..[ ok ][root@localhost recovery_mysql]# ps -ef|grep mysqlroot 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.pidroot 19078 15827 0 19:58 pts/1 00:00:00 grep mysql
创建抽取数据字典表
mysql> source dictionary/sys_tables.sqlquery ok, 0 rows affected (0.00 sec)query ok, 0 rows affected (0.00 sec)…………mysql> source dictionary/sys_indexes.sqlquery 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.sqlquery ok, 0 rows affected (0.00 sec)query ok, 11 rows affected (0.03 sec)records: 11 deleted: 0 skipped: 0 warnings: 0mysql> source dumps/default/sys_indexes.sqlquery ok, 0 rows affected (0.00 sec)query ok, 39 rows affected (0.01 sec)records: 39 deleted: 0 skipped: 0 warnings: 0mysql> source dumps/default/sys_columns.sqlquery 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数据字典信息
mysql查询视图：error 1449 (hy000)mysqldump+mysqlbinlog恢复测试mysql解锁innobackupex增量备份测试mysql merge表mysql主从切换mysql关于log_bin相关命令mysql修改密码 原文地址：使用工具直接抽取mysql数据字典, 感谢原作者分享。