MySQL数据库意外删除后恢复攻略：表结构修复与数据重建详解

日期：2024-12-12 14:43:44 作者：创始人浏览：0

mysql卸载后怎么找回原来建的数据库?

每个DBA都有删除数据库的经历吗？如果我的数据库已被删除并且我没有备份，我该怎么办？如果备份和恢复后服务无法启动，会发生什么情况？如果我的表定义已损坏并且数据不可读，我该怎么办？

我曾经遇到过一家互联网初创公司，由于维护人员不定期的备份和恢复操作，导致系统表空间文件被初始化，导致数万张表无法读取。
去营救他们。

如果数据被确定为不可读，可能不是数据丢失了，而是DBMS找不到描述数据的信息。

背景

首先，让我们了解一些关键的InnoDB数据字典表。
这存储了一些表定义信息，在恢复所使用的表结构时需要用到。
。

SYS_TABLES描述了InnoDB表信息。
CREATETABLE`SYS_TABLES`(`NAME`varchar(255)NOTNULLDEFAULT''，表名`ID`h3int(20)unsignedNOTNULLDEFAULT'0'，表ID`N_COLS`int(10)DEFAULTNULL，`TYPE`int(10)unsignedDEFAULTNULL,`MIX_ID`h3int(20)unsignedDEFAULTNULL,`MIX_LEN`int(10)unsignedDEFAULTNULL,`CLUSTER_NAME`varchar(255)DEFAULTNULL,`SPACE`int(10)unsignedDEFAULTNULL,表空间idPRIMARYKEY(`NAME`))ENGINE=InnoDBDEFAULTCHARSET=latin1;SYS_INDEXES描述InnoDB索引信息。
CREATETABLE`SYS_INDEXES`(`TABLE_ID`h3int(20)unsignedNOTNULLDEFAULT'0'，sys_tables中ID`ID`h3int(20)unsignedNOTNULLDEFAULT'0'，对应索引ID`NAME`varchar(120)DEFAULTNULL，索引名称`N_FIELDS`int(10)无符号DEFAULTNULL;索引包含的字段数量`TYPE`int(10)unsignedDEFAULTNULL,`SPACE`int(10)unsignedDEFAULTNULL,存储索引的表空间ID`PAGE_NO`int(10)unsignedDEFAULTNULL,索引rootpageidPRIMARYKEY(`TABLE_ID`,`ID`))ENGINE=InnoDBDEFAULTCHARSET=latin1;SYS_COLUMNS描述了sys_tables`POS`的ID对应的InnoDB表CREATETABLE`SYS_COLUMNS`(`TABLE_ID`h3int(20)unsignedNOTNULL)的字段信息。
整数(10)无符号NOTNULL，相对字段位置`NAME`varchar(255)DEFAULTNULL，字段名称`MTYPE`int(10)unsignedDEFAULTNULL，字段编码`PRTYPE`int(10)unsignedDEFAULTNULL，字段解析类型`LEN`int(10)unsignedDEFAULTNULL，字段字节长度`PREC`int(10)unsignedDEFAULTNULL，字段精度PRIMARYKEY(`TABLE_ID`,`POS`))ENGINE=InnoDBDEFAULTCHARSET=latin1;SYS_FIELDS描述所有索引的字段列。
CREATETABLE`SYS_FIELDS`(`INDEX_ID`h3int(20)unsignedNOTNULL，`POS`整数(10)unsignedNOTNULL，`COL_NAME`varchar(255)DEFAULTNULL，PRIMARYKEY(`INDEX_ID`,`POS`))ENGINE=InnoDBDEFAULTCHARSET=latin1;./storage/innobase/include/dict0boot.h文件定义了每个字典表对应的索引ID：字典表中的数据存储在id页中。

要在这里恢复数据，您需要使用undrop-for-innodb工具。
该工具可以读取表空间信息以获取页面并从中提取数据。

#wgethttps://github.com/chhabhaiya/undrop-for-innodb/archive/master.zip#yuminstall-ygccflexbison#make#makesys_parser

#./sys_parser表结构信息读取。

sys_parser[-h][-u][-p][-d]数据库/表

stream_parser从ibdata1或ib读取InnoDBpage。
d或分区表

#./stream_parser-foptionUsage:./stream_parser-f[-TN:M][-ssize][-tsize必须用于指定文件。
][-V|-g]其中：-h-Printthishelp-Vor-g-打印调试信息-ssize-Memory使用的fordiskcache数量（allowedexamples1G10M).Default100M-T-仅搜索indexid=NM(N-highword,M-lowwordofid)的页面-tsize-SizeofInnoDBtablespacetoscan。
仅当解析器无法自行决定时使用。

c_parser读取记录从innodbpage并保存到文件。

#./c_parserError:用法：./c_parser-4|-5|-6[-dDV]-f-ttable.sql[-TN:M][-b]其中-f--InnoDBpageordirectorywithpages（所有页面必须具有相同的index_id）-t

--CREATEstatementofatable-o--Savedumpinthisfile.Otherwiseprinttostdout-l--将SQL语句保存到此文件。
Otherwiseprinttostderr-h--Printthishelp-d--仅处理可能已删除记录的页面（默认）=NO)-D--仅恢复已删除的行（默认=否）-U--仅恢复未删除的行（默认=是）-V--Verbosemode(lotsofdebuginformation)-4--innodb_datafileisinREDUNDANTformat-5--innodb_datafileisinCOMPACTformat-6--innodb_datafileisinMySQL5.6format-T--retrievesonlypageswithindexid=NM(N-highword,M-lowwordofid)-b--外部页面目录通常可以找到它。
ispages-XXX/FIL_PAGE_TYPE_BLOB/-i<文件>--读取偏移量为<文件>的外部页。
-pprefix--在LOADDATAINFILE命令中使用目录名前缀

场景如下所示。
多种数据恢复场景。

场景一：droptable

根据是否启用innodb_file_per_table，恢复方法有所不同。
如果表被意外删除，您应该尽快停止MySQL服务而不是启动它。
如果innodb_file_per_table=ON，最好以只读模式重新挂载文件系统，以防止其他进程写入数据并覆盖旧的块设备数据。

要评估记录是否被覆盖，可以检查表中的特定记录是否可以在ibdata1中使用它作为关键字进行过滤。

#grepWOODYHOFFMANibdata1

Binaryfileibdata1matches

您还可以使用工具bvi（对于小文件）或hexdump-C（对于大文件）。

以sakila.actor表为例：CREATETABLE`actor`(`actor_id`smallint(5)unsignedNOTNULLAUTO_INCRMENT,`first_name`varchar(45)NOTNULL,`last_name`varchar(45)NOTNULL,`last_update`时间戳NOTNULLDEFAULTCURRENT_TIMESTAMPONUPDATECURRENT_TIMESTAMP,PRIMARYKEY(`actor_id`),KEY`idx_actor_last_name`(`last_name`))ENGINE=InnoDBAUTO_INCRMENT=201DEFAULTCHARSET=utf8

一、恢复表结构信息1.解析系统表空间获取页面信息

./stream_parser-f/var/lib/mysql/ibdata1

2.创建并添加新架构。
系统字典表DDL导入

catdictionary/SYS_*|mysqlrecovered

3.创建恢复目录

mkdir-pdumps/default

4.解析系统表空间中包含的字典表信息。

./c_parser-4fpages-ibdata1/FIL_PAGE_INDEX/0000000000000001。
页-tdictionary/SYS_TABLES.sql>转储/默认/SYS_TABLES2>转储/默认/SYS_TABLES.sql。
/c_parser-4fpages-ibdata1/FIL_PAGE_INDEX/0000000000000002.page-tdictionary/SYS_COLUMNS.sql>转储/默认/SYS_COLUMNS2>转储/默认/SYS_COLUMNS.sql./c_parser-4fpages-ibdata1/FIL_PAGE_INDEX/0000000000000003.page-tdictionary/SYS_INDEXES.sql>转储/默认/SYS_INDEXES2>转储/默认/SYS_INDEXES.sql./c_parser-4fpages-ibdata1/FIL_PAGE_INDEX/0000000000000004.page-tdictionary/SYS_FIELDS.sql>dumps/default/SYS_FIELDS2>dumps/default/SYS_FIELDS.sql

5.导入恢复的数据字典

catdumps/default/*.sql|mysqlrecovered

6.恢复表结构信息阅读

./sys_parser-pmsandbox-drecoveredsakila/actor

innodb引擎5.x版本并没有完整记录表结构信息，以及AUTO_INCRMENT属性、二级索引、外键约束等会丢失DECIMAL精度等信息。

如果系统中已经删除了mysql5.5版本的frm文件，还可以通过触摸原目录下与原表同名的frm文件来读取表结构信息和数据。
如果只有frm文件，想要获取表结构信息，可以使用mysqlfrm--diagnostic/path/to/xxx.frm。
当我连接到mysql时，它显示字符集信息。

innodb_file_per_table=OFF

由于这是共享表空间模式，数据页存储在ibdata1中，可以从ibdata1中提取数据。
文件。

1.获取表的tableid。
sys_table存储表的tableid。
sys_table表indexid为1，因此我们从0000000000000001.page./c_parser-4Dfpages-ibdata1/FIL_PAGE_INDEX获取表id。
/0000000000000001.page-tdictionary/SYS_TABLES.sql|grepsakila/actor000000000B282A000001430D4DSYS_TABLES“sakila/actor”1584100“”0000000000B282A000001430D4DSYS_TABLES“sakila/演员”1584100“”0

2。
使用tableid获取表的主键ID，使用sys_indexes存储表索引信息。
通过在nnodb索引组织表中查找主键ID来查找数据。
sys_indexes的indexid为3，因此我们从0000000000000003.page

./c_parser-4Dfpages-ibdata1/FIL_PAGE_INDE中获取主键ID。
X/00000000000003.page-tdictionary/sys_indexes.sql|X/00000000000003.page-tdictionary/sys_indexes.sqlGREP158000000000B282A000001430BCASYS_INDEXES158376“默认”1304294967295000000000B282A000001430C3CSYS_INDEXES158377“idx_actor_last_name”1004294967295000000000B282A000001430BCASYS_INDEXES158376“默认”1304294967295000000000B282A000001430C3CSYS_INDEXES158377“idx_actor_last_name”1004294967295

3。
如果您知道主键ID，则可以从该页面提取表数据并生成

./c_parser。
-4fpa。
ges-ibdata1/FIL_PAGE_INDEX/0000000000000376.page-tsakila/actor.sql>dumps/default/actor2>dumps/default/actor_load.sql

4、最后恢复的数据导入。

catdumps/default/*.sql|mysqlsakila

单击了解更多详细信息。

网络链接点击输入图片描述