レプリケーションエラー対処法（for MySQL）

おことわり

• MySQL におけるレプリケーションエラーへの対処を社内向けに書いたものを一部修正したものです
• レプリケーションの仕組みに関してはココには書いてません

はじめに

• 必ず、更新クエリを叩く前に master であるかを確認してから叩くべし！
  • めっちゃ当たり前なんですが一応…
  • エラーが起こらないに越したことはない
• 後述のデータ復元で役に立つので、更新する前のデータを控えておくと被害が最小限で済みがち
  • たとえば Sequel Pro を使っている場合は、該当行を選択して右クリックメニューから INSERT 文としてコピー を選んでどっかに控えておくとかで良い
• それでもエラーが起きてしまった（slave を更新したことに気づいた）際には以下を参考にすべし

想定されるケース

更新対象のテーブルがない

• slave に間違って DROP TABLE とかしてテーブル自体を飛ばした後、INSERT とか叩かれない限り通常起こり得ない
  • まあ、可能性としてはある…

更新対象のテーブル構造が master と違う

• これも通常は起こり得ないが、slave に誤って ALTER TABLE とか打っちゃうと起こりがち

INSERT したが、既に同じ UNIQUE KEY を持ったデータが存在する

• slave に間違って INSERT 打ってから、そのまま master に同じクエリを INSERT すると起きる

UPDATE したが、対象のデータが存在しない

• slave を間違って更新し、master で対象のデータに対して更新クエリを叩くと起きる

DELETE したが、対象のデータが存在しない

• UPDATE のケースと起こる条件はほぼ一緒

対処法

slave で更新クエリを実行したことに気づいたとき

とにかく master と状態を合わせる

• 前述のとおり、もし更新する前の状態（データ内容）が分かっていればそれに復元させることが第一
  • もし誤って slave に INSERT してしまった場合は、すぐに該当のデータを消せば良い
  • UPDATE の場合は、UNIQUE KEY が変わっていると厄介なので、すぐに元の状態に UPDATE し直す
  • DELETE の場合は、そのまま無視して後からスキップするのでも良いが、レプリケーションが止まる前に元の状態に戻しておいたほうが手数が少なくてすむ
  • ALTER TABLE DROP TABLE などの場合も同様だが、テーブルサイズが大きい（≒データ復元のほうが時間がかかる）場合など、むしろエラーが起こった後にスキップしたほうが時間がかからないケースもある
• そもそも slave に更新クエリを走らせちゃったことに早く気づけるかどうかが状況を大きく左右するので、念入りに確認すべし
  • 気づければ超ラッキーくらいの気持ちで

レプリケーションエラーが起きた後の対処

エラーが起きる条件

• slave に更新クエリを打った後、master に特定の更新クエリが走った時点でレプリケーションエラーは起きる
  • なので、更新頻度が少ないテーブルでは、slave に更新クエリを打った大分後に気づくケースがある

レプリケーション止まったらすぐやること

• とりあえず SHOW SLAVE STATUS でどんなエラーが出ているかを見る
  • エラーが起きたクエリとかは出てこないが、どのテーブルでどんなエラーが起きているかはわかるので、そこから判断する
• 参照系として使われている slave がエラーになった場合はできるだけ早くAPサーバー等から参照されないようにする
  • 対処が終わった後に入れるのを忘れずに

データ挿入・更新系（INSERT, UPDATE とか）のエラーの場合

• 対象のデータが判明している場合は、該当のデータをエラーが出る直前の master と同じ状態にする
  • 必ずしも最新の master の状態と一緒とは限らない
• どのデータが対象なのかがわからないという場合（エラー直前の状態がわからない、master とテーブル構造が異なっていてエラーになったときも同様）は別途後述

データ削除系（DELETE, DROP TABLE）エラーの場合

• 既にエラーの起きた slave ではデータが消えているということなので無視して良い

テーブル構造変更系（ALTER TABLE）のエラーの場合

• これも既に master と同じ構造になっていればそのまま無視して良い

該当の更新クエリをスキップする（必要に応じて）

• SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1 とすることで、エラーの起きた更新クエリをスキップできる
• UPDATE や INSERT でエラーが発生した際には必ずしもスキップする必要はない
  • エラーが起きる直前の状態にした場合は、むしろエラーが起きたクエリを再実行する必要がある

レプリケーションを再開する

• START SLAVE でレプリケーションを再開
• SHOW SLAVE STATUS を何回か叩いてみて、Seconds_Behind_Master: 0 となるまで待つ

エラー対象がわからない場合

ほとんど更新がないテーブルの場合

• 該当テーブルを master で dump してそのまま突っ込んでスキップすれば大体解決する

更新頻度がそれなりにあるテーブルの場合

• これもとりあえずは該当テーブルの最新の状態を dump して突っ込むしかない
• が、そうしてからレプリケーションを再開すると、エラーになった後に入ったデータでエラーになるため、その後の更新クエリでエラーになりスキップ、またエラーが起きてスキップ…という無限ループに陥る可能性が高い

とはいえ…

• そもそも、上述の無限ループの正体は一体何かと言うと、エラーが起こった時点から dump を取った時点までの該当テーブルに対する更新クエリ（特に INSERT や DELETE）がエラーになるので、それらをスキップしなければならない、ということである
  • 理論上はエラーになるたびにスキップし続けていけば、いずれは master の状態に追いつく（はず…）
• よって、上述のエラー→スキップ→再開の流れをレプリケーションが追いつくまで自動でやるスクリプトを書いて実行したら良いのでは
• あるいは、諦めて slave サーバーを再構築したほうが早いかも？
  • そのへんは更新頻度、どんくらいレプリケーションが遅れてるか、DBサイズ、サーバーコピーにかかる時間…などによってケースバイケース

まとめ

• とにかくDB更新する前には master なのかを確認する
• もし slave を変更してしまっても慌てず元の状態に戻せばだいたいなんとかなる
• レプリケーションエラーが起きてしまったときは状況に応じて対処すべし
  • 削除・テーブル構造変更系のクエリなら、エラーになったクエリをスキップしてレプリケーション再開する
  • 更新系クエリなら、エラー直前の状態に戻してレプリケーション再開する
  • 直前の状態に戻せないときは…
    • 最新の dump を master からコピー
    • レプリケーション再開→エラーになるクエリをスキップする、を繰り返す
  • 大分後になって気づいてしまった場合は再構築も検討