MySQL database failed to start example of data recovery

Friends familiar with Oracle know that when the Oracle database does not start properly, you can save data and reduce loss by dul or other tripartite tools that directly read data from data files. If you use the InnoDB engine in MySQL, you can also extract related records when the MySQL database does not start. To extract the data dictionary chapter, the following will continue to provide the drop recovery, truncate recovery, delete recovery and other MySQL unconventional recovery chapters.

Create a get_dict test table

mysql> use Xifenfei;
Reading table information for completion of table and column names
You can turn off the 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.111cn.net ');
Query OK, 1 row Affected (0.00 sec)

mysql> INSERT into Get_dict value (2, ' Www.111cn.net-xifenfei ');
Query OK, 1 row Affected (0.00 sec)

mysql> INSERT into Get_dict value (3, ' xifenfei-www.111cn.net ');
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)

Close MySQL Database

[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

Using tools to parse InnoDB files

[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 ' 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 ' 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

Introduction to main documents

[Root@localhost recovery_mysql]# ls-l pages-ibdata1/fil_page_index/


Total 1388


-rw-r--r--1 root 16384 Dec 18:51 0000000000000001.page


-rw-r--r--1 root 16384 Dec 18:51 0000000000000002.page


-rw-r--r--1 root 49152 Dec 18:51 0000000000000003.page


-rw-r--r--1 root 49152 Dec 18:51 0000000000000004.page


-rw-r--r--1 root 16384 Dec 18:51 0000000000000005.page


-rw-r--r--1 root 114688 Dec 18:51 0000000000000011.page


-rw-r--r--1 root 114688 Dec 18:51 0000000000000012.page


-rw-r--r--1 root 114688 Dec 18:51 0000000000000013.page


-rw-r--r--1 root 114688 Dec 18:51 0000000000000014.page


-rw-r--r--1 root 114688 Dec 18:51 0000000000000015.page


-rw-r--r--1 root 147456 Dec 18:51 0000000000000016.page


-rw-r--r--1 root 98304 Dec 18:51 0000000000000017.page


-rw-r--r--1 root 114688 Dec 18:51 0000000000000018.page


-rw-r--r--1 root 49152 Dec 18:51 0000000000000019.page


-rw-r--r--1 root 49152 Dec 18:51 0000000000000020.page


-rw-r--r--1 root 49152 Dec 18:51 0000000000000021.page


-rw-r--r--1 root 65536 Dec 18:51 0000000000000025.page


-rw-r--r--1 root 16384 Dec 18:51 18446744069414584320.page


0000000000000001.page mainly records the table information file in MySQL


0000000000000002.page is primarily the information file that records the columns of tables in MySQL


0000000000000003.page mainly records the index information file of the 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-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_tablest '
(' 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 0 "ID" 1 4 0 0


000000000300 800000012d0138 sys_columns 1 "for_name" 1 4 0 0


............


000000000D1D 95000001510129 sys_columns 0 "id" 6 1283 4 0


000000000d1d 9500000151013E sys_columns 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_columnst ' (' table_id ', ' POS ', ' NAME ', ' mtype ', ' prtype ', ' LEN ', ' PREC ');

Extract Index data

[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_indexest '


(' 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 "id_ind" 1 3 0 302


............


000000000B02 820000013504c8 sys_indexes "Gen_clust_index" 0 1 6 3


000000000d1d 9500000151016B sys_indexes "PRIMARY" 1 3 9 3

start the MySQL database

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

Create an Extract data dictionary table

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)

Loading extracted data dictionary data

Mysql> Source Dumps/default/sys_tables.sql
Query OK, 0 rows Affected (0.00 sec)

Query OK, 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, 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, Affected Rows (0.00 sec)
records:115 deleted:0 skipped:0 warnings:0

Validating the extracted data dictionary data

Mysql> desc sys_tables


->;


+--------------+---------------------+------+-----+---------+-------+


| Field | Type | Null | Key | Default | Extra |


+--------------+---------------------+------+-----+---------+-------+


| NAME | varchar (255) | NO |         PRI |       | |


| ID | bigint (unsigned) |     NO | |       0 | |


| N_cols | Int (10) |     YES | |       NULL | |


| TYPE | int (a) unsigned |     YES | |       NULL | |


| mix_id | bigint (unsigned) |     YES | |       NULL | |


| Mix_len | int (a) unsigned |     YES | |       NULL | |


| Cluster_Name | varchar (255) |     YES | |       NULL | |


| Space | int (a) 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 (unsigned) | NO | PRI |       NULL | |


| POS | int (a) unsigned | NO | PRI |       NULL | |


| NAME | varchar (255) |     YES | |       NULL | |


| Mtype | int (a) unsigned |     YES | |       NULL | |


| Prtype | int (a) unsigned |     YES | |       NULL | |


| LEN | int (a) unsigned |     YES | |       NULL | |


| PREC | int (a) 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)

Here you can basically see that the MySQL database does not start, the use of tools to properly extract the MySQL data dictionary information