Mysql数据库drop表不用跑路，表空间传输助你恢复数据

﹏ヽ暗。殇╰゛Y 2022-11-07 04:29 71阅读 0赞

今天给大家介绍一种，在Mysql数据库中，利用InnoDb的表空间传输功能，帮助你恢复drop的业务表。

# Mysql表空间传输限制 #

要使用Mysql数据库表空间传输功能，有2个限制  
1.Mysql数据库版本必须在5.6以上  
2.Mysql数据库必须开启innodb\_file\_per\_table

# 测试目标 #

测试库tmp中的test1表被drop了，需要用testdb库中的test1表记录进行恢复

# 目标库测试表drop #

登录数据库，drop掉tmp.test1业务表

[root@localhost] 16:14:12 [tmp]>drop table test1;
    Query OK, 0 rows affected (0.41 sec)

**在目标库创建test1空表**  
从源库testdb导出test1表定义语句，并查看原表记录

[root@localhost] 15:49:45 [testdb]>show create table testdb.test1\G;
    *************************** 1. row ***************************
           Table: test1
    Create Table: CREATE TABLE `test1` (
      `id` int(11) NOT NULL,
      `name1` char(10) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
      `name2` char(20) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
    1 row in set (0.00 sec)
    
    ERROR:
    No query specified
    
    [root@localhost] 15:49:54 [testdb]>select * from testdb.test1;
    +----+-------+-------+
    | id | name1 | name2 |
    +----+-------+-------+
    |  1 | test1 | test1 |
    |  2 | test  | test  |
    +----+-------+-------+
    2 rows in set (0.00 sec)

# 在目标库上创建空表tmp.test1 #

[root@localhost] 15:50:45 [tmp]>show create table tmp.test1\G;
    *************************** 1. row ***************************
           Table: test1
    Create Table: CREATE TABLE `test1` (
      `id` int(11) NOT NULL,
      `name1` char(10) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
      `name2` char(20) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
    1 row in set (0.00 sec)

discard删除目标库业务表ibd文件

ALTER TABLE tmp.test1 DISCARD TABLESPACE;

从源库中拷贝test1表的ibd文件到目标库

[mysql@mysql tmp]$ cp /data/mysql/data/3306/testdb/test1.ibd /data/mysql/data/3306/tmp/
    [mysql@mysql tmp]$ ls -l
    total 108
    -rw-r-----. 1 mysql mysql  8620 Sep 17 15:50 test1.frm
    -rw-r-----. 1 mysql mysql 98304 Sep 17 15:52 test1.ibd

**目标库导入ibd文件**  
在目标库中，导入ibd文件

[root@localhost] 15:51:34 [tmp]>ALTER TABLE tmp.test1 IMPORT TABLESPACE;
    Query OK, 0 rows affected, 1 warning (0.02 sec)
    
    [root@localhost] 15:53:11 [tmp]>SHOW WARNINGS;
    +---------+------+-------------------------------------------------------------------------------------------------------------------------------------------+
    | Level   | Code | Message                                                                                                                                   |
    +---------+------+-------------------------------------------------------------------------------------------------------------------------------------------+
    | Warning | 1810 | InnoDB: IO Read error: (2, No such file or directory) Error opening './tmp/test1.cfg', will attempt to import without schema verification |
    +---------+------+-------------------------------------------------------------------------------------------------------------------------------------------+
    1 row in set (0.00 sec)
    
    [root@localhost] 15:53:18 [tmp]>select * from tmp.test1;
    +----+-------+-------+
    | id | name1 | name2 |
    +----+-------+-------+
    |  1 | test1 | test1 |
    |  2 | test  | test  |
    +----+-------+-------+
    2 rows in set (0.00 sec)

到此，可以看到，已经将源库testdb.test1业务表的数据，恢复到目标库tmp.test1的业务表了。

**总结**  
在这里有一个步骤最为重要，就是导入ibd文件，可以想想，导入idb文件的时候，Mysql到底做了那些事情呢，总共做了3件事情  
1.导入的ibd文件，每个page都会做一次checksum，看看是否有坏页  
2.更新每个page的头部的表空间id和LSN号  
3.将page页设置成脏页，并刷新到磁盘

https://mp.weixin.qq.com/s/4iXbW445nX1yn1qG\_eafbA