41 | 怎么最快地复制一张表？

林晓斌



该思维导图由 AI 生成，仅供参考

我在上一篇文章最后，给你留下的问题是怎么在两张表中拷贝数据。如果可以控制对源表的扫描行数和加锁范围很小的话，我们简单地使用 insert … select 语句即可实现。
当然，为了避免对源表加读锁，更稳妥的方案是先将数据写到外部文本文件，然后再写回目标表。这时，有两种常用的方法。接下来的内容，我会和你详细展开一下这两种方法。
为了便于说明，我还是先创建一个表 db1.t，并插入 1000 行数据，同时创建一个相同结构的表 db2.t。
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 中。
mysqldump 方法一种方法是，使用 mysqldump 命令将数据导出成一组 INSERT 语句。你可以使用下面的命令：
mysqldump -h$host -P$port -u$user --add-locks=0 --no-create-info --single-transaction  --set-gtid-purged=OFF db1 t --where="a>900" --result-file=/client_tmp/t.sql
把结果输出到临时文件。
这条命令中，主要参数含义如下：
–single-transaction 的作用是，在导出数据的时候不需要对表 db1.t 加表锁，而是使用 START TRANSACTION WITH CONSISTENT SNAPSHOT 的方法；
–add-locks 设置为 0，表示在输出的文件结果里，不增加" LOCK TABLES t WRITE;" ；
–no-create-info 的意思是，不需要导出表结构；
–set-gtid-purged=off 表示的是，不输出跟 GTID 相关的信息；
–result-file 指定了输出文件的路径，其中 client 表示生成的文件是在客户端机器上的。

公开

同步至部落

取消

完成

0/2000

荧光笔

直线

曲线

笔记

复制

AI

深入了解
翻译
英语
中文简体
中文繁体
法语
德语
日语
韩语
俄语
西班牙语
阿拉伯语
解释
总结

本文介绍了在MySQL中最快地复制一张表的方法，包括使用mysqldump命令导出INSERT语句、直接导出.csv文件以及使用mysqldump的--tab参数导出表结构定义文件和CSV数据文件的方法。此外，还介绍了在MySQL 5.6版本中引入的可传输表空间的方法，实现物理拷贝表的功能。文章总结了三种方法的优缺点，指出物理拷贝方式速度最快，但有一定的局限性；而逻辑备份方式则更为灵活，支持跨引擎使用。总的来说，本文详细介绍了两种常用的方法，以及它们的执行流程和注意事项，为读者提供了快速了解MySQL表复制的技术特点。

仅可试看部分内容，如需阅读全部内容，请付费购买文章所属专栏
《MySQL 实战 45 讲》，新⼈⾸单¥68

立即购买

登录后留言

全部留言(42)

最新
精选

长杰
课后题答案不加“local”，是读取服务端的文件，这个文件必须在 secure_file_priv 指定的目录或子目录下；而备库的apply线程执行时先讲csv内容读出生成tmp目录下的临时文件，这个目录容易受secure_file_priv的影响，如果备库改参数设置为Null或指定的目录，可能导致load操作失败，加local则不受这个影响。
作者回复: 👍
2019-02-17

68
poppy
关于思考题，我理解是备库的同步线程其实相当于备库的一个客户端，由于备库的会把binlog中t.csv的内容写到/tmp/SQL_LOAD_MB-1-0中，如果load data命令不加'local'表示读取服务端的文件，文件必须在secure_file_priv指定的目录或子目录，此时可能找不到该文件，主备同步执行会失败。而加上local的话，表示读取客户端的文件，既然备份线程都能在该目录下创建临时文件/tmp/SQL_LOAD_MB-1-0,必然也有权限访问，把该文件传给服务端执行。
作者回复: 👍这是其中一个原因
2019-02-15

25
尘封
老师mysqldump导出的文件里，单条sql里的value值有什么限制吗默认情况下，假如一个表有几百万，那mysql会分为多少个sql导出？问题：因为从库可能没有load的权限，所以local
作者回复: 好问题，会控制单行不会超过参数net_buffer_length，这个参数是可以通过--net_buffer_length 传给mysqldump 工具的
2019-02-15
3
18
☆appleう
通知对方更新数据的意思是: 针对事务内的3个操作:插入和更新两个都是本地操作，第三个操作是远程调用，这里远程调用其实是想把本地操作的那两条通知对方(对方:远程调用)，让对方把数据更新，这样双方(我和远程调用方)的数据达到一致，如果对方操作失败，事务的前两个操作也会回滚，主要是想保证双方数据的一致性，因为远程调用可能会出现网络延迟超时等因素，极端情况会导致事务10s左右才能处理完毕，想问的是这样耗时的事务会带来哪些影响呢？设计的初衷是想这三个操作能原子执行，只要有不成功就可以回滚，保证两方数据的一致性耗时长的远程调用不放在事务中执行，会出现我这面数据完成了，而对方那面由于网络等问题，并没有更新，这样两方的数据就出现不一致了
作者回复: 嗯了解了这种设计我觉得就是会对并发性有比较大的影响。一般如果网络状态不好的，会建议把这个更新操作放到消息队列。就是说 1. 先本地提交事务。 2. 把通知这个动作放到消息队列，失败了可以重试； 3. 远端接收事件要设置成可重入的，就是即使同一个消息收到两次，也跟收到一次是相同的效果。 2 和3 配合起来保证最终一致性。这种设计我见到得比较多，你评估下是否符合你们业务的需求哈
2019-02-15
2
15
库淘淘
如果不加local 如secure_file_priv 设置为null 或者路径可能就不能成功,这样加了之后可以保证执行成功率不受参数secure_file_priv影响。还有发现物理拷贝文件后，权限所属用户还得改下，不然import tablespace 会报错找不到文件，老师是不是应该补充上去，不然容易踩坑。
作者回复: 嗯嗯，有同学已经踩了，我加个说明进去，多谢提醒
2019-02-15

12
undifined
老师，用物理导入的方式执行 alter table r import tablespace 时提示ERROR 1812 (HY000): Tablespace is missing for table `db1`.`r`. 此时 db1/ 下面的文件有 db.opt r.cfg r.frm r.ibd t.frm t.ibd；这个该怎么处理执行步骤： mysql> create table r like t; Query OK, 0 rows affected (0.01 sec) mysql> alter table r discard tablespace; Query OK, 0 rows affected (0.01 sec) mysql> flush table t for export; Query OK, 0 rows affected (0.00 sec) cp t.cfg r.cfg cp t.ibd r.ibd mysql> unlock tables; Query OK, 0 rows affected (0.01 sec) mysql> alter table r import tablespace; ERROR 1812 (HY000): Tablespace is missing for table `db1`.`r`.
作者回复: 应该就是评论区其他同学帮忙回复的权限问题了吧？
2019-02-15
4
9
信信
老师好，唯一索引的加next-key lock时，会退化会记录锁。这中间会先拿到间隙锁再释放，还是从一开始就不会获取间隙锁，直接只获取记录锁呢？
作者回复: 在我们这篇的例子里面，insert duplicate key后导致加锁这个，是不会退化的哦。如果是说我们在21篇讲的加锁规则里面，这个退化的效果就是直接不加间隙锁
2019-02-15
2
7
☆appleう
老师，我想问一个关于事务的问题，一个事务中有3个操作，插入一条数据(本地操作),更新一条数据(本地操作)，然后远程调用，通知对方更新上面数据(如果远程调用失败会重试，最多3次，如果遇到网络等问题，远程调用时间会达到5s,极端情况3次会达到15s)，那么极端情况事务将长达5-15s，这样会带来什么影响吗？
作者回复: “通知对方更新上面数据” 是啥概念，如果你这个事务没提交，其他线程也看不到前两个操作的结果的。设计上不建议留这么长的事务哈，最好是可以先把事务提交了，再去做耗时的操作。
2019-02-15

7
skyoo
mysql> select * from t; +----+------+ | id | name | +----+------+ | 1 | Bob | | 2 | Mary | | 3 | Jane | | 4 | Lisa | | 5 | Mary | | 6 | Jane | | 7 | Lisa | +----+------+ 7 rows in set (0.00 sec) mysql> create table tt like t; Query OK, 0 rows affected (0.03 sec) mysql> alter table tt discard tablespace; Query OK, 0 rows affected (0.01 sec) mysql> flush table t for export; Query OK, 0 rows affected (0.01 sec) mysql> unlock tables; Query OK, 0 rows affected (0.00 sec) mysql> alter table tt import tablespace; Query OK, 0 rows affected (0.03 sec) mysql> show tables; +----------------+ | Tables_in_test | +----------------+ | t | | t2 | | tt | +----------------+ 3 rows in set (0.00 sec) mysql> select * from t; +----+------+ | id | name | +----+------+ | 1 | Bob | | 2 | Mary | | 3 | Jane | | 4 | Lisa | | 5 | Mary | | 6 | Jane | | 7 | Lisa | +----+------+ 7 rows in set (0.00 sec) mysql> select * from tt; +----+------+ | id | name | +----+------+ | 1 | Bob | | 2 | Mary | | 3 | Jane | | 4 | Lisa | | 5 | Mary | | 6 | Jane | | 7 | Lisa | +----+------+ 7 rows in set (0.00 sec) ll 后查看 tt.cfg 文件没有自动删除 5.7mysql -rw-r-----. 1 mysql mysql 380 2月 15 09:51 tt.cfg -rw-r-----. 1 mysql mysql 8586 2月 15 09:49 tt.frm -rw-r-----. 1 mysql mysql 98304 2月 15 09:51 tt.ibd
作者回复: 你说得对，👍细致 import动作不会自动删除cfg文件，我图改一下
2019-02-15

6
小灰灰zyh
老师您好，想问下如果是迁移5000W左右的一张表，使用导出CSV文件的方式效率高吗？
作者回复: 导出挺快的，但是导入就不好办了。~
2019-04-16

3

收起评论