SSH経由でMySQLの準同期レプリケーションを行う方法

SSHで暗号化された通信経路でMySQLの準同期レプリケーションを行うための方法を紹介します。今回使用したMySQLのバージョンは5.5.30です。

 

1. マスタとスレーブと間でSSH経由で通信ができるようにする

レプリケーションの設定に入る前に、マスタDBがあるサーバー(以降、マスタと表記)とそのレプリケーション先のDBがあるサーバー(以降、スレーブと表記)の間の通信がちゃんとできるか確認しましよう。

 

------- スレーブでやること ----------
まず、以下のコマンドを実行してSSHポートフォワーディングによってスレーブの10000ポートからマスタの3306ポートにアクセスできるようにします。
ssh -f -N -C -L 10000:localhost:3306 user01@master.tamurasouko.com -p 22

上記のコマンドで下図のようなポートフォワーディングを実現しています。このコマンドの詳細についてはこちらのメモを参考にしてください。
ssh_master_and_slave

 

-------マスタでやること ----------
次に、マスタDBにMySQLクライアントで接続し、以下のコマンドでレプリケーション用のユーザを作成します。
mysql> GRANT REPLICATION SLAVE ON *.* TO slave_user@'localhost' IDENTIFIED BY 'slave_password';

上記のコマンドにはちょっとしたポイントがあります。それはユーザ(slave_user)のホストlocalhostにしている点です。

変だとおもいませんか?マスタに接続してレプリケーションを行うのは外部サーバーのスレーブなのに、マスタへの接続に使用するユーザのホストをスレーブではなく、localhost、つまりマスタ自身にしているところが一見おかしいように思えます。

このような設定にしているのはSSHのポートフォワーディングを利用しているためです。ポートフォワーディングはスレーブのlocalhostから通信を「転送」しているため、その通信を受け取ったマスタもlocalhostからの通信だな、と判断するわけです。(たぶん。このあたりの理解はあやしいです。)

レプリケーション用のユーザの作成が完了したら、以下のコマンドをMySQLクライアントで実行して作成したユーザをDBMSに反映させます。
mysql> FLUSH PRIVILEGES;

 

------- スレーブでやること ----------
ここまで設定が終わったら、スレーブからマスタにポートフォワーディングで接続できるか以下のコマンドを実行して確認します。
mysql -u slave_user -p -h 127.0.0.1 --port=10000

問題なく実行できればOKです。

 

2. 準同期レプリケーションのための設定をする

マスタ・スレーブ間の通信ができることを確認したところで、レプリケーションの設定をMySQLの設定ファイル(/etc/my.cnf)に記述していきます。設定はマスタ、スレーブそれぞれ行う必要があります。以下の設定を追記したらMySQLサーバーを両方とも再起動してください。

マスタのmy.cnf

# サーバーID
server-id=10

# バイナリーログファイルを生成
log-bin
# ログの保持を保証する期間
expire_logs_days=14

# レプリケーションプラグイン(マスタ用)を読み込む
plugin-load=rpl_semi_sync_master=semisync_master.so

# このサーバーをレプリケーションのマスタに指定
rpl_semi_sync_master_enabled=1
# スレーブから応答が帰ってこない場合の待ち時間を指定(ミリ秒)
rpl_semi_sync_master_timeout=1000

 

スレーブのmy.cnf

# サーバーID(※マスタより大きな値を設定)
server-id = 11

# レプリケーションプラグイン(スレーブ用)を読み込む
plugin-load=rpl_semi_sync_slave=semisync_slave.so

# このサーバーをレプリケーションのスレーブに指定
rpl_semi_sync_slave_enabled=1

 

3. データのコピーとログ位置を確認する

レプリケーションを始める前にマスタとスレーブのデータが同一である必要があります。そのためにマスタDBの全データをダンプ(別ファイルに書き出すこと)し、そのダンプデータをスレーブにコピーします。

 

------- マスタでやること ----------
まず、マスタのDBがこのコピー作業中に変更されないようにMySQLクライアントから以下のコマンドを実行し、ロックします。
mysql> FLUSH TABLES WITH READ LOCK;

この状態で以下のコマンドをシェルから実行し、マスタDBの全データをダンプします。
mysqldump -u root --password='root_password' --all-databases --master-data --events > all_dump.db

また、このときのバイナリーログの位置を以下のコマンドをMySQLクライアント上で実行して取得します。このログ位置はスレーブにてレプリケーションマスタを設定するときに使用します。(後述します)
mysql> SHOW MASTER STATUS;

ここまでの作業が終わったら、以下のコマンドでロックを解除します。
mysql> UNLOCK TABLES;

ここまでがマスタ側での作業です。

 

------- スレーブでやること ----------
次に、マスタDBのダンプデータをSFTPなどを用いてスレーブにもってきます。そして以下のコマンドを実行してスレーブDBにマスタDBのデータを投入します。
mysql -u root ---password='root_password' < all_dump.db

 

4. スレーブにレプリケーション先を設定する

------- スレーブでやること ----------
マスタとスレーブのデータが同一になったところで、スレーブにレプリケーション元のマスタを設定します。スレーブで以下のコマンドを実行します。
mysql> CHANGE MASTER TO master_host='localhost', master_port=10000, master_user='slave_user', master_password='slave_password', master_log_file='mysqld-bin.000001', master_log_pos=2530

SSHポートフォワーディングによってマスタに接続するため、コマンドのmaster_hostやmaster_portの値は上記のようになります。また、前節においてマスタでSHOW MASTER STATUSコマンドを実行して得られたログ位置をmaster_log_posの値として設定しています。

 

5. レプリケーション開始

------- スレーブでやること ----------
スレーブで以下のコマンドを実行すれば準同期レプリケーションが開始されます。
mysql> START SLAVE;

 

6. レプリケーションが行われているか確認

------- スレーブでやること ----------
実際にマスタを更新してデータがスレーブにも更新されるか見て確認してもいいですが、以下のコマンドをスレーブ側で実行して確認することもできます。(両方やっておけば確実です)
mysql> SHOW SLAVE STATUS\G

上記のコマンドを実行すると、Slave_IO_RunningSlave_SQL_Runningという項目が表示されます。これらがどちらもYesになっていればOKです。

 

環境

CentOS 6.4
MySQL 5.5.30
openSSL 1.0.0