MySQL 复制表的方法

admin MySQL 2022-02-09 19:58:28 MySQL 表 MySQL 复制表"

1、mysqldump

执行过程：

一、将数据导出为 sql 文件。

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

将数据导出为 sql 文件保存。上面几个参数的含义分别是：

1、–single-transaction 的作用是，在导出数据的时候不需要对表 db1.t 加表锁，而是使用 START TRANSACTION WITH CONSISTENT SNAPSHOT 的方法；

2、–add-locks 设置为 0，表示在输出的文件结果里，不增加" LOCK TABLES t WRITE;" ；

3、–no-create-info 的意思是，不需要导出表结构；

4、–set-gtid-purged=off 表示的是，不输出跟 GTID 相关的信息；

5、–result-file 指定了输出文件的路径，其中 client 表示生成的文件是在客户端机器上的。

二、执行文件，添加到表中

mysql -h127.0.0.1 -P13000 -uroot db2 -e "source /client_tmp/t.sql"

source 并不是一条 SQL 语句，而是一个客户端命令。也就是服务器端具体执行的是文件中的一条条 sql 语句，所以 binlog 记录的都是具体的 sql。

特点

1、生成的 sql 文件保存在客户端

2、默认保存数据方式是多个记录对，如下面格式

如果想要保存为一条语句只保存一条记录，那么可以加上参数–skip-extended-insert。

2、导出 CSV 文件（最灵活）

执行过程

一、导出为 CSV 文件

select * from db1.t where a>900 into outfile '/server_tmp/t.csv';

注意：

1、into outfile 指定了文件的生成位置（/server_tmp/），这个位置必须受参数 secure_file_priv 的限制。

参数 secure_file_priv 的可选值和作用分别是：

　　1）如果设置为 empty，表示不限制文件生成的位置，这是不安全的设置；

　　2）如果设置为一个表示路径的字符串，就要求生成的文件只能放在这个指定的目录，或者它的子目录；

　　3）如果设置为 NULL，就表示禁止在这个 MySQL 实例上执行 select … into outfile 操作。

2、如果同一个目录下存在同名文件，就会报错

3、一般情况下一条记录就对应 CSV 文件中的一行，但是如果某个字段值中有 "换行、制表符" 那么文件中也会包含，并且使用 "\" 来转义。

二、导入数据

load data infile '/server_tmp/t.csv' into table db2.t;

过程：

1、打开文件 /server_tmp/t.csv，以制表符 (\t) 作为字段间的分隔符，以换行符（\n）作为记录之间的分隔符，进行数据读取；

2、启动事务。

3、判断每一行的字段数与表 db2.t 是否相同：

　　1）若不相同，则直接报错，事务回滚；

　　2）若相同，则构造成一行，调用 InnoDB 引擎接口，写入到表中。

4、重复步骤 3，直到 /server_tmp/t.csv 整个文件读入完成，提交事务。

特点

1、文件保存在服务器端

2、关于 binlog 的记录，过程如下：

　　1）主库执行完成后，将 /server_tmp/t.csv 文件的内容直接写到 binlog 文件中。

　　2）往 binlog 文件中写入语句 load data local infile ‘/tmp/SQL_LOAD_MB-1-0' INTO TABLE `db2`.`t`。

　　3）把这个 binlog 日志传到备库。

　　4）备库的 apply 线程在执行这个事务日志时：

　　　　a. 先将 binlog 中 t.csv 文件的内容读出来，写入到本地临时目录 /tmp/SQL_LOAD_MB-1-0 中；

　　　　b. 再执行 load data 语句，往备库的 db2.t 表中插入跟主库相同的数据。

　　关于 "local"：

　　　　1）不加“local”，是读取服务端的文件，这个文件必须在 secure_file_priv 指定的目录或子目录下；

　　　　2）加上“local”，读取的是客户端的文件，只要 mysql 客户端有访问这个文件的权限即可。这时候，MySQL 客户端会先把本地文件传给服务端（其他会话涉及的操作），然后执行上述的 load data 流程。

3、上面的导出操作并不会导出表结构，所以，如果向导出表结构，可以使用 mysqldump 来同时导出 CSV 和表结构

mysqldump -h$host -P$port -u$user --single-transaction --set-gtid-purged=OFF db1 t --where="a>900" --tab=$secure_file_priv

会在$secure_file_priv 定义的目录下，创建一个 t.sql 文件保存建表语句，同时创建一个 t.txt 文件保存 CSV 数据。

3、物理拷贝（最快）

在5.6之前，想要直接把.frm和.ibd文件拷贝到要拷贝的目录下是不行的，因为一个Innodb表除了需要这两个文件还需要在数据字典中注册。但是从 5.6 开始可以解决这一问题，在 5.6 引入了可传输空间，可以通过导出 + 导入表空间来实现拷贝

过程

假设我们现在的目标是在 db1 库下，复制一个跟表 t 相同的表 r，具体的执行步骤如下：

1、执行 create table r like t，创建一个相同表结构的空表；

2、执行 alter table r discard tablespace，这时候 r.ibd 文件会被删除；

3、执行 flush table t for export，这时候 db1 目录下会生成一个 t.cfg 文件；

4、在 db1 目录下执行 cp t.cfg r.cfg; cp t.ibd r.ibd；这两个命令（这里需要注意的是，拷贝得到的两个文件，MySQL 进程要有读写权限）；

5、执行 unlock tables，这时候 t.cfg 文件会被删除；

6、执行 alter table r import tablespace，将这个 r.ibd 文件作为表 r 的新的表空间，由于这个文件的数据内容和 t.ibd 是相同的，所以表 r 中就有了和表 t 相同的数据。

注意：

1、在第 3 步执行完 flsuh table 命令之后，db1.t 整个表处于只读状态，直到执行 unlock tables 命令后才释放读锁；

2、在执行 import tablespace 的时候，为了让文件里的表空间 id 和数据字典中的一致，会修改 r.ibd 的表空间 id。而这个表空间 id 存在于每一个数据页中。因此，如果是一个很大的文件（比如 TB 级别），每个数据页都需要修改，所以你会看到这个 import 语句的执行是需要一些时间的。当然，如果是相比于逻辑导入的方法，import 语句的耗时是非常短的。