MySQL数据库备份和迁移，是DBA日常工作中，经常需要做的工作。备份一般由物理备份和逻辑备份两种，对于用xtrabackup进行物理备份来说，表数据和用户权限都会全部备份和恢复；如果是逻辑备份的话，用户数据和授权信息都需要单独的导出导入，用户数据直接用 mysqldump 工具导出导入即可，但mysql中用户权限不能直接导出导入，一个权限一个权限的处理，有比较麻烦。
所以编写一个mysql用户权限脚本，对源数据库中的用户授权信息进行导出，就非常必要了。只要有用户权限的授权sql，不论是平时备份，还是迁移时授权，都会很有用处。
mysql用户权限的获取，整体可以从mysql.user表来获取用户的相关权限来生成相应的SQL语句，然后在目标服务器上来执行生成的SQL语句即可。

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

[python]  view plain  copy
  1. #!/bin/bash    
  2. #Function export user privileges    
  3.   
  4. source /etc/profile  
  5.   
  6. pwd=123456    
  7. expgrants()    
  8. {    
  9.   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" | \  
  10.   mysql -u'root' -p${pwd} -S /data/mysql_3306/tmp/mysql.sock $@ | \  
  11.   sed 's/GRANT.∗GRANT.∗/\1;/;s/^Grantsfor.∗Grantsfor.∗/-- \1 /;/--/{x;p;x;}'    
  12. }    
  13.   
  14. 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.∗GRANT.∗ /\1;/ ; s/ ^Grantsfor.∗Grantsfor.∗ / --  \1 /;/ --/{x;p;x;} '   

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

# ./exp_grants.sh

# cat grants.sql


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

[html]  view plain  copy
  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.   
  8. source /etc/profile  
  9.   
  10. pwd=123456    
  11. expgrants()    
  12. {    
  13.   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" | \  
  14.   mysql -u'root' -p${pwd} -S /data/mysql_3306/tmp/mysql.sock $@ | \  
  15.   sed 's/GRANT.∗GRANT.∗/\1;/;s/^Grantsfor.∗Grantsfor.∗/-- \1 /;/--/{x;p;x;}'    
  16. }    
  17.   
  18. expgrants > ./grants.sql    
  19. [root@test07 tmp]#   
  20. [root@test07 tmp]#   
  21. [root@test07 tmp]# ./exp_grants.sh   
  22. [root@test07 tmp]#   
  23. [root@test07 tmp]#   
  24. [root@test07 tmp]# cat grants.sql   
  25.   
  26. -- Grants for root@127.0.0.1   
  27. GRANT ALL PRIVILEGES ON *.* TO 'root'@'127.0.0.1' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' WITH GRANT OPTION;  
  28.   
  29. -- Grants for wgphp@192.168.%.%   
  30. GRANT USAGE ON *.* TO 'wgphp'@'192.168.%.%';  
  31. GRANT SELECT, INSERT, UPDATE, EXECUTE ON `test`.* TO 'wgphp'@'192.168.%.%';  
  32.   
  33. -- Grants for root@192.168.226.163   
  34. GRANT ALL PRIVILEGES ON *.* TO 'root'@'192.168.226.163' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9';  
  35.   
  36. -- Grants for test@192.168.226.163   
  37. GRANT USAGE ON *.* TO 'test'@'192.168.226.163' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9';  
  38. GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER ON `test04`.* TO 'test'@'192.168.226.163';  
  39. GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER ON `test03`.* TO 'test'@'192.168.226.163';  
  40. GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER ON `test01`.* TO 'test'@'192.168.226.163';  
  41. GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER ON `test02`.* TO 'test'@'192.168.226.163';  
  42.   
  43. -- Grants for root@::1   
  44. GRANT ALL PRIVILEGES ON *.* TO 'root'@'::1' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' WITH GRANT OPTION;  
  45.   
  46. -- Grants for root@localhost   
  47. GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' WITH GRANT OPTION;  
  48. GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION;  
  49.   
  50. -- Grants for test@localhost   
  51. GRANT USAGE ON *.* TO 'test'@'localhost' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9';  
  52. GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER ON `test04`.* TO 'test'@'localhost';  
  53. GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER ON `test01`.* TO 'test'@'localhost';  
  54. GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER ON `test02`.* TO 'test'@'localhost';  
  55. GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER ON `test03`.* TO 'test'@'localhost';  
  56. [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； 才能够让授权生效。



第三步的测试过程为：


[html]  view plain  copy
  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. +-------+-----------------+