通过.frm 和.ibd恢复mysql数据库
1、系统:CentOS Linux release 7.9.2009 (Core)
2、mysql 5.7.30 引擎：inndb
一、恢复步骤：
1.建立表结构
2.删除新建的表空间
3.拷贝.ibd数据文件覆盖新建的文件
4.导入表空间
PS：前提环境
innodb_file_per_table=1
innodb_force_recovery=1
参考 vim /etc/my.cnf
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html
[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
datadir=/var/lib/mysql
# datadir=/home/mysql
socket=/var/lib/mysql/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
innodb_file_per_table=1
# innodb_force_recovery=1
default-storage-engine=innodb
二、恢复表结构
1、下载安装包
[https://downloads.mysql.com/archives/get/p/30/file/mysql-utilities-1.6.5-1.el7.noarch.rpm](https://downloads.mysql.com/archives/get/p/30/file/mysql-utilities-1.6.5-1.el7.noarch.rpm)
[https://downloads.mysql.com/archives/get/p/29/file/mysql-connector-python-2.1.7-1.el7.x86_64.rpm](https://downloads.mysql.com/archives/get/p/29/file/mysql-connector-python-2.1.7-1.el7.x86_64.rpm)
rpm -ivh mysql-connector-python-2.1.7-1.el7.x86_64.rpm
rpm -ivh mysql-utilities-1.6.5-1.el7.noarch.rpm
【查看版本】mysqlfrm --version
mysqlfrm --diagnostic /mysql/data/database1
注：ROW_FORMAT = Dynamic
# 查看帮助
mysqlfrm --help
# 推荐，这样可以统一编码问题
mysqlfrm --server=root:pass@localhost:3306 /mysql/data/temp1/t1.frm \
/mysql/data/temp2/g1.frm --port=3310
三、具体操作
1、在前后加上
SET foreign_key_checks = 0; --先设置外键约束检查关闭
SET foreign_key_checks = 1; --开启外键约束检查，以保持表结构完整性
2、删除新建的表空间
单个执行删除表空间语句
ALTER TABLE table_name DISCARD TABLESPACE;
批量删除空间，执行以下语句。
SELECT CONCAT( 'ALTER TABLE ', table_name, ' DISCARD TABLESPACE;' ) FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'db_name';
3、导入表空间
单个执行导入表空间语句
ALTER TABLE table_name IMPORT TABLESPACE;
批量导入表空间
SELECT CONCAT('ALTER TABLE ', table_name, ' IMPORT TABLESPACE;') FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'db_name';
至此，数据库即可恢复。
