MySQL -- 拷贝表

初始化

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

$ 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);

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

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

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

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，表示执行这条命令的客户端本地文件（这里的客户端即备库本身）

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目录下执行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语句的耗时还是非常短的

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 |
+----------+

小结

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