MySQL マスタースレーブ同期メカニズムと同期遅延問題追跡プロセス

序文

DBA として、仕事中に MySQL マスターとスレーブの同期遅延の問題に遭遇することがよくあります。これらの同期遅延の問題には、実際には多くの原因があり、マスターとスレーブ間のネットワークの問題、ネットワーク帯域幅の問題、大規模なトランザクション、またはシングルスレッドのレプリケーションによる遅延が原因である可能性があります。

今日、問題が発生しました。Mysql がエラーを報告し続け、マスターとスレーブの同期の遅延が大きすぎたり間違っていたりしました。そこでこの記事では、マスター/スレーブ同期のメカニズム原理とトラブルシューティングのアイデアを紹介します。

障害の顕在化

最も直感的なパフォーマンスは次のとおりです。

mysql> スレーブステータスを表示します\G;
 // 状態 1 Seconds_Behind_Master: NULL
 // 状態 2 Seconds_Behind_Master: 0
 // 状態 3 Seconds_Behind_Master: 79

継続的なクエリでは、属性値はほとんどの場合 0 ですが、Null や 79 などの遅延値が表示されることもあります。これにより、マスタースレーブ同期遅延の監視が継続して警告されます。

原因と解決策

複数のバックアップサーバーのサーバー ID が同じであるため、ホストは長時間バックアップサーバーに接続できず、正常に同期できません。

サーバー ID を変更した後、データベースを再起動して回復します。

マスタースレーブ同期メカニズム

MySQL マスタースレーブ同期 (レプリケーションとも呼ばれます) は、次の主な機能を備えた組み込みの高可用性および高パフォーマンスのクラスターソリューションです。

データ分散: 同期には大きな帯域幅は必要なく、複数のデータセンターにデータを複製できます。
読み取り負荷分散: サーバークラスターを通じて、DNS ポーリングや Linux LVS などの GSLB (グローバル負荷分散) 方式を使用して、メインサーバーの読み取り負荷を軽減できます。
データベースのバックアップ: レプリケーションはバックアップの一部ですが、バックアップの代わりになるものではありません。スナップショットと組み合わせる必要もあります。
高可用性とフェイルオーバー: スレーブサーバーはマスターサーバーにすばやく切り替えることができるため、ダウンタイムと回復時間が短縮されます。

マスタースレーブ同期は 3 つのステップに分かれています。

マスターサーバー (master) は、データの変更をバイナリログ (binlog) に記録します。
スレーブサーバーは、マスターサーバーのバイナリログを自身のリレーログにコピーします。
サーバーからのリレーログのログをやり直し、変更を独自のデータベースに適用して、データの一貫性を実現します。

マスタースレーブ同期は非同期リアルタイム同期であり、リアルタイムで送信しますが、実行に遅延があります。マスターサーバーの負荷が大きい場合、遅延もそれに応じて増加します。

上の図から、合計 3 つのスレッドが必要であることがわかります。

プライマリサーバーのログ転送スレッド: バイナリログの増分をスタンバイサーバーに転送する役割を担う
スレーブサーバーのI/Oスレッド: マスターサーバーのバイナリログを読み取り、リレーログとして保存する役割を担う
スレーブサーバーのSQLスレッドはリレーログの実行を担当します。

MySQL スレッドを表示

MySQL のステータスを表示するにはshow full processlist;コマンドを使用できます。

ホストのステータス:

スタンバイマシンのステータス:

ご覧のとおり、私のクラスターアーキテクチャは 1 台のホストと 4 台のスタンバイマシンで構成されているため、ホストには 4 つの同期スレッド (すべての binlog データはスタンバイマシンに送信され、binlog ログの更新を待機しています) と 1 つの表示コマンドスレッド (完全なプロセスリストを表示) があります。スタンバイマシンには、表示コマンドスレッドが 1 つ、I/O スレッドが 1 つ (マスターが同期データイベントを送信するのを待機中)、および SQL スレッドが 1 つ (すべてのリレーログを読み取り、I/O スレッドによる更新を待機中) あります。

同期ステータスの表示

マスタースレーブ同期は非同期かつリアルタイムであるため、遅延が発生します。スタンバイマシンの同期遅延を表示するには、show slave status; を使用します。

マスタースレーブ同期で注意する必要があるいくつかのプロパティは赤でマークされています。

Slave_IO_State: 現在のI/Oスレッドの状態
Master_Log_File: 現在同期されているマスターサーバーのバイナリファイル
Read_Master_Log_Pos: 現在同期されているマスターサーバーのバイナリファイルのオフセット（バイト単位）。図に示すように、12.9M（13630580/1024/1024）が同期されています。
Relay_Master_Log_File: 現在のリレーログ同期のバイナリファイル
Slave_IO_Running: スレーブサーバー内の I/O スレッドの実行ステータス。YES は正常に実行されていることを意味します。
Slave_SQL_Running: スレーブサーバー内の SQL スレッドの実行ステータス。YES は正常に実行されていることを意味します。
Exec_Master_Log_Pos: 同期が完了したマスターサーバーのバイナリログオフセットを示します。
Seconds_Behind_Master: スレーブサーバーのデータがマスターサーバーより遅れている期間を示します。

show master status; コマンドを使用して、マスターサーバーの実行ステータスを表示することもできます。

通常のマスタースレーブ同期ステータス:

スレーブIO実行中: はい
スレーブSQL実行中: はい
マスターより遅れている秒数: 0

トラブルシューティング

マスタースレーブ同期のメカニズムを理解した後、今日遭遇した問題を見てみましょう。スタンバイマシンのステータスを確認すると、3 つの状態でいくつかの重要な属性値が観察されます。

mysql> スレーブステータスを表示します\G;
#状態1:
 Slave_IO_State: マスターイベントの読み取りに失敗した後に再接続中
 スレーブIO実行中: いいえ
 スレーブSQL実行中: はい
 マスターより遅れている秒数: NULL
#状態2:
 Slave_IO_State: マスターがイベントを送信するのを待機中
 スレーブIO実行中: はい
 スレーブSQL実行中: はい
 マスターより遅れている秒数: 0
#状態3:
 Slave_IO_State: マスターイベントをリレーログにキューイング
 スレーブIO実行中: はい
 スレーブSQL実行中: はい
 マスターとの差: 636

MySQL マスタースレーブレプリケーションスレッドの状態遷移を通じて、3 つの状態の異なる意味を確認できます。

# 状態 1# スレッドはマスター サーバーに再接続しようとしています。接続が再確立されると、状態はマスターがイベントを送信するのを待機中に変わります。
マスターイベントの読み取りに失敗した後の再接続
# 状態 2# スレッドはプライマリ サーバーに接続し、バイナリ ログ イベントが到着するのを待機しています。プライマリ サーバーがアイドル状態の場合は、さらに長く続く可能性があります。待機が slave_read_timeout 秒続くと、タイムアウトが発生します。この時点で、スレッドは接続が切断されたと見なし、再接続を試みます。
マスターがイベントを送信するのを待っています

# 状態 3 # スレッドはイベントを読み取り、SQL スレッドが処理できるようにリレー ログにコピーしています。
マスターイベントをリレーログにキューイングする

ここでは、何らかの理由でスレーブサーバーがマスターサーバーから切断され、再接続を試行し続け、再接続が成功した後に再び切断されていると推測できます。

ホストの動作を見てみましょう。

問題は 10.144.63.* と 10.144.68.* の 2 台のマシンで発生していることがわかりました。そのうちの 1 台のエラーログを確認しました。

190214 11:33:20 [注記] スレーブ: サーバーから終了パケットを受信しました。マスターがシャットダウンしたようです。
190214 11:33:20 [注記] スレーブ I/O スレッド: ログイベントの読み取りに失敗しました。再試行のために再接続しています。位置 13628070 のログ 'mysql-bin.005682'

Google でキーワード「Slave: received end packet from server, apparent master shut down:」を検索すると、「Confusing MySQL Replication Error Message」の記事で、原因は 2 つのスタンバイサーバーのサーバー ID が重複していることであることがわかります。

ある日、私にもそれが起こり、それを知るのにほぼ 1 時間かかりました。
今後は、常にベースの my.cnf を使用して他のサーバーにコピーし、最初にサーバー ID を増やします。
MySQL では数値の代わりにサーバー名を使用できますか?

バグ修正

問題を特定した後、重複があるかどうかを確認したところ、2 台のバックアップマシンのフィールドが実際に同じであることがわかりました。

vim の my.cnf

#レプリケーション
ログ bin = mysql bin
# この乱数は server-id=177230069 と同じです
同期バイナリログ=1

別の番号を変更して保存し、MySQL プロセスを再起動すると、アラームが復元されます。

要約する

結局のところ、この問題の解決方法は非常に簡単ですが、問題のトラブルシューティングを行う際には、最初は混乱していたものの、最後にはアイデアが明確になるという変化がよく見られます。この記事の主な利点は、マスタースレーブ同期のメカニズムと問題を追跡するためのアイデアを理解できるようにすることです。次回は、マスタースレーブ同期によってもたらされる問題を迅速に解決できることを願っています。

さて、以上がこの記事の全内容です。この記事の内容が皆さんの勉強や仕事に一定の参考学習価値を持つことを願っています。ご質問があれば、メッセージを残してコミュニケーションしてください。123WORDPRESS.COM をご愛顧いただきありがとうございます。

参考文献