Mysql使用.ibd文件批量恢复数据

1 场景

Mysql 宕机，数据库损坏，启动不起来。
数据库正常启动时，如果使用navicat看到库表，但是使用sql操作时，提示表不存在，这种情况也可以使用该方法进行恢复。

2 .ibd 是什么

使用InnoDB存储引擎创建数据库时，会生成以下文件：

• .frm文件：这是表结构定义文件，包含表的元数据信息，例如列名、数据类型和约束等。每个表都有一个对应的.frm文件。
• .ibd文件：这是InnoDB存储引擎的数据文件，包含表的实际数据和索引。每个InnoDB表都有一个对应的.ibd文件。在MySQL 5.7之前，每个InnoDB表通常都有一个单独的.ibd文件。

该文件所在位置，在my.conf中配置的 datadir 目录的所在库下，例如我配置的是/home/wenda/mysql-data,那么库名为test，.ibd与.frm文件所在目录就是 /home/wenda/mysql-data/test。

3 恢复

3.1 恢复步骤

1. 创建新的数据库实例，创建数据库，数据库名与字符集与原库保持一致。
2. 创建表，表名、表结构与原库一致（作者使用navicat导出的原库表结构创建的）
3. 通过 .idb 文件批量恢复表数据信息
   1. 新库的表结构与表空间脱离
   2. 将需要恢复的 .idb 文件替换到新数据库实例的data目录的数据库下

3.2 详细步骤

如果使用工具已经在新的数据库示例上创建好库表结构，请直接看3.2.3即可。

3.2.1 安装mysqlfrm工具

# 下载工具
wget https://cdn.mysql.com/archives/mysql-utilities/mysql-utilities-1.6.5.tar.gz
# 解压
tar -xvf mysql-utilities-1.6.5.tar.gz
cd mysql-utilities-1.6.5/
# 安装(需要Python环境，python2.6及以上)
python ./setup.py build
python ./setup.py install
# 检查是否安装成功
mysqlfrm --version

3.2.2 使用mysqlfrm获取表结构创建语句

mysqlfrm 有–basedir模式以及–server 模式，推荐–server，能够还原最为准确的信息
使用方式（需要启动原mysql）

1. 执行如下命令

# mysqlfrm –server=用户名:密码@数据库地址:端口 需恢复的frm文件目录 –diagnostic > 保存SQL文件名.sql
mysqlfrm --server=root:password@x.x.x.x:3306 /xx/mysql/data/test/*.frm --diagnostic > test_frm.sql

注：mysqlfrm仅仅显示整个数据库的表结构创建语句，可以显示在控制台也可以输出到文件，不过需要手动执行这些表的创建SQL语句

2. 在新的数据库实例上创建数据库

CREATE database test;

3. 导入表结构

mysql -uroot -ppassword test < test_frm.sql

3.2.3 通过 .ibd 文件批量恢复数据

1. 单独创建个目录，用于存放临时的SQL文件

mkdir /home/mysql_tmp
cd /home/mysql_tmp

2. 表结构与表空间脱离，生产脱离后的SQL

mysql -uroot -p -h x.x.x.x -P 3306 -e " SELECT concat('alter table ', table_name, ' discard tablespace;') FROM information_schema.tables WHERE table_schema = 'test';" > /home/mysql_tmp/test.sql

3. 将SQL文件中没用的内容删除

sed '/^c/d' /home/mysql_tmp/test.sql > /home/mysql_tmp/test_new.sql

4. 将SQL文件导入新库

mysql -uroot -p -h x.x.x.x -P 3306 test < test_new.sql

5. 将需要恢复的 .idb 文件替换到新库 在my.conf中配置的 datadir/ 库名 目录下

cp /xx/mysql/test/*.ibd  /xx/mysql-new/test/

6. 修改文件权限

chown -R mysql. /xx/mysql-new/test/

7. 导入表空间

mysql -uroot -p -h x.x.x.x -P 3306 -e " SELECT concat('alter table ', table_name, ' import tablespace;') FROM information_schema.tables WHERE table_schema = 'test';" > /home/mysql_tmp/test/test_import.sql

8. 清除没用的数据

sed '/^c/d' /home/mysql_tmp/test/test_import.sql > /home/mysql_tmp/test/test_import_new.sql

9. 导入SQL文件

mysql -uroot -p -h x.x.x.x -P 3306 test < test_import_new.sql

4 总结

重要的数据一定要采取各种容灾措施，增加备份策略，恢复数据太痛苦。。。
原文链接：https://dycloudnavite.love/2023/01/13/%E3%80%90mysql%E6%95%B0%E6%8D%AE%E6%8D%9F%E5%9D%8F%EF%BC%8C%E9%80%9A%E8%BF%87ibd%E5%92%8Cfrm%E6%96%87%E4%BB%B6%E6%89%B9%E9%87%8F%E6%81%A2%E5%A4%8D%E6%95%B0%E6%8D%AE%E5%BA%93%E6%95%B0%E6%8D%AE%E3%80%91%20(1)/index.html?_sw-precache=f504f9c79df2ead4fdd23866bed81da9