zuntan02のはてなブログ

備忘録的なものです。時々職場の技術者ブログにも転記してますが、メインはこちらで。

【MySQL】レプリケーション時のスレーブでread_only設定するかどうか問題

【メモ】

MySQLレプリケーションするとき、間違ってスレーブを更新してしまわないよう、スレーブのmy.cnfに

[mysqld]
(省略)
read_only

を追記してる例を見た。今までやってなかった。slaveでupdateが無くもない人生なので、今後つけるかどうか検証

nippondanji.blogspot.jp
”スレーブを参照専用にするには、my.cnfファイルでread_onlyオプションを指定しておくと良い。read_onlyを指定しておけば、SUPER権限のないユーザは更新が出来なくなる。”
「SUPER権限を持つアカウント以外の更新クエリーは実行できなくなる」はず

【結論】

SUPER権限のあるユーザでやってしまえば更新処理ができちゃうけど、習慣としてはつけておくべき、とのこと。
ただ、フェイルオーバーを考えると、read_onlyはつけない方向で。
(Masterが死んだとき、hostsの向き先変更だけでSLAVEに書き込み開始したい)


【結論に至る調査メモ】

■SUPER権限って?
→SUPER権限の有無は以下で確認可能

mysql> SELECT user, Super_priv FROM mysql.user ;
+-----------+------------+
| user      | Super_priv |
+-----------+------------+
| root      | Y          |
| mysql.sys | N          |
| repl      | N          |
+-----------+------------+

→replはレプリケーションユーザだけど、必要ないの?
MySQL :: MySQL 5.6 リファレンスマニュアル :: 17.1.1.3 レプリケーション用ユーザーの作成
レプリケーションの目的にだけアカウントを作成する場合、そのアカウントには REPLICATION SLAVE 権限だけが必要です。”
ということらしい。

【試してみた】

Super権限じゃないユーザを作成
GRANT ALL PRIVILEGES ON `testdb`.* TO 'nosuper'@'localhost' IDENTIFIED BY 'パスワード';
FLUSH PRIVILEGES;


# マスターにnosuperユーザでログインし、insert
→insertできた

# スレーブにnosuperユーザでログインし、insert
→ERROR 1290 (HY000): The MySQL server is running with the --read-only option so it cannot execute this statement
→マスタでinsertした値はレプリケーションされている。


■その他大変ありがたい参考記事

http://blog.hypermkt.jp/my-conf_readony/