MySQL 5.6 マスター/スレーブサーバの設定メモ

MySQL 5.6 マスター/スレーブサーバの設定メモ
あぱーブログ
2015年8月24日  カテゴリー:Linux  タグ: ,
Pocket
LINEで送る

MySQL のレプリケーションを使うと、負荷分散、ホットスタンバイ、データのバックアップといったことが、簡単に実現できます。MySQLの中でも特に人気のある機能ではないでしょうか。この便利なレプリケーションを使うには、そこそこの設定が必要です。そしてよく忘れます(^^;) そこで今回は、マスターサーバの設定から、スレーブサーバを構築するまでの手順をメモしておきました。

MySQL サーバ構成

ごく単純な、マスター1台、スレーブ1台の構成です。サーバOSは CentOS7.1.1503 MySQLのバージョンは 5.6.23 です。
mysql-slave-01

MySQL マスターサーバの設定

マスターサーバの my.cnf 設定

vi /etc/my.cnf

[mysqld]
# 各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を再起動して、設定を反映します。

systemctl restart mysqld

レプリケーション関連設定の詳細

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

GRANT REPLICATION SLAVE ON *.* TO repl@172.16.1.22 IDENTIFIED BY '<パスワード>';

「repl」ユーザが作成されたことを確認します。

SELECT user,host,password FROM mysql.user;
(略)
| repl | 172.16.1.22 | ***** |

マスターDBのダンプ

レプリケーションの開始時のみ、手動でマスターのDB全体を、スレーブにコピーする必要があります。

mysqldump -u root -p \
--all-databases \
--events \
--single-transaction \
--flush-logs \
--master-data=2 \
--hex-blob \
--default-character-set=utf8 > master_db.sql

ダンプしたファイルをスレーブサーバにコピーしておきます。

scp master_db.sql root@172.16.1.22:

マスターサーバでの設定は以上です。続いてスレーブサーバの設定です。

MySQL スレーブサーバの設定

スレーブサーバの my.cnf 設定

server-id の値と、read_only を設定する以外は、マスターサーバの設定と同じにすることが望ましいです。
vi /etc/my.cnf

[mysqld]
# 各MySQLサーバを識別するためのIDの設定
server-id=2
 
# 更新を禁止する(スレーブ用)
read_only
 
(以下はマスターサーバと同じ)

マスターDBのダンプの読み込み

マスターサーバでダンプしたデータを、スレーブに読み込みます。

mysql -u root -p < master_db.sql

ダンプした時点の、マスターのバイナリログファイル名(MASTER_LOG_FILE)と開始位置(MASTER_LOG_POS)を確認しておきます。

head -n 100 master_db.sql | grep CHANGE
 
-- CHANGE MASTER TO MASTER_LOG_FILE='mysqld-bin.000002', MASTER_LOG_POS=120;

レプリケーションの設定

mysql -u root -p

CHANGE MASTER TO
 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 MASTER;
RESET SLAVE;

レプリケーションの開始

START SLAVE;

レプリケーションの動作確認

Slave_IO_Running と Slave_SQL_Running がどちらも「Yes」になっていれば、レプリケーションが正常に動作しています。

SHOW SLAVE STATUS\G
(略)
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
(略)

エラーの原因(その1)3306番ポートのブロック

上の動作確認で、Slave_IO_Running が「Connecting」から「Yes」に変わらない場合は、マスターサーバの MySQL の待受ポート 3306/tcp がブロックされている可能性があります。

Slave_IO_Running: Connecting ← いつまでたっても接続中
Slave_SQL_Running: Yes

この場合は、マスターサーバで 3306/tcp の接続を許可しておきましょう。以下は CentOS7で採用されている firewalld コマンドでの設定です。

firewall-cmd --add-port=3306/tcp --permanent
firewall-cmd --reload

しばらくすると Slave_IO_Running が「Yes」に変わります。もし変わらない場合は、MySQLを再起動してください。

エラーの原因(その2)UUID の重複

VirtualBoxで、MySQL設定済みのサーバをクローンした時に、以下のエラーが発生し、レプリケーションが開始できませんでした。

Slave_IO_Running: No
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 が生成され、レプリケーションも正常に開始します。

rm /var/lib/mysql/auto.cnf
systemctl restart mysqld

これは最近よくありそうなトラブルですね(^^;) サーバを丸ごとコピーしたら、必ず /var/lib/mysql/auto.cnf を削除しましょう。

但し、これはレプリケーション環境構築時に設定が必要なserver_idの代替となるものではありませんので、注意して下さい。また、auto.cnfファイルはデータディレクトリ配下に自動的に生成されるため、マスターのデータディレクトリ配下をコピーしてスレーブを構築した場合は、スレーブ起動前にauto.cnfファイルを削除する必要がある点にも注意して下さい(auto.cnfファイルを削除してから起動することで、auto.cnfファイルが自動生成されます)。

MySQL 5.6での機能強化点(その3)- 人気のレプリケーションが更に機能強化 より引用

終わりに

次回は、今回設定した MySQLスレーブサーバのレプリケーション状態を Zabbix で監視する方法をご紹介したいと思います。

スポンサーリンク
Pocket
LINEで送る

MySQL 5.6 マスター/スレーブサーバの設定メモ」への1件のフィードバック

  1. ピンバック: MySQLのレプリケーション設定 – igreks開発日記

コメントを残す

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です