MySQL恢复ibd数据文件
MySQL中基表在磁盘的存储方式为一个idb文件(MySQL8.0开始只有一个文件,MySQL5.7一个表会对应2个文件,一个frm文件表结构文件,一个ibd文件,存放表数据),当innodb存储引擎的表发生损坏,可以从最近的一个物理备份中拷贝ibd文件,恢复。非常好用的一个小技巧,具体操作如下:
模拟数据
测试环境为MySQL8.0,MySQL5.7也是类似操作。
mysql> SELECT version();
+-----------+
| version() |
+-----------+
| 8.0.19 |
+-----------+
1 row in set (0.00 sec
创建一个表t,插入3行数据
-- 重建测试SCHEMA t1
mysql> DROP DATABASE IF EXISTS t1;
mysql> CREATE DATABASE t1;
-- 重建测试表 t
mysql> USE t1;
mysql> DROP TABLE IF EXISTS t;
mysql> CREATE TABLE `t` (
`id` int NOT NULL,
`c` int DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
-- 插入测试数据
mysql> INSERT INTO t VALUES(1,1),(2,2),(3,3);
mysql> SELECT * FROM t;
+----+------+
| id | c |
+----+------+
| 1 | 1 |
| 2 | 2 |
| 3 | 3 |
+----+------+
3 rows in set (0.00 sec)
复制表
这里复制主要是为了说明可以利用t表的ibd文件直接还原出另一个t表,让MySQL识别这个ibd文件。如果是遇到同类问题也可以用这个方式快速还原出一个表,前提是需要直到这个表的表结构,以及有物理备份备份
-- 假设将表t的数据还原到表r上,先创建与表t相同表结构的r表
mysql> DROP TABLE IF EXISTS r;
mysql> CREATE TABLE r LIKE t;
-- 删掉r表的表空间
mysql> ALTER TABLE r DISCARD TABLESPACE;
-- 锁住表t,数据目录同时会生成t.cfg文件
mysql> FLUSH TABLE t for export;
-- [root@localhost t1]# ll
-- total 84
-- -rw-r-----. 1 mysql mysql 649 Apr 7 02:44 t.cfg
-- -rw-r-----. 1 mysql mysql 114688 Apr 7 02:40 t.ibd
--
-- 确保表t脏页全部刷到磁盘,方便对ibd文件进行复制,for export运行之后会生成.cfg的表元数据文件,该文件用于在导入期间进行元数据的相关验证
-- 如果是一个加密的表空间,for export的时候还会生成一个cfp文件,也需要进行复制
另开一个窗口,进入mysql的数据目录,复制t.cfg,t.ibd文件,修改权限
# cp t.cfg r.cfg
# cp t.ibd r.ibd
# chown -R mysql:mysql ./*
再回到mysql终端
-- 解锁表
mysql> unlock tables;
-- 导入r.ibd表空间文件
mysql> ALTER TABLE r IMPORT TABLESPACE; SHOW WARNINGS;
-- 查看数据释放恢复成功
mysql> SELECT * FROM r;
+----+------+
| id | c |
+----+------+
| 1 | 1 |
| 2 | 2 |
| 3 | 3 |
+----+------+
3 rows in set (0.00 sec)