ITPub博客

首页 > 数据库 > MySQL > mysql中用户权限导出的脚本

mysql中用户权限导出的脚本

原创 MySQL 作者:天空的鹰 时间:2015-12-11 15:26:13 0 删除 编辑

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


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


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


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



点击(此处)折叠或打开

  1. #!/bin/bash
  2. #Function export user privileges

  3. source /etc/profile

  4. pwd=123456
  5. expgrants()
  6. {
  7.   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" | \
  8.   mysql -u'root' -p${pwd} -S /data/mysql_3306/tmp/mysql.sock $@ | \
  9.   sed 's/\(GRANT .*\)/\1;/;s/^\(Grants for .*\)/-- \1 /;/--/{x;p;x;}'
  10. }

  11. 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


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


点击(此处)折叠或打开

  1. [root@test07 tmp]# > grants.sql
  2. [root@test07 tmp]#
  3. [root@test07 tmp]#
  4. [root@test07 tmp]# cat exp_grants.sh
  5. #!/bin/bash
  6. #Function export user privileges

  7. source /etc/profile

  8. pwd=123456
  9. expgrants()
  10. {
  11.   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" | \
  12.   mysql -u'root' -p${pwd} -S /data/mysql_3306/tmp/mysql.sock $@ | \
  13.   sed 's/\(GRANT .*\)/\1;/;s/^\(Grants for .*\)/-- \1 /;/--/{x;p;x;}'
  14. }

  15. expgrants > ./grants.sql
  16. [root@test07 tmp]#
  17. [root@test07 tmp]#
  18. [root@test07 tmp]# ./exp_grants.sh
  19. [root@test07 tmp]#
  20. [root@test07 tmp]#
  21. [root@test07 tmp]# cat grants.sql

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

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

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

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

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

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

  40. -- Grants for test@localhost
  41. GRANT USAGE ON *.* TO 'test'@'localhost' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9';
  42. GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER ON `test04`.* TO 'test'@'localhost';
  43. GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER ON `test01`.* TO 'test'@'localhost';
  44. GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER ON `test02`.* TO 'test'@'localhost';
  45. GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER ON `test03`.* TO 'test'@'localhost';
  46. [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; 才能够让授权生效。



第三步的测试过程为:

点击(此处)折叠或打开

  1. [root@test07 tmp]#
  2. [root@test07 tmp]#
  3. [root@test07 tmp]# mysql -B -u'root' -p123456 -N -S /data/mysql_3306/tmp/mysql.sock -e "select user,host from mysql.user;"
  4. root 127.0.0.1
  5. wgphp 192.168.%.%
  6. root 192.168.226.163
  7. test 192.168.226.163
  8. root ::1
  9. root localhost
  10. test localhost
  11. [root@test07 tmp]#
  12. [root@test07 tmp]# mysql -u'root' -p123456 -S /data/mysql_3306/tmp/mysql.sock -e "select user,host from mysql.user;"
  13. +-------+-----------------+
  14. | user | host |
  15. +-------+-----------------+
  16. | root | 127.0.0.1 |
  17. | wgphp | 192.168.%.% |
  18. | root | 192.168.226.163 |
  19. | test | 192.168.226.163 |
  20. | root | ::1 |
  21. | root | localhost |
  22. | test | localhost |
  23. +-------+-----------------+
  24. [root@test07 tmp]#
  25. [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;"
  26. +------+-----------------+
  27. | user | host |
  28. +------+-----------------+
  29. | root | 127.0.0.1 |
  30. | root | 192.168.226.163 |
  31. | root | ::1 |
  32. | root | localhost |
  33. +------+-----------------+
  34. [root@test07 tmp]#
  35. [root@test07 tmp]#
  36. [root@test07 tmp]# mysql -u'root' -p123456 -S /data/mysql_3306/tmp/mysql.sock < /tmp/grants.sql
  37. [root@test07 tmp]#
  38. [root@test07 tmp]# mysql -u'root' -p123456 -S /data/mysql_3306/tmp/mysql.sock -e "flush privileges;select user,host from mysql.user;"
  39. +-------+-----------------+
  40. | user | host |
  41. +-------+-----------------+
  42. | root | 127.0.0.1 |
  43. | wgphp | 192.168.%.% |
  44. | root | 192.168.226.163 |
  45. | test | 192.168.226.163 |
  46. | root | ::1 |
  47. | root | localhost |
  48. | test | localhost |
  49. +-------+-----------------+


来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/24638123/viewspace-1870495/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
多年数据库运维和架构经验,对 MySQL、Oracle、PostgreSQL、Greenplum、MongoDB 等多种常见数据库有丰富的运维实践经验,擅长数据库架构设计、维护优化、数据流转、Shell 和 Python 开发;乐于技术交流,网名 yumushui 进行了大量的技术总结和思考分享。

注册时间:2015-05-21

  • 博文量
    18
  • 访问量
    42877