对于单个的ibd文件,大部分情况下可以通过DISCARD TABLESPACE和IMPORT TABLESPACE方式进行恢复
mysql> use test;
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> CREATE TABLE `t1` (
-> `id` int DEFAULT NULL
-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
Query OK, 0 rows affected (0.01 sec)
mysql> insert into t1 values(1);
Query OK, 1 row affected (0.02 sec)
mysql> insert into t1 values(2);
Query OK, 1 row affected (0.01 sec)
mysql> insert into t1 values(3);
Query OK, 1 row affected (0.00 sec)
关闭mysql服务，备份mysql中的t1.ibd文件
[root@xifenfei ~]# service mysql stop
Shutting down MySQL..... SUCCESS!
[root@xifenfei test]# cp t1.ibd t1_bak
启动mysql服务,并删除并创建新的t1表(表结构相同)
[root@xifenfei test]# service mysql start
Starting MySQL..................... SUCCESS!
[root@xifenfei test]# mysql -uroot -poracle test
mysql: [Warning] Using a password on the command line interface can be insecure.
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.31 MySQL Community Server - GPL
Copyright (c) 2000, 2022, Oracle and/or its affiliates.
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> drop table t1;
Query OK, 0 rows affected (0.20 sec)
mysql>
mysql> CREATE TABLE `t1` (
-> `id` int DEFAULT NULL
-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
Query OK, 0 rows affected (0.01 sec)
DISCARD TABLESPACE操作
mysql> ALTER TABLE t1 DISCARD TABLESPACE;
Query OK, 0 rows affected (0.01 sec)
把备份的t1.ibd还原回去并修改权限
[root@xifenfei test]# mv t1_bak t1.ibd
[root@xifenfei test]# ls -ltr
total 112
-rw-r-----. 1 root root 114688 Dec 18 17:24 t1.ibd
[root@xifenfei test]# chown mysql.mysql t1.ibd
IMPORT TABLESPACE并验证数据
mysql> ALTER TABLE t1 IMPORT TABLESPACE;
Query OK, 0 rows affected, 1 warning (0.24 sec)
mysql> select * from t1;
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
+------+
3 rows in set (0.00 sec)
在恢复途中如果遇到表定义不对,或者ibd文件损坏，或者版本不匹配等各种情况,可能在IMPORT TABLESPACE的时候可能出现类似ERROR 1808 (HY000): Schema mismatch (Clustered index validation failed. Because the .cfg file is missing, table definition of the IBD file could be different. Or the data file itself is already corrupted.)错误
mysql> alter table `t1` import tablespace;
ERROR 1808 (HY000): Schema mismatch (Clustered index validation failed.
Because the .cfg file is missing, table definition of the IBD file could be different.
Or the data file itself is already corrupted.)
如果出现此类错误,无法直接通过该方法进行解决,参考frm和ibd文件数据库恢复,使用专业恢复工具进行处理
