MySQL数据库无法启动实现数据恢复的例子
利用工具获取MySQL数据字典的例子这篇文章介绍的是有一些技巧,本文章是介绍在mysql未启动的情况利用dul或者其他三方工具直接读取数据文件中数据并进行恢复了,具体如下.
熟悉Oracle的朋友都知道,当Oracle数据库无法正常启动之时,可以通过dul或者其他三方工具直接读取数据文件中数据,从而来抢救数据,减少损失,在mysql中如果使用了innodb引擎也可以在mysql数据库不启动启动下抽取相关记录.本文为抽取数据字典篇章,后续将继续提供drop恢复,truncate恢复,delete恢复等mysql非常规恢复篇章.
创建一张get_dict测试表:
mysql>usexifenfei;
Readingtableinformationforcompletionoftableandcolumnnames
Youcanturnoffthisfeaturetogetaquickerstartupwith-A
Databasechanged
mysql>showtables;
+--------------------+
|Tables_in_xifenfei|
+--------------------+
|t_delete|
+--------------------+
1rowinset(0.00sec)
mysql>createtableget_dict(idintnotnullprimarykey,namevarchar(100));
QueryOK,0rowsaffected(0.01sec)
mysql>insertintoget_dictvalue(1,''www.phpfensi.com'');
QueryOK,1rowaffected(0.00sec)
mysql>insertintoget_dictvalue(2,''www.phpfensi.com-xifenfei'');
QueryOK,1rowaffected(0.00sec)
mysql>insertintoget_dictvalue(3,''xifenfei-www.phpfensi.com'');
QueryOK,1rowaffected(0.00sec)
mysql>showtables;
+--------------------+
|Tables_in_xifenfei|
+--------------------+
|get_dict|
|t_delete|
+--------------------+
2rowsinset(0.00sec
mysql>selectTABLE_NAME,TABLE_SCHEMA,TABLE_TYPEfrominformation_schema.tables
->wheretable_name=''get_dict'';
+------------+--------------+------------+
|TABLE_NAME|TABLE_SCHEMA|TABLE_TYPE|
+------------+--------------+------------+
|get_dict|xifenfei|BASETABLE|
+------------+--------------+------------+
1rowinset(0.01sec)
mysql>selectTABLE_NAME,NON_UNIQUE,TABLE_SCHEMA,INDEX_SCHEMA,INDEX_NAME,COLUMN_NAMEfrom
->INFORMATION_SCHEMA.STATISTICSwhereTABLE_NAME=''get_dict'';
+------------+------------+--------------+--------------+------------+-------------+
|TABLE_NAME|NON_UNIQUE|TABLE_SCHEMA|INDEX_SCHEMA|INDEX_NAME|COLUMN_NAME|
+------------+------------+--------------+--------------+------------+-------------+
|get_dict|0|xifenfei|xifenfei|PRIMARY|id|
+------------+------------+--------------+--------------+------------+-------------+
1rowinset(0.00sec)
mysql>selectTABLE_SCHEMA,TABLE_NAME,COLUMN_NAME,ORDINAL_POSITIONfrom
->information_schema.COLUMNSwheretable_name=''get_dict'';
+--------------+------------+-------------+------------------+
|TABLE_SCHEMA|TABLE_NAME|COLUMN_NAME|ORDINAL_POSITION|
+--------------+------------+-------------+------------------+
|xifenfei|get_dict|id|1|
|xifenfei|get_dict|name|2|
+--------------+------------+-------------+------------------+
2rowsinset(0.01sec)
关闭mysql数据库:
[root@localhostrecovery_mysql]#servicemysqlstop
ShuttingdownMySQL..[OK]
[root@localhostrecovery_mysql]#ps-ef|grepmysql
root1887615827018:05pts/100:00:00grepmysql
使用工具解析innodb文件:
[root@localhostrecovery_mysql]#./stream_parser-f/var/lib/mysql/ibdata1
Openingfile:/var/lib/mysql/ibdata1
Fileinformation:
Openingfile:/var/lib/mysql/ibdata1
Fileinformation:
IDofdevicecontainingfile:2054
inodenumber:1782889
IDofdevicecontainingfile:2054
protection:100660inodenumber:1782889
(regularfile)
protection:100660numberofhardlinks:1
(regularfile)
userIDofowner:101
numberofhardlinks:1
groupIDofowner:102
userIDofowner:101
deviceID(ifspecialfile):0
groupIDofowner:102
blocksizeforfilesystemI/O:4096
deviceID(ifspecialfile):0
numberofblocksallocated:24616
blocksizeforfilesystemI/O:4096
Openingfile:/var/lib/mysql/ibdata1
numberofblocksallocated:24616
Fileinformation:
厖厖厖roo
userIDofowner:101
groupIDofowner:102
deviceID(ifspecialfile):0
blocksizeforfilesystemI/O:4096
IDofdevicecontainingfile:2054
numberofblocksallocated:24616
inodenumber:1782889
protection:100660(regularfile)
numberofhardlinks:1
userIDofowner:101
groupIDofowner:102
deviceID(ifspecialfile):0
blocksizeforfilesystemI/O:4096
numberofblocksallocated:24616
timeoflastaccess:1417922668SunDec711:24:282014
timeoflastmodification:1418294104ThuDec1118:35:042014
timeoflaststatuschange:1418294104ThuDec1118:35:042014
timeoflastaccess:1417922668SunDec711:24:282014
totalsize,inbytes:12582912(12.000MiB)
timeoflastmodification:1418294104ThuDec1118:35:042014
timeoflaststatuschange:1418294104ThuDec1118:35:042014
Sizetoprocess:12582912(12.000MiB)
totalsize,inbytes:12582912(12.000MiB)
Sizetoprocess:12582912(12.000MiB)
Allworkersfinishedin0sec
主要文件介绍:
[root@localhostrecovery_mysql]#ls-lpages-ibdata1/FIL_PAGE_INDEX/
total1388
-rw-r--r--1rootroot16384Dec1118:510000000000000001.page
-rw-r--r--1rootroot16384Dec1118:510000000000000002.page
-rw-r--r--1rootroot49152Dec1118:510000000000000003.page
-rw-r--r--1rootroot49152Dec1118:510000000000000004.page
-rw-r--r--1rootroot16384Dec1118:510000000000000005.page
-rw-r--r--1rootroot114688Dec1118:510000000000000011.page
-rw-r--r--1rootroot114688Dec1118:510000000000000012.page
-rw-r--r--1rootroot114688Dec1118:510000000000000013.page
-rw-r--r--1rootroot114688Dec1118:510000000000000014.page
-rw-r--r--1rootroot114688Dec1118:510000000000000015.page
-rw-r--r--1rootroot147456Dec1118:510000000000000016.page
-rw-r--r--1rootroot98304Dec1118:510000000000000017.page
-rw-r--r--1rootroot114688Dec1118:510000000000000018.page
-rw-r--r--1rootroot49152Dec1118:510000000000000019.page
-rw-r--r--1rootroot49152Dec1118:510000000000000020.page
-rw-r--r--1rootroot49152Dec1118:510000000000000021.page
-rw-r--r--1rootroot65536Dec1118:510000000000000025.page
-rw-r--r--1rootroot16384Dec1118:5118446744069414584320.page
0000000000000001.page主要是记录mysql中表信息文件
0000000000000002.page主要是记录mysql中的表的列的信息文件
0000000000000003.page主要是记录mysql中表的index信息文件
抽取table数据:
[root@localhostrecovery_mysql]#./c_parser-4fpages-ibdata1/FIL_PAGE_INDEX/0000000000000001.page-t
dictionary/SYS_TABLES.sql>dumps/default/SYS_TABLES2>dumps/default/SYS_TABLES.sql
[root@localhostrecovery_mysql]#grepgetdumps/default/SYS_TABLES|head-5
000000000D1D95000001510110SYS_TABLES"xifenfei/get_dict"2321080""9
[root@localhostrecovery_mysql]#catdumps/default/SYS_TABLES.sql
SETFOREIGN_KEY_CHECKS=0;
LOADDATALOCALINFILE''/tmp/recovery_mysql/dumps/default/SYS_TABLES''REPLACEINTOTABLE`SYS_TABLES`
FIELDSTERMINATEDBY''t''OPTIONALLYENCLOSEDBY''"''LINESSTARTINGBY''SYS_TABLESt''
(`NAME`,`ID`,`N_COLS`,`TYPE`,`MIX_ID`,`MIX_LEN`,`CLUSTER_NAME`,`SPACE`);
抽取column数据:
[root@localhostrecovery_mysql]#./c_parser-4fpages-ibdata1/FIL_PAGE_INDEX/0000000000000002.page
-tdictionary/SYS_COLUMNS.sql>dumps/default/SYS_COLUMNS2>dumps/default/SYS_COLUMNS.sql
[root@localhostrecovery_mysql]#catdumps/default/SYS_COLUMNS
--Pageid:10,Format:REDUNDANT,Recordslist:Valid,Expectedrecords:(115115)
000000000300800000012D0123SYS_COLUMNS110"ID"1400
000000000300800000012D0138SYS_COLUMNS111"FOR_NAME"1400
厖厖[r
000000000D1D95000001510129SYS_COLUMNS230"id"6128340
000000000D1D9500000151013ESYS_COLUMNS231"name"15243031000
--Pageid:10,Foundrecords:115,Lostrecords:NO,Leafpage:YES
[root@localhostrecovery_mysql]#moredumps/default/SYS_COLUMNS.sql
SETFOREIGN_KEY_CHECKS=0;
LOADDATALOCALINFILE''/tmp/recovery_mysql/dumps/default/SYS_COLUMNS''REPLACEINTOTABLE
`SYS_COLUMNS`FIELDSTERMINATEDBY''t''OPTIONALLYENCLOSEDBY
''"''LINESSTARTINGBY''SYS_COLUMNSt''(`TABLE_ID`,`POS`,`NAME`,`MTYPE`,`PRTYPE`,`LEN`,`PREC`);
抽取index数据:
[root@localhostrecovery_mysql]#./c_parser-4fpages-ibdata1/FIL_PAGE_INDEX/0000000000000003.page
-tdictionary/SYS_INDEXES.sql>dumps/default/SYS_INDEXES2>dumps/default/SYS_INDEXES.sql
[root@localhostrecovery_mysql]#moredumps/default/SYS_INDEXES.sql
SETFOREIGN_KEY_CHECKS=0;
LOADDATALOCALINFILE''/tmp/recovery_mysql/dumps/default/SYS_INDEXES''REPLACEINTOTABLE
`SYS_INDEXES`FIELDSTERMINATEDBY''t''OPTIONALLYENCLOSEDBY''"''LINESSTARTINGBY''SYS_INDEXESt''
(`TABLE_ID`,`ID`,`NAME`,`N_FIELDS`,`TYPE`,`SPACE`,`PAGE_NO`);
[root@localhostrecovery_mysql]#moredumps/default/SYS_INDEXES
--Pageid:11,Format:REDUNDANT,Recordslist:Valid,Expectedrecords:(1313)
000000000300800000012D0177SYS_INDEXES1111"ID_IND"130302
厖厖[r
000000000B02820000013504C8SYS_INDEXES2022"GEN_CLUST_INDEX"0163
000000000D1D9500000151016BSYS_INDEXES2325"PRIMARY"1393
启动mysql数据库:
[root@localhostrecovery_mysql]#servicemysqlstart
StartingMySQL..[OK]
[root@localhostrecovery_mysql]#ps-ef|grepmysql
root189481019:57pts/100:00:00/bin/sh/usr/bin/mysqld_safe--datadir=/var/lib/mysql
--pid-file=/var/lib/mysql/localhost.localdomain.pid
mysql19049189481419:57pts/100:00:00/usr/sbin/mysqld--basedir=/usr--datadir=/var/lib/mwww.shanxiwang.netysql
--plugin-dir=/usr/lib64/mysql/plugin--user=mysql
--log-error=/var/lib/mysql/localhost.localdomain.err--pid-file=/var/lib/mysql/localhost.localdomain.pid
root1907815827019:58pts/100:00:00grepmysql
创建抽取数据字典表:
mysql>sourcedictionary/SYS_TABLES.sql
QueryOK,0rowsaffected(0.00sec)
QueryOK,0rowsaffected(0.00sec)
…………
mysql>sourcedictionary/SYS_INDEXES.sql
QueryOK,0rowsaffected(0.00sec)
QueryOK,0rowsaffected(0.00sec)
………
mysql>sourcedictionary/SYS_COLUMNS.sql
QueryOK,0rowsaffected(0.00sec)
QueryOK,0rowsaffected(0.00sec)
厖厖
m
mysql>showtables;
+----------------+
|Tables_in_test|
+----------------+
|SYS_COLUMNS|
|SYS_INDEXES|
|SYS_TABLES|
+----------------+
3rowsinset(0.00sec)
加载抽取数据字典数据:
mysql>sourcedumps/default/SYS_TABLES.sql
QueryOK,0rowsaffected(0.00sec)
QueryOK,11rowsaffected(0.03sec)
Records:11Deleted:0Skipped:0Warnings:0
mysql>sourcedumps/default/SYS_INDEXES.sql
QueryOK,0rowsaffected(0.00sec)
QueryOK,39rowsaffected(0.01sec)
Records:39Deleted:0Skipped:0Warnings:0
mysql>sourcedumps/default/SYS_COLUMNS.sql
QueryOK,0rowsaffected(0.00sec)
QueryOK,115rowsaffected(0.00sec)
Records:115Deleted:0Skipped:0Warnings:0
验证抽取数据字典数据:
mysql>descSYS_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||
+--------------+---------------------+------+-----+---------+-------+
8rowsinset(0.00sec)
mysql>SELECTNAME,IDfromSYS_TABLESWHERENAME=''xifenfei/get_dict'';
+-------------------+----+
|NAME|ID|
+-------------------+----+
|xifenfei/get_dict|23|
+-------------------+----+
1rowinset(0.00sec)
mysql>descSYS_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||
+----------+---------------------+------+-----+---------+-------+
7rowsinset(0.00sec)
mysql>SELECTTABLE_ID,NAME,MTYPEFROMSYS_COLUMNSWHERETABLE_ID=23;
+----------+------+-------+
|TABLE_ID|NAME|MTYPE|
+----------+------+-------+
|23|id|6|
|23|name|1|
+----------+------+-------+
2rowsinset(0.01sec)
mysql>SELECTTABLE_ID,ID,NAME,TYPEFROMSYS_INDEXESWHERETABLE_ID=23;
+----------+----+---------+------+
|TABLE_ID|ID|NAME|TYPE|
+----------+----+---------+------+
|23|25|PRIMARY|3|
+----------+----+---------+------+
1rowinset(0.00sec)
这里基本上可以看出来,在mysql数据库未启动情况下,使用工具可以正常抽取mysql数据字典信息.
|
|
