如果可以控制对源表的扫描行数和加锁范围很小的话,我们简单地使用insert ... select 语句即可实现。
为了避免对源表加读锁,更稳妥的方案是先将数据写到外部文件,然后再写回目标表。有两种方法。
创建一个表db1.t,插入1000行数据,同时创建一个相同结构的表db2.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 SANPSHOT的方法;
- --add-locks设置为0,表示在输出文件结果里,不增加“LOCK TABLES t WRITE;”;
- --no-create-info的意思是,不需要导出表结构;
- --set-gtid-purged=off表示的是,不输出GTID相关的信息;
- --result-file指定了输出文件的路径,其中client表示生成的文件是在客户端机器上的。
导出结果类似下面:
如果希望生成的文件中一条insert语句直插入一行的话,可以在执行mysqldump命令的时候,加上参数--skip-extended-insert。
然后,通过下面命令,将这些insert语句放到db2库里执行。
mysql -h127.0.0.1 -p13000 -uroot db2 -e "source /client_tmp/t.sql"
source是一个客户端命令,mysql客户端执行这个命令的流程是这样的:
- 打开文件,默认以分号为结尾读取一条条的SQL语句;
- 将SQL语句发送到服务端执行。
导出csv文件
select * from db1.t where a>900 into outfile '/server_tmp/t.csv';
使用这条语句,需要注意:
- 这条语句会将结果保存在服务端。如果执行命令的客户端和MySQL服务端不在同一个机器上,客户端机器的临时目录下是不会生成t.csv文件的。
- into outfile指定了文件的生成位置(/server-tmp/),这个位置必须受参数secure_file_priv的限制。参数secure_file_priv的可选值和作用分别是:
2.1 如果设置为empty,表示不限制文件生成的位置,这是不安全的设置;
2.2 如果设置为一个表示路径的字符串,就要求生成的文件只能放在这个指定的目录,或者它的子目录;
2.3 如果设置为NULL,就表示禁止这个MySQL实例上执行select ... into outfile操作。
3.这条命令不会帮你覆盖文件,因此你要确保/server_tmp/t.csv这个文件不存在,否则执行语句时就会因为有同名文件的存在而报错。
4.这条命令生成的文本文件中,原则上一个数据行对应文本文件的一行。但是,如果字段中包含换行符,在生成的文本中也会有换行符。不过类似换行符、制表符这类符号,前面都会跟上“\”这个转义字符,这就可以跟字段之间、数据行之间的分隔符区分开。
得到.csv文件后,可以用下面的load data命令将数据导入到目录表db2.t中。
load data infile '/server_tmp/t.csv' into table db2.t;
这条语句的执行流程如下:
- 打开文件/server_tmp/t.csv,以制表符(\t)作为字段间的分隔符,以换行符(\n)作为记录之间的分隔符,进行数据读取;
- 启动事务;
- 判断每一行的字段数与表db2.t是否相同:
若不相同,直接报错,事务回滚;
若相同,则构造成一行,调用InnoDB引擎接口,写入到表中。
4.重复步骤3,直到/server_tmp/t.csv整个文件读入完成,提交事务。
物理拷贝法
前面都是逻辑导数据方法。
物理导数据方法?比如,直接把db1.t表的.frm文件和.ibd文件拷贝到db2目录下,是否可行呢?
答案是不行。
因为,一个InnoDB表,除了包含这两个物理文件外,还需要再数据字段中注册。直接拷贝,数据字典中没有db2.t这个表,系统是不会识别和接受它们的。
在MySQL 5.6版本引入了可传输表空间的方法,可以通过导出+导入表空间的方式,实现物理拷贝的功能。
假设现在的目标是在db1库下,复制一个跟表t相同的表r,具体的执行步骤如下:
- 执行create table r like t,创建一个相同表结构的空表;
- 执行alter table r discard tablespace,这时候r.ibd文件会被删除;
- 执行flush table t for export,这时候db1目录下会生成一个t.cfg文件;
- 在db1目录下执行cp t.cfg r.cfg; cp t.idb r.ibd; 这两个命令(需要注意,拷贝得到的两个文件,MySQL进程要有读写权限);
- 执行unlock tables,这时候t.cfg文件会被删除;
- 执行alter table r import tablespace,将这个r.ibd文件作为表r的新的表空间,由于这个文件的数据内容和t.idb是相同的,所以表r中就有了和表t相同的数据。
这个流程,需要注意:
- 在第三步执行完flush table命令之后,db1.t整个表处于只读状态,直到执行unlock tables命令后才释放读锁;
- 在执行import tablespace的时候,为了让文件里的表空间id和数据字典中的一致,会修改r.ibd的表空间id。而这个表空间id存在于每一个数据页中。因此,如果是一个很大的文件(比如TB级别),每个数据页都需要修改,所以你会看到这个import语句的执行时需要一些时间的。当然,如果是相比于逻辑导入的方法,import语句的耗时是非常短的。