通过.frm 和.ibd恢复mysql数据库

通过.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';

至此，数据库即可恢复。