Mysql复制表三种实现方法及grant解析
作者:Jimmyhe 发布时间:2024-01-13 06:03:23
如何快速的复制一张表
首先创建一张表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语句可以用来重建内存数据,达到一致状态。
比如某时刻删除了数据表的记录,但是内存的数据还存在,导致了给用户赋权失败,因为在数据表中找不到记录。
同时重新创建这个用户也不行,因为在内存判断的时候,会认为这个用户还存在。
来源:https://www.cnblogs.com/jimmyhe/p/11220664.html
猜你喜欢
- 步骤一:下载对应的CURL压缩包并在windows上配置好环境变量进入CURL官网下载对应的windows压缩包。地址:点击打开链接把下载好
- 用pytesseract识别图片中的数字Win 平台 使用步骤一、安装包。二、找个图片,运行如下识别程序。示例程序:import pytes
- 0. 学习目标我们已经知道算法是具有有限步骤的过程,其最终的目的是为了解决问题,而根据我们的经验,同一个问题的解决方法通常并非唯一。这就产生
- 平常在使用python命令过程中,基本上都是用来安装python库时才使用到在控制台的python命令。然而,python命令还有更多的妙用
- 删除表数据操作清空所有表记录:TRUNCATE TABLE your_table_name;或者批量删除满足条件的表记录:BEGIN &nb
- 一个小的解决方法分享:正常安装的情况下,你所需要的包都能在python文件夹下找到,找到你所需要的包 ,把它复制到Python35\Lib\
- BluePrint是一个非常成熟也非常流行的CSS框架,很多网站和wordpress基于Blueprint搭建前端结构。最近,bluepri
- 目录循环加判断retrying我们在程序开发中,经常会需要请求一些外部的接口资源,而且我们不能保证每次请求一定会成功,所以这些涉及到网络请求
- 之前写了Python实现登录接口的示例代码,最近需要回顾,就顺便发到随笔上了要求:1.输入用户名和密码2.认证成功,显示欢迎信息3.用户名3
- 本文实例讲述了Python基于Socket实现的简单聊天程序。分享给大家供大家参考,具体如下:需求:SCIENCE 和MOOD两个人软件专业
- 全局变量与局部变量# num1是全局变量num1 = 1# num2是局部变量def func():num2 = 2在函数外(且不在函数里)
- 具体代码如下:Function ASTCreateFtpSite(IPAddress, RootDirectory,&n
- 基本语句结构if 判断条件1: 执行语句1……elif 判断条件2:
- 最近很多小伙伴在尝鲜chatGPT,使用中遇到网站的1020的错误码,博主也遇到了相似的问题,不同的人运行环境不一样,可能解决方案不一样,接
- 前提:我训练的是二分类网络,使用语言为pytorchVaribale包含三个属性:data:存储了Tensor,是本体的数据grad:保存了
- REPLACE语法REPLACE(String,from_str,to_str)即:将String中所有出现的from_str替换为to_s
- 引言解释器环境:python3.5.1我们都知道python网络编程的两大必学模块socket和socketserver,其中的socket
- 前言在本次python文章中,主要通过定义一个排序方法,实现一组数列能够按照另一组数列指定的位置进行重新排序输出,默认正序排序,可通过Tru
- python的列表list可以用for循环进行遍历,实际开发中发现一个问题,就是遍历的时候删除会出错,例如l = [1,2,3,4]for
- 简介:本文介绍了图像检索的三种实现方式,均用python完成,其中前两种基于直方图比较,哈希法基于像素分布。 检索方式是:提前导入图片库作为