位置：首页 > 数据库 > MySQL
MySQL无法启动实现数据恢复的简单示例
日期：2023-06-26 人气：

大家好，对MySQL数据库无法启动实现数据恢复的例子感兴趣的小伙伴，下面一起跟随三零脚本的小编来看看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.q3060.com');
QueryOK,1rowaffected(0.00sec) mysql>insertintoget_dictvalue(2,'www.q3060.com-xifenfei');
QueryOK,1rowaffected(0.00sec) mysql>insertintoget_dictvalue(3,'xifenfei-www.q3060.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: ………………
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
………… 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
………… 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/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
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) …………
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数据字典信息.

 

您可能感兴趣的文章

上一篇：MySQL 两种查询指定某字段，顺序排序的实现方法
下一篇：MySQL 慢查询记录和mysqlbinlog配置示例
猜你喜欢
  • 【推荐】Mysql 高效率随机获得一条或多条记录的方法
  • MySQL数据库随机读取、更新n条数据的实现方法
  • 浅析mariadb multi-source replication,mariadb多主复制
  • MySQL数据库创建修改、删除数据表示例
  • MySQL数据库在SQL语句使用replace替换字符示例
  • MySQL数据库的连接与关闭的示例
  • MySQL数据库MySQLbinlog把mysql二进制文件转换文本文件的简单示例
  • MYSQL 高效随机获取大表数据的简单示例
  • 解决MYSQL提示 max_open_files: 2048 max_connections: 800 table_cache: 619的问题
  • MySQL 三种效率对比随机获取数据的简单示例
  • mysql实现高效率随机获取数据的简单示例
  • mysql 快速导入导出数据(load data和outfile)的简单示例
  • mysql rand随机数据优化与性能对比
  • mysql 高效率随机获取n条数据
  • MySQL 高效率随机数据的的代码
  • MySQL数据库备份、还原方法总结
  • MySQL数据库innobackupex xtrabackup备份/还原的简单示例
  • MYSQL 获取随机记录的高效率算法示例
  • mysql优化：触发器和auto_increment的简单示例
  • mysql 解决Copying to tmp table on disk 影响性能的问题
    • ️专业收藏整理多类脚本学习资料的网站。