Use tools to extract MySQL Data Dictionary directly

Anyone familiar with Oracle knows that when the Oracle database cannot be started normally, you can use dul or other third-party tools to directly read data in the data file to save data and reduce losses, if the innodb engine is used in mysql, related records can be extracted if the mysql database is not started. this document is the chapter for data dictionary extraction.

Anyone familiar with Oracle knows that when the Oracle database cannot be started normally, you can use dul or other third-party tools to directly read data in the data file to save data and reduce losses, if the innodb engine is used in mysql, related records can be extracted if the mysql database is not started. this document is the chapter for data dictionary extraction.

Anyone familiar with Oracle knows that when the Oracle database cannot be started normally, you can use dul or other third-party tools to directly read data in the data file to save data and reduce losses, if the innodb engine is used in mysql, related records can be extracted if the mysql database is not started. this document is the chapter for extracting data dictionaries. We will continue to provide the following chapters for non-conventional mysql recovery, such as drop recovery, truncate recovery, and delete recovery.
Create a get_dict test table

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)

Close mysql database

[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

Use tools to parse innodb files

[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

Main file Introduction

[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 records the table information files in mysql.
0000000000000002. page is an information file that records the columns of a table in mysql.
0000000000000003. page is mainly used to record the index information file of a table in mysql.

Extract table data

[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`);

Extract column data

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

[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

Start mysql database

[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

Create a dictionary table for Data Extraction

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)

Load the extracted data dictionary data

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

Verify the extraction of data dictionary data

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)

It can be basically seen that, when the mysql database is not started, the mysql Data Dictionary information can be normally extracted using tools.

• Mysql query view: ERROR 1449 (HY000)
• Mysqldump + mysqlbinlog restoration test
• Mysql unlock
• Innobackupex Incremental Backup Test
• Mysql Merge table
• Mysql master-slave switchover
• Mysql related commands
• MYSQL password change

Original article address: Use a tool to extract the MySQL Data Dictionary directly. Thank you for sharing it with the original author.