一.mysqldump方法
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
然后通过下面命令，将语句放到db2库里执行：
mysql -h127.0.0.1 -P13000 -uroot db2 -e "source /client_tmp/t.sql"
二.导出CSV文件
另一种方法是将结果直接导出为csv文件，使用如下语法：
select * from db1.t where a>900 into outfile '/server_tmp/t.csv';
得到csv导出文件后，可以用load data命令将数据导入到目标表中：
load data infile '/root/t.csv' into table db2.t;
注意：如果binlog_format=statement，这个load语句记录到
binlog里以后，怎么在备库重放呢？
以下是其完整流程：
（1）主库执行完成后，将csv文件内容直接写到binlog文件中。
（2）向binlog文件中写入语句load data local infile '/tmp/SQL_LOAD_MB-1-0' INTO TABLE db2.t。
（3）把这个binlog日志传到备库。
（4）备库的apply线程在执行这个事务日志时：先将binlog中t.csv文件内容读出，写入本地临时目录/tmp/SQL_LOAD_MB-1-0；再执行load data语句，往备库db2.t表中插入跟主库相同数据。
三.物理拷贝方法
直接把db1.t表的.frm文件和.ibd文件拷贝到db2目录下，是否可行呢？
不行！
mysql 5.6版本引入了可传输表空间方法，通过导出+导入表空间的方式，实现物理拷贝表的功能。
假设要在db1库下复制一个跟表t相同的表r，具体执行过程如下：
（1）create table r like t
（2）alter table r discard tablespace，这时r.idb文件会被删除
（3）flush table t for export，这时db1目录下会生成一个t.cfg文件
（4）在db1目录下执行cp t.cfg r.cfg; cp t.ibd r.ibd；这两个命令
（5）执行unlock tables，这时t.cfg文件会被删除
（6）执行alter table r import tablespace，将这个r.ibd文件作为表r的新的表空间，由于这个文件的数据内容与t.ibd相同，所以表r中就有了和表t相同的数据。
四.总结
对比一下上面三种方法的优缺点：
（1）物理拷贝速度最快，尤其对于大表。但是有三点局限：
- 必须全表拷贝
- 需要到服务器上拷贝
- 必须是InnoDB引擎
（2）mysqldump可以控制数据的过滤条件，但不能使用join这种复杂的where条件写法。
（3）select...into outfile支持所有SQL写法，但一次只能到处一张表数据，且表结构也需要另外的语句单独备份。
网友评论