MySQL 复制表的方法
作者:萌新J 时间:2024-01-21 02:22:50
目录
1、mysqldump
执行过程:
特点
2、导出 CSV 文件(最灵活)
执行过程
特点
3、物理拷贝(最快)
过程
局限
总结
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 文件数据到本地临时文件),最后一个执行效率是最高的,但是不能跨引擎,且只能进行全量拷贝。
来源:https://www.cnblogs.com/mengxinJ/p/14387096.html