2017/12/04 追記

レプリケーションの手順に誤りがあります。

無停止で前日のdumpからスレーブを作って同期をとった、から

↓↓↓↓↓↓↓↓↓

メンテナンスモードでマスターへの更新を止めた状態でdumpをとり、スレーブを作って同期
その後にメンテナンスモードをオフして、マスターへの更新を再開

という内容に更新予定です。

設定当時はよくわかっておらず、最初は更新を止めずに雑にレプリケーションしていました。
結果、複数のレコードを喪失したままレプリケーションが実行されていたので、やり直しています。

はじめに

my.cnf のチューニングと計測がいったん終わり、MySQLサーバー(5.5)が安定稼働してきました。
そこで 今まで稼働を停止していた レプリケーションサーバーを再稼働させた時のことをまとめたいと思います。

手順周りは、作業しながらのメモから書き起こしたもので、多少雑な面があるのはご容赦ください。

※相変わらずMySQLは5.5のままなので、GTIDは使っていません

レプリケーションとは

基本

データベース全体の複製（レプリカ）になります。
ざっくりとですが、以下の特徴を持ちます。

別のサーバーに複製（スレーブ）を持つことができる
MySQLの標準機能なので、割と手軽に設定できる
- ミドルウェアなど用意する必要がない
ほぼ時間差なしに複製される
- 重い更新クエリや、トランザクションを使わない更新の連発を実行すれば、その分遅延は発生する

構成

マスター（単一）←→スレーブ（単一または複数）で構成されます。

マスター

データを更新する側
更新内容をスレーブが取りに来る
- マスターはcommit後、スレーブに対し更新イベントを送信（通知）する
マスターは複数のスレーブを持つことができる

スレーブ

マスターでの更新内容（更新イベント）を受け取る
マスターのバイナリログ（commitログ）を取得しに行く（IOスレッド）
更新内容をDBに反映（SQLスレッド）
スレーブは1つのマスターのみ持てる
更新用ではなく参照用のDB

※マルチマスター（全てのサーバーがマスターでありスレーブである、つまり書き込み・読み込みを行う）の場合は考慮しない前提で進めます。

利点

参照(SELECT)性能の向上

参照（検索）処理の負荷が高い場合、 スレーブサーバーを参照系にすることで、負荷分散が実現 できます。

僕が担当しているDBは、重たい検索クエリを結構な頻度で実行するため、期待が持てます。
参照系をスレーブにする予定があるので、参照と更新を分ける話は別エントリーにしようと思います。

可用性の高い構成の実現

マスターの障害時、スレーブをマスターに昇格（切り替え）することで、 ダウンタイムを短縮できます。
昇格時のダウンタイムをゼロにすることはできませんが、単一のDBサーバーを復旧させるより早いのは確実です。
ただし、本来ならば、フェイルオーバー(※)の仕組みの導入を考えるべきです。
現段階では考慮していないので、今後の課題となりそうです。

※フェイルオーバーとは、自動的に待機系や副系に切り替えること。
現段階では自動化されていないので、スイッチオーバー(手動)となります。

バックアップサーバーとしての利用が可能

スレーブサーバーでバックアップを取得することで、 マスターに影響を与えることなくバックアップを取得可能 です。
バックアップはDBを止めて取るのが望ましいのですが、スレーブはいつ止めても構わず、いつでも再開できる、という利点があります。
ただし、参照系でスレーブを使用している場合は、停止してのバックアップではなく、

バックアップ用のスレーブを作る
停止せずにバックアップを取る
- 負荷やロックを考えて、テーブル単位に取るなど工夫

など、考慮が必要だと考えています。

参考

参考というか、教本として手本にさせて頂きました。
レプリケーションの基本は
MySQL入門　レプリケーション編
にものすごく詳しいです。

僕の場合、今回は5.5なので、5.6の言及も設定もしていません

状況

今までマスターがしょっちゅうダウンし、都度スレーブの設定を変更しなければならないという理由のため、スレーブが停止されていました。
さらにそのまま忘れ去られていたので、整合性の取れない部分が多々ありました。

マスターDBがしょっちゅうダウンし、都度マスターログファイルが変わる
- あまりにも頻発するので、スレーブ側の設定を合わせることをやめていた
スレーブのインスタインスを再起動していた
- マスターのmysqlに登録されたuserに対応するIPが古く、接続できなかった
my.cnfが古いままだった

よって、最初からではありませんが、

レプリケーション用ユーザー作成
マスターデータのリストア
レプリケーション再開

が必要となるので、その手順、および各種ステータスの詳細、設定時に発生したトラブルシューティングを解説して行きます。

設定手順

レプリケーション専用ユーザーの作成

マスター側に、スレーブから接続するための専用ユーザーを作成します。
既存のユーザーでも接続できますが、スレーブ側の以下のファイルに、ユーザー名とパスワードがプレーンテキストで保存されます。

/var/lib/mysql/master.info

$ sudo cat /var/lib/mysql/master.info
18
mysql-bin.000077
18143993
[マスターのIPアドレスまたはホスト名]
[マスターに接続するmysqlユーザー名]
[マスターに接続するmysqlパスワード]
3306
60
....

よって、 他のアカウントのセキュリティーを損なう可能性を最小限に抑える べく、レプリケーションプロセスのみ権限があるユーザーを作成しておきます。
与える権限は、スレーブがマスターからバイナリログイベントを読み取れるようになる
REPLICATION SLAVE
を設定します。

master

mysql(master)> CREATE USER 'repl'@'[スレーブのIPアドレスまたはホスト名]' IDENTIFIED BY PASSWORD '**************';
mysql(master)> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'[スレーブのホスト名またはIP]';

また、以下のコマンドでもユーザーを作成できます。

mysql(master)> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'[スレーブのホスト名またはIP]' IDENTIFIED BY PASSWORD '**************';

が、

本来は CREATE USER で作成するべき
本来権限を与えるコマンド「GRANT」が、たまたま「無ければ作る」という動きをしているから作れる

だけで、後々仕様が変わる可能性を考えると、
「CREATE USER」からの「GRANT」
で覚えておく方が適切と思います。

MySQLでGrant文でユーザを作成するのに違和感を感じる
に詳しく説明されています。

マスターの状態の確認

マスターのmy.cnf

以下が設定されていることを確認してください。
設定されていなければ、設定を追加し、mysqldを再起動します。

(master)/etc/my.cnf

[mysqld]

# スレーブに通知するバイナリログ(実際はナンバリングされる)
log-bin=mysql-bin

# レプリケーションを行う、サーバーのユニークID
server-id=1001

# バイナリログファイルのローテート日数
set-variable=expire_logs_days=3

バイナリログファイルのローテート日数
DBサーバーは、使い方によっては容量が枯渇します。
バイナリログは大抵1GBとサイズが大きいので、なるべくローテートして、使わなくなったバイナリログファイルを残さないようにしましょう。

マスターのバイナリログの位置情報の確認

スレーブが取得するバイナリログの位置情報を確認します。
この情報は、スレーブに後ほど登録しますので、メモしておく必要があります。
バイナリログはローテートされますが、採番が新しくなると、スレーブも同期されますので、現段階では「開始する位置」として登録することになります。

mysql> show master status\G
*************************** 1. row ***************************
            File: mysql-bin.000075
        Position: 588201136
    Binlog_Do_DB: 
Binlog_Ignore_DB: 
1 row in set (0.01 sec)

File

レプリケーション用にマスターが出力しているバイナリログファイルです。
mysqld再起動のたびに変更される可能性があるので、

oom-kill された
設定を変更した
何かしらの理由で再起動

の場合は、スレーブ側との値の確認を忘れないようにしましょう。

Position

バイナリログファイルの位置情報です。
マスターで更新処理が走り、バイナリログに出力される都度更新されます。
スレーブ情報を更新する際は、この値も更新しましょう。

Binlog_Do_DB

バイナリログに記録するよう指定されているデータベース名です。
指定がなければ全DBとなります。

Binlog_Ignore_DB

バイナリログに記録しないよう指定されているデータベース名です。
指定がなければ除外はありません。

スレーブのレプリケーション設定状態

以下のコマンドで確認します。
以前の設定の残骸がありますが、古すぎて稼働していません。
今回は、この設定も見直して行きます。

mysql> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
               Slave_IO_State: 
                  Master_Host: [マスターのホスト名またはIPアドレス]
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000009
          Read_Master_Log_Pos: 303456264
               Relay_Log_File: mysqld-relay-bin.000015
                Relay_Log_Pos: 303456299
        Relay_Master_Log_File: mysql-bin.000009
             Slave_IO_Running: No
            Slave_SQL_Running: No
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 303456153
              Relay_Log_Space: 0
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 0
1 row in set (0.00 sec)

Slave_IO_State

スレーブの現在のステータス。
レプリケーションが途切れている（参照しているマスターバイナリがない）ため、空文字となっています。
正しくレプリケーションされていれば
Waiting for master to send event
と表示されます。

Master_Host

マスターのホスト名またはIPアドレスです。

Master_User

スレーブからマスターへ接続するためのユーザーです。
先ほど作成したユーザー名を登録します。
なお、接続時のパスワードは別のコマンドで設定します。

Master_Port

マスターDBサーバーが開いているポート番号。
MySQLは通常 3306 です。

Connect_Retry

接続再試行の秒数。デフォルトは60秒です。
CHANGE MASTER TO ステートメントで設定します。

Master_Log_File

レプリケーション対象のマスターDBのバイナリログファイルの名前です。
現在のマスターのバイナリログファイル名となるので、先ほどメモしたファイル名をここに登録します。

Read_Master_Log_Pos

現在のマスターバイナリログファイルから、スレーブのI/Oスレッドが最後に読み取った位置です。
ここから、レプリケーションが再開・実行されます。
マスターを止めずにdumpするなら、dump実行直前の値を設定することで、レプリケーションが再開されることになります。

Relay_Log_File

スレーブのI/Oスレッドが、マスタのバイナリログをスレーブ側に保存したもの。
ゆえにバイナリログと同じ内容となります。
スレーブ側で不要と判断されれば、自動で削除されるので、マスター側のようにローテートを考慮する必要はありません。

Relay_Log_Pos

スレーブのSQLスレッドが、現在のリレーログファイル内で最後に読み取って更新を実行した位置です。

Relay_Master_Log_File

SQLスレッドが最後に実行したクエリが記録されていた、マスタのバイナリログファイル名。
基本的に Relay_Log_File と同じものです。

Slave_IO_Running

I/Oスレッド（バイナリログファイル取得）が稼働中かどうかを示します。
NOの場合、マスターに接続できていません。

YES:稼働中
NO:停止中

Slave_SQL_Running

SQLスレッド（取得したバイナリログファイル内のSQL実行）が稼働中かどうかを示します。
NOの場合、クエリが実行されておらず、同期が取れていません。

YES:稼働中
NO:停止中

Replicate_Do_DB

--replicate-do-db オプションで指定されたDBのリスト。
ここから続く Replicate_* の項目は今回は設定しませんので、深堀はしません。

Replicate_Ignore_DB
Replicate_Do_Table
Replicate_Ignore_Table
Replicate_Wild_Do_Table
Replicate_Wild_Ignore_Table

Last_Errno

最後に実行したクエリのエラー番号。0 ならばエラーは起きていません。
下記の Last_Error と同様、エラー時に確認する項目になります。

Last_Error

最後に実行したクエリのエラーメッセージ。空文字 ならばエラーは起きていません。

Skip_Counter

マスターからのイベントのスキップ数。以下の構文で設定します。
SET GLOBAL sql_slave_skip_counter = N
スレーブを何らかの理由でリカバリした場合などで、マスターからイベント（SQL）をスキップしたい場合に用います。

スキップを設定・反映するには、
mysql> STOP SLAVE;
でスレーブを一度止める必要があります。

例：マスターとスレーブの間で以下のような不整合があり、レプリケーションが止まり、リストアする場合
- すでにテーブルが存在する
- UNIQUE制約に引っかかる

イベントグループの記述も含め、詳しくは以下に解説があります。
SQL_SLAVE_SKIP_COUNTERについて教えてもらったよ

Exec_Master_Log_Pos

SQLスレッドが最後に実行したクエリの、マスタのバイナリログでの位置。
Read_Master_Log_Pos との違いは、I/Oスレッドが読み込んだ位置と、SQLスレッドがクエリを実行した位置で、こちらは後者です。

Relay_Log_Space

存在するリレーログファイルのサイズ。（単位はbyte）
複数ある場合は合計のサイズが記録されます。

Until_Condition

mysql> START SLAVE; コマンドで UNTIL 構文を実行した場合の値。
UNTILとは指定したログポジションまで達したら、SQLスレッドを停止するための設定です。
ログファイル、ポジションの指定は以下です。

mysql> START SLAVE UNTIL master_log_file='{file_name}', master_log_pos={position}

UNTIL構文が実行されていなければ None
スレーブがマスタのバイナリログの指定位置まで読み込んでいたら Master
スレーブがリレーログの指定位置まで読み込んでいたら Relay
- この状態で、UNTILで設定した位置まで処理されたと判断できる

以下に詳しく解説されています。
MySQLレプリケーション復旧　テーブルロックをせず特定テーブルのみコピーする　その1

START SLAVE UNTIL とは
- 指定したログポジションまで達したら、SQLスレッドを停止するための設定。テーブル単位で復旧するなど、部分単位でSTART SLAVEする際などに設定します。これにより、スレーブ停止時とdump時のログポジションを一致させることが可能となります。

Until_Log_File

SQLスレッドが実行を停止するログファイル名。
UNTILが実行されていなければ空文字です。

Until_Log_Pos

SQLスレッドが実行を停止するポジション。
UNTILが実行されていなければ0です。

Master_SSL_Allowed

スレーブがマスターに接続する際、SSL接続が許可されているかどうかの内容です。

Yes : 許可されている
No : 許可されていない
Ignored : 許可されているが、スレーブのSSLサポートが無効の場合

Master_SSL_CA_File

今回SSL関連は設定しませんので、解説は割愛します。

Master_SSL_CA_Path
Master_SSL_Cert
Master_SSL_Cipher
Master_SSL_Key
Master_SSL_Verify_Server_Cert

Seconds_Behind_Master

スレーブがマスターに比べて遅延している秒数です。

遅延がない場合：0
レプリケーションされていない場合：NULL

レプリケーションの実行順序である、以下のいずれかのタイミングで遅延が発生した場合、秒数が記録されます。

マスターでトランザクションがCOMMITされる
マスターからスレーブへ更新があったことが通知される
スレーブが非同期でバイナリログの内容をマスターから取得する
スレーブ上で同じトランザクション（SQL）が実行される

例：マスターで10秒かかる更新を実行した場合
スレーブでも10秒かかるので、遅延は10秒となります。

漢のコンピュータ道の
MySQLにおけるレプリケーション遅延の傾向と対策
に詳しいです。

Last_IO_Errno

最後に実行したI/Oスレッドのエラー番号。
何もなければ 0 です。

Last_IO_Error

同、エラーメッセージです。
何もなければ空文字です。

Last_SQL_Errno

最後に実行したSQLスレッドのエラー番号。
何もなければ 0 です。

Last_SQL_Error

同、エラーメッセージです。
何もなければ空文字です。

Replicate_Ignore_Server_Ids

マスターが複数ある場合の、イベントを無視するマスターのサーバーID。
空文字の場合は指定なしです。
以下の場合、サーバーID "5, 6, 10" のイベントが無視される。
最初の数字 "3" は、無視されるサーバーの台数です。

Ignored_server_ids: 3 5 6 10

Master_Server_Id

マスターのmy.cnfで設定したID。
レプリケーションが実行されると更新される。
未実行ならばゼロ。

Tips

バイナリログ

実行された更新クエリが保存されているバイナリファイル。
mysqlbinlog で読み取ることができる。
リレーログも同じもの。

I/OスレッドとSQLスレッド

I/Oスレッド
- バイナリログファイルのI/Oを司るスレッド
SQLスレッド
- クエリを実行するスレッド

同期が外れてしまった場合

再度再開するコマンドは以下です。

マスターのステータスを確認し、バイナリログファイルとポジションを取得

mysql(master)> show master status\G

スレーブのログファイル名と、ポジションを更新

mysql(slave)> CHANGE MASTER TO MASTER_LOG_FILE=’{file}’,MASTER_LOG_POS={position};

レプリケーション実行

マスターのdumpを取る

今回は運用上DBを止められないので、深夜バッチで取っているバックアップから、スレーブにリストアします。
本来は、マスターを停止した上でdumpを取り、リストアしてポジションを合わせた上で実行する方が良いです。

手順

ポジションの値を帰宅前に記録、スレーブに登録しておく
- DB停止でのdumpが運用上難しいため、4:00に実行されるバックアップを翌日利用します。
作業用サーバーに、バックアップからdumpファイル取得
- dumpファイルサイズが大きく、リストア中にスレーブのストレージが100%を超える可能性が高いため、作業用サーバーからリストアを実行します。
作業用サーバーから、スレーブに対しmysql接続できるユーザーを一時的に作成
- 使い終わったら削除します。
作業用サーバーからリストア実行
バイナリログが次々に増えていくので、容量を監視する
- 容量が足りなくなりそうならば、古い順にバイナリログを削除
- rmコマンドは(ちゃんとわかってない場合)危険なので、 PURGE MASTER LOGS TO ‘mysql-bin.xxxxxx’; を推奨
リストアが完了したら、スレーブの設定を変更する
- CHANGE MASTER TO コマンドを実行
mysql> START SLAVE; を実行してレプリケーション再開
mysql> SHOW SLAVE STATUS¥G で実行状態を監視

トラブルシューティング

スレーブの設定変更に失敗した

先ほどメモしたマスターのバイナリログファイル名、およびポジションを設定しようとしたら、エラーになりました。

mysql> CHANGE MASTER TO master_log_file = 'XXXXXX', master_log_pos = XXXXXX;
ERROR 1201 (HY000): Could not initialize master info structure; more error messages can be found in the MySQL error log

マスター情報を更新できなかったよ
より詳しいメッセージはMySQLエラーログに出力されているよ

とのこと。

/var/log/mysqld.log を参照すると、以下のエラーが記録されていました。

170327 17:21:34 [ERROR] Failed to open the relay log './mysqld-relay-bin.xxxxxx' (relay_log_pos xxxxxxxxxxx)
170327 17:21:34 [ERROR] Could not find target log during relay log initialization

リレーログ初期化しようと思ったら、ターゲットが見つからないよ

とのこと。
しばらく動かしていなかったので、シーケンス番号がズレていたのかもしれません。
ここでは、ズレを補完するより、 スレーブさんには今までのことをいったん忘れてもらった方が良い と判断し、以下のコマンドを実行します。

mysql> RESET SLAVE;

再度、CHANGE MASTERコマンドでマスターのバイナリログファイル名とポジションを設定し、成功することを確認します。

mysql> CHANGE MASTER TO master_log_file = 'XXXXXX', master_log_pos = XXXXXX;

スレーブがSlave_IO_Running: Connectingとなってしまう

レプリケーションを再開したところ、接続中のままステータスが変わりませんでした。
スレーブからマスターにバイナリログファイルを取得しようとした際、なんらかの理由で取得できていないようです。

考えられる原因

バイナリログファイル名の設定に誤りがある
マスターの接続設定でスレーブからの接続を許可していない

バイナリログのファイル名を確認する

スレーブで設定した「取得しにいくマスターのバイナリログファイル名」を確認します。

Master_Log_File: mysqld-bin.xxxxxx

マスターの設定というと・・・。

File: mysql-bin.000075

と、"d"が余計なため、ファイルが読み込めない。
マスターのバイナリログを見ると、 d はついていない。

以下を実行し、ファイル名を合わせます。

CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.xxxxxx'

マスターの接続設定でスレーブからの接続を許可していない

上記を実行しても、 Slave_IO_Running: Connecting のまま。
スレーブから接続しているMysqlのユーザーに、適切な接続権限があるかどうか確認します。

(slave)$ mysql -h [マスターのホスト名またはIP] -u repl -p

スレーブかマスターに接続してみると、接続権限がなかったようです。

ERROR 1045 (28000): Access denied for user 'repl'@'[マスターのホスト名またはIP]' (using password: YES)

というわけで。
マスター側で作成したレプリケーション用ユーザーに対し、スレーブからの接続を許可します。
今回は、スレーブの再起動時にIPが変わったため、マスターの設定が無効になっていたのが原因です。

GRANT REPLICATION SLAVE ON *.* TO 'repl'@'[スレーブのホスト名またはIP]' IDENTIFIED BY PASSWORD '**************';

REPLICATION SLAVE は、上述したように レプリケーション専用のユーザー権限設定 です。
くれぐれも全権限とか付与しないようにしましょう。予期せぬ脆弱性の原因になりかねませんので。

PRIMARY KEY が重複してレプリケーションが止まる

以下のエラーが出て、レプリケーションが停止してしまいました。

mysql(slave)> SHOW SLAVE STATUS\G
(中略)
Last_SQL_Errno: 1062
Last_SQL_Error: Error 'Duplicate entry '10208303' for key 'PRIMARY'' on query. Default database:...

原因

dumpデータとポジション取得が同時ではなかったため、スレーブにすでにPKがあるデータをINSERTしようとしていました。

ポジション獲得：前日
dump取得：翌日朝4:00

上記の差分でPKが重複してしまいます。

1レコードずつスキップして合わせてみる

以下のコマンドで1レコードずつスキップを行いましたが、重複する行が多すぎてキリがありませんでした。

mysql> STOP SLAVE;
mysql> SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1;
mysql> START SLAVE;

PK重複レコードを全てスキップする

重複レコードは全部飛ばして構わないので、スレーブ側の my.cnf に以下を追記し、mysqld再起動しました。
スキップするエラーコードは Last_SQL_Errno: 1062 に記録されている値を使います。

/etc/my.cnf

[mysqld]

# Duplicate entry skip
slave-skip-errors=1062

再起動後、以下のコマンドでスレーブを再稼働します。

mysql> STOP SLAVE;
mysql> START SLAVE;

ステータスを確認します。

mysql> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: ********
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000075
          Read_Master_Log_Pos: 878873047
               Relay_Log_File: mysqld-relay-bin.000003
                Relay_Log_Pos: 17303271
        Relay_Master_Log_File: mysql-bin.000075
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 605504154
              Relay_Log_Space: 290674997
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: 244615
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 1001
1 row in set (0.00 sec)

確認

以下の項目から、レプリケーションが実行されていることがわかります。

               Slave_IO_State: Waiting for master to send event
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

Waiting状態で、IOもSQLもいずれもYesで、稼働状態であることがわかります。

          Exec_Master_Log_Pos: 605504154
              Relay_Log_Space: 290674997

Master_Log_Pos の値が実際のマスターの値に近づいていくのが確認できます。
マスターの値は以下です。

mysql> SHOW MASTER STATUS\G
*************************** 1. row ***************************
            File: mysql-bin.000075
        Position: 879066686
    Binlog_Do_DB: 
Binlog_Ignore_DB: 
1 row in set (0.00 sec)

結果

最終的にPositionが追いつき、以下の値がゼロになったことで、同期されました。
エラーも起こっていないですね。

        Seconds_Behind_Master: 0
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error:

まとめ

複製が完了して可用性が高くなった
- マスターがダウンしても、スイッチオーバーでスレーブに切り替えが可能に
バックアップサーバーとして運用開始
- マスターに影響を与えることなくバックアップ取得可能に
- 今まではバックアップ時にLAが上がる、ネットワークに大きなデータが流れるなど、影響があった
参照系のサーバーとして扱うことが可能に
- プログラム側の対応が必要だが、今後のパフォーマンスチューニングに大きく貢献

課題

【追記】本番とデータを完全に同期したい

マスターにロックをかけずに作成した関係で、同期しきれていないデータが存在します。
今回の対応では、サービス的に「作業中のデータは捨てて構わない」という判断がなされたので実行しましたが、本質的なスレーブを作る必要があります。

フェイルオーバーしたい

マスターがダウンした際、自動でスレーブをマスターに昇格させる冗長化（フェイルオーバー）の対応は、今後の課題です。

参照系のサーバーとしてスレーブを利用

プログラムで、特に重い検索クエリをスレーブに向けることで、パフォーマンスチューニングをさらに進めたいです。

所感

「簡単にできる」と言われていたものほど、奥深い。
簡単には簡単な理由があるし、複雑には複雑な理由がある。
設定項目をマニュアル通りに実行するだけではなく、1つ1つ意味を調べて実行して文章にまとめる。
それが楽しいと思う瞬間なのだと毎回思います。

「痒いところに全部手が届いてる」ようなエントリー目指して今後も妥協しない姿勢を保っていきたいと思います。

その他

MySQL 5.5系のバグ

MySQL 5.6.10 以下に存在するレプリケーションのバグと詳細について

SlaveDB の MySQL のバージョンが 5.6.10 以下

バイナリログのフォーマットが ROW もしくは MIXED

MasterDB の table_id が UINT_MAX (64bit Linux 環境で 4294967295) を超えた場合

MasterDB の table_id が UINT_MAX を超えると、SlaveDB 側でオーバーフローが発生し、書き込み対象のテーブルを取得できず、テーブルへの書き込みに失敗する、という事象です。

table_id は MySQL を再起動しない限り増え続ける値のため、長期間に渡り MySQL を運用している環境にて、特に注意が必要だと考えられます。

参考

MySQLにおけるレプリケーション遅延の傾向と対策
 SQL_SLAVE_SKIP_COUNTERについて教えてもらったよ
 【DBA中級者向け】MySQL レプリケーションの設定

MySQL レプリケーション設定 -手順・各種ステータスの詳細とトラブルシューティング-