mysql中用户权限导出的脚本

最新推荐文章于 2022-10-14 16:17:48 发布
最新推荐文章于 2022-10-14 16:17:48 发布
阅读量6k 收藏 1
点赞数 1
分类专栏： shell脚本 MySQL数据库 文章标签： shell mysql 脚本 迁移
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/yumushui/article/details/50264123
版权


MySQL数据库备份和迁移，是DBA日常工作中，经常需要做的工作。备份一般由物理备份和逻辑备份两种，对于用xtrabackup进行物理备份来说，表数据和用户权限都会全部备份和恢复；如果是逻辑备份的话，用户数据和授权信息都需要单独的导出导入，用户数据直接用 mysqldump 工具导出导入即可，但mysql中用户权限不能直接导出导入，一个权限一个权限的处理，有比较麻烦。


所以编写一个mysql用户权限脚本，对源数据库中的用户授权信息进行导出，就非常必要了。只要有用户权限的授权sql，不论是平时备份，还是迁移时授权，都会很有用处。


mysql用户权限的获取，整体可以从mysql.user表来获取用户的相关权限来生成相应的SQL语句，然后在目标服务器上来执行生成的SQL语句即可。


1、创建导出用户权限的脚本
在源服务器上，创建一个用于导出用户权限的exp_grants.sh 脚本，脚本内容如下： 

#!/bin/bash  
#Function export user privileges  

source /etc/profile

pwd=123456  
expgrants()  
{  
  mysql -B -u'root' -p${pwd} -N -S /data/mysql_3306/tmp/mysql.sock $@ -e "SELECT CONCAT(  'SHOW GRANTS FOR ''', user, '''@''', host, ''';' ) AS query FROM mysql.user" | \
  mysql -u'root' -p${pwd} -S /data/mysql_3306/tmp/mysql.sock $@ | \
  sed 's/\(GRANT .*\)/\1;/;s/^\(Grants for .*\)/-- \1 /;/--/{x;p;x;}'  
}  

expgrants > ./grants.sql



这个导出脚本的整体思路是：分成三个步骤，用管道连接起来，前一步操作的结果，作为后一步操作的输入：
第一步先使用concat函数，查询mysql.user中的用户授权，并连接成一个show grants for 命令，执行命令时，加上 "-B -N"选项，让输出结果没有列名和方框，只是命令；
第二步将上一步的show grants for 命令，再次执行一次，得出mysql中每个授权对应的具体的GRANT授权命令；
第三步使用sed和正则表达式，将上一步的运行结果进行处理，在show grant for 行中加上注释前缀和一个空行，在具体授权的GRANT行中结尾加一个分号，形成可以执行的sql授权命令。
其中正则表达式添加和修改如下，红色为选择行条件，紫色为添加和修改的内容：
  sed 's/ \(GRANT .*\) /\1;/ ; s/ ^\(Grants for .*\) / --  \1 /;/ --/{x;p;x;} '   



2、在源服务器上执行上面脚本，生成授予权限的SQL脚本

# ./exp_grants.sh

# cat grants.sql


上面两个步骤操作在我的测试环境中为： 

[root@test07 tmp]# > grants.sql 
[root@test07 tmp]# 
[root@test07 tmp]# 
[root@test07 tmp]# cat exp_grants.sh 
#!/bin/bash  
#Function export user privileges  

source /etc/profile

pwd=123456  
expgrants()  
{  
  mysql -B -u'root' -p${pwd} -N -S /data/mysql_3306/tmp/mysql.sock $@ -e "SELECT CONCAT(  'SHOW GRANTS FOR ''', user, '''@''', host, ''';' ) AS query FROM mysql.user" | \
  mysql -u'root' -p${pwd} -S /data/mysql_3306/tmp/mysql.sock $@ | \
  sed 's/\(GRANT .*\)/\1;/;s/^\(Grants for .*\)/-- \1 /;/--/{x;p;x;}'  
}  

expgrants > ./grants.sql  
[root@test07 tmp]# 
[root@test07 tmp]# 
[root@test07 tmp]# ./exp_grants.sh 
[root@test07 tmp]# 
[root@test07 tmp]# 
[root@test07 tmp]# cat grants.sql 

-- Grants for root@127.0.0.1 
GRANT ALL PRIVILEGES ON *.* TO 'root'@'127.0.0.1' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' WITH GRANT OPTION;

-- Grants for wgphp@192.168.%.% 
GRANT USAGE ON *.* TO 'wgphp'@'192.168.%.%';
GRANT SELECT, INSERT, UPDATE, EXECUTE ON `test`.* TO 'wgphp'@'192.168.%.%';

-- Grants for root@192.168.226.163 
GRANT ALL PRIVILEGES ON *.* TO 'root'@'192.168.226.163' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9';

-- Grants for test@192.168.226.163 
GRANT USAGE ON *.* TO 'test'@'192.168.226.163' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9';
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER ON `test04`.* TO 'test'@'192.168.226.163';
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER ON `test03`.* TO 'test'@'192.168.226.163';
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER ON `test01`.* TO 'test'@'192.168.226.163';
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER ON `test02`.* TO 'test'@'192.168.226.163';

-- Grants for root@::1 
GRANT ALL PRIVILEGES ON *.* TO 'root'@'::1' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' WITH GRANT OPTION;

-- Grants for root@localhost 
GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' WITH GRANT OPTION;
GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION;

-- Grants for test@localhost 
GRANT USAGE ON *.* TO 'test'@'localhost' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9';
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER ON `test04`.* TO 'test'@'localhost';
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER ON `test01`.* TO 'test'@'localhost';
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER ON `test02`.* TO 'test'@'localhost';
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER ON `test03`.* TO 'test'@'localhost';
[root@test07 tmp]#



3、在目标服务器上执行授予权限的SQL脚本，进行授权
将生成的脚本在目标服务器上执行即可：
 mysql -uname -ppwd < grants.sql

或者先登录到mysql客户端，在用source命令执行：
mysql -uname -ppwd -S /data/mysql_3306/tmp/mysql.sock
mysql> source /tmp/grants.sql
mysql> flush privileges;



需要注意：
a、目标服务上为非空服务器，已经存在一些账户及权限应考虑会覆盖的问题。
b、如果仅仅需要迁移非root用户，可以在原脚本中添加过滤条件，即 where user<>'root' 。
c、由于测试环境中mysql的端口、socket、用户密码等不一定是默认配置，mysql连接时需要根据情况修改。
d、第三步在目标服务器上执行了授予权限的sql脚本后，记得要执行 flush privileges； 才能够让授权生效。



第三步的测试过程为： 


[root@test07 tmp]# 
[root@test07 tmp]# 
[root@test07 tmp]# mysql -B -u'root' -p123456 -N -S /data/mysql_3306/tmp/mysql.sock -e "select user,host from mysql.user;"
root    127.0.0.1
wgphp   192.168.%.%
root    192.168.226.163
test    192.168.226.163
root    ::1
root    localhost
test    localhost
[root@test07 tmp]# 
[root@test07 tmp]# mysql -u'root' -p123456  -S /data/mysql_3306/tmp/mysql.sock -e "select user,host from mysql.user;"     
+-------+-----------------+
| user  | host            |
+-------+-----------------+
| root  | 127.0.0.1       |
| wgphp | 192.168.%.%     |
| root  | 192.168.226.163 |
| test  | 192.168.226.163 |
| root  | ::1             |
| root  | localhost       |
| test  | localhost       |
+-------+-----------------+
[root@test07 tmp]# 
[root@test07 tmp]# mysql -u'root' -p123456  -S /data/mysql_3306/tmp/mysql.sock -e "delete from mysql.user where user<>'root';select user,host from mysql.user;"      
+------+-----------------+
| user | host            |
+------+-----------------+
| root | 127.0.0.1       |
| root | 192.168.226.163 |
| root | ::1             |
| root | localhost       |
+------+-----------------+
[root@test07 tmp]# 
[root@test07 tmp]# 
[root@test07 tmp]# mysql -u'root' -p123456  -S /data/mysql_3306/tmp/mysql.sock < /tmp/grants.sql
[root@test07 tmp]# 
[root@test07 tmp]# mysql -u'root' -p123456  -S /data/mysql_3306/tmp/mysql.sock -e "flush privileges;select user,host from mysql.user;"                                        
+-------+-----------------+
| user  | host            |
+-------+-----------------+
| root  | 127.0.0.1       |
| wgphp | 192.168.%.%     |
| root  | 192.168.226.163 |
| test  | 192.168.226.163 |
| root  | ::1             |
| root  | localhost       |
| test  | localhost       |
+-------+-----------------+


yumushui
关注
专栏目录
mysql用户权限导出
xuexiyangaaa的博客
10-14 706
shell脚本导出mysql用户权限
mysql设置用户禁止数据导出_mysql 常用用户权限管理和数据导入导出命令
weixin_42719781的博客
02-03 2112
一 、数据导入导出mysql将查询结果导出csv文件into outfile例句：select * from table_name into outfile '/tmp/tmp.csv' fields terminated by ',';详解：① into outfile '/tmp/tmp.csv' 指定导出文件的目录和文件名,注意，这里导出的数据文件是存放在数据库服务器所在的机器② field...
MySQL中导出用户权限设置的脚本
weixin_30925411的博客
12-01 113
在对MySQL数据库进行迁移的时候，有时候也需要迁移源数据库内的用户与权限。对于这个迁移我们可以从mysql.user表来获取用户的相关权限来生成相应的SQL语句，然后在目标服务器上来执行生成的SQL语句即可。本文提供了两个生成提取用户权限的脚本并给出演示。 说明：mysql中直接通过授权即可使用对应用户，不必使用创建用户命令（如CREATE USER 'xxx'@'%'...
mysql user导出权限_mysql快速导出user权限
weixin_34336068的博客
02-07 192
总是有各种各样的mysql数据库用户权限的导出，在此总结一条命令，快速导出，提高效率。 echo select concat(show grants for ,user,@,host, ;') from mysql.user where user like XXX_%|/usr/local/mysql/bin/mysql -u$user -P$port -p$pass |grep -v ^conc...
mysql 导入导出；创建用户
Tower_SHR的博客
10-19 226
文章目录DBA命令创建用户授权导出数据导入数据 DBA命令 创建用户 create user 用户名 identified by ‘密码’; 授权 mysql> grant all privileges on dbname.tbname to ‘yangxin’@’%’ identified by ‘yangxin123456’ with grant option; all privileges：表示将所有权限授予给用户。也可指定具体的权限，如：SELECT、CREATE、DROP等。【粗粒度
高级DBA教你MYSQL导出所有用户权限shell脚本实例觉得有效
nasen512的博客
10-14 1023
MYSQL导出所有用户权限shell脚本实例觉得有效。
Mysql导出sql脚本的方式（亲测有效）
疏雨过的博客
04-11 8670
1：进入到数据库的Bin目录：D： cd MySQL\mysql-5.6.24-winx64\bin 2：运行下面这句话， mysql dump -h 127.0.0.1 -u root -p 数据库 名 &gt; 命名.sql root是用户名。 如图： 3.去D:\MySQL\mysql-5.6.24-winx64\bin这个目录下就可以看到你的sql脚本了...
MySQL中导出用户权限设置的脚本分享
09-10
主要介绍了MySQL中导出用户权限设置的脚本分享,本文通过导出mysql.user表中数据实现导出权限设置,需要的朋友可以参考下
mysql导出所有用户权限脚本
jc_benben的专栏
08-17 1812
#!/bin/bash #Function export user privileges exp_grants() { mysql -B -u'root' -p'123456' -N $@ -e "SELECT CONCAT( 'SHOW GRANTS FOR ''', user, '''@''', host, ''';' ) AS query FROM mysql.user
Navicat for MySQL导出表结构脚本的简单方法
09-09
2. 导出脚本的目的与应用场景 - **备份**：定期导出表结构脚本可以作为数据库的备份，以防数据丢失或系统故障。 - **迁移**：当需要将数据库从一个环境迁移到另一个环境时，导出的脚本可以用于在新环境中重建相同...
MySQL数据库的用户权限使用和数据的备份和恢复
Enweitech Software Works
11-10 3751
数据库的操作，表的操作，很重要，但是我感觉对于数据库的安全才是最重要的，来看看该怎么管理好自己的数据库吧，该注意的地方已经做了注释，希望大家可以好好学习。 在本机设置数据库管理员初始密码 [root@xu mysql]# mysqladmin -hlocalhost -uroot password "123" 修改密码 （要知道之前的密码） [r
mysql 用户操作_MySQL基础操作----用户操作
weixin_34933151的博客
02-27 397
一,查询数据库已有的用户mysql>use mysql;mysql> select user,host fromuser;+------------------+-----------+| user | host |+------------------+-----------+| debian-sys-maint | localhost || mys...
【MySQL基础篇】数据导出导入权限与local_infile参数
Sebastien23的博客
02-17 9963
【MySQL基础篇】数据导出导入权限与local_infile参数问题背景数据导出测试创建测试库（在主库进行）测试数据导出（在从库进行）测试数据导入（在主库进行） 问题背景 MySQL高可用集群架构中，应用需要使用select ... into outfile和load data [local] infile来进行数据导入导出操作。其中，数据导出（只涉及读操作）发生在只读Slave节点，通过localhost连入数据库；数据导入（涉及读写操作）发生在Master节点，通过集群vip连入数据库。 DB
从sqlserver到mysql数据导出后导入脚本（实测，实用）
最新发布
04-26
4. **权限和用户管理**：MySQL和SQL Server的权限模型有差异，迁移时需创建相应的MySQL用户并分配权限。 5. **约束处理**：外键约束在MySQL中处理方式与SQL Server略有不同，导入前可能需要暂时禁用外键检查，导入...
PHP运维管理
热门推荐
linking530的专栏
04-03 2万+
<?php $password = "osyunwei";//设置密码 error_reporting(E_ERROR); header("content-Type: text/html; charset=gb2312"); set_time_limit(0); function Root_GP(&$array) { while(list($key,$var) = each($array)
导出MySQL用户权限
乐沙弥的世界
10-15 7580
在对MySQL数据库进行迁移的时候，有时候也需要迁移源数据库内的用户与权限。对于这个迁移我们可以从mysql.user表来获取用户的相关权限来生成相应的SQL语句，然后在目标服务器上来执行生成的SQL语句即可。本文提供了生成提取用户权限的脚本并给出演示。
MySQL 查看用户授予的权限与删除（好文章！！）
HD243608836的博客
11-17 1648
在MySQL中，如何查看一个用户被授予了那些权限呢？ 授予用户的权限可能分全局层级权限、数据库层级权限、表层级别权限、列层级别权限、子程序层级权限。具体分类如下： 全局层级 全局权限适用于一个给定服务器中的所有数据库。这些权限存储在mysql.user表中。GRANT ALL ON *.*和REVOKE ALL ON *.*只授予和撤销全局权限。 数据库层级 数据库权限适用于一个给定数据库中的所有目标。这些权限存储在mysql.db和mysql.host表中。GRANT ALL ON db_n.
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值