一种快速复制单表的方法
作者:快盘下载 人气://
一种快速复制mysql单表的方法
//
01
复制MySQL单表的方法
作为MySQL DBA，在日常运维过程中，经常需要对某张表进行备份恢复。单个表常用的数据备份方法有下面几种：
1、mysqldump或者mysqlpump原生工具，通常情况下，可以通过--database和--tables选项来过滤想要的表。然后通过mysql命令行或者source 指令来恢复表结构。
2、通过select into outfile xxx 的方法来导出表的数据，然后使用load data的方式将表恢复到另外一个表里面。
3、insert into tbl_B select * from tbl_A的方法
今天，我们来看另外一种物理复制的方法。
02
利用物理复制的方法复制一张表的数据
下面的例子，演示从表src到表dst的数据复制过程（基于MySQL 8.0.24）
1、首先我们创建一个表src，并插入几条数据：
mysql> create table src (id int auto_increment primary key,name varchar(20));
Query OK, 0 rows affected (0.05 sec)
mysql> insert into src (name) values ('zhangsan'),('lisi'),('wangwu');
Query OK, 3 rows affected (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select * from src;
+----+----------+
| id | name |
+----+----------+
| 1 | zhangsan |
| 2 | lisi |
| 3 | wangwu |
+----+----------+
3 rows in set (0.00 sec)
2、然后我们创建目标表dst：
mysql> create table dst like src; Query OK, 0 rows affected (0.10 sec)
查看数据目录里面的数据（dst.ibd、src.ibd）：
root@4a1214a30f43:/var/lib/mysql/test# ls -l total 816 -rw-r----- 1 mysql mysql 114688 Jun 7 15:17 dst.ibd -rw-r----- 1 mysql mysql 114688 Jun 7 15:16 src.ibd
3、我们通过alter table discard的方法丢弃表dst的idb文件（这一步的目的是为了后面复制src的数据过来）：
mysql> alter table dst discard tablespace; Query OK, 0 rows affected (0.02 sec) 查看ibd文件情况，发现dst的ibd文件已经被删除 root@4a1214a30f43:/var/lib/mysql/test# ls -l total 736 -rw-r----- 1 mysql mysql 114688 Jun 7 15:16 src.ibd
4、执行下面的命令，生成一个src的cfg文件，如下：
mysql> flush table src for export; Query OK, 0 rows affected (0.00 sec) 生成了一个src.cfg的cfg文件 root@4a1214a30f43:/var/lib/mysql/test# ls -l total 740 -rw-r----- 1 mysql mysql 655 Jun 7 15:18 src.cfg -rw-r----- 1 mysql mysql 114688 Jun 7 15:16 src.ibd
5、然后我们拷贝源表src的cfg文件和ibd文件到目标表dst，命令如下：
cp src.cfg dst.cfg cp src.ibd dst.ibd
6、复制完成之后，其实这个目标表dst还是不能查询的，会报错，报错的结果如下：
mysql> select * from dst; ERROR 1100 (HY000): Table 'dst' was not locked with LOCK TABLES
7、然后我们执行unlock tables，释放源表的src.cfg文件，
mysql> unlock tables; Query OK, 0 rows affected (0.00 sec)
并用alter table的方法为目标表dst导入这个ibd文件：
mysql> alter table dst import tablespace; ERROR 1812 (HY000): Tablespace is missing for table `test`.`dst`. mysql> 报错不难看出来，找不到文件，权限问题，修复方法如下： root@4a1214a30f43:/var/lib/mysql/test# ls -l total 820 -rw-r----- 1 root root 655 Jun 7 15:19 dst.cfg -rw-r----- 1 root root 114688 Jun 7 15:19 dst.ibd -rw-r----- 1 mysql mysql 114688 Jun 7 15:16 src.ibd root@4a1214a30f43:/var/lib/mysql/test# chown mysql.mysql dst.* root@4a1214a30f43:/var/lib/mysql/test# ls -l total 852 -rw-r----- 1 mysql mysql 655 Jun 7 15:19 dst.cfg -rw-r----- 1 mysql mysql 114688 Jun 7 15:21 dst.ibd -rw-r----- 1 mysql mysql 114688 Jun 7 15:16 src.ibd 修复后重新导入ibd文件，成功： mysql> alter table dst import tablespace; Query OK, 0 rows affected (0.06 sec)
8、查询最终的结果，发现数据已经导入：
mysql> select * from dst; +----+----------+ | id | name | +----+----------+ | 1 | zhangsan | | 2 | lisi | | 3 | wangwu | +----+----------+ 3 rows in set (0.00 sec)
03
物理复制方法介绍
上面的操作，过程看着有点复杂，这里简单解释一下，你可能会觉得复制一张表干嘛这么复杂？直接insert into select 语句，执行就完事儿了，但是这种语句本身是逻辑的SQL语句，如果要复制的表，本身比较大，这条SQL可能执行的时间很长。
上述物理复制的方法，核心在于中间的cp命令，它的本质是物理拷贝，如果某个表非常大，那么这个物理拷贝，就比逻辑上的SQL写入快很多。
关于上述物理复制过程，我简单总结一下：
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的文件，.cfg文件包含了当前表的元信息，如果你使用cat命令，可以看到：
root@4a1214a30f43:/var/lib/mysql/test# cat src.cfg
4a1214a30f43 test/src@!@!id
Pname
DB_ROW_ID
DB_TRX_ID
DB_ROLL_PTR
CLUST_IND_SDI+y-id
DB_TRX_ID
DB_ROLL_PTRcompressed_lenuncompressed_lendataPRIMARYid
DB_TRX_ID
DB_ROLL_PTRname
5、处理完表复制后，需要使用UNLOCK tables释放源表的MDL锁。
注意：
因为存在锁表的情况，所以这种方法更适合在从库上停掉复制关系，然后执行这个表复制的操作。如果有业务操作当前的源表，请慎用该方法。
加载全部内容