【MySQL 5.7】通过frm和ibd文件恢复数据库
由于某种原因，源数据库（版本MySQL 5.7.19，操作系统CentOS 7）无法启动或者数据表无法访问。
实验：将源库中的某张表，在新的数据库中（版本MySQL 5.7.23，操作系统CentOS 7 ）进行恢复。
首先明确一下，既然拥有了ibd文件，那么说明数据库采用的是独立表空间，即：
|
1 2 3 4 5 6 7 8 9 |
mysql> show variables like 'innodb_file_per_table'; +-----------------------+-------+ | Variable_name | Value | +-----------------------+-------+ | innodb_file_per_table | ON | +-----------------------+-------+ 1 row in set (0.02 sec) mysql> |
第一步：需要得到需要恢复表的表结构。如果有该表的建表脚本，那么跳过此步，直接创建即可。
假设，要恢复的表非常多，并且，没有准确的建表脚本，那么可以采用mysql的工具，在frm文件中挖掘表结构，工具安装如下：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 |
# 下载 mysql-utilities-1.6.5-1 [root@gzh soft]# wget https://cdn.mysql.com/archives/mysql-utilities/mysql-utilities-1.6.5-1.el7.noarch.rpm --2019-12-04 18:12:36-- https://cdn.mysql.com/archives/mysql-utilities/mysql-utilities-1.6.5-1.el7.noarch.rpm Resolving cdn.mysql.com (cdn.mysql.com)... 104.86.185.42 Connecting to cdn.mysql.com (cdn.mysql.com)|104.86.185.42|:443... connected. HTTP request sent, awaiting response... 200 OK Length: 856440 (836K) [application/x-redhat-package-manager] Saving to: ‘mysql-utilities-1.6.5-1.el7.noarch.rpm’ 100%[==============================================================================================================================>] 856,440 854KB/s in 1.0s 2019-12-04 18:12:37 (854 KB/s) - ‘mysql-utilities-1.6.5-1.el7.noarch.rpm’ saved [856440/856440] [root@gzh soft]# # 下载 mysql-connector-python-2.1.8-1 [root@gzh soft]# wget https://cdn.mysql.com//Downloads/Connector-Python/mysql-connector-python-2.1.8-1.el7.x86_64.rpm --2019-12-04 18:24:38-- https://cdn.mysql.com//Downloads/Connector-Python/mysql-connector-python-2.1.8-1.el7.x86_64.rpm Resolving cdn.mysql.com (cdn.mysql.com)... 104.86.185.42 Connecting to cdn.mysql.com (cdn.mysql.com)|104.86.185.42|:443... connected. HTTP request sent, awaiting response... 200 OK Length: 295968 (289K) [application/x-redhat-package-manager] Saving to: ‘mysql-connector-python-2.1.8-1.el7.x86_64.rpm’ 100%[==============================================================================================================================>] 295,968 203KB/s in 1.4s 2019-12-04 18:24:40 (203 KB/s) - ‘mysql-connector-python-2.1.8-1.el7.x86_64.rpm’ saved [295968/295968] [root@gzh soft]# ### 安装工具包及python连接mysql环境，注意版本 root@gzh soft]# rpm -ivh mysql-connector-python-2.1.8-1.el7.x86_64.rpm warning: mysql-connector-python-2.1.8-1.el7.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY Preparing... ################################# [100%] Updating / installing... 1:mysql-connector-python-2.1.8-1.el################################# [100%] [root@gzh soft]# [root@gzh soft]# [root@gzh soft]# rpm -ivh mysql-utilities-1.6.5-1.el7.noarch.rpm warning: mysql-utilities-1.6.5-1.el7.noarch.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY Preparing... ################################# [100%] Updating / installing... 1:mysql-utilities-1.6.5-1.el7 ################################# [100%] [root@gzh soft]# |
第二步：生成建表脚本
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 |
# 首先在目标库创建一个目录，目录的名字最好与数据库名字相同，测试库为dm，然后再将frm和ibd文件拷贝至新建的目录下 [root@gzh dm]# ll total 492 -rw-r----- 1 root root 8774 Dec 4 18:11 updateappnumber.frm -rw-r----- 1 root root 491520 Dec 4 18:11 updateappnumber.ibd [root@gzh dm]# # 利用 mysqlfrm 工具挖掘frm文件，获取建表脚本 [root@gzh dm]# mysqlfrm --diagnostic /soft/dm/updateappnumber.frm >/soft/dm/updateappnumber.sql [root@gzh dm]# [root@gzh dm]# ll total 496 -rw-r----- 1 root root 8774 Dec 4 18:11 updateappnumber.frm -rw-r----- 1 root root 491520 Dec 4 18:11 updateappnumber.ibd -rw-r--r-- 1 root root 877 Dec 4 18:29 updateappnumber.sql [root@gzh dm]# ## 通过查看建表脚本，大家应该能猜到为啥frm和ibd这两个文件要放在dm目录下，因为生成出来的脚本中，会包含数据库的名字，默认会使用frm所咋的目录名字 [root@gzh dm]# cat updateappnumber.sql # 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 /soft/dm/updateappnumber.frm: # The .frm file is a TABLE. # CREATE TABLE Statement: CREATE TABLE `dm`.`updateappnumber` ( `ID` int(11) NOT NULL AUTO_INCREMENT, `ApplicationNumber` varchar(39) NOT NULL, `ApplicationType` char(3) DEFAULT NULL, `BatchNo` varchar(60) DEFAULT NULL, `InsertDate` date DEFAULT NULL, `state` int(1) DEFAULT NULL, PRIMARY KEY `PRIMARY` (`ID`), KEY `idx_appnum` (`ApplicationNumber`) ) ENGINE=InnoDB; #...done. [root@gzh dm]# |
第三步：创建表及进行表空间卸载
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 |
## 创建数据库dm mysql> CREATE DATABASE `dm` CHARACTER SET 'utf8'; mysql> ## 最好切换到dm库，应用建表脚本 mysql> use dm; Database changed mysql> mysql> source /soft/dm/updateappnumber.sql Query OK, 0 rows affected (0.02 sec) mysql> show tables; +-----------------+ | Tables_in_dm | +-----------------+ | updateappnumber | +-----------------+ 1 row in set (0.00 sec) mysql> mysql> select * from updateappnumber; Empty set (0.04 sec) mysql> ## 对该表进行表空间卸载 mysql> alter table updateappnumber discard tablespace; Query OK, 0 rows affected (0.01 sec) mysql> mysql> select * from updateappnumber; ERROR 1814 (HY000): Tablespace has been discarded for table 'updateappnumber' mysql> mysql> exit Bye [root@gzh dm]# |
第四步：停服务，将ibd文件拷贝至新建的数据库所在目录中
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 |
## shutdown MySQL服务 [root@gzh dm]# service mysql stop Shutting down MySQL.. SUCCESS! [root@gzh dm]# ## 查看目标dm库目录，发现里面并没有ibd文件，因为刚刚创建的updateappnumber表并未写入数据，所以还未产生ibd文件 [root@gzh dm]# ll /data/db/mysql/2587/dm/ total 16 -rw-r----- 1 mysql mysql 61 Dec 4 18:28 db.opt -rw-r----- 1 mysql mysql 8774 Dec 4 18:30 updateappnumber.frm [root@gzh dm]# ## 将源库的ibd文件mv或cp到目标库的dm目录中 [root@gzh dm]# mv /soft/dm/updateappnumber.ibd /data/db/mysql/2587/dm/. [root@gzh dm]# [root@gzh dm]# ll /data/db/mysql/2587/dm/ total 496 -rw-r----- 1 mysql mysql 61 Dec 4 18:28 db.opt -rw-r----- 1 mysql mysql 8774 Dec 4 18:30 updateappnumber.frm -rw-r----- 1 root root 491520 Dec 4 18:11 updateappnumber.ibd [root@gzh dm]# ## 这一步非常重要，一定要看一下ibd文件的属主和属组，必须要与frm相同 [root@gzh dm]# chown mysql.mysql /data/db/mysql/2587/dm/updateappnumber.ibd [root@gzh dm]# [root@gzh dm]# ll /data/db/mysql/2587/dm/ total 496 -rw-r----- 1 mysql mysql 61 Dec 4 18:28 db.opt -rw-r----- 1 mysql mysql 8774 Dec 4 18:30 updateappnumber.frm -rw-r----- 1 mysql mysql 491520 Dec 4 18:11 updateappnumber.ibd [root@gzh dm]# |
第五步：启动数据库，装载表空间，完成恢复
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 |
[root@gzh dm]# mysql -u root -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 3 Server version: 5.7.23 MySQL Community Server (GPL) Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> mysql> use dm; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed, 1 warning mysql> ## 装载表空间 mysql> alter table updateappnumber import tablespace; Query OK, 0 rows affected, 1 warning (0.02 sec) mysql> mysql> select count(*) from updateappnumber; +----------+ | count(*) | +----------+ | 4184 | +----------+ 1 row in set (0.00 sec) mysql> |
总结：
整体恢复过程相对简单（虽然我写的比较啰嗦），总结一下几步：
1：确保有frm和ibd文件
2：最好有该表的建表脚本，如果实在没有，就利用工具挖掘frm文件获取建表脚本
3：建库建表
4：卸载表空间
5：关闭数据库，拷贝ibd文件。（理论上先进行锁表操作，再拷贝也可以，不用重启，声明：本人并没有做过测试）
6：启动数据库，装载表空间