初始化

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
CREATE DATABASE db1;
USE db1;

CREATE TABLE t(id INT PRIMARY KEY, a INT, b INT, INDEX(a)) ENGINE=InnoDB;
DELIMITER ;;
CREATE PROCEDURE idata()
BEGIN
DECLARE i INT;
SET i=1;
WHILE (i <= 1000) DO
INSERT INTO t VALUES (i,i,i);
SET i=i+1;
END WHILE;
END;;
DELIMITER ;
CALL idata();

CREATE DATABASE db2;
CREATE TABLE db2.t LIKE db1.t;

-- 目标:把db1.t里面a>900的数据导出来,插入到db2.t

mysqldump

1
2
3
4
5
$ mysqldump -uroot --add-locks=0 --no-create-info --single-transaction  --set-gtid-purged=OFF db1 t --where="a>900" --result-file=/tmp/t.sql

# 部分结果
$ cat /tmp/t.sql
INSERT INTO `t` VALUES (901,901,901),(902,902,902),(903,903,903)...(999,999,999),(1000,1000,1000);
  1. mysqldump命令将数据导出成一组INSERT语句,把结果输出到客户端的临时文件
  2. --single-transaction
    • 导出数据时不需要对表db1.t加表锁
    • 采用的是START TRANSACTION WITH CONSISTENT SNAPSHOT
  3. --add-locks=0
    • 表示在输出到文件结果里,不增加LOCK TABLES t WRITE
  4. --no-create-info
    • 不需要导出表结构
  5. --set-gtid-purged=OFF
    • 不输出跟GTID相关的信息
  6. --result-file
    • 执行客户端上输出文件的路径
  7. 输出结果中的INSERT语句会包含多个value对,为了后续如果使用这个文件写入数据,执行会更快
    • 如果要一个INSERT语句只插入一行数据的话,增加参数--skip-extended-insert

应用到db2

1
$ mysql -h$host -P$port -u$user db2 -e "source /client_tmp/t.sql"
  1. source是一个客户端命令,打开文件,默认以分号结尾读取一条条的SQL语句
  2. 将SQL语句发送到服务端执行,slowlogbinlog都会记录这些语句

导出CSV

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
mysql> SYSTEM cat cat /usr/local/etc/my.cnf
cat: cat: No such file or directory
# Default Homebrew MySQL server config
[mysqld]
# Only allow connections from localhost
bind-address = 127.0.0.1
slow_query_log = 1
long_query_time = 0
secure-file-priv = "/tmp"

mysql> SELECT @@secure_file_priv;
+--------------------+
| @@secure_file_priv |
+--------------------+
| /tmp/ |
+--------------------+

mysql> SELECT * FROM db1.t WHERE a>900 INTO OUTFILE '/tmp/t.csv';
Query OK, 100 rows affected (0.01 sec)

mysql> SYSTEM du -sh /tmp/t.csv
4.0K /tmp/t.csv
  1. secure-file-priv
    • secure-file-priv="",表示不限制文件生成的位置,不安全
    • secure-file-priv="/XXX",要求生成的文件只能存放在指定的目录或其子目录
    • secure-file-priv=NULL,表示禁止在这个MySQL实例上执行SELECT...INTO OUTFILE
  2. SELECT...INTO OUTFILE语句
    • 将结果保存在服务端
    • 不会覆盖文件
    • 原则上一个数据行对应文本文件的一行
    • 不会生成表结构文件
      • mysqldump提供--tab参数,可以同时导出表结构定义文件csv数据文件

LOAD DATA

1
LOAD DATA INFILE '/tmp/t.csv' INTO TABLE db2.t;
  1. 打开文件/tmp/t.csv
    • 以制表符\t作为字段间的分隔符,以换行符\n作为记录间的分隔符,进行数据读取
  2. 启动事务
  3. 判断每一行的字段数db.t是否相同
    • 如果不相同,则直接报错,回滚事务
    • 如果相同,则构造这一行,调用InnoDB引擎的接口,写入到表中
  4. 重复步骤3,直到/tmp/t.csv整个文件读入完成,提交事务

主备同步

binlog_format=STATEMENT

  1. 主库执行完成后,将/tmp/t.csv文件的内容都直接写到binlog文件
  2. 往binlog文件写入
    • LOAD DATA LOCAL INFILE '/tmp/SQL_LOAD_MB-1-0' INTO TABLE db2.t;
  3. 把binlog传到备库
  4. 备库的应用日志线程在执行这个事务日志时
    • 先把binlog中的t.csv文件的内容读出来,写到本地临时目录/tmp/SQL_LOAD_MB-1-0
    • 再执行LOAD DATA LOCAL语句,往备库的db2.t插入跟主库相同的数据
      • LOCAL,表示执行这条命令的客户端本地文件(这里的客户端即备库本身)

LOCAL

  1. LOAD DATA
    • 读取的是服务端文件,文件必须在secure_file_priv指定的目录或其子目录
  2. LOAD DATA LOCAL
    • 读取的是客户端文件,只需要MySQL客户端有访问这个文件的权限即可
    • 此时,MySQL客户端会先把本地文件传给服务端,然后再执行流程

物理拷贝

  1. 直接把db1.t的frm文件和ibd文件拷贝到db2目录下,是不行的
    • 因为一个InnoDB表,除了包含这两个物理文件外,还需要在数据字典中注册
  2. MySQL 5.6引入了可传输表空间,可以通过导出+导入表空间的方式,实现物理拷贝表

执行步骤

  1. 假设在db1库下,复制一个跟表t相同的表r
  2. 执行CREATE TABLE r LIKE t;,创建一个相同表结构的空表
  3. 执行ALTER TABLE r DISCARD TABLESPACE;,此时r.ibd文件会被删除
  4. 执行FLUSH TABLE t FOR EXPORT;
    • 此时在db1目录下会生成t.cfg文件
    • 整个db1.t处于只读状态,直到执行UNLOCK TABLES
  5. 在db1目录下执行cp t.cfg r.cfgcp t.ibd r.ibd
  6. 执行UNLOCK TABLES;,此时t.cfg文件会被删除
  7. 执行ALTER TABLE r IMPORT TABLESPACE;
    • 将这个r.ibd文件作为表r新的表空间
    • 由于这个文件的内容与t.ibd是相同的,因此表r中数据与表t相同
    • 为了让文件里的表空间id和数据字典中的一致,会修改r.ibd的表空间id
      • 表空间id存在于每一个数据页
      • 如果是一个很大的文件,每个数据页都需要修改,IMPORT语句会需要点时间
      • 但相对于逻辑拷贝的方法,IMPORT语句的耗时还是非常短的
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
mysql> CREATE TABLE r LIKE t;
Query OK, 0 rows affected (0.41 sec)

mysql> SYSTEM ls /usr/local/var/mysql/db1
r.ibd t.ibd

-- 删除r.ibd
mysql> ALTER TABLE r DISCARD TABLESPACE;
mysql> SYSTEM ls /usr/local/var/mysql/db1
t.ibd

-- 生成t.cfg,t处于只读状态
mysql> FLUSH TABLE t FOR EXPORT;
mysql> SYSTEM ls /usr/local/var/mysql/db1
t.cfg t.ibd

mysql> UNLOCK TABLES;
Query OK, 0 rows affected (0.00 sec)

mysql> ALTER TABLE r IMPORT TABLESPACE;
Query OK, 0 rows affected (0.23 sec)

mysql> SELECT COUNT(*) from r;
+----------+
| COUNT(*) |
+----------+
| 1000 |
+----------+

小结

  1. 物理拷贝速度最快,尤其对于大表来说
    • 必须全表拷贝
    • 需要到服务器上拷贝数据
    • 不支持跨引擎使用,源表和目标表都是使用InnoDB引擎
  2. mysqldump生成包含INSERT语句的方法,加上where过滤,可以只导出部分数据
    • 不支持类似join等复杂的写法
    • 逻辑拷贝,支持跨引擎使用
  3. SELECT...INTO OUTFILE最灵活,支持所有的SQL语法
    • 每次只能导出一张表的数据,而且表结构需要另外的语句单独备份
    • 逻辑拷贝,支持跨引擎使用

参考资料

《MySQL实战45讲》