MySQL中用户权限导出的脚本(2)

-- 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 |
+-------+-----------------+