物理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:01
mysql-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语句