mysql通过frm和ibd恢复Innodb引擎数据

环境说明

操作系统：centos7

mysql版本：5.7.38

innodb：配置了独立表空间

mysql数据文件：xxx.frm 与 xxx.ibd, 其中frm存储的表结构，idb存储的是数据

恢复步骤

1.查看异常的mysql版本，新搭建相同版本的mysql服务

确定版本为5.7.38

[root@VM-32-13-centos ~]# /usr/sbin/mysqld  -version
2023-01-09T01:51:10.026925Z 0 [ERROR] --verbose is for use with --help; did you mean --log-error-verbosity?
2023-01-09T01:51:10.026964Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2023-01-09T01:51:10.030606Z 0 [Note] /usr/sbin/mysqld (mysqld 5.7.38) starting as process 2662467 ...
2023-01-09T01:51:10.043509Z 0 [ERROR] Fatal error: Please read "Security" section of the manual to find out how to run mysqld as root!

2023-01-09T01:51:10.043628Z 0 [ERROR] Aborting

2023-01-09T01:51:10.044509Z 0 [Note] Binlog end
2023-01-09T01:51:10.045121Z 0 [Note] /usr/sbin/mysqld: Shutdown complete

2. 通过frm恢复表结构

安装依赖
yum install mysql-utilities

2.1 获取表结构

开始恢复tmp_pv表的表结构
[root@node100]# mysqlfrm --diagnostic tmp_pv.frm
# WARNING: Cannot generate character set or collation names without the --server option.
# CAUTION: The diagnostic mode is a best-effort parse of the .frm file. As such, it may not identify all of the components of the table correctly. This is especially true for damaged files. It will also not read the default values for the columns and the resulting statement may not be syntactically correct.
# Reading .frm file for tmp_pv.frm:
# The .frm file is a TABLE.
# CREATE TABLE Statement:

CREATE TABLE `tmp_pv` (
  `md5id` varchar(765) DEFAULT NULL comment '游戏MD5加密', 
  `all_pv` int(11) NOT NULL comment '全部pv' 
) ENGINE=InnoDB;

#...done.

2.2 创建mysql表

mysql执行建表语句
CREATE TABLE `tmp_pv` (
  `md5id` varchar(765) DEFAULT NULL comment '游戏MD5加密', 
  `all_pv` int(11) NOT NULL comment '全部pv' 
) ENGINE=InnoDB;

3 通过ibd恢复表数据

3.1：对刚刚手动创建的tmp_pv表，(表空间卸载同时删除ibd文件)
mysql执行卸载表空间操作，这一步执行完，对应的tmp_pv.ibd也会删除掉
mysql> alter table tmp_pv discard tablespace;

3.2：把原始的tmp_pv.ibd复制过来

3.3：重新装载tmp_pv.ibd
mysql> alter table tmp_pv import tablespace;