MySQLのslave_exec_modeパラメータの詳細な説明

今日、slave_exec_modeというパラメータを偶然見ました。マニュアルの説明から、このパラメータはMySQLレプリケーションに関連しており、動的に変更できる変数であることがわかりました。デフォルトモードはSTRICT（厳密モード）で、オプション値はIDEMPOTENTモード（べき等モード）です。スレーブを IDEMPOTENT モードに設定すると、エラー 1032 (スレーブに存在しないキー) および 1062 (重複キー、主キー、または一意のキーが存在する必要がある) を防ぐことができます。このモードは、ROW EVENT の binlog モードでのみ有効であり、STATEMENT EVENT の binlog モードでは無効です。 IDEMPOTENT モードは主にマルチマスターレプリケーションと NDB CLUSTER の状況で使用され、他の状況では推奨されません。上記の紹介から、このパラメータによりスレーブライブラリは指定されたエラーをスキップできるようになるため、質問は次のようになります。

1: sql_slave_skip_counter と比較した利点は何ですか?

2: slave-skip-errors = N と比較して、どのような利点がありますか?

この記事では、これら 2 つの質問について、関連するテストと説明を行います。

環境：

MySQL バージョン: Percona MySQL 5.7

レプリケーションモード: ROW、GTID が有効になっていません

テスト：

① 1062 エラー: テーブル db.x で ... イベントを実行できませんでした。キー 'PRIMARY' のエントリ 'xx' が重複しています。Error_code: 1062。

マスターとスレーブのテストテーブル構造:

テーブル `x` を作成します (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 主キー (`id`)
) エンジン=InnoDB AUTO_INCREMENT=4 デフォルト文字セット=utf8

マスターとスレーブのテーブルレコード:

マ：

x から * を選択します。
+----+
|id|
+----+
| 2 |
| 3 |
+----+
セットに2行（0.01秒）

サ:

x から * を選択します。
+----+
|id|
+----+
| 1 |
| 2 |
| 3 |
+----+
セット内の 3 行 (0.00 秒)

マスターとスレーブのテーブルレコードに矛盾があり、id=1 のレコードがマスターにありません。

現時点では、スレーブ上の slave_exec_mode はデフォルトの STRICT モードです。

'slave_exec_mode' のような変数を表示します。
+-----------------+--------+
| 変数名 | 値 |
+-----------------+--------+
| スレーブ実行モード | 厳密 |
+-----------------+--------+
セット内の 1 行 (0.00 秒)

M の binlog モードは次のとおりです。

'binlog_format' のような変数を表示します。 +--------------+-------+
| 変数名 | 値 |
+---------------+-------+
| binlog_format | 行 |
+---------------+-------+
セット内の 1 行 (0.00 秒)

Mで実行:

x値(1)、(4)、(5)に挿入します。
クエリは正常、3 行が影響を受けました (0.00 秒)
記録: 3 重複: 0 警告: 0

id=1 のレコードがスレーブに既に存在するため、スレーブレプリケーションはエラー 1062 を報告します。

最終SQLエラー番号: 1062
Last_SQL_Error: テーブル dba_test.x で Write_rows イベントを実行できませんでした。キー 'PRIMARY' のエントリ '1' が重複しています。Error_code: 1062。ハンドラー エラー HA_ERR_FOUND_DUPP_KEY。イベントのマスター ログ mysql-bin-3306.000006、end_log_pos 7124

このエラーが発生した場合、一貫した方法は sql_slave_skip_counter=N を実行することです。

1. グローバル sql_slave_skip_counter=N を設定します。N は N 個のイベントをスキップすることを意味します。
2. 覚えておくべき最も重要なことは、N が 1 に設定されると、次のトランザクションがスキップされるということです。
3. N 番目のイベントをスキップした後、その位置がトランザクション内に該当する場合は、トランザクション全体がスキップされます。4. 挿入/更新/削除は、エンジンとログ形式によって決定される 1 つのイベントのみに必ずしも対応するわけではありません。

sql_slave_skip_counter の単位は「イベント」です。多くの人は、このパラメータの単位は「トランザクション」であると考えていますが、これは実際には誤りです。トランザクションには複数のイベントが含まれており、N 個のイベントをスキップしても同じトランザクション内である可能性があるからです。上記のエラー 1062 の場合、N を 1 ～ 4 に設定すると同じ効果があり、トランザクションがスキップされます。実行された SQL によって 4 つのイベントが生成されるためです。

6950 からの 'mysql-bin-3306.000006' の binlog イベントを表示します。
+-----------------------+------+------------+-------------+-------------+---------------------------------+
| ログ名 | 位置 | イベント タイプ | サーバー ID | ログ終了位置 | 情報 |
+-----------------------+------+------------+-------------+-------------+---------------------------------+
| mysql-bin-3306.000006 | 6950 | クエリ | 169 | 7026 | 開始 |
| mysql-bin-3306.000006 | 7026 | テーブルマップ | 169 | 7074 | テーブル ID: 707 (dba_test.x) |
| mysql-bin-3306.000006 | 7074 | Write_rows | 169 | 7124 | table_id: 707 フラグ: STMT_END_F |
| mysql-bin-3306.000006 | 7124 | Xid | 169 | 7155 | COMMIT /* xid=74803 */ |
+-----------------------+------+------------+-------------+-------------+---------------------------------+
セット内の 4 行 (0.00 秒)

したがって、このエラーを処理する方法は次のとおりです。

1: スレーブSQLをスキップする

スレーブを停止します。クエリは正常、影響を受けた行は 0 行です (0.00 秒)
グローバルsql_slave_skip_counter=[1-4]を設定します。
クエリは正常、影響を受けた行は 0 行 (0.00 秒)
スレーブを起動します。
クエリは正常、影響を受けた行は 0 行 (0.00 秒)

2: 設定ファイルでslave-skip-errors=1062を指定します（再起動が必要です）

どちらの方法でもレプリケーションを通常の状態に復元できますが、マスターデータとスレーブデータの間に不整合が発生し (注意して使用してください)、スレーブデータベースで id=4 および 5 のレコードが失われます。 2 番目の方法でもデータベースを再起動する必要がありますが、その際にはこの記事で紹介した slave_exec_mode パラメータが役立ちます。スレーブライブラリで次のパラメータを設定します。

グローバルslave_exec_modeを'IDEMPOTENT'に設定します。
クエリは正常、影響を受けた行は 0 行 (0.00 秒)
スレーブを停止します。クエリは正常、影響を受けた行は 0 行です (0.00 秒)
スレーブを起動します。
クエリは正常、影響を受けた行は 0 行 (0.00 秒)

マスターでも実行します:

x値(1)、(4)、(5)に挿入します。

マスターとスレーブのデータが同期されており、レプリケーションの異常がないことは驚くべきことです。

マ：
x から * を選択; +----+
|id|
+----+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
+----+
セット内の行数は 5 です (0.00 秒)

サ:
x から * を選択; +----+
|id|
+----+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
+----+
セット内の 5 行 (0.01 秒)

上記のテストから、パラメータをslave_exec_mode='IDEMPOTENT'に設定すると、エラーイベントをスキップできることがわかります。

② 1032 エラー: テーブル db.x で ... イベントを実行できませんでした。'x' にレコードが見つかりません。Error_code: 1032。

このエラーは、ROW モードでのレプリケーションにはデータの一貫性に関する厳しい要件があるために発生します。

マスターとスレーブのテストテーブル構造:

テーブル `x` を作成します (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 主キー (`id`)
) エンジン=InnoDB AUTO_INCREMENT=4 デフォルト文字セット=utf8

マスターとスレーブのテーブルレコード:

マ：

x から * を選択; +----+
|id|
+----+
| 1 |
| 2 |
| 3 |
+----+
セット内の 3 行 (0.00 秒)

サ:

x から * を選択します。
+----+
|id|
+----+
| 1 |
| 3 |
+----+
セット内の 2 行 (0.00 秒)

マスターとスレーブのテーブルレコードに矛盾があり、スレーブに id=2 のレコードがありません。現時点では、スレーブ上の slave_exec_mode はデフォルトの STRICT モードです。

'slave_exec_mode' のような変数を表示します。
+-----------------+--------+
| 変数名 | 値 |
+-----------------+--------+
| スレーブ実行モード | 厳密 |
+-----------------+--------+
セット内の 1 行 (0.00 秒)

M の binlog モードは次のとおりです。

'binlog_format' のような変数を表示します。 +--------------+-------+
| 変数名 | 値 |
+---------------+-------+
| binlog_format | 行 |
+---------------+-------+
セット内の 1 行 (0.00 秒)

Mで実行:

始める;
INSERT INTO x SELECT 4;
id = 2 の場合、x から削除します。
INSERT INTO x SELECT 5;
専念;

id=2 のレコードがスレーブ上に存在しないため、スレーブレプリケーションはエラー 1032 を報告します。

最終SQLエラー番号: 1032
Last_SQL_Error: テーブル dba_test.x で Delete_rows イベントを実行できませんでした。'x' にレコードが見つかりません。Error_code: 1032。ハンドラー エラー HA_ERR_KEY_NOT_FOUND。イベントのマスター ログ mysql-bin-3306.000006、end_log_pos 12102

同様に、上記のテストで説明した 2 つの方法でもレプリケーションは機能しますが、データは失われます。 id=4 と 5 のレコードは失われます。スレーブライブラリでパラメータの設定を続行します。

グローバルslave_exec_modeを'IDEMPOTENT'に設定します。
クエリは正常、影響を受けた行は 0 行 (0.00 秒)
スレーブを停止します。クエリは正常、影響を受けた行は 0 行です (0.00 秒)
スレーブを起動します。
クエリは正常、影響を受けた行は 0 行 (0.00 秒)

M でも同じことを行います。

始める;
INSERT INTO x SELECT 4;
id = 2 の場合、x から削除します。
INSERT INTO x SELECT 5;
専念;

また、マスターとスレーブのデータが同期されており、レプリケーションの異常がないことに驚かれるかもしれません。

注意: slave_exec_mode='IDEMPOTENT' では、DDL 操作をべき等にすることはできません。また、例のスレーブテーブルの id フィールドタイプを int から bigint に変更するなど、異なるフィールド長によって発生するエラーをべき等にすることもできません。 binlog_format が ROW モードの場合にのみ使用でき、1032 および 1062 の場合はべき等モードでのみ使用できます。

要約:

上記のテスト概要では、slave_exec_mode パラメータの場合、エラー 1062 と 1032 をスキップでき、同じトランザクション内の通常のデータ実行には影響しません。トランザクションが複数の SQL ステートメントで構成されている場合は、問題のあるイベントをスキップできます。

このパラメータは良さそうですが、マニュアルには、通常のレプリケーション環境で有効にすることは推奨されないと記載されています。 NDB 以外のストレージエンジンの場合、重複キーエラーとキーなしエラーを安全に無視できることが確実な場合にのみ、 IDEMPOTENT モードを使用する必要があります。このパラメータは、NBD クラスタ用に特別に設計されています。NBD クラスタモードでは、このパラメータは IDEMPOTENT モードにのみ設定できます。したがって、独自のアプリケーションシナリオに基づいて決定する必要があります。通常の状況では、マスターとスレーブは一致しており、エラーがあれば報告されます。ただし、特別な処理を行う場合は一時的に有効にすることができます。

また、GTID モードのレプリケーションでは sql_slave_skip_counter はサポートされていません。このモードでのレプリケーションは自分でテストできます。

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