物理ibd文件恢复数据:（企业案例）
当前操作环境为 MySQL-5.7.20版本
损坏库：olda库下的student无法使用，但是ibd文件还存在(student表有20万条数据)
新库：创建一个一模一样结构的表，拷贝损坏表的ibd文件到新库的指定库路径下，重新导入表空间数据。
mysql-01节点：（损坏库，找到损坏库的表结构语句）
#复制损坏表的表格式语句，到新的MySQL的指定库下创建一样格式的表（如果损坏到无法查看，那就开发找损坏表格式）
mysql> show create table student;
+---------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+---------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| student | CREATE TABLE `student` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '学生学号',
`name` varchar(50) NOT NULL COMMENT '学生姓名',
`cardid` char(18) NOT NULL,
`age` tinyint(3) unsigned NOT NULL DEFAULT '18' COMMENT '学生年龄',
`gender` enum('男','女') NOT NULL DEFAULT '男' COMMENT '学生性别',
`birthday` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '学生入学时间',
`telm` char(11) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `cardid` (`cardid`),
UNIQUE KEY `telnum` (`telm`)
) ENGINE=InnoDB AUTO_INCREMENT=200001 DEFAULT CHARSET=utf8 |
+---------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+mysql-02节点：（新库，创建与损坏表一样表结构的表）
mysql> create database olda;
Query OK, 1 row affected (0.00 sec)
mysql> use olda;
Database changed
mysql> CREATE TABLE `student` (
-> `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '学生学号',
-> `name` varchar(50) NOT NULL COMMENT '学生姓名',
-> `cardid` char(18) NOT NULL,
-> `age` tinyint(3) unsigned NOT NULL DEFAULT '18' COMMENT '学生年龄',
-> `gender` enum('男','女') NOT NULL DEFAULT '男' COMMENT '学生性别',
-> `birthday` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '学生入学时间',
-> `telm` char(11) DEFAULT NULL,
-> PRIMARY KEY (`id`),
-> UNIQUE KEY `cardid` (`cardid`),
-> UNIQUE KEY `telnum` (`telm`)
-> ) ENGINE=InnoDB AUTO_INCREMENT=200001 DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.11 sec)mysql-02节点：（新库，移除新库的表空间数据ibd文件）
mysql> alter table olda.student discard tablespace;
Query OK, 0 rows affected (0.01 sec)mysql-01节点：（损坏库，拷贝ibd文件到新库的指定库下）
[root@mysql-01 olda]# scp /usr/local/mysql/mydata/olda/student.ibd 172.16.1.30:/usr/local/mysql/mydata/olda/
root@172.16.1.30's password:
student.ibd 100% 52MB 52.0MB/s 00:01mysql-02节点：（新库，重新导入student表空间数据ibd文件）
#授权文件给mysql用户
[root@mysql-02 ~]# chown -R mysql.mysql /usr/local/mysql/mydata/olda/student.ibd
#重新导入损坏库的ibd文件对应的表空间数据
mysql> alter table olda.student import tablespace;
Query OK, 0 rows affected, 1 warning (2.19 sec)查看数据：（mysql-02节点）
mysql> select * from olda.student limit 5;
+----+--------+--------------------+-----+--------+---------------------+-------------+
| id | name | cardid | age | gender | birthday | telm |
+----+--------+--------------------+-----+--------+---------------------+-------------+
| 2 | olda_0 | 558028812249598585 | 26 | 女 | 2019-03-12 13:58:20 | 97124093860 |
| 3 | olda_1 | 797671815953624647 | 62 | 男 | 2019-03-12 13:58:20 | 55671735114 |
| 4 | olda_2 | 917621002201308892 | 99 | 女 | 2019-03-12 13:58:20 | 01255766912 |
| 5 | olda_3 | 103233400938302068 | 7 | 男 | 2019-03-12 13:58:20 | 98291102596 |
| 6 | olda_4 | 864538584193844687 | 4 | 女 | 2019-03-12 13:58:20 | 16790101875 |
+----+--------+--------------------+-----+--------+---------------------+-------------+
5 rows in set (0.00 sec)如果表数量过多，用concat字符串拼接成SQL语句