Mysql复制表三种实现方法及grant解析

如何快速的复制一张表

首先创建一张表db1.t,并且插入1000行数据,同时创建一个相同结构的表db2.t

假设,现在需要把db1.t里面的a>900的数据行导出来,插入到db2.t中

mysqldump方法

几个关键参数注释:

  • –single-transaction的作用是,在导出数据的时候不需要对表db1.t加表锁,而是使用
  • START TRANSACTION WITH CONSISTENT SNAPSHOT的方法;
  • –no-create-info的意思是,不需要导出表结构;
  • –result-file指定了输出文件的路径,其中client表示生成的文件是在客户端机器上的。

导出csv文件

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

这条语句会将结果保存在服务端。如果你执行命令的客户端和MySQL服务端不在同一个机器上,客户端机器的临时目录下是不会生成t.csv文件的。

这条命令不会帮你覆盖文件,因此你需要确保/server_tmp/t.csv这个文件不存在,否则执行语句时就会因为有同名文件的存在而报错。

得到.csv导出文件后,你就可以用下面的load data命令将数据导入到目标表db2.t中。

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

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

启动事务。

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

  • 若不相同,则直接报错,事务回滚;
  • 若相同,则构造成一行,调用InnoDB引擎接口,写入到表中。

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

物理拷贝方法

mysqldump方法和导出CSV文件的方法,都是逻辑导数据的方法,也就是将数据从表db1.t中读出来,生成文本,然后再写入目标表db2.t中。有物理导数据的方法吗?比如,直接把db1.t表的.frm文件和.ibd文件拷贝到db2目录下,是否可行呢?答案是不行的。

因为,一个InnoDB表,除了包含这两个物理文件外,还需要在数据字典中注册。直接拷贝这两个文件的话,因为数据字典中没有db2.t这个表,系统是不会识别和接受它们的。

在MySQL 5.6版本引入了可传输表空间(transportable tablespace)的方法,可以通过导出+导入表空间的方式,实现物理拷贝表的功能。

假设现在的目标是在db1的库下,复制一个跟表t相同的表r,具体执行步骤:

  • 执行create table r like t,创建一个相同表结构的空表,
  • 执行alter table r discard tablespace,这时候r.ibd文件会被删除
  • 执行flush table t for export这时候会生成一个t.cfg
  • 在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相同的数据。

这三种方法的优缺点

物理拷贝的方式速度最快,尤其对于大表拷贝来说是最快的方法。但必须是全拷贝,不能是部分拷贝,需要到服务器上拷贝数据,在用户无法登录数据库主机时无法使用,而且源表和目标表都必须是innodb引擎。

用mysqldump生成包含INSERT语句文件的方法,可以在where参数增加过滤条件,来实现只导出部分数据。这个方式的不足之一是,不能使用join这种比较复杂的where条件写法。

用select … into outfile的方法是最灵活的,支持所有的SQL写法。但,这个方法的缺点之一就是,每次只能导出一张表的数据,而且表结构也需要另外的语句单独备份。

后两种都是逻辑备份方式,可以跨引擎使用的。

mysql全局权限

SELECT * FROM MYSQL.USER WHERE USER='UA'\G 显示所有权限

作用域整个mysql,信息保存在mysql的user表里

赋予用户ua一个最高权限:

grant all privileges on *.* to 'ua'@'%' with grant option;

这个grant命令做了两个动作:分别将磁盘中的mysql.user表里将权限的字段都修改为Y,和内存中的acl_user中用户对应的对象将access值修改为‘全1'

Mysql复制表三种实现方法及grant解析

扫一扫手机访问