MySQL マスターとスレーブの不整合とその解決策の詳細な説明

1. MySQL マスタースレーブ非同期

1.1 ネットワーク遅延

MySQLのマスタースレーブレプリケーションは、binlogに基づく非同期レプリケーションであるため

バイナリログファイルをネットワーク経由で転送する場合、ネットワーク遅延がマスターとスレーブの非同期の主な原因であることは言うまでもありません。これは、コンピュータルーム間でデータを同期する場合に特に発生する可能性があります。したがって、読み取りと書き込みの分離が必要であり、ビジネス層からの初期設計に注意を払う必要があります。

1.2 マスターマシンとスレーブマシンの負荷が一致しない

MySQL マスタースレーブレプリケーションでは、マスターデータベースで 1 つの IO スレッドが開始され、そこから 1 つの SQL スレッドと 1 つの IO スレッドが開始されるため、いずれかのマシンの負荷が非常に高く、ビジー状態の場合、いずれかのスレッドのリソースが不足し、マスタースレーブ間の不整合が発生します。

1.3 一貫性のない max_allowed_packet 設定

マスターデータベースに設定されている max_allowed_packet がスレーブデータベースよりも大きくなっています。マスターデータベースでは大きな SQL 文を実行できますが、スレーブデータベースでは小さく設定されているため実行できず、マスターとスレーブの間で不整合が発生します。

1.4 一貫性のない自動インクリメントキー

マスタースレーブの不整合は、キー自動増分キーの開始キー値と自動増分ステップ設定の不整合によって発生します。

1.5 同期パラメータ設定の問題

MySQL の異常なダウンタイムが発生した場合、sync_binlog=1 または innodb_flush_log_at_trx_commit=1 が設定されていないと、binlog または relaylog ファイルが破損し、マスターとスレーブ間で不整合が発生する可能性が高くなります。

1.6 バグ

MySQLのバグによって生じるマスターとスレーブの非同期

1.7 バージョンの不一致

特に、上位バージョンがマスターで下位バージョンがスレーブの場合、マスターデータベースでサポートされている機能はスレーブデータベースではサポートされません。

1.8 マスタースレーブ不整合最適化構成

上記の状況を踏まえて、まずmax_allowed_packet、自動増分キーの開始点と増加点が一致するように設定し、パフォーマンスを犠牲にしてマスターでsync_binlogを有効にします。innodbを使用するライブラリの場合は、次の構成が推奨されます。

innodb_flush_logs_at_trx_commit = 1
innodb-support_xa = 1 # MySQL 5.0 以上 innodb_safe_binlog # MySQL 4.0

同時に、上記の推奨事項から次の2つのパラメータを追加します。

スレーブ開始をスキップ
読み取り専用

2. マスタースレーブ非同期問題を解決する方法

2.1 マスタースレーブ非同期シナリオの説明

今日、Mysqlのマスターデータベースとスレーブデータベースが同期されていないことがわかりました

まずマスターライブラリに移動します。

mysql>プロセスリストを表示します。

プロセスがスリープしすぎていないか確認します。正常だとわかりました。

マスターステータスを表示します。

メインデータベースのステータスが正常であることを確認します。

mysql> マスターステータスを表示します。
+-------------------+----------+--------------+-------------------------------+
| ファイル | 位置 | Binlog_Do_DB | Binlog_Ignore_DB |
+-------------------+----------+--------------+-------------------------------+
| mysqld-bin.000001 | 3260 | | mysql、テスト、情報スキーマ |
+-------------------+----------+--------------+-------------------------------+
セット内の 1 行 (0.00 秒)

次にスレーブをチェックします

mysql>スレーブステータスを表示\G                        

スレーブIO実行中: はい
スレーブSQL実行中: いいえ

これはスレーブが同期していないことを示しています

2.2 解決策1: エラーを無視して同期を続行する

この方法は、マスターデータベースとスレーブデータベースのデータがそれほど変わらない場合、またはデータを完全に統合できない場合に適しています。データ要件が厳しくない状況に適しています。

奴隷を停止します。

ステップをスキップしたエラーを示します。後ろの数字は可変です

グローバル sql_slave_skip_counter を 1 に設定します。
スレーブを起動します。

次に、mysql> show slave status\G を使用して次を表示します。

スレーブIO実行中: はい
スレーブSQL実行中: はい

OK、マスターとスレーブの同期ステータスは正常になりました。。。

2.3 方法2: マスタースレーブを再度実行し、同期を完了する

この方法は、マスターデータベースとスレーブデータベースのデータが大きく異なる場合や、データを完全に統合する必要がある場合に適しています。

解決手順は次のとおりです。

1.まずメインデータベースに入り、データが書き込まれないようにテーブルをロックします。

コマンドを使用します:

mysql> 読み取りロック付きのテーブルをフラッシュします。

注: これは読み取り専用としてロックされており、ステートメントでは大文字と小文字は区別されません。

2. データのバックアップを実行する

データをmysql.bak.sqlファイルにバックアップします。

[root@server01 mysql]#mysqldump -uroot -p -hlocalhost > mysql.bak.sql

ここで注意すべき点が 1 つあります。データベースのバックアップは定期的に実行する必要があります。データの安全性を確保するには、シェルスクリプトまたは Python スクリプトを使用するのが便利です。

3. マスターステータスを確認する

mysql> マスターステータスを表示します。 
+——————-+————-+————–+——————————-+ 
| ファイル | 位置 | Binlog_Do_DB | Binlog_Ignore_DB | 
+——————-+————-+————–+——————————-+ 
| mysqld-bin.000001 | 3260 | | mysql、テスト、情報スキーマ | 
+——————-+————-+————–+——————————-+ 
セット内の 1 行 (0.00 秒)

4. データ復旧のためにMySQLバックアップファイルをスレーブマシンに転送する

scpコマンドの使用

[root@server01 mysql]# scp mysql.bak.sql [email protected]:/tmp/

5.スレーブ状態を停止する

mysql> スレーブを停止します。

6. 次に、データベースからmysqlコマンドを実行してデータのバックアップをインポートします。

mysql> ソース /tmp/mysql.bak.sql

7. スレーブデータベースの同期を設定します。同期ポイントは、マスターデータベースの show master status 情報の | File | Position 項目であることに注意してください。

マスターを、master_host = '192.168.1.206'、master_user = 'rsync'、master_port=3306、master_password=”、master_log_file = 'mysqld-bin.000001'、master_log_pos=3260 に変更します。

8.スレーブ同期を再開する

mysql> スレーブを起動します。

9. 同期ステータスを確認する

mysql> スレーブステータスを表示\G 表示:

スレーブIO実行中: はい
スレーブSQL実行中: はい

OK、同期が完了しました。

3. MySQLマスターとスレーブ間の遅延を監視する方法

3.1 はじめに:

日常業務では、MYSQL マスタースレーブレプリケーションを確認する 2 つの側面があります。

コピーの全体的な構造が完全であることを確認します。

データが一貫しているかどうかを確認する必要があります。

前者については、レプリケーションスレッドが正常に動作しているかどうか、マスタースレーブ間の遅延が許容範囲内かどうかを監視できます。後者については、マスターテーブルとスレーブテーブル内のデータの md5 コードが一致しているかどうかを確認することで、データの一貫性を確保できます。確認には、Maatkit ツールキットの mk-table-checksum ツールを使用してください。
このドキュメントでは、マスタースレーブ遅延を確認する方法について説明します。

マスタースレーブ遅延判定には通常、Seconds_Behind_Masterとmk-heartbeatの2つの方法があります。

3.2 方法1.

show slave status\G コマンド出力の Seconds_Behind_Master パラメータの値を監視することで、マスタースレーブ遅延があるかどうかを判断できます。

mysql> スレーブステータスを表示します\G;
************************** 1. 行 ****************************
        Slave_IO_State: マスターがイベントを送信するのを待機中
         マスターホスト: 192.168.1.205
         マスターユーザー: repl
         マスターポート: 3306
        接続再試行: 30
       マスターログファイル: edu-mysql-bin.000008
     読み取りマスターログ位置: 120
        リレーログファイル: edu-mysql-relay-bin.000002
        リレーログ位置: 287
    リレーマスターログファイル: edu-mysql-bin.000008
       スレーブIO実行中: はい
      スレーブSQL実行中: はい
       レプリケート_Do_DB: 
     レプリケート_無視_DB: 
      テーブルの複製: 
    無視テーブルを複製: 
   Replicate_Wild_Do_Table: 
 Replicate_Wild_Ignore_Table: 
          最終エラー番号: 0
          最終エラー: 
         スキップカウンタ: 0
     実行マスターログ位置: 120
       リレーログスペース: 464
       Until_Condition: なし
        ログファイルまで: 
        ログ位置まで: 0
      マスターSSL許可: いいえ
      マスターSSLCAファイル: 
      マスターSSLCAパス: 
       マスターSSL証明書: 
      マスターSSL暗号: 
        マスターSSLキー: 
    マスターより遅れている秒数: 0
Master_SSL_Verify_Server_Cert: いいえ
        最終IOエラー番号: 0
        最後のIOエラー: 
        最終SQLエラー番号: 0
        最後のSQLエラー: 
 Replicate_Ignore_Server_Ids: 
       マスターサーバーID: 205
         マスター_UUID: 7402509d-fd14-11e5-bfd0-000c2963dd15
       マスター情報ファイル: /home/mysql/data/master.info
          SQL_遅延: 0
     SQL_残り遅延: NULL
   Slave_SQL_Running_State: スレーブはすべてのリレー ログを読み取りました。スレーブ I/O スレッドがそれを更新するのを待機しています。
      マスター再試行回数: 86400
         マスターバインド: 
   最終IOエラータイムスタンプ: 
   最終SQLエラータイムスタンプ: 
        マスターSSL証明書: 
      マスターSSLCrlパス: 
      取得済み_Gtid_Set: 
      実行されたGtidセット: 
        自動位置: 0
セット内の 1 行 (0.00 秒)

上記は show slave status\G の出力です。これらの構造は、監視に有用な多くのパラメータを提供します。

Slave_IO_Running このパラメータは、io_thread の監視項目として使用できます。Yes は、io_thread がマスターデータベースに正常に接続され、レプリケーション作業を実行できることを意味します。No は、マスターデータベースとの通信が異常であることを意味します。これは主に、マスターとスレーブ間のネットワークによって発生します。

Slave_SQL_Running このパラメータは、sql_thread が正常かどうか、具体的にはステートメントが正常に実行されたかどうかを示します。主キーが重複していたり、テーブルが存在しないという状況がよく発生します。

Seconds_Behind_Master は、sql_thread によって実行されたイベントのタイムスタンプと io_thread によってコピーされたイベントのタイムスタンプ (ts と略記) を比較して得られる差分値です。NULL - io_thread または sql_thread のいずれかが失敗したことを示します。つまり、スレッドの実行ステータスは Yes ではなく No です。 0 — この値はゼロであり、これが最も注目する値です。これは、マスタースレーブレプリケーションが良好であり、遅延が存在しないことを意味します。正の値 - マスターとスレーブの間に遅延があることを示します。数値が大きいほど、スレーブはマスターより遅れます。負の値 - めったに見られません。上級 DBA がこれを見たと言っているのを聞いたことがあります。実際、これはバグ値です。このパラメータは負の値をサポートしていないため、表示されないはずです。

注：Seconds_Behind_Masterの計算方法は問題を引き起こす可能性があります。ご存知のとおり、マスターデータベースのリレーログとbinログの内容はまったく同じです。SQL文を記録するときは、その時点のtsが記録されるため、参照値はbinlogから取得されます。実際には、マスターとスレーブはNTPで同期する必要はありません。つまり、マスターとスレーブのクロックの一貫性を保証する必要はありません。また、比較は実際には io_thread と sql_thread の間で行われ、io_thread は実際にはメインデータベースに関連しているため、問題が発生します。

マスターデータベースの I/O 負荷が大きい場合やネットワークがブロックされている場合、io_thread は binlog を時間内にコピーできません (中断されずにコピーが継続されます)。一方、sql_thread は常に io_thread スクリプトに追いつくことができます。この場合、Seconds_Behind_Master の値は 0 になります。

私たちはそれが遅延ではないと考えていますが、実際にはそうではありません。このため、このパラメータを使用してデータベースに遅延があるかどうかを監視するのは不正確だと批判されることがあります。ただし、この値は必ずしも不正確というわけではありません。

io_thread とマスターネットワークが非常に良好な場合、この値も非常に貴重です。 ''前に、Seconds_Behind_Master パラメータは負の値になることを説明しました。この値は、io_thread の最新の ts と sql_thread によって実行された ts の差であることはすでにわかっています。

前者は常に後者よりも大きくなります。唯一の可能性は、イベントの ts にエラーが発生し、それが前のものよりも小さくなることです。これが発生すると、負の値が表示されることがあります。

3.2 方法2

mk-heartbeat: Maatkit ユニバーサルツールキット内のツールで、レプリケーションの遅延を正確に判断する方法と考えられています。

mk-heartbeat の実装も timestmp 比較を利用して実現されます。まず、同じ NTP サーバーとクロックを同期して、マスターサーバーとスレーブサーバーの一貫性を確保する必要があります。マスターデータベースにハートビートテーブルを作成する必要があります。このテーブルには、少なくとも id と ts の 2 つのフィールドが含まれています。id は server_id で、ts は現在のタイムスタンプ now() です。この構造はスレーブデータベースにもコピーされます。テーブルが構築された後、マスターデータベースでバックグラウンドプロセスモードで更新操作コマンドの行が実行され、定期的にテーブルにデータを挿入します。デフォルトのサイクルは 1 秒です。同時に、スレーブデータベースはバックグラウンドで監視コマンドを実行し、コピーされたレコードの ts 値をマスターデータベースの同じ ts 値と比較します。差が 0 の場合、遅延がないことを意味します。差が大きいほど、遅延の秒数が多くなります。レプリケーションは非同期であり、ts が完全に一貫していないことは誰もが知っているため、ツールは 0.5 秒のギャップを許容し、この範囲内の差は無視して遅延がないものと見なすことができます。このツールは実際のコピーを使用し、タイムスタンプを巧みに使用して遅延をチェックします。

以上がこの記事の全内容です。皆様の勉強のお役に立てれば幸いです。また、123WORDPRESS.COM を応援していただければ幸いです。。

以下もご興味があるかもしれません: