DevOpsツールとベストプラクティスの習得 - DevOpsナレッジハブ DevOpsツールとベストプラクティスの習得 - DevOpsナレッジハブ

よくあるMySQLレプリケーション障害を迅速にトラブルシューティングする

この実践ガイドで、よくあるMySQLレプリケーション障害を迅速に解決しましょう。`SHOW REPLICA STATUS`からのエラーコードの解釈、MySQLエラーログの調査、バイナリログの目的の理解を学びます。この記事では、重複エントリ、欠落したbinlogファイル、データの乖離などの問題を診断するための実用的な手順とベストプラクティスを提供し、健全なレプリケーション設定を維持するのに役立ちます。

よくあるMySQLレプリケーション障害を迅速にトラブルシューティングする

MySQLレプリケーション障害は、2つの質問を分けて考えると修正が容易になります。レプリカはソースからイベントを取得できるか、そして取得したイベントを適用できるか？これらは異なる障害です。ネットワークの問題、バイナリログの欠落、パスワードの誤り、ホスト権限の誤りは通常I/Oスレッドを停止させます。重複キー、行の欠落、DDLの不一致、データのずれは通常SQLスレッドを停止させます。

ステータス出力から始めましょう。現代のMySQLでは：

SHOW REPLICA STATUS\G

古いシステムでは：

SHOW SLAVE STATUS\G

サーバーがサポートするコマンドを使用してください。新しい出力ではReplica_IO_Running、Replica_SQL_Running、Seconds_Behind_Sourceなどの名前が使用されます。古い出力ではSlave_IO_Running、Slave_SQL_Running、Seconds_Behind_Masterが使用されます。

最初に確認すべき項目は：

  • Replica_IO_Running：レプリカが接続され、ソースのバイナリログを読み取っているかどうか。
  • Replica_SQL_Running：レプリカがリレーログイベントを適用しているかどうか。
  • Last_IO_ErrnoとLast_IO_Error：フェッチが失敗した理由。
  • Last_SQL_ErrnoとLast_SQL_Error：適用が失敗した理由。
  • Relay_Master_Log_File、Exec_Master_Log_Pos、または新しいソース位置フィールド：レプリカがストリームのどこにいるか。

修正にすぐに飛びつかないでください。最初に完全なステータス出力をインシデントノートにコピーしてください。RESET REPLICAを実行したり、トランザクションをスキップしたり、レプリカを再指定したりすると、最良の証拠の一部が失われます。

I/Oスレッドが停止している場合

Replica_IO_RunningがNoの場合、レプリカはソースからの読み取りに成功していません。SQLスレッドはしばらく古いリレーログイベントを適用し続けるかもしれませんが、最終的には尽きます。

一般的な原因は：

  • ソースのホストまたはポートが間違っている。
  • ファイアウォール、セキュリティグループ、またはルーティングルールが接続をブロックしている。
  • レプリケーションユーザーのパスワードが間違っている。
  • レプリケーションユーザーがレプリカが実際に使用するものとは異なるホストから許可されている。
  • ソースでバイナリログが無効になっている。
  • ソースがレプリカが要求したバイナリログファイルをパージした。
  • TLS設定が変更され、レプリカが認証できなくなった。

レプリカホストからテスト：

mysql -h source-db.example.com -u repl_user -p

直接ログインが失敗した場合、レプリケーションも失敗します。ソースでアカウントを確認：

SHOW GRANTS FOR 'repl_user'@'replica_host_or_ip';

アカウントにはREPLICATION SLAVE権限が必要です。権限名はMySQLの権限でまだ「SLAVE」を使用しています。

また、バイナリログが有効かどうかを確認：

SHOW VARIABLES LIKE 'log_bin';
SHOW MASTER STATUS;

新しいバージョンでは、SHOW BINARY LOG STATUSが利用可能かもしれません。ポイントは同じです：ソースにはバイナリログが必要であり、要求されたファイルがまだ存在している必要があります。

エラー1236：バイナリログの欠落または読み取り不能

Last_IO_Errno: 1236は、レプリカがソースが提供できないバイナリログファイルまたは位置を要求していることを意味するエラーの1つです。正確なメッセージは異なります。最初のログファイルが見つからない、ログイベントが読み取れない、または読み取り中にソースが接続を閉じたと言うかもしれません。

最も一般的な運用上のケースは単純です：レプリカが長時間ダウンしており、ソースが必要なバイナリログをパージしたということです。

ソースに残っているログを確認：

SHOW BINARY LOGS;

次に、そのリストをレプリカステータスで指定されたファイルと比較します。レプリカがmysql-bin.000120を必要とし、ソースが現在mysql-bin.000140から始まる場合、レプリカはバイナリログから追いつくことができません。

現実的な選択肢は3つあります：

  • ソースから取得した新しいバックアップからレプリカを復元または再構築する。
  • プロセスがサポートしている場合、必要なデータがまだある別のレプリカをクローンソースとして使用する。
  • GTIDを使用していて、欠落したトランザクションが他の場所に存在する場合、それらを提供できる有効なソースから再構成する。

レプリケーションを開始させるためだけに、新しいログ位置を推測しないでください。これにより、欠落したトランザクションがあるレプリカが作成されます。正常に見えても、静かに間違ったデータを返す可能性があります。

復旧後、ディスク容量が許せばバイナリログの保持期間を増やします：

[mysqld]
binlog_expire_logs_seconds=604800

この例は約7日です。メンテナンスやインシデント中にレプリカがオフラインになる可能性がある時間に基づいて値を選択してください。

SQLスレッドが停止している場合

Replica_SQL_RunningがNoの場合、レプリカはイベントをフェッチしたが適用できなかったことを意味します。これは多くの場合、接続の問題ではなくデータの整合性の問題です。

完全なLast_SQL_Errorを読んでください。通常、テーブル、キー、失敗した操作、場合によってはソースログの位置が示されます。その後、何かを変更する前に、ソースとレプリカの両方で関連する行を調査します。

既知のバイナリログ位置の周りの失敗したイベントについては、mysqlbinlogでイベントを表示できます：

mysqlbinlog --start-position=123456 --stop-position=124500 /var/lib/mysql/mysql-bin.000321

ソースのバイナリログがローカルホストにない場合は、リモートオプションを使用するか、コピーしたログファイルを調査します。行ベースのイベントには注意してください：読み取り可能にするためにデコードオプションとテーブルメタデータが必要な場合があります。

エラー1062：重複エントリ

Last_SQL_Errno: 1062は、レプリカが行を挿入または更新しようとして、すでに存在するユニークキーにヒットしたことを意味します。

典型的な原因は：

  • 誰かがレプリカに直接書き込んだ。
  • レプリカが間違ったスナップショットから初期化された。
  • 以前のレプリケーションエラーがスキップされた。
  • マルチソースまたはアクティブ-アクティブ設計でオートインクリメント設定が間違っている。
  • アプリケーションの書き込みが誤って2つの書き込み可能サーバーに送られた。

誘惑される修正は：

STOP REPLICA;
SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;
START REPLICA;

古い構文ではSTOP SLAVEとSTART SLAVEを使用します。これは、行が重要でないことを確認した後、使い捨てのレポートレプリカには許容されるかもしれません。後で昇格される可能性があるレプリカには危険です。スキップすると、レプリカはソースと同じトランザクション履歴を持たなくなります。

より安全なプロセスは：

  1. 競合するテーブルとキーを特定する。
  2. ソースとレプリカの行を比較する。
  3. レプリカの行を削除、更新するか、レプリカを再構築するかを決定する。
  4. 決定を記録する。これはデータ整合性イベントであるため。

レプリカがフェイルオーバー用である場合、未知の違いを手動で修正するよりも再構築の方がクリーンなことがよくあります。

エラー1032：レコードが見つからない

Last_SQL_Errno: 1032は通常、レプリカがローカルに存在しない行を更新または削除しようとしたことを意味します。これは多くの重複キー問題の鏡像です。ソースには行がありましたが、レプリカにはありませんでした。

一般的な原因は：

  • レプリカで行が手動で削除された。
  • 以前のトランザクションがスキップされた。
  • 初期ダンプでデータが欠落した。
  • レプリケーションフィルターが以前の書き込みを除外した。

欠落した行が無害だと仮定しないでください。UPDATEが行を見つけられない場合、レプリカはすでにソースと異なっています。影響を受けるキーの周りのカウントとサンプルデータを比較してください。テーブルが小さい場合、テーブルのリロードが合理的かもしれません。大きいまたは重要な場合は、整合性ツールを使用するか、レプリカを再構築してください。

認証とホスト権限の問題

パスワードのローテーションやネットワーク変更後によくある失敗は、アクセス拒否のように見えるI/Oエラーです：

Access denied for user 'repl_user'@'10.0.2.15'

エラー内のホストはMySQLが見るものです。特にNAT、プロキシ、コンテナネットワークでは、期待したホスト名と一致しない場合があります。

ソースでユーザーを調査：

SELECT user, host, plugin FROM mysql.user WHERE user = 'repl_user';

レプリカが10.0.2.15から接続する場合、'repl_user'@'replica.internal'の権限は、名前解決と権限が一致しない限り一致しないかもしれません。ネットワーク設計に合った明示的なホストパターンを優先してください。

プラグインが異なる場合、古いクライアントは新しい認証プラグインを使用するアカウントに対して失敗する可能性があります。クライアントを更新する方が認証を弱めるより通常は良いですが、混合バージョン環境では計画的な互換性変更が必要かもしれません。

リレーログの問題

ソース接続は正常でも、レプリカにリレーログの破損やローカルディスクの問題がある場合があります。エラーはリレーログの読み取り失敗、切り詰められたイベント、またはリレーログの位置を示すかもしれません。

まずディスクの健全性と空き容量を確認してください。ディスクがいっぱいになると、いくつかの奇妙なレプリケーション症状が発生する可能性があります：

df -h
iostat -xz 1

リレーログが破損しているが、ソースに必要なバイナリログがまだある場合、リレーログをリセットしてレプリカに再度フェッチさせることができます。正確なコマンドはバージョンとトポロジーに依存します。リセットコマンドを軽率に実行しないでください。すでに実行されたソースログファイルと位置を確認してください。

多くの場合、この種の問題はレプリカホストに根本的なストレージ問題がある兆候です。レプリカを信頼する前にそれを修正してください。

レプリケーションラグは常に障害ではない

Seconds_Behind_Sourceは両方のスレッドが実行中でも高くなることがあります。これはレプリケーションが生きているが遅れていることを意味します。ラグは停止したスレッドとは異なる扱いをしてください。

確認：

  • レプリカのディスクが飽和状態か？
  • ソースが書き込みバーストを生成しているか？
  • レプリカでの長い読み取りがSQLスレッドと競合しているか？
  • レプリカがソースより小さいか遅いか？
  • バックアップジョブやスナップショットが同時に開始されたか？

ラグが減少している場合、レプリカは追いついています。ラグが増加している場合、負荷を減らすか容量を追加してください。ラグのあるレプリカを再起動しても、持続的なリソースボトルネックを解決することはほとんどありません。

フィルターとマルチソースレプリケーション

レプリケーションフィルターは障害を読み取りにくくする可能性があります。レプリカは意図的に一部のデータベースやテーブルを無視するかもしれませんが、アプリケーションは関連データが存在することを期待するかもしれません。フィルターを使用する場合、レプリカが破損していると仮定する前にそれらを調査してください：

SHOW REPLICA STATUS\G

Replicate_Do_DB、Replicate_Ignore_DB、Replicate_Do_Table、または書き換えルールに言及するフィールドを探してください。古い出力ではSHOW SLAVE STATUSで同じ一般的な名前を使用します。

フィルタリングは特にクロスデータベース書き込みでリスクがあります。トランザクションがapp.ordersとaudit.order_eventsを更新するが、レプリカがauditをフィルターで除外する場合、結果のコピーは技術的にフィルターと整合性があっても、監査行を期待するワークフローには役に立たないかもしれません。ステートメントベースのロギングでは、選択されたデフォルトデータベースがイベントのレプリケーションに影響を与える可能性があるため、データベースフィルターはさらに驚くべきものになります。

マルチソースレプリケーションは別の層を追加します。1つのチャネルが正常でも別のチャネルが停止している場合があります。その場合、出力の最初のブロックだけを読むのではなく、すべてのチャネルのステータスを確認してください：

SHOW REPLICA STATUS\G

チャネルベースの設定では、ステータス出力にチャネル名が含まれます。正常なチャネルをリセットせずに、失敗したチャネルを修正してください。2つのソースが同じテーブルに重複するキーを書き込むことができる場合、重複キーエラーは多くの場合、一度きりのレプリケーション障害ではなく設計上の問題です。

隠れたデータのずれを避ける

最悪のレプリケーション障害は、Yesと言いながら間違ったデータを含むものです。ずれは、スキップされたトランザクション、レプリカへの直接書き込み、失敗したインポート、悪いフィルター、手動修正の後に発生する可能性があります。

重要なレプリカについては、整合性チェックをスケジュールしてください。Percona Toolkitのpt-table-checksumが一般的に使用され、pt-table-syncは制御された状況での差異の修復に役立ちます。これらのツールは負荷を生成する可能性があるため、最初にテストし、本番環境に合った制限で実行してください。

また、偶発的な書き込みからレプリカを保護します：

[mysqld]
read_only=ON
super_read_only=ON

アプリケーションの読み取りには別の資格情報を使用してください。アプリケーションユーザーに「念のため」レプリカへの広範な書き込み権限を与えないでください。

迅速なインシデントチェックリスト

レプリケーションが壊れたときはこの順序を使用してください：

  1. SHOW REPLICA STATUS\G出力を保存する。
  2. I/OスレッドまたはSQLスレッドが停止したか確認する。
  3. Last_IO_ErrorまたはLast_SQL_Errorを読む；エラー番号だけに頼らない。
  4. MySQLエラーログで一致するタイムスタンプを確認する。
  5. I/O障害の場合、ネットワーク、資格情報、権限、TLS、バイナリログの可用性をテストする。
  6. SQL障害の場合、ソースとレプリカの両方で影響を受ける行またはテーブルを調査する。
  7. 修復するか、文書化されたリスクでスキップするか、テーブルをリロードするか、レプリカを再構築するかを決定する。
  8. 復旧後、実際の書き込みテストを実行し、ラグを監視する。

ほとんどのMySQLレプリケーション障害は、1つの魔法のコマンドで解決されるわけではありません。証拠を保存し、どのスレッドが失敗したかを特定し、実行中だが信頼できないレプリカを残さない修正を選択することで解決されます。