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

最新推荐文章于 2022-03-18 21:44:11 发布

蓝盾9527

最新推荐文章于 2022-03-18 21:44:11 发布

阅读量393

点赞数

分类专栏： (待完善)mysql实战42讲——读书笔记文章标签： mysql 数据库 sql

本文链接：https://blog.csdn.net/m0_58509603/article/details/121326173

版权

(待完善)mysql实战42讲——读书笔记专栏收录该内容

27 篇文章 0 订阅

订阅专栏

本文介绍了三种MySQL数据导出方法：mysqldump生成INSERT语句、直接导出CSV文件和物理拷贝表空间。mysqldump可按条件导出部分数据，但不支持复杂查询；select...into outfile灵活但每次只能导出单表；物理拷贝速度快，但受限于全表拷贝和InnoDB引擎。每种方法有其适用场景和优缺点，选择时需根据实际需求权衡。

摘要由CSDN通过智能技术生成

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表示生成的文件是在客户端机器上的。

导出CSV文件

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

select * from db1.t where a>900 into outfile '/server_tmp/t.csv';

select …into outfile方法不会生成表结构文件, 所以我们导数据时还需要单独的命令得到表结构定义。

物理拷贝方法

在MySQL 5.6版本引入了可传输表空间(transportable tablespace)的方法，可以通过导出+导入表空间的方式，实现物理拷贝表的功能。

小结

介绍了三种将一个表的数据导入到另外一个表中的方法。我们来对比一下这三种方法的优缺点。

物理拷贝的方式速度最快，尤其对于大表拷贝来说是最快的方法。如果出现误删表的情况，用备份恢复出误删之前的临时库，然后再把临时库中的表拷贝到生产库上，是恢复数据最快的方法。但是，这种方法的使用也有一定的局限性：

必须是全表拷贝，不能只拷贝部分数据；
需要到服务器上拷贝数据，在用户无法登录数据库主机的场景下无法使用；
由于是通过拷贝物理文件实现的，源表和目标表都是使用InnoDB引擎时才能使用。

用mysqldump生成包含INSERT语句文件的方法，可以在where参数增加过滤条件，来实现只导出部分数据。这个方式的不足之一是，不能使用join这种比较复杂的where条件写法。
用select … into outfile的方法是最灵活的，支持所有的SQL写法。但，这个方法的缺点之一就是，每次只能导出一张表的数据，而且表结构也需要另外的语句单独备份。

后两种方式都是逻辑备份方式，是可以跨引擎使用的。
我们前面介绍binlog_format=statement的时候，binlog记录的load data命令是带local的。既然这条命令是发送到备库去执行的，那么备库执行的时候也是本地执行，为什么需要这个local呢？如果写到binlog中的命令不带local，又会出现什么问题呢？