hostname=192.168.1.12
username=lottery
password=lottery
touch CREATE_user.txt
for port in `ps -ef | grep mysql| grep socket| grep datadir| awk -F= '{print $NF}'`
do
sock=`ps -ef | grep mysql| grep socket| grep datadir|awk -F".pid" '{print $NF}'| grep $port`
echo "-------"$port"-create_user.sql:-------" >>CREATE_user.txt
echo "select concat('show grants for ''',user,'''@''',host, ''';') from mysql.user where user <>'root' and user <>'' " | \
mysql --host=$hostname --user=$username --password=$password $sock -N | \
mysql --host=$hostname --user=$username --password=$password $sock -N | \
sed "s/$/;/" >>CREATE_user.txt
done
cat CREATE_user.txt
rm -rf CREATE_user.txt
指令碼列印資訊如下：
-------3306-create_user.sql:-------
GRANT ALL PRIVILEGES ON *.* TO 'lottery'@'192.168.%' IDENTIFIED BY PASSWORD '*6E9EF176ABE11111146AF84111111171111111';
GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'repl'@'192.168.1.%' IDENTIFIED BY PASSWORD '*AC111111FDDC8943AB31CBD111111E79F7953EA';
GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'repl'@'192.168.2%' IDENTIFIED BY PASSWORD '*AC2111111FDDC8943A11111147D758E79F7953EA';
GRANT USAGE ON *.* TO 'zabbix'@'localhost' IDENTIFIED BY PASSWORD '*6BB4837EB74329101111118DDA7DC61111112AD9';
-------3308-create_user.sql:-------
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%' IDENTIFIED BY PASSWORD '*AC241830FFDDC11111131CBD47D758E79111111A';
GRANT ALL PRIVILEGES ON *.* TO 'lottery'@'192.168.%' IDENTIFIED BY PASSWORD '*6E9EF111111590A33746AF84DB6348D171111111';
GRANT SELECT, INSERT, UPDATE, DELETE ON *.* TO 'mycat'@'192.168.1.%' IDENTIFIED BY PASSWORD '*6EA111111950B4A6BFC111111925FD11111147A42';
GRANT SELECT, INSERT, UPDATE, DELETE ON *.* TO 'mycat_user'@'192.168.1%' IDENTIFIED BY PASSWORD '*6EAF111111B4A6BFC92BA04111111086111111112';
GRANT SHUTDOWN ON *.* TO 'admin'@'localhost' IDENTIFIED BY PASSWORD '*4E74B1831111113952F4C163CB961DFBC5111111';
[root@MIU-MYSQL-002 ~]#
pt-show-grants 列印許可權介紹
l 功能介紹：
規範化和列印 mysql許可權，讓你在複製、比較 mysql許可權以及進行版本控制的時候更有效率！
l 用法介紹：
pt-show-grants [OPTION...] [DSN] 選項自行用help 檢視，DSN 選項也請檢視help，選項區分大小寫。
l 使用示例：
檢視指定mysql的所有使用者許可權： pt-show-grants --host='localhost' --user='root' --password='zhang@123'
檢視執行資料庫的許可權： pt-show-grants --host='localhost' --user='root' --password='zhang@123' --database='hostsops'
檢視每個使用者許可權生成 revoke收回許可權的語句： pt-show-grants --host='localhost' --user='root' --password='zhang@123' --revoke