とーますメモ

Ruby on Rails / Goなどの学習メモ

【MySQL】レプリケーション設定をリモート(Master)-> ローカル(Slave)で設定

ローカル(開発用)環境のMySQLのデータと
リモート(ステージング)環境のMySQLデータを同期させたいという思いから
色々と情報をネット上で検索してみたのだが、
大体のレプリケーション設定は同一のネットワーク上で設定することが
前提とされているものが多かったため、自分用にメモ。

前提知識

よく利用するコマンド

コマンド 機能 DB
SLAVE START USER='' PASSWORD='' レプリケーションの開始/再開 Slave
SLAVE STOP レプリケーションの停止 Slave
RESET SLAVE Salveの状態のリセット Slave
SHOW MASTER STATUS¥G マスターの状態を表示 Master
SHOW SLAVE STATUS¥G スレーブの状態を表示 Slave

※「¥G」は表示を縦並びで表示させるために使用。

基本は以下のサイトのように設定するが
MySQLでMaster-Slave構成のレプリケーション設定 - Qiita
Slaveアカウントの作成のやり方が少し違う。

やり方は以下のサイトを参考にする。
SSH経由でMySQLの準同期レプリケーションを行う方法 | 株式会社田村倉庫

Slaveアカウントを作成

作成するユーザーはスレーブがマスタのバイナリ ログを読み込むときに接続するユーザーとなる。
またSSHポートフォワーディングを利用したアクセスになるため
ホストは「localhost」として設定する。

設定例)

ホスト ユーザ パスワード
localhost slave password
GRANT REPLICATION SLAVE ON *.* TO slave@'localhost' IDENTIFIED BY 'password';

Slave DBの設定

Slave DBでMaster DBへ接続するためのパラメータを設定。
設定の仕方は以下の2通りが存在する

  1. change master コマンドでの設定例
mysql> change master to
    -> master_host='localhost',          # Master DBサーバのホスト名/IPアドレス
    -> master_log_file='xxxxxx-bin.000001',  # バイナリログのファイル名
    -> master_log_pos=xxx;                   # バイナリログの現在位置

※master_user='slave'とmaster_password='password'は、
上記のchange masterコマンド実行時に設定できるが、セキュリティの関係上
Salve Start時に「START SLAVE user='slave' password='password'」として実行する。


change masterコマンドを実行すると、
master.infoというファイルが作成され、中身を見ると設定内容が確認できる。

  1. my.cnfに設定する


change masterコマンドで設定する場合
オンラインで設定を更新できるというメリットがある。
設定内容があまり変わらない場合は、my.cnfに設定するという方法もありか。


SSHポートフォワーディングの設定

「slave start」を行う前に、この設定を行う。

下記の設定を行うことにより、ローカルからホスト名(localhost)+ポート番号(10000)にアクセスすることで
リモートサーバ内のMySQL(ポート番号:3306)にアクセスできる。

ssh -f -N -C -L 10000:localhost:3306 リモートSSHユーザ名@リモートホスト名 -p リモートのポート番号
オプション 説明
-f このポートフォワーディングをバックグラウンドで実行する。(必須)
-N SSHトンネリングのみに使用する。(必須)
-C データを圧縮して送る。

下記を参考
SSH経由でMySQLの準同期レプリケーションを行う方法 | 株式会社田村倉庫
SSHのポートフォワーディングでMySQLに接続する方法 | 株式会社田村倉庫
社壊人エンジニア備忘録: mysqlのリモートレプリケーション

レプリケーションフィルタ

DB、テーブル単位で変更できる。
my.cnfに設定するパターンと
オンラインで変更することも可能