复制表的方法


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 语句的耗时是非常短的。

 

局限

1、必须是全表拷贝,不能条件拷贝

2、需要到服务器上拷贝数据,在用户无法登录数据库主机的场景下无法使用

3、由于是通过拷贝物理文件实现的,源表和目标表都是使用 InnoDB 引擎时才能使用

 

总结

1、前两个都是逻辑备份,也就是可以跨引擎使用,最后一个不行

2、前两个可以条件拷贝,最后一个不行

3、第二个功能是最灵活的,但是在集群从库接收时会比较耗时(需要先拷贝 CSV 文件数据到本地临时文件),最后一个执行效率是最高的,但是不能跨引擎,且只能进行全量拷贝。

  • 分享:
评论
还没有评论
    发表评论 说点什么