Mysql数据恢复

一、数据库数据恢复

  本博讲解的是应对数据库崩溃无法恢复，但是仍旧可以获取到数据库原文件的前提下，针对数据库原文件进行的数据恢复。主要讲解 MySiam 和 InnoDB 两种引擎数据表的恢复方法。

这两种数据表的位置均在 /var/lib/mysql，不同的安装方式可能位置不一样

0、测试环境

Centos7、MySQL8.0.13

1、MySiam引擎数据表恢复

前提

  进行以下方法的数据恢复，首先要保证，之前被破坏的数据库和新的数据库版本完全一致，否则会报告原数据库表版本同新数据库不一致。
  每个MySiam引擎数据表都有一个表结构文件 sdi ，其内所存的便是该表的版本、表结构等信息。其中必须要保证以下三个字段同新数据库版本完全一致，这三个字段均在 sdi 文件的第一行。
  不确定的话可以打开新数据库中的随意一个 sdi 文件，对比是否是自己需要的数据库版本。当然，如果版本不一致可以现在别的地方安装一个同 sdi 文件同样版本的数据库，先把数据导出来再往新数据库转移也是可以的嘛！！！

mysqld_version_id":80013,"dd_version":80013,"sdi_version":1

准备

  MySiam引擎数据表：每张表包括三个后缀分别为 sdi，MYD，MYI 的文件。

数据恢复

• 创建新的数据库

> create database Dd;

• 将所有后缀为 MYD，MYI 的数据文件复制到 Dd 数据库目录下，并注意修改这部分文件的读写权限、所有者、所有组，同新数据库中其它的数据表一致，否则恢复数据的时候会因为权限问题出现无法读取数据的情况。

/var/lib/mysql/Dd

• Mysql本身有一个文件系统安全保护机制，非Mysql指定的安全文件夹，Mysql数据库不会对其中的文件进行操作，当然可以对Mysql的变量 secure_file_priv 进行设置，修改安全文件夹目录，但一般不要乱修改。

>show variables like '%secure_file_priv%';

• 将所有后缀为 sdi 的文件复制到变量 secure_file_priv 所在的目录，或其子目录，默认为以下路径。同时注意修改文件权限、所有用户、所有组，以防Mysql无权限操作表结构文件。

/var/lib/mysql-files

• 注意 sdi 文件名按理来说应该为表名，但为了某些目的，可能是为了防止表名过长，原表建表之初会对 sdi 文件名进行重构，类似下方示例：

表名：     linxia_search_UserGaoKuan
文件名： linxia_search_Us_393.sdi

  明显重构后的文件名不是表名，所以我们需要将 sdi 文件名修改为表名。若不清楚原表名，可以打开文件 sdi 文件（普通的json格式文档）查看 dd_object 下的 name 关键字，即为原表名。

$ mv linxia_search_Us_393.sdi linxia_search_UserGaoKuan.sdi

• 进入被导入表的数据库，将需要导入的数据表一次性全部导入新数据库中

> use Dd;
> import table from '/var/lib/mysql-files/*.sdi'

2、InnoDB引擎数据表恢复

前提

  此方法应该适用于所有Mysql版本。Mysql8.0后，新版本 InnoDB 数据表不再是分为以 frm 为后缀的表结构文件和以 ibd 为后缀的数据文件，而是全部集中在 ibd 文件中。

准备

  InnoDB引擎数据表：每张表包括一个后缀为 ibd 的文件。

数据恢复

• 不同于恢复MySiam引擎数据的直接导入数据，InnoDB需要先自建数据表，然后再导入数据
• 先创建一个数据库

> create database Db;

• 然后从 ibd 文件中导出数据表结构，如 2.ibd ：
  • 方法一：使用Mysql自带的工具 ibd2sdi ，即意为将数据文件转换为表结构文件。

     ibd2sdi --dump-file=activity.txt  activity.ibd
    

    最后导出的是关于表的所有信息，太多冗余，不利于提取建表的关键信息

• 方法二：直接使用linux工具 jq（Linux下面把文本字符串格式化成json格式的工具） 提取表最基本的关键信息，当然你可以学习一下 jq 的使用方法，提取起它你需要的关键信息

  ibd2sdi activity.ibd |jq  '.[]?|.[]?|.dd_object?|({table:.name?},(.columns?|.[]?|{name:.name,type:.column_type_utf8,is_nullable:.is_nullable}))' > activity.json
  

  最后导出的数据表结构如下图

  以下为批量导出数据表结构脚本

#!/bin/bash
files='/var/lib/mysql-files/*.ibd'
for file in ${files};
do
filename=${file##*/};
ibd2sdi $file |jq  '.[]?|.[]?|.dd_object?|({table:.name?},(.columns?|.[]?|{name:.name,type:.column_type_utf8,is_nullable:.is_nullable}))' > ${filename}.json
done

• 根据导出的数据表结构手动重建表，然后我们会发现在新数据库路径下（这里我们默认为 /var/lib/mysql/Db）下也会生成一个跟我们需要导出数据的 ibd 文件同名的 ibd 文件，注意重建表之后需要将 Mysql 服务关闭，后续需要修改一些必要的mysql服务器配置以重启 Mysql
• 打开新生成的 ibd 文件，以十六进制文件格式打开，可以发现第三行中间有一个序列 001d 0000 001d，每张表在建表之初都会赋予一个id，此id同样保存在 /var/lib/mysql/ibdata 文件中，001d 即为此表在本 Mysql 中的 id，记住此id。之后使用 :%!xxd -r 还原为二进制文件后，退出。

$ vim activity.ibd
:%!xxd
:%!xxd -r
:wq

• 以同样的方式打开旧 ibd 文件，并修改相应位置的 id 序列，如 0010 0000 0010 修改为 001d 0000 001d ，然后还原为二进制文件，保存退出。然后直接使用旧 ibd 文件替换新建的 ibd 文件，同样要注意替换后文件的权限问题，防止后续因权限报错，无法读取数据。
• 添加Mysql配置，此新添加的配置目的为用户数据被破坏后，强制恢复数据所用。恢复完数据后需要将该配置移除。

[mysqld]
innodb_force_recovery=6
innodb_purge_threads=0

• 修改配置后启动Mysql，你就会发现 InnoDB 引擎数据表数据恢复了，这个时候将所有数据全部导出到 .sql 文件中
• 删除数据库路径下的 ibd 文件，移除 Mysql 强制恢复数据的配置后，重启 Mysql
• 这时候可能会报错 InnoDB 引擎数据丢失，当然是因为删了数据的原因。so，重建 InnoDB 引擎数据表，使用之前保存的 .sql 文件，将数据全部导入到新数据表中，至此，数据恢复完成。
• 注意此强制恢复数据有一定概率会使Mysql本身崩溃，所以做好每一步的数据保存工作，有可能需要重装Mysql。

二、后记

其实关于其中很多原理细节我没有提及，详细说明我会不定时更新。

三、引用

Mysql如何利用ibd文件恢复数据？
【MySQL】MySQL8.0 ibd2sdi 根据ibd文件恢复表结构
MySQL工具ibd2sdi官方文档
MySQL利用MySiam原文件恢复数据官方文档