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'

如果有新的客户端使用用户名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语句可以用来重建内存数据,达到一致状态。

比如某时刻删除了数据表的记录,但是内存的数据还存在,导致了给用户赋权失败,因为在数据表中找不到记录。
同时重新创建这个用户也不行,因为在内存判断的时候,会认为这个用户还存在。

以上就是本文的全部内容,希望对大家的学习有所帮助,也希望大家多多支持我们。

(0)

相关推荐

  • Mysql将一个表中的某一列数据复制到另一个表中某一列里的方法

    mysql复制表中的一列到另一个表中 有时候,我们需要复制某个字段一整列的数据到另外一个新的字段中,这很简单,SQL可以这么写: UPDATE tb_1 SET content_target = content_source; 大概写法如下: Update {your_table} set {source_field} = {object_field} WHERE cause 有Navicat等工具更好,可以直接选中一列数据,拷贝粘贴到你需要的列中.如果是同一个表那没什么问题,如果是新表,请保持

  • mysql跨数据库复制表(在同一IP地址中)示例

    数据库表间数据复制分类 在利用数据库开发时,常常会将一些表之间的数据互相导入.当然可以编写程序实现,但是,程序常常需要开发环境,不方便.最方便是利用sql语言直接导入.既方便而修改也简单.以下就是导入的方法. 1. 表结构相同的表,且在同一数据库(如,table1,table2) Sql : 复制代码 代码如下: insert into table1 select   *    from table2 (完全复制)insert into table1 select   distinct   * 

  • MySQL查询结果复制到新表的方法(更新、插入)

    MySQL中可以将查询结果复制到另外的一张表中,复制的话通常有两种情况,一种是更新已有的数据,另一种是插入一条新记录.下面通过例子来说明.首先构建两个测试表. 表t1: 表t2: 1.如果t2表中存在score值,将score更新到t1表中.方法如下: UPDATE t1,t2 SET t1.score = t2.score WHERE t1.id = t2.id AND t2.score IS NOT NULL 这就是将查询结果作为条件更新另一张表,当然,t2也可以是更为复杂的一个查询结果而不

  • MySQL数据表字段内容的批量修改、清空、复制等更新命令

    最近遇到一点麻烦事,新安装的PHPwind6.0正式版社区在导入之前的会员帐号资料时,发现很多会员的mail地址貌似胡乱填写的,之前的PHPwind5.5版本没有开启mail地址验证功能,所以估计很多用户胡乱填写了email地址,所以我就想要求所有正式会员重新验证邮件地址来重新激活会员帐号,结果发现社区根本没有这项功能,挣扎了N久,数据库的会员资料数据表被反复安装=删除了好几遍,总算找到了一个批量修改的方法. 不过这样操作会连社区创建者的账号都改成未激活,所以要是不清楚还真的不敢动手. 在PHP

  • mysql把一个表某个字段的内容复制到另一张表的某个字段的SQL语句写法

    需求:把一个表某个字段内容复制到另一张表的某个字段. 实现sql语句1: 复制代码 代码如下: UPDATE file_manager_folder f1LEFT OUTER JOIN file_manager_folder f2     ON f1.name = f2.name AND f2.parentId = 54SET f1.parentId = 54 WHERE f2.name IS NULL AND f1.id IN (1,2,3); 实现sql语句2: 复制代码 代码如下: upd

  • mysql中复制表结构的方法小结

    mysql中用命令行复制表结构的方法主要有一下几种: 1.只复制表结构到新表 CREATE TABLE 新表 SELECT * FROM 旧表 WHERE 1=2 或者 CREATE TABLE 新表 LIKE 旧表 2.复制表结构及数据到新表 CREATE TABLE 新表 SELECT * FROM 旧表 3.复制旧表的数据到新表(假设两个表结构一样)  INSERT INTO 新表 SELECT * FROM 旧表 4.复制旧表的数据到新表(假设两个表结构不一样) INSERT INTO

  • MySQL快速复制数据库数据表的方法

    某些时候,例如为了搭建一个测试环境,或者克隆一个网站,需要复制一个已存在的mysql数据库.使用以下方法,可以非常简单地实现. 假设已经存在的数据库名字叫db1,想要复制一份,命名为newdb.步骤如下: 1. 首先创建新的数据库newdb #mysql -u root -ppassword mysql>CREATE DATABASE `newdb` DEFAULT CHARACTER SET UTF8 COLLATE UTF8_GENERAL_CI; 2. 使用mysqldump及mysql的

  • MySQL中表复制:create table like 与 create table as select

    复制代码 代码如下: CREATE TABLE A LIKE B 此种方式在将表B复制到A时候会将表B完整的字段结构和索引复制到表A中来. 复制代码 代码如下: CREATE TABLE A AS SELECT x,x,x,xx FROM B LIMIT 0 此种方式只会将表B的字段结构复制到表A中来,但不会复制表B中的索引到表A中来.这种方式比较灵活可以在复制原表表结构的同时指定要复制哪些字段,并且自身复制表也可以根据需要增加字段结构. 两种方式在复制表的时候均不会复制权限对表的设置.比如说原

  • 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

  • MySQL复制表的三种方式(小结)

    复制表结构及其数据 下面这个语句会拷贝数据到新表中. 注意:这个语句其实只是把select语句的结果建一个表,所以新表不会有主键,索引. create table table_name_new as (select * from table_name_old); 只复制表结构 create table table_name_new as select * from table_name_old where 1=2; 或者 create table table_name_new like tabl

  • Python操作MySQL数据库的三种方法总结

    1. MySQLdb 的使用 (1) 什么是MySQLdb? MySQLdb 是用于 Python 连接 MySQL 数据库的接口,它实现了 Python 数据库 API 规范 V2.0,基于 MySQL C API 上建立的. (2) 源码安装 MySQLdb: https://pypi.python.org/pypi/MySQL-python $ tar zxvf MySQL-python-*.tar.gz $ cd MySQL-python-* $ python setup.py buil

  • JavaScript复制变量三种方法实例详解

    这篇文章主要介绍了JavaScript复制变量三种方法实例详解,文中通过示例代码介绍的非常详细,对大家的学习或者工作具有一定的参考学习价值,需要的朋友可以参考下 直接将一个变量赋给另一个变量时,系统并不会创造一个新的变量,而是将原变量的地址赋给了新变量名.举个栗子: 复制代码 复制代码 let obj = { a: 1, b: 2, }; let copy = obj; obj.a = 5; console.log(copy.a); // Result // a = 5; // 更改obj的值,

  • MySQL 复制表的方法

    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-trans

  • 浅谈MySQL8.0 异步复制的三种方式

    本实验中分别针对空库.脱机.联机三种方式,配置一主两从的mysql标准异步复制.只做整服务器级别的复制,不考虑对个别库表或使用过滤复制的情况. 实验环境 [root@slave2 ~]# cat /etc/hosts 192.168.2.138 master 192.168.2.192 slave1 192.168.2.130 slave2 mysql> select version(); +-----------+ | version() | +-----------+ | 8.0.16 |

  • 安装MySQL常见的三种方式

    目录 安装MySQL的方式常见的有三种: rpm包形式 通用二进制形式 源码编译 1,rpm包形式 (1) 操作系统发行商提供的 (2) MySQL官方提供的(版本更新,修复了更多常见BUG)www.mysql.com/downloads 关于MySQL中rpm包类型的介绍: MySQL-client         客户端组件  MySQL-debuginfo      调试MySQL的组件  MySQL-devel          想针对于MySQL编译安装PHP等依赖于MySQL的组件包

  • SQL中的三种去重方法小结

    目录 distinct group by row_number 在使用SQL提数的时候,常会遇到表内有重复值的时候,比如我们想得到 uv (独立访客),就需要做去重. 在 MySQL 中通常是使用 distinct 或 group by子句,但在支持窗口函数的 sql(如Hive SQL.Oracle等等) 中还可以使用 row_number 窗口函数进行去重. 举个栗子,现有这样一张表 task: task_id order_id start_time 1 123 2020-01-05 1 2

  • Python写入MySQL数据库的三种方式详解

    目录 场景一:数据不需要频繁的写入mysql 场景二:数据是增量的,需要自动化并频繁写入mysql 方式一 方式二 总结 大家好,Python 读取数据自动写入 MySQL 数据库,这个需求在工作中是非常普遍的,主要涉及到 python 操作数据库,读写更新等,数据库可能是 mongodb. es,他们的处理思路都是相似的,只需要将操作数据库的语法更换即可. 本篇文章会给大家分享数据如何写入到 mysql,分为两个场景,三种方式. 场景一:数据不需要频繁的写入mysql 使用 navicat 工

  • Oracle、MySQL和SqlServe三种数据库分页查询语句的区别介绍

    先来定义分页语句将要用到的几个参数: int currentPage ; //当前页 int pageRecord ; //每页显示记录数 以之前的ADDRESSBOOK数据表为例(每页显示10条记录): 一.SqlServe下载 分页语句 String sql = "select top "+pageRecord +" * from addressbook where id not in (select top "+(currentPage-)*pageRecor

随机推荐