0%

MySQL 快速复制表

发表于 更新于 分类于 阅读次数： Valine：
本文字数： 4k 阅读时长 ≈ 4 分钟

今天这篇文章来说一下怎么在两张表中拷贝数据。如果可以控制对源表的扫描行数和加锁范围很小的话，我们简单地使用 insert … select 语句即可实现。当然，为了避免对源表加读锁，还有几种更稳妥的方法。为了便于说明，我还是先创建一个表 db1.t，并插入 1000 行数据，同时创建一个相同结构的表 db2.t。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
create database db1;
use db1;
create table t(id int primary key, a int, b int, index(a))engine=innodb;
delimiter ;;
 create procedure idata()
 begin
 declare i int;
 set i=1;
 while(i<=1000)do
 insert into t values(i,i,i);
 set i=i+1;
 end while;
 end;;
delimiter ;
call idata();

create database db2;
create table db2.t like db1.t;

假设，我们要把 db1.t 里面 a>900 的数据行导出来，插入到 db2.t 中。

1. mysqldump 方法

一种方法是，使用 mysqldump 命令将数据导出成一组 INSERT 语句。你可以使用下面的命令：

1
mysqldump -h$host -P$port -u$user -p --add-locks=0 --no-create-info --single-transaction  --set-gtid-purged=OFF db1 t --where="a>900" --result-file=/client_tmp/t.sql

在 Linux 服务器终端把结果输出到临时文件。

这条命令中，主要参数含义如下：

  1. –single-transaction 的作用是，在导出数据的时候不需要对表 db1.t 加表锁，而是使用 START TRANSACTION WITH CONSISTENT SNAPSHOT 的方法；
  2. –add-locks 设置为 0，表示在输出的文件结果里，不增加 “LOCK TABLES t WRITE;” ；
  3. –no-create-info 的意思是，不需要导出表结构；
  4. –set-gtid-purged=off 表示的是，不输出跟 GTID 相关的信息；
  5. –result-file 指定了输出文件的路径，其中 client 表示生成的文件是在客户端机器上的。

通过这条 mysqldump 命令生成的 t.sql 文件中就包含了如图 1 所示的 INSERT 语句。

图 1 mysqldump 输出文件的部分结果

可以看到，一条 INSERT 语句里面会包含多个 value 对，这是为了后续用这个文件来写入数据的时候，执行速度可以更快。如果你希望生成的文件中一条 INSERT 语句只插入一行数据的话，可以在执行 mysqldump 命令时，加上参数–skip-extended-insert。然后，你可以通过下面这条命令，将这些 INSERT 语句放到 db2 库里去执行（前提 db2 库已经存在 t 表）。

1
mysql -h127.0.0.1 -P13000 -uroot -p db2 -e "source /client_tmp/t.sql"

需要说明的是，source 并不是一条 SQL 语句，而是一个客户端命令。mysql 客户端执行这个命令的流程是这样的：

  1. 打开文件，默认以分号为结尾读取一条条的 SQL 语句；
  2. 将 SQL 语句发送到服务端执行。

也就是说，服务端执行的并不是这个“source t.sql”语句，而是 INSERT 语句。所以，不论是在慢查询日志（slow log），还是在 binlog，记录的都是这些要被真正执行的 INSERT 语句。

2. 导出 CSV 文件

另一种方法是直接将结果导出成.csv 文件。MySQL 提供了下面的语法，用来将查询结果导出到服务端本地目录：

1
select * from db1.t where a>900 into outfile '${secure-file-priv}/server_tmp/t.csv';

这条语句会将结果保存在服务端。如果你执行命令的客户端和 MySQL 服务端不在同一个机器上，客户端机器的临时目录下是不会生成 t.csv 文件的。且这条命令不会帮你覆盖文件，因此你需要确保 /server_tmp/t.csv 这个文件不存在，否则执行语句时就会因为有同名文件的存在而报错。另外，导出时可能会出现下面的错误：

1
The MySQL server is running with the --secure-file-priv option so it cannot execute this statement

这是因为 MySQL 限制了数据的导出路径。MySQL 导入导出文件只能在 secure-file-priv 变量的指定路径下的文件才可以导入导出。有以下 2 种解决办法：

  • 首先使用show variables like '%secure%';语句查看 secure-file-priv 变量配置。secure_file_priv 的值指定的是 MySQL 导入导出文件的路径。将 SQL 语句中的导出文件路径修改为该变量的指定路径，再执行导入导出操作即可。也可以在 my.ini 配置文件中修改 secure-file-priv 的值，然后重启服务即可。
  • 如果 secure_file_priv 值为 NULL，则为禁止导出，可以在 MySQL 安装路径下的 my.ini 文件中添加secure_file_priv=设置路径语句，然后重启服务即可。

另外需要说明的是，secure_file_priv 如果值为空，表示不限制文件生成的位置，这是不安全的设置。得到.csv 导出文件后，你就可以用下面的 load data 命令将数据导入到目标表 db2.t 中。

1
load data infile '${secure-file-priv}/server_tmp/t.csv' into table db2.t;

t.csv 中保存的数据如下：

1
2
3
4
901	901	901	901
902	902	902	902
903	903	903	903
...

上述 load data 语句的执行流程如下所示。

  1. 打开文件 /server_tmp/t.csv，以制表符 (\t) 作为字段间的分隔符，以换行符（\n）作为记录之间的分隔符，进行数据读取；
  2. 启动事务。
  3. 判断每一行的字段数与表 db2.t 是否相同：
    • 若不相同，则直接报错，事务回滚；
    • 若相同，则构造成一行，调用 InnoDB 引擎接口，写入到表中。
  4. 重复步骤 3，直到 /server_tmp/t.csv 整个文件读入完成，提交事务。

3. 物理拷贝方法

前面我们提到的 mysqldump 方法和导出 CSV 文件的方法，都是逻辑导数据的方法，也就是将数据从表 db1.t 中读出来，生成文本，然后再写入目标表 db2.t 中。你可能会问，有物理导数据的方法吗？比如，直接把 db1.t 表的.frm 文件和.ibd 文件拷贝 到 db2 目录下，是否可行呢？

答案是不行的。因为，一个 InnoDB 表，除了包含这两个物理文件外，还需要在数据字典中注册。直接拷贝这两个文件的话，因为数据字典中没有 db2.t 这个表，系统是不会识别和接受它们的。

不过，在 MySQL 5.6 版本引入了可传输表空间(transportable tablespace) 的方法，可以 通过导出 + 导入表空间的方式，实现物理拷贝表的功能。假设我们现在的目标是在 db1 库下，复制一个跟表 t 相同的表 r，具体的执行步骤如下：

  1. 执行 create table r like t，创建一个相同表结构的空表；
  2. 执行 alter table r discard tablespace，这时候 r.ibd 文件会被删除；
  3. 执行 flush table t for export，这时候 db1 目录下会生成一个 t.cfg 文件；
  4. 在 db1 目录下执行 cp t.cfg r.cfg; cp t.ibd r.ibd;这两个命令（这里需要注意的是，拷 贝得到的两个文件，MySQL 进程要有读写权限）；
  5. 执行 unlock tables，这时候 t.cfg 文件会被删除；
  6. 执行 alter table r import tablespace，将这个 r.ibd 文件作为表 r 的新的表空间，由于 这个文件的数据内容和 t.ibd 是相同的，所以表 r 中就有了和表 t 相同的数据。

至此，拷贝表数据的操作就完成了。这个流程的执行过程图如下：

图 3 物理拷贝表

关于拷贝表的这个流程，有以下几个注意点：

  1. 在第 3 步执行完 flsuh table 命令之后，db1.t 整个表处于只读状态，直到执行 unlock tables 命令后才释放读锁；
  2. 在执行 import tablespace 的时候，为了让文件里的表空间 id 和数据字典中的一致，会修改 r.ibd 的表空间 id。而这个表空间 id 存在于每一个数据页中。因此，如果是一个很大的文件（比如 TB 级别），每个数据页都需要修改，所以你会看到这个 import 语句的 执行是需要一些时间的。当然，如果是相比于逻辑导入的方法，import 语句的耗时是非常短的。
------ 本文结束------