はじめに
MySQL のサーバがたくさんあって、ユーザや権限がまちまちだったり、ちょっとずつ違う状態だったりするのを是正するために、全DBサーバから全アカウントと権限を抽出してみた。
DB機だけで60台くらいあるんで。。。
これだけ
$ mysql -u root -p -h server -N -e 'select concat("SHOW GRANTS FOR `",user, "`@`",host, "`;") from mysql.user order by user,host;' > showgrants.sql
$ mysql -u root -p -h server -N < showgrants.sql > createuser.sql
まぁSQLでSQL作って実行するだけなんだけどね。
$ cat showgrants.sql
SHOW GRANTS FOR `hoge`@`10.10.10.%`;
SHOW GRANTS FOR `munin`@`localhost`;
SHOW GRANTS FOR `root`@`localhost`;
SHOW GRANTS FOR `zabbix`@`localhost`;
あ、もちろんはしょってあります。
$ cat king-dbm01/createuser.sql
GRANT USAGE ON *.* TO 'hoge'@'10.10.10.%' IDENTIFIED BY PASSWORD 'パスワードな文字列'
GRANT SELECT, PROCESS, SUPER, REPLICATION CLIENT ON *.* TO 'munin'@'localhost' IDENTIFIED BY PASSWORD '*CF5C09FAD38AFB92F43C9BEE1A0F69CE3736770D' WITH GRANT OPTION
GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD 'パスワードな文字列' WITH GRANT OPTION
GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION
GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'zabbix'@'localhost' IDENTIFIED BY PASSWORD 'パスワードな文字列'
- シェルでサーバ毎にぶん回して（.my.cnf 書いてパスワード設定して -p とか外して）サーバ名/ファイル、みたいな感じで全部抽出した。
- 最初の SQL で user, host でソートしてるので、diff とるのも簡単。
- mysql.user からユーザ一覧作るだけでは漏れちゃう権限（FILEとか後付するようなやつ）もちゃんとチェックできた（漏れてた。。。_no
- -N でヘッダ消すのがミソ
- user,host を `` で括ることで無名アカウントとかもちゃんと処理できるのもGOOD
ずっと後でまた使うかもしれないから qiita にメモってことで。
検証求む
うちのDB機ではこれで問題なく全部チェックできたと思うんだけど、何か問題とかおかしいとことかあったらコメントで教えてくれるとうれしす。
おまけ
mysqldump
$ mysqldump -u root -p -x --allow-keywords mysql > test.dump
こういうのだと、いらんテーブル情報もごっそり入ってるし、MySQLのバージョン違いで困る（mysql_upgradeとかあるけど）し、なによりそもそも権限周りのチェックには使えない。
select user,host,password from mysql.user
ユーザは一覧できるけど、この後 show grants 文作るのも１つずつ投げるのも面倒だったので作ったって感じ。