MySQL数据库无法启动实现数据恢复的例子

SEO探索者团队 SEO服务&网站优化

利用工具获取MySQL数据字典的例子这篇文章介绍的是有一些技巧,本文章是介绍在mysql未启动的情况利用dul或者其他三方工具直接读取数据文件中数据并进行恢复了,具体如下.

熟悉Oracle的朋友都知道,当Oracle数据库无法正常启动之时,可以通过dul或者其他三方工具直接读取数据文件中数据,从而来抢救数据,减少损失,在mysql中如果使用了innodb引擎也可以在mysql数据库不启动启动下抽取相关记录.本文为抽取数据字典篇章,后续将继续提供drop恢复,truncate 恢复,delete恢复等mysql非常规恢复篇章.

创建一张get_dict测试表:

  1. mysql>usexifenfei;
  2. Readingtableinformationforcompletionoftableandcolumnnames
  3. Youcanturnoffthisfeaturetogetaquickerstartupwith-A
  4. Databasechanged
  5. mysql>showtables;
  6. +--------------------+
  7. |Tables_in_xifenfei|
  8. +--------------------+
  9. |t_delete|
  10. +--------------------+
  11. 1rowinset(0.00sec)
  12. mysql>createtableget_dict(idintnotnullprimarykey,namevarchar(100));
  13. QueryOK,0rowsaffected(0.01sec)
  14. mysql>insertintoget_dictvalue(1,'www.phpfensi.com');
  15. QueryOK,1rowaffected(0.00sec)
  16. mysql>insertintoget_dictvalue(2,'www.phpfensi.com-xifenfei');
  17. QueryOK,1rowaffected(0.00sec)
  18. mysql>insertintoget_dictvalue(3,'xifenfei-www.phpfensi.com');
  19. QueryOK,1rowaffected(0.00sec)
  20. mysql>showtables;
  21. +--------------------+
  22. |Tables_in_xifenfei|
  23. +--------------------+
  24. |get_dict|
  25. |t_delete|
  26. +--------------------+
  27. 2rowsinset(0.00sec
  28. mysql>selectTABLE_NAME,TABLE_SCHEMA,TABLE_TYPEfrominformation_schema.tables
  29. ->wheretable_name='get_dict';
  30. +------------+--------------+------------+
  31. |TABLE_NAME|TABLE_SCHEMA|TABLE_TYPE|
  32. +------------+--------------+------------+
  33. |get_dict|xifenfei|BASETABLE|
  34. +------------+--------------+------------+
  35. 1rowinset(0.01sec)
  36. mysql>selectTABLE_NAME,NON_UNIQUE,TABLE_SCHEMA,INDEX_SCHEMA,INDEX_NAME,COLUMN_NAMEfrom
  37. ->INFORMATION_SCHEMA.STATISTICSwhereTABLE_NAME='get_dict';
  38. +------------+------------+--------------+--------------+------------+-------------+
  39. |TABLE_NAME|NON_UNIQUE|TABLE_SCHEMA|INDEX_SCHEMA|INDEX_NAME|COLUMN_NAME|
  40. +------------+------------+--------------+--------------+------------+-------------+
  41. |get_dict|0|xifenfei|xifenfei|PRIMARY|id|
  42. +------------+------------+--------------+--------------+------------+-------------+
  43. 1rowinset(0.00sec)
  44. mysql>selectTABLE_SCHEMA,TABLE_NAME,COLUMN_NAME,ORDINAL_POSITIONfrom
  45. ->information_schema.COLUMNSwheretable_name='get_dict';
  46. +--------------+------------+-------------+------------------+
  47. |TABLE_SCHEMA|TABLE_NAME|COLUMN_NAME|ORDINAL_POSITION|
  48. +--------------+------------+-------------+------------------+
  49. |xifenfei|get_dict|id|1|
  50. |xifenfei|get_dict|name|2|
  51. +--------------+------------+-------------+------------------+
  52. 2rowsinset(0.01sec)

关闭mysql数据库:

  1. [root@localhostrecovery_mysql]#servicemysqlstop
  2. ShuttingdownMySQL..[OK]
  3. [root@localhostrecovery_mysql]#ps-ef|grepmysql
  4. root1887615827018:05pts/100:00:00grepmysql

使用工具解析innodb文件:

  1. [root@localhostrecovery_mysql]#./stream_parser-f/var/lib/mysql/ibdata1
  2. Openingfile:/var/lib/mysql/ibdata1
  3. Fileinformation:
  4. Openingfile:/var/lib/mysql/ibdata1
  5. Fileinformation:
  6. IDofdevicecontainingfile:2054
  7. inodenumber:1782889
  8. IDofdevicecontainingfile:2054
  9. protection:100660inodenumber:1782889
  10. (regularfile)
  11. protection:100660numberofhardlinks:1
  12. (regularfile)
  13. userIDofowner:101
  14. numberofhardlinks:1
  15. groupIDofowner:102
  16. userIDofowner:101
  17. deviceID(ifspecialfile):0
  18. groupIDofowner:102
  19. blocksizeforfilesystemI/O:4096
  20. deviceID(ifspecialfile):0
  21. numberofblocksallocated:24616
  22. blocksizeforfilesystemI/O:4096
  23. Openingfile:/var/lib/mysql/ibdata1
  24. numberofblocksallocated:24616
  25. Fileinformation:
  26. ………………
  27. userIDofowner:101
  28. groupIDofowner:102
  29. deviceID(ifspecialfile):0
  30. blocksizeforfilesystemI/O:4096
  31. IDofdevicecontainingfile:2054
  32. numberofblocksallocated:24616
  33. inodenumber:1782889
  34. protection:100660(regularfile)
  35. numberofhardlinks:1
  36. userIDofowner:101
  37. groupIDofowner:102
  38. deviceID(ifspecialfile):0
  39. blocksizeforfilesystemI/O:4096
  40. numberofblocksallocated:24616
  41. timeoflastaccess:1417922668SunDec711:24:282014
  42. timeoflastmodification:1418294104ThuDec1118:35:042014
  43. timeoflaststatuschange:1418294104ThuDec1118:35:042014
  44. timeoflastaccess:1417922668SunDec711:24:282014
  45. totalsize,inbytes:12582912(12.000MiB)
  46. timeoflastmodification:1418294104ThuDec1118:35:042014
  47. timeoflaststatuschange:1418294104ThuDec1118:35:042014
  48. Sizetoprocess:12582912(12.000MiB)
  49. totalsize,inbytes:12582912(12.000MiB)
  50. Sizetoprocess:12582912(12.000MiB)
  51. Allworkersfinishedin0sec

主要文件介绍:

  1. [root@localhostrecovery_mysql]#ls-lpages-ibdata1/FIL_PAGE_INDEX/
  2. total1388
  3. -rw-r--r--1rootroot16384Dec1118:510000000000000001.page
  4. -rw-r--r--1rootroot16384Dec1118:510000000000000002.page
  5. -rw-r--r--1rootroot49152Dec1118:510000000000000003.page
  6. -rw-r--r--1rootroot49152Dec1118:510000000000000004.page
  7. -rw-r--r--1rootroot16384Dec1118:510000000000000005.page
  8. -rw-r--r--1rootroot114688Dec1118:510000000000000011.page
  9. -rw-r--r--1rootroot114688Dec1118:510000000000000012.page
  10. -rw-r--r--1rootroot114688Dec1118:510000000000000013.page
  11. -rw-r--r--1rootroot114688Dec1118:510000000000000014.page
  12. -rw-r--r--1rootroot114688Dec1118:510000000000000015.page
  13. -rw-r--r--1rootroot147456Dec1118:510000000000000016.page
  14. -rw-r--r--1rootroot98304Dec1118:510000000000000017.page
  15. -rw-r--r--1rootroot114688Dec1118:510000000000000018.page
  16. -rw-r--r--1rootroot49152Dec1118:510000000000000019.page
  17. -rw-r--r--1rootroot49152Dec1118:510000000000000020.page
  18. -rw-r--r--1rootroot49152Dec1118:510000000000000021.page
  19. -rw-r--r--1rootroot65536Dec1118:510000000000000025.page
  20. -rw-r--r--1rootroot16384Dec1118:5118446744069414584320.page

0000000000000001.page主要是记录mysql中表信息文件

0000000000000002.page主要是记录mysql中的表的列的信息文件

0000000000000003.page主要是记录mysql中表的index信息文件

抽取table数据:

  1. [root@localhostrecovery_mysql]#./c_parser-4fpages-ibdata1/FIL_PAGE_INDEX/0000000000000001.page-t
  2. dictionary/SYS_TABLES.sql>dumps/default/SYS_TABLES2>dumps/default/SYS_TABLES.sql
  3. [root@localhostrecovery_mysql]#grepgetdumps/default/SYS_TABLES|head-5
  4. 000000000D1D95000001510110SYS_TABLES"xifenfei/get_dict"2321080""9
  5. [root@localhostrecovery_mysql]#catdumps/default/SYS_TABLES.sql
  6. SETFOREIGN_KEY_CHECKS=0;
  7. LOADDATALOCALINFILE'/tmp/recovery_mysql/dumps/default/SYS_TABLES'REPLACEINTOTABLE`SYS_TABLES`
  8. FIELDSTERMINATEDBY't'OPTIONALLYENCLOSEDBY'"'LINESSTARTINGBY'SYS_TABLESt'
  9. (`NAME`,`ID`,`N_COLS`,`TYPE`,`MIX_ID`,`MIX_LEN`,`CLUSTER_NAME`,`SPACE`);

抽取column数据:

  1. [root@localhostrecovery_mysql]#./c_parser-4fpages-ibdata1/FIL_PAGE_INDEX/0000000000000002.page
  2. -tdictionary/SYS_COLUMNS.sql>dumps/default/SYS_COLUMNS2>dumps/default/SYS_COLUMNS.sql
  3. [root@localhostrecovery_mysql]#catdumps/default/SYS_COLUMNS
  4. --Pageid:10,Format:REDUNDANT,Recordslist:Valid,Expectedrecords:(115115)
  5. 000000000300800000012D0123SYS_COLUMNS110"ID"1400
  6. 000000000300800000012D0138SYS_COLUMNS111"FOR_NAME"1400
  7. …………
  8. 000000000D1D95000001510129SYS_COLUMNS230"id"6128340
  9. 000000000D1D9500000151013ESYS_COLUMNS231"name"15243031000
  10. --Pageid:10,Foundrecords:115,Lostrecords:NO,Leafpage:YES
  11. [root@localhostrecovery_mysql]#moredumps/default/SYS_COLUMNS.sql
  12. SETFOREIGN_KEY_CHECKS=0;
  13. LOADDATALOCALINFILE'/tmp/recovery_mysql/dumps/default/SYS_COLUMNS'REPLACEINTOTABLE
  14. `SYS_COLUMNS`FIELDSTERMINATEDBY't'OPTIONALLYENCLOSEDBY
  15. '"'LINESSTARTINGBY'SYS_COLUMNSt'(`TABLE_ID`,`POS`,`NAME`,`MTYPE`,`PRTYPE`,`LEN`,`PREC`);

抽取index数据:

  1. [root@localhostrecovery_mysql]#./c_parser-4fpages-ibdata1/FIL_PAGE_INDEX/0000000000000003.page
  2. -tdictionary/SYS_INDEXES.sql>dumps/default/SYS_INDEXES2>dumps/default/SYS_INDEXES.sql
  3. [root@localhostrecovery_mysql]#moredumps/default/SYS_INDEXES.sql
  4. SETFOREIGN_KEY_CHECKS=0;
  5. LOADDATALOCALINFILE'/tmp/recovery_mysql/dumps/default/SYS_INDEXES'REPLACEINTOTABLE
  6. `SYS_INDEXES`FIELDSTERMINATEDBY't'OPTIONALLYENCLOSEDBY'"'LINESSTARTINGBY'SYS_INDEXESt'
  7. (`TABLE_ID`,`ID`,`NAME`,`N_FIELDS`,`TYPE`,`SPACE`,`PAGE_NO`);
  8. [root@localhostrecovery_mysql]#moredumps/default/SYS_INDEXES
  9. --Pageid:11,Format:REDUNDANT,Recordslist:Valid,Expectedrecords:(1313)
  10. 000000000300800000012D0177SYS_INDEXES1111"ID_IND"130302
  11. …………
  12. 000000000B02820000013504C8SYS_INDEXES2022"GEN_CLUST_INDEX"0163
  13. 000000000D1D9500000151016BSYS_INDEXES2325"PRIMARY"1393

启动mysql数据库:

  1. [root@localhostrecovery_mysql]#servicemysqlstart
  2. StartingMySQL..[OK]
  3. [root@localhostrecovery_mysql]#ps-ef|grepmysql
  4. root189481019:57pts/100:00:00/bin/sh/usr/bin/mysqld_safe--datadir=/var/lib/mysql
  5. --pid-file=/var/lib/mysql/localhost.localdomain.pid
  6. mysql19049189481419:57pts/100:00:00/usr/sbin/mysqld--basedir=/usr--datadir=/var/lib/mysql
  7. --plugin-dir=/usr/lib64/mysql/plugin--user=mysql
  8. --log-error=/var/lib/mysql/localhost.localdomain.err--pid-file=/var/lib/mysql/localhost.localdomain.pid
  9. root1907815827019:58pts/100:00:00grepmysql

创建抽取数据字典表:

  1. mysql>sourcedictionary/SYS_TABLES.sql
  2. QueryOK,0rowsaffected(0.00sec)
  3. QueryOK,0rowsaffected(0.00sec)
  4. …………
  5. mysql>sourcedictionary/SYS_INDEXES.sql
  6. QueryOK,0rowsaffected(0.00sec)
  7. QueryOK,0rowsaffected(0.00sec)
  8. …………
  9. mysql>sourcedictionary/SYS_COLUMNS.sql
  10. QueryOK,0rowsaffected(0.00sec)
  11. QueryOK,0rowsaffected(0.00sec)
  12. …………
  13. mysql>showtables;
  14. +----------------+
  15. |Tables_in_test|
  16. +----------------+
  17. |SYS_COLUMNS|
  18. |SYS_INDEXES|
  19. |SYS_TABLES|
  20. +----------------+
  21. 3rowsinset(0.00sec)

加载抽取数据字典数据:

  1. mysql>sourcedumps/default/SYS_TABLES.sql
  2. QueryOK,0rowsaffected(0.00sec)
  3. QueryOK,11rowsaffected(0.03sec)
  4. Records:11Deleted:0Skipped:0Warnings:0
  5. mysql>sourcedumps/default/SYS_INDEXES.sql
  6. QueryOK,0rowsaffected(0.00sec)
  7. QueryOK,39rowsaffected(0.01sec)
  8. Records:39Deleted:0Skipped:0Warnings:0
  9. mysql>sourcedumps/default/SYS_COLUMNS.sql
  10. QueryOK,0rowsaffected(0.00sec)
  11. QueryOK,115rowsaffected(0.00sec)
  12. Records:115Deleted:0Skipped:0Warnings:0

验证抽取数据字典数据:

  1. mysql>descSYS_TABLES
  2. ->;
  3. +--------------+---------------------+------+-----+---------+-------+
  4. |Field|Type|Null|Key|Default|Extra|
  5. +--------------+---------------------+------+-----+---------+-------+
  6. |NAME|varchar(255)|NO|PRI|||
  7. |ID|bigint(20)unsigned|NO||0||
  8. |N_COLS|int(10)|YES||NULL||
  9. |TYPE|int(10)unsigned|YES||NULL||
  10. |MIX_ID|bigint(20)unsigned|YES||NULL||
  11. |MIX_LEN|int(10)unsigned|YES||NULL||
  12. |CLUSTER_NAME|varchar(255)|YES||NULL||
  13. |SPACE|int(10)unsigned|YES||NULL||
  14. +--------------+---------------------+------+-----+---------+-------+
  15. 8rowsinset(0.00sec)
  17. mysql>SELECTNAME,IDfromSYS_TABLESWHERENAME='xifenfei/get_dict';
  18. +-------------------+----+
  19. |NAME|ID|
  20. +-------------------+----+
  21. |xifenfei/get_dict|23|
  22. +-------------------+----+
  23. 1rowinset(0.00sec)
  24. mysql>descSYS_COLUMNS
  25. ->;
  26. +----------+---------------------+------+-----+---------+-------+
  27. |Field|Type|Null|Key|Default|Extra|
  28. +----------+---------------------+------+-----+---------+-------+
  29. |TABLE_ID|bigint(20)unsigned|NO|PRI|NULL||
  30. |POS|int(10)unsigned|NO|PRI|NULL||
  31. |NAME|varchar(255)|YES||NULL||
  32. |MTYPE|int(10)unsigned|YES||NULL||
  33. |PRTYPE|int(10)unsigned|YES||NULL||
  34. |LEN|int(10)unsigned|YES||NULL||
  35. |PREC|int(10)unsigned|YES||NULL||
  36. +----------+---------------------+------+-----+---------+-------+
  37. 7rowsinset(0.00sec)
  38. mysql>SELECTTABLE_ID,NAME,MTYPEFROMSYS_COLUMNSWHERETABLE_ID=23;
  39. +----------+------+-------+
  40. |TABLE_ID|NAME|MTYPE|
  41. +----------+------+-------+
  42. |23|id|6|
  43. |23|name|1|
  44. +----------+------+-------+
  45. 2rowsinset(0.01sec)
  46. mysql>SELECTTABLE_ID,ID,NAME,TYPEFROMSYS_INDEXESWHERETABLE_ID=23;
  47. +----------+----+---------+------+
  48. |TABLE_ID|ID|NAME|TYPE|
  49. +----------+----+---------+------+
  50. |23|25|PRIMARY|3|
  51. +----------+----+---------+------+
  52. 1rowinset(0.00sec)

这里基本上可以看出来,在mysql数据库未启动情况下,使用工具可以正常抽取mysql数据字典信息.

相关广告
  • MySQL数据库无法启动实现数据恢复的例子 MySQL数据库无法启动实现数据恢复的例子 MySQL数据库无法启动实现数据恢复的例子
相关阅读

MySQL数据库无法启动实现数据恢复的例子

2019/10/10 17:32:14 | 谷歌SEO算法 | 友情链接作弊