MySQL 如何快速複制使用者權限到其他環境

腳本用途：

 1、mysql主從搭建，從庫使用者權限的複制（可直接在主庫執行腳本，将輸出結果在從庫執行）

 2、實作 pt-show-grants【列印權限資訊】（線上可選擇不用安裝pt軟體..通過腳本實作pt-show-grants功能）

 3、使用者權限統計

<b>腳本：</b>

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:-------" &gt;&gt;create_user.txt

     echo "select concat('show grants for ''',user,'''@''',host, ''';') from mysql.user where user &lt;&gt;'root' and user &lt;&gt;'' " | \

     mysql --host=$hostname --user=$username --password=$password $sock -n | \

     sed "s/$/;/"  &gt;&gt;create_user.txt

done

cat create_user.txt

rm -rf create_user.txt

<b></b>

<b>腳本列印資訊如下：</b>

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

<b>pt-show-grants 列印權限介紹</b>

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

【源于本人筆記】 若有書寫錯誤，表達錯誤，請指正...