MySQL のレプリケーションを使うと、負荷分散、ホットスタンバイ、データのバックアップといったことが、簡単に実現できます。MySQLの中でも特に人気のある機能ではないでしょうか。この便利なレプリケーションを使うには、そこそこの設定が必要です。そしてよく忘れます(^^;) そこで今回は、マスターサーバの設定から、スレーブサーバを構築するまでの手順をメモしておきました。
MySQL サーバ構成
ごく単純な、マスター１台、スレーブ１台の構成です。サーバOSは CentOS7.1.1503 MySQLのバージョンは 5.6.23 です。
MySQL マスターサーバの設定
マスターサーバの my.cnf 設定
vi /etc/my.cnf
# 各MySQLサーバを識別するためのIDの設定
server-id=1
# バイナリログの有効化
log_bin=mysql-bin
# バイナリログをコミットと同時にディスクに書込む
sync_binlog=1
# クラッシュセーフなレプリケーション設定
relay_log_info_repository=TABLE
relay_log_recovery=ON
#----- レプリケーション関連の設定はここまでです -----#
# 基本設定
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
symbolic-links=0
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
# 文字コードの設定
skip-character-set-client-handshake
character-set-server = utf8
# スロークエリログの設定
slow_query_log=1
slow_query_log_file=/var/log/mysql/slow_query.log
long_query_time=1
min_examined_row_limit=1000
[mysqld_safe]
log-error=/var/log/mysql/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
※ログの出力先を /var/log/mysql/ 以下に変更しています。
MySQLを再起動して、設定を反映します。
レプリケーション関連設定の詳細
server-id=1
他のMySQLサーバの server-id と、かぶらないように設定します。指定できる値は 1 から 4294967295（2の32乗 - 1）です。
マスターおよび各スレーブでは、--server-id オプションを使用して、範囲が 1 から 232 − 1 の一意レプリケーション ID を確立する必要があります。「一意」とは、各 ID が、ほかのレプリケーションマスターまたはスレーブで使用されるほかのあらゆる ID と異なっている必要があるということです。たとえば、server-id=3。
MySQL 5.6 リファレンスマニュアル より引用
log_bin=mysql-bin
バイナリログを有効にします。バイナリログには、実行したSQLなど、DBの更新情報が含まれています。このバイナリログを、スレーブサーバに転送することによって、データ同期を実現していますので、レプリケーションをするためには必須の設定です。また、引数でバイナリログ名を指定できます。指定しない場合は、ホスト名がバイナリログ名の一部に使われます。
sync_binlog=1
「sync_binlog=1」にすると、バイナリログの整合性、すなわちマスター/スレーブ間の整合性を高められる反面、以前のバージョンではサーバへの負荷が大きいため、この設定を断念することもありました。MySQL5.6では、この負荷がかなり軽減されたようです。(^^)/
従来は複数トランザクションからコミットが実行されると、それぞれのトランザクションの内容を1つ1つ順番にディスクにフラッシュする必要がありました。MySQL 5.6では、グループコミットにより複数トランザクションの情報をまとめて1回のI/Oでディスクにフラッシュできるようになっているため、sync_binlog=1設定時のオーバーヘッドを大幅に削減できています。
MySQL 5.6での機能強化点（その3）- 人気のレプリケーションが更に機能強化 より引用
relay_log_info_repository=TABLE
relay_log_recovery=ON
マスター/スレーブサーバの障害発生時に、復旧作業の手間を軽減できます。MySQL 5.6.17 以降を使っているのであれば、この機能を使わない手はありませんね。
このように、クラッシュセーフでないことが、レプリケーションを運用する上で最大のリスクであった。だが、MySQL 5.6ではそれが解消されているわけである。もしまだMySQL 5.5以前のバージョンでレプリケーションを使用しているのであれば、ぜひMySQL 5.6へのアップグレードを検討して頂きたい。クラッシュ時の復旧作業の手間がガラリと変わるはずだ。
漢(オトコ)のコンピュータ道 より引用
レプリケーション用ユーザの作成
スレーブサーバ（172.16.1.22）からのみ接続を許可します。ユーザ名は、慣習的に「repl」を指定していますが、なんでもかまいません。
mysql -uroot -p
「repl」ユーザが作成されたことを確認します。
（略）
| repl | 172.16.1.22 | ***** |
マスターDBのダンプ
レプリケーションの開始時のみ、手動でマスターのDB全体を、スレーブにコピーする必要があります。
--all-databases \
--events \
--single-transaction \
--flush-logs \
--master-data=2 \
--hex-blob \
--default-character-set=utf8 > master_db.sql
ダンプしたファイルをスレーブサーバにコピーしておきます。
マスターサーバでの設定は以上です。続いてスレーブサーバの設定です。
MySQL スレーブサーバの設定
スレーブサーバの my.cnf 設定
server-id の値と、read_only を設定する以外は、マスターサーバの設定と同じにすることが望ましいです。
vi /etc/my.cnf
# 各MySQLサーバを識別するためのIDの設定
server-id=2
# 更新を禁止する（スレーブ用）
read_only
（以下はマスターサーバと同じ）
マスターDBのダンプの読み込み
マスターサーバでダンプしたデータを、スレーブに読み込みます。
ダンプした時点の、マスターのバイナリログファイル名（MASTER_LOG_FILE）と開始位置（MASTER_LOG_POS）を確認しておきます。
-- CHANGE MASTER TO MASTER_LOG_FILE='mysqld-bin.000002', MASTER_LOG_POS=120;
レプリケーションの設定
mysql -u root -p
MASTER_HOST='172.16.1.21',
MASTER_PORT=3306,
MASTER_USER='repl',
MASTER_PASSWORD='＜パスワード＞',
MASTER_LOG_FILE='mysqld-bin.000002',
MASTER_LOG_POS=120;
MASTER_HOST
マスターサーバのIPアドレスを指定します。
MASTER_PORT
マスターサーバの MySQL の待受ポート番号を指定します。ポート番号を変更していなければ「3306」です。
MASTER_USER
先ほど作成した、レプリケーション用ユーザ名を指定します。
MASTER_PASSWORD
レプリケーション用ユーザのパスワードを指定します。
MASTER_LOG_FILE
先ほど調べた、マスターサーバ上のバイナリログファイル名を指定します。
MASTER_LOG_POS
こちらも先ほど調べた、開始位置を指定します。
もし設定をやり直したい場合は、下記コマンドを実行し、一度レプリケーション設定をクリアしてから、再度設定してください。
RESET SLAVE;
レプリケーションの開始
レプリケーションの動作確認
Slave_IO_Running と Slave_SQL_Running がどちらも「Yes」になっていれば、レプリケーションが正常に動作しています。
（略）
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
（略）
エラーの原因（その１）3306番ポートのブロック
上の動作確認で、Slave_IO_Running が「Connecting」から「Yes」に変わらない場合は、マスターサーバの MySQL の待受ポート 3306/tcp がブロックされている可能性があります。
Slave_SQL_Running: Yes
この場合は、マスターサーバで 3306/tcp の接続を許可しておきましょう。以下は CentOS7で採用されている firewalld コマンドでの設定です。
firewall-cmd --reload
しばらくすると Slave_IO_Running が「Yes」に変わります。もし変わらない場合は、MySQLを再起動してください。
エラーの原因（その２）UUID の重複
VirtualBoxで、MySQL設定済みのサーバをクローンした時に、以下のエラーが発生し、レプリケーションが開始できませんでした。
Slave_SQL_Running: Yes
（略）
Last_IO_Error: Fatal error: The slave I/O thread stops because master and slave have equal MySQL server UUIDs; these UUIDs must be different for replication to work.
server-id とはまた別の UUID がマスター/スレーブサーバで重複しているのが原因とのこと。
スレーブサーバの /var/lib/mysql/auto.cnf（←ここにUUIDが格納されている）を削除して、MySQL を再起動すれば新たな UUID が生成され、レプリケーションも正常に開始します。
systemctl restart mysqld
これは最近よくありそうなトラブルですね(^^;) サーバを丸ごとコピーしたら、必ず /var/lib/mysql/auto.cnf を削除しましょう。
但し、これはレプリケーション環境構築時に設定が必要なserver_idの代替となるものではありませんので、注意して下さい。また、auto.cnfファイルはデータディレクトリ配下に自動的に生成されるため、マスターのデータディレクトリ配下をコピーしてスレーブを構築した場合は、スレーブ起動前にauto.cnfファイルを削除する必要がある点にも注意して下さい（auto.cnfファイルを削除してから起動することで、auto.cnfファイルが自動生成されます）。
MySQL 5.6での機能強化点（その3）- 人気のレプリケーションが更に機能強化 より引用
終わりに
次回は、今回設定した MySQLスレーブサーバのレプリケーション状態を Zabbix で監視する方法をご紹介したいと思います。
コメント
[…] 参考サイト） https://blog.apar.jp/linux/3163/ http://muramasa64.fprog.org/diary/?date=20111017 http://koexuka.blogspot.jp/2009/06/mysql_10.html http://qiita.com/ryounagaoka/items/7be0479a36c97618907f http://d.hatena.ne.jp/omiyan/20110128/p1 […]