如何从mysql数据库提取数据字典_使⽤⼯具直接抽取MySQL
数据字典
熟悉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 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');
Query OK, 1 row affected (0.00 sec)
mysql> insert into get_dict value(2,'www.xifenfei-xifenfei');
Query OK, 1 row affected (0.00 sec)
mysql> insert into get_dict value(3,'xifenfei-www.xifenfei');
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,C
OLUMN_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): 0mysql连接工具
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

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。