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,又会出现什么问题呢?
|