MySQL如何快速恢复单表（物理方式）

GreatSQL社区

发布于 2023-02-22 10:29:44

1.6K0

发布于 2023-02-22 10:29:44

文章被收录于专栏：GreatSQL出品技术文章

* GreatSQL社区原创内容未经授权不得随意使用，转载请联系小编并注明来源。

使用方法

1、首先创建一个测试表test1，并插入几条数据：

mysql> create table test1 (id int auto_increment primary key,name varchar(20));
Query OK, 0 rows affected (0.05 sec)

mysql> insert into test1 (name) values ('张三'),('李四'),('王二');
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from test1;
+----+--------+
| id | name   |
+----+--------+
|  1 | 张三   |
|  2 | 李四   |
|  3 | 王二   |
+----+--------+
3 rows in set (0.00 sec)

2、创建目标表test2：

mysql> create table test2 like test1;
Query OK, 0 rows affected (0.10 sec)

查看数据目录里面的ibd文件（test2.ibd、test1.ibd）：

-rw-r-----. 1 * * 114688 Nov  2 16:20 test1.ibd
-rw-r-----. 1 * * 114688 Nov  2 16:23 test2.ibd

3、通过alter table discard的方法丢弃表test2的idb文件（为下一步复制test1的数据过来做准备）：

mysql> alter table test2 discard tablespace;   
Query OK, 0 rows affected (0.02 sec)

查看ibd文件情况，发现test2的ibd文件已经被删除

-rw-r----- 1 * * 114688 Nov  2 16:20 test1.ibd

4、执行下面的命令，生成一个test1的cfg文件，如下：

mysql> flush table test1 for export; 
Query OK, 0 rows affected (0.00 sec)

生成了一个test1.cfg的cfg文件

-rw-r----- 1 * *    655 Nov  2 16:25 test1.cfg
-rw-r----- 1 * * 114688 Nov  2 16:20 test1.ibd

5、拷贝源表test1的cfg文件和ibd文件到目标表test2，并修改文件权限：

cp test1.cfg test2.cfg
cp test1.ibd test2.ibd
chown -R mysql.mysql test2.*

6、复制完成之后，执行select命令发现出现以下报错：

mysql> select * from test2;
ERROR 1100 (HY000): Table 'test2' was not locked with LOCK TABLES

7、执行unlock tables，释放源表的test1.cfg文件，然后导入ibd文件：

mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)

并用alter table的方法为目标表test2导入这个ibd文件：

mysql> alter table test2 import tablespace; 
Query OK, 0 rows affected (0.03 sec)
1 row in set (0.00 sec)

8、再次执行select，发现数据已经导入：

mysql> select * from test2;
+----+--------+
| id | name   |
+----+--------+
|  1 | 张三   |
|  2 | 李四   |
|  3 | 王二   |
+----+--------+
3 rows in set (0.00 sec)

物理复制方法介绍

上述单表物理复制的方法，核心在于cp命令，因为是通过物理拷贝，所以如果复制的表非常大，那么通过物理拷贝，就会比逻辑上的SQL写入快很多，比如insert into select语句。

简单总结一下上述物理复制过程：

1、create table like语法创建一个相同表结构的空的目标表
2、目标表执行alter table discard，丢弃ibd文件
3、源表执行flush table for export语法，生成.cfg文件，并锁表
4、使用cp命令复制源表cfg文件和ibd文件为目标表
5、unlock tables 释放源表的cfg文件和锁
6、alter table import命令导入目标表的ibd数据文件。

flush table for export语法介绍：

1、这个命令是为了将内存中关于这个表的数据刷新到磁盘上，确保数据都能被binlog所记录；
2、这个操作需要flush table或者reload权限；
3、这个操作会持有当前表的共享MDL锁，阻止其他会话修改表结构，在FOR EXPORT操作完成时不会释放先前获取的MDL锁，需要手工释放
4、InnoDB会在与该表相同的数据库目录中生成一个名为table_name.cfg的文件
5、处理完表复制后，需要使用UNLOCK tables释放源表的MDL锁或者断开连接。

注意：

因为flush table for export锁表，所以这种方法更适合在从库上停掉复制关系，然后执行这个表复制的操作。如果有业务操作当前的源表，需要谨慎考虑。

Enjoy GreatSQL :)

《零基础学习MySQL》视频课程

戳此小程序即可直达B站

https://www.bilibili.com/video/BV1Da411W7Va?spm_id_from=333.999.0.0&vd_source=ae1951b64ea7b9e6ba11f1d0bbcff0e4

文章推荐：

关于 GreatSQL

GreatSQL是由万里数据库维护的MySQL分支，专注于提升MGR可靠性及性能，支持InnoDB并行查询特性，是适用于金融级应用的MySQL分支版本。

GreatSQL社区官网: https://greatsql.cn/

Gitee: https://gitee.com/GreatSQL/GreatSQL

GitHub: https://github.com/GreatSQL/GreatSQL

Bilibili：

https://space.bilibili.com/1363850082/video

本文参与腾讯云自媒体同步曝光计划，分享自微信公众号。

原始发表：2022-12-10，如有侵权请联系 cloudcommunity@tencent.com 删除

https

本文分享自 GreatSQL社区微信公众号，前往查看

如有侵权，请联系 cloudcommunity@tencent.com 删除。

本文参与腾讯云自媒体同步曝光计划，欢迎热爱写作的你一起参与！

登录后参与评论

0 条评论

热度