MySQL中用户权限导出的脚本

400次阅读

共计 5437 个字符，预计需要花费 14 分钟才能阅读完成。

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；才能够让授权生效。

第三步的测试过程为：