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

• GreatSQL社区原创内容未经授权不得随意使用，转载请联系小编并注明来源。
  
• GreatSQL是MySQL的国产分支版本，使用上与MySQL一致。
  
• 作者：xryz
  
• 文章来源：社区投稿
  

使用方法

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

1. mysql> create table test1 (id int auto_increment primary key,name varchar(20));
2. Query OK, 0 rows affected (0.05 sec)
4. mysql> insert into test1 (name) values ('张三'),('李四'),('王二');
5. Query OK, 3 rows affected (0.01 sec)
6. Records: 3  Duplicates: 0  Warnings: 0
8. mysql> select * from test1;
9. +----+--------+
10. | id | name   |
11. +----+--------+
12. |  1 | 张三   |
13. |  2 | 李四   |
14. |  3 | 王二   |
15. +----+--------+
16. 3 rows in set (0.00 sec)

复制代码

2、创建目标表test2：

1. mysql> create table test2 like test1;
2. Query OK, 0 rows affected (0.10 sec)
3. 查看数据目录里面的ibd文件（test2.ibd、test1.ibd）：
4. -rw-r-----. 1 * * 114688 Nov  2 16:20 test1.ibd
5. -rw-r-----. 1 * * 114688 Nov  2 16:23 test2.ibd

复制代码

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

1. mysql> alter table test2 discard tablespace;   
2. Query OK, 0 rows affected (0.02 sec)
4. 查看ibd文件情况，发现test2的ibd文件已经被删除
6. -rw-r----- 1 * * 114688 Nov  2 16:20 test1.ibd

复制代码

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

1. mysql> flush table test1 for export;
2. Query OK, 0 rows affected (0.00 sec)
4. 生成了一个test1.cfg的cfg文件
6. -rw-r----- 1 * *    655 Nov  2 16:25 test1.cfg
7. -rw-r----- 1 * * 114688 Nov  2 16:20 test1.ibd

复制代码

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

1. cp test1.cfg test2.cfg
2. cp test1.ibd test2.ibd
3. chown -R mysql.mysql test2.*

复制代码

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

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

复制代码

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

1. mysql> unlock tables;
2. Query OK, 0 rows affected (0.00 sec)
4. 并用alter table的方法为目标表test2导入这个ibd文件：
6. mysql> alter table test2 import tablespace;
7. Query OK, 0 rows affected (0.03 sec)
8. 1 row in set (0.00 sec)

复制代码

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

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

复制代码

物理复制方法介绍

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

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

复制代码

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

alter table for export语法介绍：

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

注意：

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

Enjoy GreatSQL
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！