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;
  1. 打开文件/server_tmp/t.csv，以制表符(\t)作为字段间的分隔符，以换行符（\n）作为记录之间的分隔符，进行数据读取；
  2. 启动事务。
  3. 判断每一行的字段数与表db2.t是否相同：
    • 若不相同，则直接报错，事务回滚；
    • 若相同，则构造成一行，调用InnoDB引擎接口，写入到表中。
  4. 重复步骤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，具体执行步骤：

  1. 执行create table r like t，创建一个相同表结构的空表，
  2. 执行alter table r discard tablespace，这时候r.ibd文件会被删除
  3. 执行flush table t for export这时候会生成一个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.ibd文件作为表r的新的表空间，由于这个文件的数据内容和t.ibd是相同的，所以表r中就有了和表t相同的数据。

这三种方法的优缺点

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

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

  3. 用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’

  • 如果有新的客户端使用用户名ua登录成功，mysql会为新连接维护一个线程对象，所有关于全局权限的判断，都是直接使用线程对象内部保存的权限位。

    • grant命令对于全局权限，同时更新了磁盘和相应的内存，接下来新创建的连接会使用新的权限
    • 对于已经存在的连接，它的全局权限不受grant的影响。

  • 如果要回收上面权限：

    • revoke all privileges on *.* from 'ua'@'%';

      同样也是相对应的两个操作，磁盘中权限字段修改位N，内存中对象的access的值修改位0。

mysqlDB权限

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

使用SELECT * FROM MYSQL.DB WHERE USER = 'UA'\G来查看当前用户的db权限，同样的也是对磁盘和内存中的对象修改权限。

db权限存储在mysql.db表中

注意：和全局权限不同，db权限会对已经存在的连接对象产生影响。

mysql表权限和列权限

表权限放在mysql.tables_priv中，列权限存放在mysql.columns_priv中，这两类权限组合起来存放在内存的hash结构column_priv_hash中。

跟db权限类似，这两个权限每次grant的时候都会修改数据表，也会同步修改内存中的hash结构，因此，这两类权限的操作，也会影响到已经存在的连接。

flush privileges的使用场景

有些文档里提到，grant之后马上执行flush privileges命令，才能使赋权语句生效。其实更准确的说法应该是在数据表中的权限跟内存中的权限数据不一致的时候，flush privileges语句可以用来重建内存数据，达到一致状态。

  • 比如某时刻删除了数据表的记录，但是内存的数据还存在，导致了给用户赋权失败，因为在数据表中找不到记录。
  • 同时重新创建这个用户也不行，因为在内存判断的时候，会认为这个用户还存在。
posted @ 2019-07-21 11:35  Jimmyhe  阅读(1060)  评论(0)  编辑  收藏  举报