MySQLのレプリケーション設定手順・切替手順などをまとめました。
実績のあるやり方ですが、考えながら書いたのでもしかすると書き間違えや無駄な手順があるかもです。参考まで。
見出し
スレーブのスレッド
スレーブには2つのスレッドが存在しました。
I/OスレッドとSQLスレッドです。
I/Oスレッド
マスタの更新ログを取りに行き、スレーブにリレーログとして保存します。DBの更新はしません。
SQLスレッド
リレーログを読み込み、SQLを実行してDBを更新します。
それぞれが別に走っているところもポイントです。別に走っているので、スレッドを停止する場合はI/Oスレッドを止めてからSQLスレッドを止める必要があります。そうしないとリレーログだけ残ってしまう可能性があるからです。
レプリケーションの設定手順
わかりやすいサイト様があったのでどうぞ
http://qiita.com/shotaTsuge/items/83e4387d6c8488677e17
レプリケーションの切替手順
GTIDを使用しない場合のレプリケーション例を挙げて説明します。以下のDBサーバを用意しました。
DB２台構成の場合 バックアップなし・ストレージエンジンMyISAMありの場合
- 東京サーバ（マスタDB）
- 大阪サーバ（スレーブDB）
マスタ１台スレーブ１台のシンプル構成です。
WEBサーバは特に触れないのでWEBサーバとします。
東京サーバに障害発生した場合
メンテナンス開始
↓
大阪サーバのレプリケーション・スレーブ停止
mysql> show slave status\G — Read_Master_Log_Pos に Exec_Master_Log_Posが追いつくまで待つ
mysql> stop slave io_thread; — I/Oスレッド停止
mysql> show processlist; — SQLスレッドが動いている可能性があるので何も実行されていない事を確認
mysql> stop slave;
↓
大阪サーバのダンプ・バックアップをする
mysql> mysqldump –add-drop-database –add-drop-table –lock-all-tables -u[user] -p[password] [schema] > oosakadb.dump
↓
大阪サーバのマスタとしてのpositionを確認する
mysql> show master status;
+——————+———–+————–+——————+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+——————+———–+————–+——————+
| mysql-bin.xxxxxx | yyyyyyyyy |
+——————+———–+————–+——————+
※この内容を控えておく。①
※mysqldumpコマンドに–master-dataオプションを追加する事でこのpositionの内容も書き込まれる。
↓
WEBサーバを大阪サーバに接続する
↓
メンテナンス終了
稼働再開（図）
※スレーブが構築出来るまで待っても良い
東京サーバが復旧した
東京サーバにダンプ・リストアをする
mysql> mysql -u[user] -p[password] [schema] < oosakadb.dump
↓
東京サーバのマスタ情報をクリアする
mysql> reset master;
↓
東京サーバのスレーブ情報をクリアする
mysql> reset slave;
↓
東京サーバのスレーブ情報を設定する
mysql>
CHANGE MASTER TO
MASTER_HOST = ‘[大阪サーバのホスト/IP]’,
MASTER_USER = ‘[レプリケーションユーザ名]’,
MASTER_PASSWORD = ‘[レプリケーションパスワード]’,
MASTER_LOG_FILE = ‘mysql-bin.xxxxxx’, — ←先ほど控えた①大阪サーバのshow master statusの内容
MASTER_LOG_POS = yyyyyyyyy; — ←先ほど控えた①大阪サーバのshow master statusの内容
↓
東京サーバのレプリケーション（スレーブ）を開始する
mysql> start slave;
このままだと障害前後で東京と大阪が逆になってしまっているので、東京マスタ大阪スレーブに戻す。
メンテナンス開始
↓
大阪サーバのロックと確認
mysql> show processlist; — 実行中でない事を確認
mysql> flush tables with read lock; — ロック
mysql> show status like ‘Key_blocks_not_flushed’; — メモリ上は変更されたけどディスクフラッシュがまだされていないキャッシュのブロック数：これが0である事を確認
mysql> show engine innodb status\G — InnoDBの場合ログがテーブル書き込み済（Log sequence number = Log flushed up to）となっている事を確認
↓
大阪サーバのマスタとしてのpositionを確認する
mysql> show master status;
+——————+———–+————–+——————+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+——————+———–+————–+——————+
| mysql-bin.xxxxxx | yyyyyyyyy |
+——————+———–+————–+——————+
※この内容を控えておく。②
↓
東京サーバのスレーブとしての大阪サーバのマスタpositionを確認する
mysql> show slave status\G
Master_Log_File: mysql-bin.xxxxxx ←先ほど控えた②大阪サーバのshow master statusの内容と等しい事
Exec_Master_Log_Pos: yyyyyyyyy ←先ほど控えた②大阪サーバのshow master statusの内容と等しい事
↓
東京サーバのレプリケーション・スレーブ停止
mysql> show slave status\G — Read_Master_Log_Pos に Exec_Master_Log_Posが追いつくまで待つ
mysql> stop slave io_thread; — I/Oスレッド停止
mysql> show processlist; — SQLスレッドが動いている可能性があるので何も実行されていない事を確認
mysql> stop slave;
↓
東京サーバのマスタ情報をクリアする
mysql> reset master;
↓
東京サーバのスレーブ情報をクリアする
mysql> reset slave;
↓
東京サーバのマスタとしてのpositionを確認する
mysql> show master status;
+——————+———–+————–+——————+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+——————+———–+————–+——————+
| mysql-bin.xxxxxx | yyyyyyyyy |
+——————+———–+————–+——————+
※この内容を控えておく。③
↓
大阪サーバのマスタ情報をクリアする
mysql> reset master;
↓
大阪サーバのスレーブ情報をクリアする
mysql> reset slave;
↓
大阪サーバのスレーブ情報を設定する
mysql>
CHANGE MASTER TO
MASTER_HOST = ‘[東京サーバのホスト/IP]’,
MASTER_USER = ‘[レプリケーションユーザ名]’,
MASTER_PASSWORD = ‘[レプリケーションパスワード]’,
MASTER_LOG_FILE = ‘mysql-bin.xxxxxx’, — ←先ほど控えた③東京サーバのshow master statusの内容
MASTER_LOG_POS = yyyyyyyyy; — ←先ほど控えた③東京サーバのshow master statusの内容
↓
大阪サーバのレプリケーション（スレーブ）を開始する
mysql> start slave;
DB３台構成の場合
- 東京サーバ（マスタDB)
- 大阪サーバ（スレーブDB)
- 北海道サーバ（スレーブDB)
マスタ１台子スレーブ２台の構成です。
WEBサーバは特に触れないのでWEBサーバとします。
東京サーバに障害発生した場合の例
メンテナンス開始
↓
大阪サーバのレプリケーション・スレーブ停止
mysql> show slave status\G — Read_Master_Log_Pos に Exec_Master_Log_Posが追いつくまで待つ
mysql> stop slave io_thread; — I/Oスレッド停止
mysql> show processlist; — SQLスレッドが動いている可能性があるので何も実行されていない事を確認
mysql> stop slave;
↓
北海道サーバのレプリケーション・スレーブ停止
mysql> show slave status\G — Read_Master_Log_Pos に Exec_Master_Log_Posが追いつくまで待つ
mysql> stop slave io_thread; — I/Oスレッド停止
mysql> show processlist; — SQLスレッドが動いている可能性があるので何も実行されていない事を確認
mysql> stop slave;
大阪サーバと北海道サーバとでどちらが最新か確認する
大阪サーバのスレーブから見たマスタのpositionを確認する
mysql> show slave status\G
Master_Log_File: mysql-bin.xxxxxx
Exec_Master_Log_Pos: yyyyyyyyy
北海道サーバのスレーブから見たマスタのpositionを確認する
mysql> show slave status\G
Master_Log_File: mysql-bin.xxxxxx
Exec_Master_Log_Pos: yyyyyyyyy
同じ場合、大阪サーバをマスタ・北海道サーバをスレーブにする
大阪サーバのマスタ情報をクリアする
mysql> reset master;
↓
大阪サーバのスレーブ情報をクリアする
mysql> reset slave;
↓
大阪サーバのマスタとしてのpositionを確認する
mysql> show master status;
+——————+———–+————–+——————+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+——————+———–+————–+——————+
| mysql-bin.xxxxxx | yyyyyyyyy |
+——————+———–+————–+——————+
※この内容を控えておく。④
↓
北海道サーバのマスタ情報をクリアする
mysql> reset master;
↓
北海道サーバのスレーブ情報をクリアする
mysql> reset slave;
↓
北海道サーバのスレーブ情報を設定する
mysql>
CHANGE MASTER TO
MASTER_HOST = ‘[大阪サーバのホスト/IP]’,
MASTER_USER = ‘[レプリケーションユーザ名]’,
MASTER_PASSWORD = ‘[レプリケーションパスワード]’,
MASTER_LOG_FILE = ‘mysql-bin.xxxxxx’, — ←先ほど控えた④大阪サーバのshow master statusの内容
MASTER_LOG_POS = yyyyyyyyy; — ←先ほど控えた④大阪サーバのshow master statusの内容
↓
北海道サーバのレプリケーション（スレーブ）を開始する
mysql> start slave;
東京サーバが復旧した
北海道サーバのレプリケーション・スレーブ停止
mysql> show slave status\G — Read_Master_Log_Pos に Exec_Master_Log_Posが追いつくまで待つ
mysql> stop slave io_thread; — I/Oスレッド停止
mysql> show processlist; — SQLスレッドが動いている可能性があるので何も実行されていない事を確認
mysql> stop slave;
↓
北海道サーバのロックと確認
mysql> show processlist; — 実行中でない事を確認
mysql> flush tables with read lock; — ロック
mysql> show status like ‘Key_blocks_not_flushed’; — メモリ上は変更されたけどディスクフラッシュがまだされていないキャッシュのブロック数：これが0である事を確認
mysql> show engine innodb status\G — InnoDBの場合ログがテーブル書き込み済（Log sequence number = Log flushed up to）となっている事を確認
↓
北海道サーバのダンプ・バックアップをする
mysql> mysqldump –add-drop-database –add-drop-table –lock-all-tables -u[user] -p[password] [schema] > oosakadb.dump
↓
北海道サーバのスレーブとしての大阪サーバのマスタpositionを確認する
mysql> show slave status\G
Master_Log_File: mysql-bin.xxxxxx
Exec_Master_Log_Pos: yyyyyyyyy
※この内容を控えておく。⑤
↓
東京サーバのマスタ情報をクリアする
mysql> reset master;
↓
東京サーバのスレーブ情報をクリアする
mysql> reset slave;
↓
東京サーバのスレーブ情報を設定する
mysql>
CHANGE MASTER TO
MASTER_HOST = ‘[大阪サーバのホスト/IP]’,
MASTER_USER = ‘[レプリケーションユーザ名]’,
MASTER_PASSWORD = ‘[レプリケーションパスワード]’,
MASTER_LOG_FILE = ‘mysql-bin.xxxxxx’, — ←先ほど控えた⑤北海道サーバのshow slave statusの内容
MASTER_LOG_POS = yyyyyyyyy; — ←先ほど控えた⑤北海道サーバのshow slave statusの内容
↓
東京サーバのレプリケーション（スレーブ）を開始する
mysql> start slave;
大阪マスタ・北海道スレーブ・東京スレーブとなった。東京をマスタにするのは同じような考えです。
MyISAMがなくinnodbだけの場合は無停止で出来るようです。
http://qiita.com/hit/items/7747394e8f2f6a515535
GTID（Global Transaction ID）
GTIDを使うとどうなるか。
スレーブから見たマスタのpositionという概念がなくなる。
ダンプのpositionを確認するのに今まではマスタの100番positionとスレーブの100番positionは違う位置でした。
これがGTIDで共通IDが振られたので、マスタの100番positionとスレーブの100番positionは同じ位置という見方が出来るようになった。
レプリケーション形式について
なんか認識間違えてないと良いのですが・・・ご指摘ください。