mysql中用户权限导出的脚本

mgjj

[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]#