恢复mysql数据

1 概述

⭐️当mysql意外无法正常运行，但是保留了frm和ibd文件时，往往还是能通过这两种文件进行数据恢复的。
⭐️这两个文件的位置一般在/usr/local/mysql/data/dbname/或/var/lib/mysql/dbname/目录下。
⭐️如果以上两个都不适用，也通过命令查找mysql相关目录，再进行逐一查找，例如
whereis mysql

2 恢复表结构

2.1 环境和工具

系统环境：linux
当前目录：/var/test/下
待恢复的表结构文件：在 /var/test/mysql/testdb/ 下的所有frm文件
待恢复的表数据文件：在 /var/test/mysql/testdb/ 下的所有ibd文件
还原表结构工具：dbsake

2.2 下载

curl -s http://get.dbsake.net > dbsake

2.3 赋权

chmod u+x dbsake  

2.4 批量读取 表结构

生成创建表结构sql语句 至文件 /var/test/mysql/rev/rev.sql

sudo ./dbsake frmdump /var/test/mysql/testdb/*.frm > /var/test/mysql/rev/rev.sql

利用mysql可视化工具导入sql文件，因为表结构和数据库版本问题，导入时可能会遇到问题，需要根据报错信息调整sql文件，以下为常见问题

2.5 常见导入问题

• 字段类型为timestamp、不为空 时，必须有默认值。
  默认值需要是大于等于1970-00-00 08:00:02的标准时间格式（其他文章看到是1970-00-00 00:00:01，但是本地测试结果为需要多加八小时左右，可能会根据区时有所不同）
• 两个字段同时设置为timestamp、可为空、无默认值 时，可能会报错。建议至少其中一个设置默认值。

3 恢复表数据

3.1 删除新建表的表空间（mysql命令模式）

ALTER TABLE tb_name DISCARD TABLESPACE;

3.2 复制表空间并授权

cp tags.ibd /var/lib/mysql/<database_name> 
cd /var/lib/mysql/<database_name>
chown mysql:mysql tags.ibd

批量赋权可以参考以下命令：

# 在复制好的ibd文件目录下执行
chown mysql:mysql *.ibd

3.3 关联表空间（mysql命令模式）

ALTER TABLE tb_name IMPORT TABLESPACE;

3.4 可能问题

如果关联时遇报错：

Schema mismatch (Table has ROW_TYPE_DYNAMIC row format, .ibd file has ROW_TYPE_COMPACT row format.)

说明表行类型不一致，需要修改成后面的类型。
以以上报错为例，需要修改成COMPACT，则对应的修改语句如下（该语句执行后可能会删除复制的idb文件，如果删除了请重新从3.2开始执行步骤）
ALTER TABLE tb_name ROW_FORMAT=COMPACT;

3.5 批量处理

在数据表不多的情况下，按照3.1-3.4流程处理可行，但是如果表格上百张，就不太现实。所以写了生成批量表格3.1、3.3、3.4步骤的sql文件的代码，实现原理也很简单，主要就是拼接sql并写入到文件中。
php代码如下：

// 获取ibd文件目录
$list = glob('C:\Users\Administrator.DESKTOP-4PF1LET\Desktop\dbname\*.ibd');

// 生成数据库恢复文件语句
$fileContent1 = "";
$fileContent2 = "";
$fileContent3 = "";
foreach ($list as $table) {
    $table_name = pathinfo($table, PATHINFO_FILENAME);
    $fileContent1 .= ' -- 删除表空间：' . $table_name . PHP_EOL;
    $fileContent1 .= "ALTER TABLE `{$table_name}` DISCARD TABLESPACE;" . PHP_EOL . PHP_EOL;

    $fileContent2 .= ' -- 修改表行类型：' . $table_name . PHP_EOL;
    $fileContent2 .= "ALTER TABLE `{$table_name}` ROW_FORMAT = COMPACT;" . PHP_EOL . PHP_EOL;

    $fileContent3 .= ' -- 关联表空间：' . $table_name . PHP_EOL;
    $fileContent3 .= "ALTER TABLE `{$table_name}` IMPORT TABLESPACE;" . PHP_EOL;
}

file_put_contents(__DIR__ . '/del.sql', $fileContent1);
file_put_contents(__DIR__ . '/alter.sql', $fileContent2);
file_put_contents(__DIR__ . '/import.sql', $fileContent3);

4 参考

https://blog.csdn.net/xiaojin21cen/article/details/103971628
https://mp.weixin.qq.com/s/6BM22N6FfmhsWYWEJfJO9w