mysql利⽤frm和idb⽂件恢复数据库
⼀、将data⽬录下的对应的数据库⽬录复制备份到另外⼀个地⽅
[root@orderer /]# mkdir /home/data_bak
[root@orderer /]# cp -r /home/mysql-5.7.26/data/hl_central_sms_deduction/ /home/data_bak/
[root@orderer /]#
⼆、连接MYSQL，删除原来的数据库，新建⼀个跟同名的数据库
mysql> create database hl_central_sms_deduction DEFAULT CHARSET utf8;
Query OK, 1 row affected (0.01 sec)
三、利⽤MySQL Utilitie⼯具提供的mysqlfrm命令，导出数据库表结构SQL⽂件
安装MySQL Utilitie⼯具，参考wwwblogs/sky-cheng/p/12218112.html
[root@orderer hl_central_sms_deduction]# mysqlfrm --diagnostic sms_deduction_log_20191201.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 t # Reading .frm file for sms_deduction_log_20191201.frm:
# The .frm file is a TABLE.
# CREATE TABLE Statement:
CREATE TABLE `sms_deduction_log_20191201` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`phone` varchar(60) DEFAULT NULL,
`epid` varchar(60) DEFAULT NULL,
`message` varchar(3000) DEFAULT NULL,
`subcode` varchar(60) DEFAULT NULL,
`channel_id` varchar(150) DEFAULT NULL,
`push_url` varchar(3000) DEFAULT NULL,
`db_ip` varchar(150) DEFAULT NULL,
`db_name` varchar(150) DEFAULT NULL,
`created` timestamp DEFAULT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`sms_len` int(11) DEFAULT NULL,
`stype` char(4) CHARACTER SET <UNKNOWN> NOT NULL,
`dtype` varchar(80) CHARACTER SET <UNKNOWN> NOT NULL,
`msg_template` varchar(3000) DEFAULT NULL,
`province` varchar(150) DEFAULT NULL,
`qxt_fast_num` int(1) DEFAULT NULL,
`link_id` varchar(150) DEFAULT NULL,
`report_code` varchar(150) DEFAULT NULL,
PRIMARY KEY `PRIMARY` (`id`),
KEY `pk_sms_deduction_log_20191201_phone` (`phone`,`epid`,`created`) USING BTREE
) ENGINE=InnoDB;
#...done.
将SQL语句粘贴到客户端执⾏，报错
`stype` char(4) CHARACTER SET <UNKNOWN> NOT NULL,
`dtype` varchar(80) CHARACTER SET <UNKNOWN> NOT NULL,
将这两句修改为
`stype` char(4)  NOT NULL,
`dtype` varchar(80) NOT NULL,
再执⾏上⾯的建表语句，成功。
此时，查看data⽬录下的hl_central_sms_deduction⽬录下多了两个⽂件
[root@orderer hl_central_sms_deduction]# ll
156
-rw-r----- 1 mysql mysql    611?  2014:54 db.opt
-rw-r----- 1 mysql mysql  378561?  2015:45 sms_deduction_log_20191201.frm
-rw-r----- 1 mysql mysql 1146881?  2015:45 sms_deduction_log_20191201.ibd
四、执⾏解除表空间绑定命令，对应的idb⽂件⾃动删除
mysql> use hl_central_sms_deduction;
mysql数据库损坏修复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
mysql> alter table sms_deduction_log_20191201 discard tablespace;
Query OK, 0 rows affected (0.04 sec)
mysql>
查看data⽬录下的hl_central_sms_deduction⽬录
[root@orderer hl_central_sms_deduction]# ll
44
-rw-r----- 1 mysql mysql    611?  2014:54 db.opt
-rw-r----- 1 mysql mysql 378561?  2015:45 sms_deduction_log_20191201.frm
[root@orderer hl_central_sms_deduction]#
此时，idb⽂件已⾃动删除，
五、我们将备份出来的ibd⽂件复制回去，再绑定表空间
[root@orderer hl_central_sms_deduction]# cp ../../../databak/hl_central_sms_deduction/sms_deduction_log_20191201.ibd .
修改⽂件所有者权限给mysql
[root@orderer hl_central_sms_deduction]# chown mysql:mysql sms_deduction_log_20191201.ibd
绑定表空间
mysql> alter table sms_deduction_log_20191201 import tablespace;
Query OK, 0 rows affected, 1 warning (1.82 sec)
mysql>
完毕后，查看表数据，已经恢复
mysql> select count(*) from sms_deduction_log_20191201;
+----------+
| count(*) |
+----------+
|    97746 |
+----------+
1 row in set (0.04 sec)
mysql>
六、可能遇到的问题
执⾏ALTER TABLE table_name DISCARD TABLESPACE；时
报错[Error Code: 1451, SQL State: 23000] Cannot delete or update a parent row: a foreign key constraint fails ()这是由于有外键关联
SET foreign_key_checks = 0; --先设置外键约束检查关闭
SET foreign_key_checks = 1; --都执⾏完alter操作后再开启外键约束检查，以保持表结构完整性
先关闭外键约束，执⾏删除操作，然后再开启外键约束。
执⾏ALTER TABLE table_name IMPORT TABLESPACE;
报错Error Code:1812. Tablespace is missing for table <table_name>
是因为copy的ibd⽂件没有赋权，需要chown mysql:mysql table_name.ibd --赋权限