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

落叶说Mysql数据库运维 2020-10-19

440

今天给大家介绍一种，在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页设置成脏页，并刷新到磁盘

mysql

文章转载自落叶说Mysql数据库运维，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。