MySQL恢复ibd数据文件

骑猪看日落 2023-05-21 11:52 22阅读 0赞

### 文章目录 ###

*  MySQL恢复ibd数据文件
 *   *   *  模拟数据
         *  复制表
 *  参考文档

# 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)

# 参考文档 #

*  [https://dev.mysql.com/doc/refman/8.0/en/innodb-troubleshooting-datadict.html][https_dev.mysql.com_doc_refman_8.0_en_innodb-troubleshooting-datadict.html]
 *  [https://dev.mysql.com/doc/refman/8.0/en/innodb-table-import.html][https_dev.mysql.com_doc_refman_8.0_en_innodb-table-import.html]

[https_dev.mysql.com_doc_refman_8.0_en_innodb-troubleshooting-datadict.html]: https://dev.mysql.com/doc/refman/8.0/en/innodb-troubleshooting-datadict.html
[https_dev.mysql.com_doc_refman_8.0_en_innodb-table-import.html]: https://dev.mysql.com/doc/refman/8.0/en/innodb-table-import.html