如何复制一张表的,首先说,如果原表的数据很小的话,可以考虑使用 insert … select 语句来进行实现
当然,这种方式需要对原表进行加锁,为了避免加锁可能带来不必要的麻烦,可以考虑将其导入到一个外部文件,再写回目标表
复制的方式有两种,为了演示这两种导出方式,我们创建了两张表,一个是db1.t一个是db2.t
| 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 |
第一种:mysqldump方法
使用了mysqldump命令,将数据写成一组INSERT语句,使用下面的命令,将结果输出到临时文件中
mysqldump -h$host -P$port -u$user -p$password –add-lock=0 –no-create-info –single-transaction –set-gtid-purged=OFF db1 t –where=”a>900″ –result-file=/clinet_tmp/t.sql
我们分解下上面的命令
–single-transaction 导出的时候不加表锁
–add-lock=0 输出文件结果,不加LOCK TABLES t WRITE
–no-create-info 不导出表结构
–set-gtid-purged=OFF 不输出GTID相关信息
–result-file 输出文件路径,client表示是客户端机器上的
这样就生成了一个t.sql文件,包含了INSERT语句,默认为:
当然,如果想要是每一行都有一个INSERT语句的话,那么可以执行命令的时候,加上-skip-extended-insert
可以通过这个命令,将Insert放入db2库去执行
mysql -h$host -P$port -u$user -p$password db2 -e “sourcce /client_tmp/t.sql”
需要说明的是,source是一个客户端命令,告知mysql去哪里打开文件,并且读取其中SQL语句,发给服务器端去执行
服务器端其实还是接受的是INSERT语句
2.导出为CSV文件
将本地的数据导为csv文件,MySQL提供了语句 ,查询结果导出至服务器本地目录
select * from db1.t where a>900 into outfile ‘/server_tmp/t.csv’
1.这个结果保存在服务器端,如果客户端和MySQL服务器端不在一个机器上,客户端机器的临时目录不会生成csv文件
2.into_outile指定了文件的生成位置(/server_tmp/)这个位置必须接受参数secure_file_priv的限制,这个参数可以设置为
empty,不限制文件生成,当然,不安全
路径,就是只能放在这个路径的目录和子目录下
NULL,禁止使用select ….into outfile操作
3.这个命令无法去覆盖文件,所以一旦目录下已经有了这个同名文件,就会报错
4.这条命令生成的文本文件中,一个数据行对应文本中的一行,但是如果字段找那个包含换行符等,文本中也会有换行符,前面还会加上”\”这个转义符,可以跟字段之间 数据行之间的分隔符隔开
然后得到了这个文件后
讲这个文件进行重新导入,load data infile ‘/server_tmp/t.csv’ into table db2.t;
这个语句的执行流程为
打开文件 t.csv,以制表符(\t)作为字段之间的分隔符 (\n)作为分隔符,进行数据读取
启动事务
进行判断每一行的字段数和表db2.t是否相同
如果不相同,则直接报错回滚
如果相同,则构成一行,进行插入
这个流程,如果存在主备复制,则在备库上执行为
主库执行完成,将/server_tmp/t.csv文件的内容直接放在binlog文件中
往binlog文件中,写入load data local infile ‘/tmp/SQL_LOAD_MB-1-0’INTOTABLE ‘db2′.’t’
把这个binlog给备库
备库的apply线程在执行这个日志的时候
先将binlog中的t.csv读出来,写入到本地的临时目录 /tmp/SQL_LOAD_MB-1-0中
在执行load data,进行插入
在备库执行的load data语句中,多了一个local,意思为将执行这个命令的客户端所在机器的本地文件/tmp/SQL_LOAD_MB-1-0的内容,加载到表db2.t中
load data命令有两种用法
不加 local,读取服务端的文件,必须在secure_file_priv指定目录或者子目录下
加上 local,读取客户端的文件,只要mysql客户端有这个访问权限即可
顺便一提, 这个命令并不会生成表结构文件,如果需要表结构的定义,需要在mysqldump中加入-tab参数,可以导出表的定义和csv数据文件
mysqldump -h$host -P$port -u$user —single-transaction –set-gtid-purged=OFF db1 t –where=”a>900″ –tab=$secure_file_priv
上面的介绍的都是都通过逻辑导出并导入数据,有没有直接物理导入数据的方法
比如直接将db1.t表的.frm文件和.idb文件拷贝到db2目录上
有没有物理导数据的方法?直接把db1.t表的.frm文件上,和idb文件拷贝到db2目录下,可行吗
直接当然不行的,两个物理文件,需要在数据字典中注册的
MySQL 5.6引入了可传输表空间的方法,
会复制一个跟表t相同的表 可以通过导出+导入表空间的方式,来实现物理拷贝表
假设要从db1库下,拷贝一个表t相同的表r,流程如下
1.执行create table r like t,创建一个相同表结构的空表
2.alter table r discard tablespace,那么r.idb会被删除
3.执行flush table t fro export 这时候会在db1目录下生成一个t.cfg文件
4.在db1目录下,执行cp t.cfg r.cfg; cp t.ibd r.ibd两个命令
5.执行unlock tables t.cfg文件会被删除
6.alter table r import tablespace 这个r.idb文件作为表r的新的表空间,而且文件的数据内容和t.ibd是相同的,于是表r中有了表t中相同的数据
关于整个表的拷贝过程,需要注意
1.执行完成flush table命令后,整个表处于只读状态,直到执行unlock tables才释放锁
2.执行import tablespace的时候,为了数据一致,会修改r.idb的表空间id,而这个表空间id存在于每一个数据页中,因此是一个非常大的文件
本章说了三种将一个表的数据导入另一个表中
分别为两种逻辑到导入
导成sql文件,导成csv文件
和一种物理备份
物理备份的速度最快,对大表来说事合适,但是有一定的局限性,必须要全表拷贝,需要到服务器端拷贝,必须是innoDB引擎的
mysqldump不支持复杂的语句导出
select … into outfile 不能导出表结构,但支持的语句就多了
至于一个问题
为什么binlog_formate=statement的时候,binlog记录的load data命令是带local的,这个命令是发往备库去执行的,备库执行的时候是本地执行的,为什么需要这个local呢
因为如果不加客户端,就需要在服务端上去执行这个load命令,受到了secure_file_priv这个参数的限制
但是如果放在客户端上执行,只需要客户端具有本地文件夹的权限即可,而且客户端的线程既然能创建文件,想必也能执行文件