MySQL -- 拷贝表
初始化
1 | CREATE DATABASE db1; |
mysqldump
1 | $ mysqldump -uroot --add-locks=0 --no-create-info --single-transaction --set-gtid-purged=OFF db1 t --where="a>900" --result-file=/tmp/t.sql |
- mysqldump命令将数据导出成一组
INSERT
语句,把结果输出到客户端的临时文件 --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
- 执行客户端上输出文件的路径
- 输出结果中的
INSERT
语句会包含多个value对,为了后续如果使用这个文件写入数据,执行会更快- 如果要一个
INSERT
语句只插入一行数据的话,增加参数--skip-extended-insert
- 如果要一个
应用到db2
1 | $ mysql -h$host -P$port -u$user db2 -e "source /client_tmp/t.sql" |
source
是一个客户端命令,打开文件,默认以分号结尾读取一条条的SQL语句- 将SQL语句发送到服务端执行,
slowlog
和binlog
都会记录这些语句
导出CSV
1 | mysql> SYSTEM cat cat /usr/local/etc/my.cnf |
- secure-file-priv
secure-file-priv=""
,表示不限制文件生成的位置,不安全secure-file-priv="/XXX"
,要求生成的文件只能存放在指定的目录或其子目录secure-file-priv=NULL
,表示禁止在这个MySQL实例上执行SELECT...INTO OUTFILE
SELECT...INTO OUTFILE
语句- 将结果保存在服务端
- 不会覆盖文件
- 原则上一个数据行对应文本文件的一行
- 不会生成表结构文件
- mysqldump提供
--tab
参数,可以同时导出表结构定义文件和csv数据文件
- mysqldump提供
LOAD DATA
1 | LOAD DATA INFILE '/tmp/t.csv' INTO TABLE db2.t; |
- 打开文件
/tmp/t.csv
- 以制表符
\t
作为字段间的分隔符,以换行符\n
作为记录间的分隔符,进行数据读取
- 以制表符
- 启动事务
- 判断每一行的字段数和
db.t
是否相同- 如果不相同,则直接报错,回滚事务
- 如果相同,则构造这一行,调用InnoDB引擎的接口,写入到表中
- 重复步骤3,直到
/tmp/t.csv
整个文件读入完成,提交事务
主备同步
binlog_format=STATEMENT
- 主库执行完成后,将
/tmp/t.csv
文件的内容都直接写到binlog文件中 - 往binlog文件写入
LOAD DATA LOCAL INFILE '/tmp/SQL_LOAD_MB-1-0' INTO TABLE db2.t;
- 把binlog传到备库
- 备库的应用日志线程在执行这个事务日志时
- 先把binlog中的t.csv文件的内容读出来,写到本地临时目录
/tmp/SQL_LOAD_MB-1-0
- 再执行
LOAD DATA LOCAL
语句,往备库的db2.t插入跟主库相同的数据LOCAL
,表示执行这条命令的客户端本地文件(这里的客户端即备库本身)
- 先把binlog中的t.csv文件的内容读出来,写到本地临时目录
LOCAL
LOAD DATA
- 读取的是服务端文件,文件必须在
secure_file_priv
指定的目录或其子目录
- 读取的是服务端文件,文件必须在
LOAD DATA LOCAL
- 读取的是客户端文件,只需要MySQL客户端有访问这个文件的权限即可
- 此时,MySQL客户端会先把本地文件传给服务端,然后再执行流程
物理拷贝
- 直接把db1.t的frm文件和ibd文件拷贝到db2目录下,是不行的
- 因为一个InnoDB表,除了包含这两个物理文件外,还需要在数据字典中注册
- 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.t
处于只读状态,直到执行UNLOCK TABLES
- 此时在db1目录下会生成
- 在db1目录下执行
cp t.cfg r.cfg
和cp t.ibd r.ibd
- 执行
UNLOCK TABLES;
,此时t.cfg
文件会被删除 - 执行
ALTER TABLE r IMPORT TABLESPACE;
- 将这个
r.ibd
文件作为表r新的表空间 - 由于这个文件的内容与
t.ibd
是相同的,因此表r中数据与表t相同 - 为了让文件里的表空间id和数据字典中的一致,会修改
r.ibd
的表空间id- 而表空间id存在于每一个数据页
- 如果是一个很大的文件,每个数据页都需要修改,
IMPORT
语句会需要点时间 - 但相对于逻辑拷贝的方法,
IMPORT
语句的耗时还是非常短的
- 将这个
1 | mysql> CREATE TABLE r LIKE t; |
小结
- 物理拷贝速度最快,尤其对于大表来说
- 必须全表拷贝
- 需要到服务器上拷贝数据
- 不支持跨引擎使用,源表和目标表都是使用InnoDB引擎
- mysqldump生成包含
INSERT
语句的方法,加上where
过滤,可以只导出部分数据- 不支持类似join等复杂的写法
- 逻辑拷贝,支持跨引擎使用
SELECT...INTO OUTFILE
最灵活,支持所有的SQL语法- 每次只能导出一张表的数据,而且表结构需要另外的语句单独备份
- 逻辑拷贝,支持跨引擎使用
参考资料
《MySQL实战45讲》
All articles in this blog are licensed under CC BY-NC-SA 4.0 unless stating additionally.