MySQL レプリケーション問題の 3 つのパラメータの分析

01 sql_slave_skip_counter パラメータ

このパラメータは、主に特定のエラーのある「イベント」をスキップするために使用されます。ここで使用されている単語はトランザクションではなくイベントであることに注意してください。これは、その本質がイベントを 1 つずつスキップすることであるためです。このパラメータは、オフセットレプリケーションモードで使用する必要があることに注意してください。gtid レプリケーションモードを使用している場合、このパラメータは使用できません。例を見てみましょう。まず、レプリケーション関係を構築します。

マスター 10.30.124.68

スレーブ 10.30.124.128

これら 2 つのインスタンスは、相互にマスターとスレーブです。テストテーブル test.yeyz を作成し、次のように、id が主キーで一意であるデータを挿入します。

マスター

mysql:(なし) 22:25:56>>test.yeyzから*を選択します。
+----+------+
| ID | 年齢 |
+----+------+
| 1 | 1 |
| 2 | 2 |
| 3 | 3 |
| 4 | 4 |
+----+------+
セット内の 4 行 (0.00 秒)

奴隷について

mysql:(なし) 22:25:38>>test.yeyzから*を選択します。
+----+------+
| ID | 年齢 |
+----+------+
| 1 | 1 |
| 2 | 2 |
| 3 | 3 |
| 4 | 4 |
| 5 | 5 |
+----+------+
セット内の行数は 5 です (0.00 秒)

スレーブノードにはマスターノードよりも 1 つ多くのレコードがあり、id=5 の追加レコードがあることがわかります。次に、マスターノードにデータを挿入します。

mysql:(なし) 22:26:06>>test.yeyzに値（5,5）、（6,6）を挿入します。
クエリは正常、2 行が影響を受けました (0.00 秒)
記録: 2 重複: 0 警告: 0

この時点でスレーブノードを観察します。

mysql:(なし) 22:26:34>>スレーブステータスを表示\G
                  マスターホスト: 10.30.124.68
                  マスターユーザー: dba_repl
                  マスターポート: 4306
                接続再試行: 60
              マスターログファイル:mysqlbin.000002
          読み取りマスターログ位置: 523
               リレーログファイル: スレーブリレーbin.000002
                リレーログ位置: 319
        リレーマスターログファイル: mysqlbin.000002
             スレーブIO実行中: はい
            スレーブSQL実行中: いいえ
                   最終エラー番号: 1062
                   Last_Error: エラーが発生したためコーディネーターが停止しました
 労働者において、最新の失敗は次のとおりです。
 ワーカー0はトランザクション「ANONYMOUS」の実行に失敗しました
 マスターログ mysqlbin.000002、end_log_pos 492。
 エラー ログおよび/または performance_schema.replication_applier_status_by_worker を参照してください。
 この障害または他の障害（ある場合）の詳細については、表を参照してください。
                 スキップカウンタ: 0

スレーブノードの SQL スレッドが切断されていることがわかります。この時点で、マスターノードでこのエラーの位置 492 の binlog をクエリすると、次のようになります。

mysql:(なし) 22:30:28>>194 からの 'mysqlbin.000002' の binlog イベントを表示します。
  +-----------------+-----+----------------+-----------+-------------+------------------------------------------+
| ログ名 | 位置 | イベント タイプ | サーバー ID | ログ終了位置 | 情報 |
+-----------------+-----+----------------+-----------+-------------+------------------------------------------+
| mysqlbin.000002 | 194 | Anonymous_Gtid | 192 | 259 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysqlbin.000002 | 259 | クエリ | 192 | 327 | 開始 |
| mysqlbin.000002 | 327 | Rows_query | 192 | 391 | # test.yeyz に値 (5,5),(6,6) を挿入 |
| mysqlbin.000002 | 391 | テーブルマップ | 192 | 439 | テーブル ID: 108 (test.yeyz) |
| mysqlbin.000002 | 439 | Write_rows | 192 | 492 | table_id: 108 フラグ: STMT_END_F |
| mysqlbin.000002 | 492 | Xid | 192 | 523 | COMMIT /* xid=38 */ |
+-----------------+-----+----------------+-----------+-------------+------------------------------------------+
セット内の 6 行 (0.00 秒)

上記のバイナリログから、挿入操作の 1 つによって実際に 5 つのエントリが生成され、対応する位置が 259 から 492 であることがわかります。イベントについては後で説明します。

id=5 のレコードがマスターノードに挿入されたため、スレーブノードのレコードと競合しています。エラーログを確認すると、次のことがわかります。

キー「PRIMARY」の重複エントリ「5」
 Error_code: 1062; ハンドラー エラー HA_ERR_FOUND_DUPP_KEY;
 まずイベントのマスターログ、
 終了ログ 492 | 2019-07-16 22:26:25

この問題は、sql_slave_skip_counter パラメータを設定することで解決します。手順は次のとおりです。

mysql:(なし) 22:29:32>>スレーブを停止します。
クエリは正常、影響を受けた行は 0 行、警告は 1 件 (0.00 秒)

mysql:(なし) 22:32:45>>グローバルsql_slave_skip_counterを1に設定します;
クエリは正常、影響を受けた行は 0 行 (0.00 秒)

mysql:(なし) 22:33:06>>スレーブを起動します。

昨日の記事で、sql_slave_skip_counter に続く値はイベント数であると言いましたので、ここではイベントをスキップするのと同じです。MySQL では、イベントをスキップしてもトランザクションが継続している場合は、トランザクションはスキップされ続けると規定されています。

このパラメータを使用してイベントをスキップした後、データベーステーブルのデータとレプリケーションステータスを確認し、次のことを確認します。

スレーブテーブル:

mysql:(なし) 22:33:10>>スレーブステータスを表示\G
************************** 1. 行 ****************************
               Slave_IO_State: マスターがイベントを送信するのを待機中
                  マスターホスト: 10.30.124.68
                  マスターユーザー: dba_repl
                  マスターポート: 4306
                接続再試行: 60
              マスターログファイル:mysqlbin.000002
          読み取りマスターログ位置: 523
               リレーログファイル: スレーブリレーbin.000003
                リレーログ位置: 319
        リレーマスターログファイル: mysqlbin.000002
             スレーブIO実行中: はい
            スレーブSQL実行中: はい


mysql:(なし) 22:33:16>>test.yeyzから*を選択します。
+----+------+
| ID | 年齢 |
+----+------+
| 1 | 1 |
| 2 | 2 |
| 3 | 3 |
| 4 | 4 |
| 5 | 5 |
+----+------+
セット内の行数は 5 です (0.00 秒)

マスターテーブルを見てみましょう:

mysql:(なし) 22:33:36>>test.yeyzから*を選択します。
+----+------+
| ID | 年齢 |
+----+------+
| 1 | 1 |
| 2 | 2 |
| 3 | 3 |
| 4 | 4 |
| 5 | 5 |
| 6 | 6 |
+----+------+
セット内の 6 行 (0.00 秒)

マスターへのデータ挿入は成功しているが、スレーブへのデータ挿入は失敗していることがわかります。つまり、

このパラメータが誤ってスキップされると、マスターとスレーブのデータが不整合になります。

02 スレーブスキップエラーパラメータ

このパラメータは指定されたエラーをスキップするためのもので、対応するerror_codeを設定する必要があります。以下のログの内容から、error_codeの値が1062であることがわかります。

キー「PRIMARY」の重複エントリ「5」
 Error_code: 1062; ハンドラー エラー HA_ERR_FOUND_DUPP_KEY;
 まずイベントのマスターログ、
 終了ログ 492 | 2019-07-16 22:26:25

このパラメータの値を手動で 1062 に変更する必要があります。このパラメータは読み取り専用パラメータであるため、このパラメータを変更するには MySQL サービスを再起動する必要があることに注意してください。

変更後の状況は以下のとおりです。

[email protected]:(なし) 22:38:55>>'%errors%' のような変数を表示します。
+--------------------+---------+
| 変数名 | 値 |
+--------------------+---------+
| 最大接続エラー数 | 1000000 |
| スレーブスキップエラー | 1062 |
+--------------------+---------+
セットに2行（0.01秒）

この時点でマスターテーブルとスレーブテーブルのデータが更新され、更新された状況は次のようになります。

マスター：

mysql:(なし) 22:39:15>>test.yeyzから*を選択します。
+----+------+
| ID | 年齢 |
+----+------+
| 1 | 1 || 2 | 2 |
| 3 | 3 || 4 | 4 |
| 5 | 5 || 6 | 6 |
+----+------+
セット内の 6 行 (0.00 秒)

スレーブについて:

mysql:(なし) 22:40:15>>test.yeyzから*を選択します。
+----+------+
| ID | 年齢 |
+----+------+
| 1 | 1 |
| 2 | 2 |
| 3 | 3 |
| 4 | 4 |
| 5 | 5 |
| 6 | 6 |
| 7 | 7 |
+----+------+
セット内の行数は 7 です (0.00 秒)

スレーブテーブルにはマスターテーブルよりも 1 つ多くのデータ (id=7 のレコード) があることがわかりました。この時点で、マスターで実行します。

mysql:(なし) 22:34:15>>test.yeyzに値（7,7）、（8,8）を挿入します。
クエリは正常、2 行が影響を受けました (0.00 秒)
記録: 2 重複: 0 警告: 0

次のようにして、スレーブ上のレプリケーションとデータのステータスを確認します。

mysql:(なし) 22:39:05>>スレーブステータスを表示\G
************************** 1. 行 ****************************
               Slave_IO_State: マスターがイベントを送信するのを待機中
                  マスターホスト: 10.30.124.68
                  マスターユーザー: dba_repl
                  マスターポート: 4306
                接続再試行: 60
              マスターログファイル:mysqlbin.000002
          読み取りマスターログ位置: 852
               リレーログファイル: スレーブリレーbin.000005
                リレーログ位置: 648
        リレーマスターログファイル: mysqlbin.000002
             スレーブIO実行中: はい
            スレーブSQL実行中: はい
              レプリケート_Do_DB:
           レプリケート_無視_DB:
            テーブルの複製:
        無視テーブルを複製:
       Replicate_Wild_Do_Table:


 mysql:(なし) 22:40:15>>test.yeyzから*を選択します。
+----+------+
| ID | 年齢 |
+----+------+
| 1 | 1 |
| 2 | 2 |
| 3 | 3 |
| 4 | 4 |
| 5 | 5 |
| 6 | 6 |
| 7 | 7 |
+----+------+
セット内の行数は 7 です (0.00 秒)

ご覧のとおり、スレーブに id=7 のレコードがすでに存在するにもかかわらず、レプリケーションにエラーはありません。また、スレーブデータベースのデータは以前のデータと一致したままであることも判明しました。つまり、マスターデータベースに挿入された id=8 のレコードは同期されていませんでした。

要約すると、このパラメータがレプリケーションエラーをスキップすると、MySQL サービスを再起動する必要があり、マスターデータとスレーブデータの間に不整合が発生する可能性があります。

03 スレーブスキップエラー=N パラメータ

最後のパラメータを見てみましょう。このパラメータは、並列レプリケーション中のスレーブレプリケーションモードを示します。デフォルト値は厳密モードです。上記のように、まずマスターライブラリとスレーブライブラリのデータを見てみましょう。

マスターデータ:

mysql:(なし) 22:39:20>>test.yeyzから*を選択します。
                 +----+------+
| ID | 年齢 |
+----+------+
| 1 | 1 |
| 2 | 2 |
| 3 | 3 |
| 4 | 4 |
| 5 | 5 |
| 6 | 6 |
| 7 | 7 |
| 8 | 8 |
+----+------+
セット内の行数は 8 です (0.00 秒)

スレーブデータ:

mysql:(なし) 22:42:46>>test.yeyzから*を選択します。
+----+------+
| ID | 年齢 |
+----+------+
| 1 | 1 |
| 2 | 2 |
| 3 | 3 |
| 4 | 4 |
| 5 | 5 |
| 6 | 6 |
| 7 | 7 |
| 8 | 8 |
| 9 | 9 |
+----+------+
セット内の行数は 9 です (0.00 秒)

この時点で、スレーブライブラリのパラメータを次のように変更します。

mysql:(なし) 22:42:59>>'%exec%'のような変数を表示します。
+----------------------------------+--------+
| 変数名 | 値 |
+----------------------------------+--------+
| gtid_executed_compression_period | 1000 |
| 最大実行時間 | 0 |
| rbr_exec_mode | STRICT |
| スレーブ実行モード | 厳密 |
+----------------------------------+--------+
セット内の 4 行 (0.00 秒)

mysql:(なし) 22:44:05>>グローバルslave_exec_modeを'IDEMPOTENT'に設定します。
クエリは正常、影響を受けた行は 0 行 (0.00 秒)

mysql:(なし) 22:44:10>>'%exec%'のような変数を表示します。
           +----------------------------------+-------------+
| 変数名 | 値 |
+----------------------------------+-------------+
| gtid_executed_compression_period | 1000 |
| 最大実行時間 | 0 |
| rbr_exec_mode | STRICT |
| スレーブ実行モード | IDEMPOTENT |
+----------------------------------+-------------+
セット内の 4 行 (0.00 秒)

パラメータを変更した後、メインデータベースに対して挿入操作を実行します。

test.yeyzに値（9,9）、（10,10）を挿入します。

次のようにして、スレーブライブラリのレプリケーションステータスとデータステータスを確認します。

mysql:(なし) 22:44:14>>スレーブステータスを表示\G
************************** 1. 行 ****************************
               Slave_IO_State: マスターがイベントを送信するのを待機中
                  マスターホスト: 10.30.124.68
                  マスターユーザー: dba_repl
                  マスターポート: 4306
                接続再試行: 60
              マスターログファイル:mysqlbin.000002
          読み取りマスターログ位置: 1183
               リレーログファイル: スレーブリレーbin.000007
                リレーログ位置: 650
        リレーマスターログファイル: mysqlbin.000002
             スレーブIO実行中: はい
            スレーブSQL実行中: はい

セット内の 1 行 (0.00 秒)

mysql:(なし) 22:44:38>>test.yeyzから*を選択します。
+----+------+
| ID | 年齢 |
+----+------+
| 1 | 1 |
| 2 | 2 |
| 3 | 3 |
| 4 | 4 |
| 5 | 5 |
| 6 | 6 |
| 7 | 7 |
| 8 | 8 |
| 9 | 9 |
| 10 | 10 |
+----+------+
セット内の行数は 10 です (0.00 秒)

レプリケーションエラーはなく、プライマリデータベースに挿入されたデータも同期されていることがわかります。