MySQL でレプリケーション フィルターを動的に変更する方法

MySQLはレプリケーションフィルターを動的に変更します

今日遭遇した問題についてお話しします。今日は、かなり異常なビジネス上の要求に対処していました。簡単に説明します。

1. オンライン Alibaba Cloud RDS にはゲーム ログ ライブラリがあります。その中のテーブルは日次テーブル形式になっており、データ量は比較的大きいです。バックアップを実行するたびに、オンライン RDS はアラームを発生させ、アラームの内容は IO リソースが過剰に占有されているというものです。

2. このRDSにはローカルECS読み取り専用スレーブデータベースがあります。この読み取り専用スレーブデータベースは、オンラインRDSデータベースのデータをリアルタイムで同期します。この読み取り専用スレーブデータベースは、ビジネス側でクエリに使用されます。

3. ビジネス側は、これらのデータはすべてまだ有用であると述べました。読み取り専用スレーブ データベースのデータは利用可能である必要があり、オンライン RDS のデータは削除可能であり、2 週間保持するだけで済みます。

シナリオは次のようになります。DBA はアラームの問題を解決したいと考えており、ビジネス側は完全なデータを確保したいと考えています。この問題を解決するにはどうすればいいでしょうか?

この質問を見たとき、私は悪態をつきたくなりました。この要件は不合理です。1 つのデータベースを削除して、別のデータベースに保存しておくなんて、どうやってできるのでしょうか。それに、それらはすべてログ データです。コールド バックアップを作成してから、オンラインのものを削除すればいいのではないでしょうか。しかし、状況を緩和する余地はなかったので、この問題をどうやって解決するかを考え始めました。私が考えた解決策は次のとおりです。

1. 容量を拡張し、パフォーマンスを向上します。データ量が多いのでディスクを拡張します。IO使用率が高いのでパフォーマンスを向上させます。これは最も直接的な解決策ですが、最も高価な解決策でもあり、最初に切断されます。

2. 最初にバックアップし、次に削除して復元します。事前に日次テーブルデータをRDSマスターデータベースにバックアップし、データを削除します。このとき、スレーブデータベースも同期的にデータを削除し、最初のステップでバックアップしたデータをスレーブデータベースに復元します。この方法は、データが失われないことが保証されるため実行可能です。しかし、操作はかなり複雑で、手順が多すぎて、あまり便利ではありません。

3. replicate-ignore-table パラメータを使用して、指定したテーブルをフィルタリングします。このパラメータを設定すると、指定されたデータ テーブルのすべての操作をフィルターできます。このパラメータの説明については、公式ドキュメントを見てみましょう。リンクはこちらです: https://dev.mysql.com/doc/refman/5.7/en/replication-options-slave.html#option_mysqld_replicate-wild-ignore-table

説明は次のとおりです。

レプリケーションフィルタを作成し、指定されたワイルドカードパターンに一致するテーブルがあるステートメントをスレーブスレッドが複製しないようにします。無視するテーブルを複数指定するには、このオプションを複数回使用します。

上記は、このパラメータを使用して、設定したルールに一致する特定のテーブルに対する操作を除外するフィルタを作成できることを意味します (わかりにくいようです)。つまり、フィルタリング ルールを設定し、テーブル a をルールに追加すると、テーブル a に対する操作はスレーブ データベースに同期されなくなります。

これは私たちのニーズに沿ったものです。つまり、テーブルをフィルタリングするように設定すると、テーブルを削除してもデータベースからテーブルが削除されず、目的の結果が得られます。この機能をテストしてみましょう:

まず、データベース test_ignore を作成し、その中にテーブルを作成します。

メインデータベースでの操作:

mysql:test_ignore >>テーブルを表示;
空のセット (0.00 秒)

mysql:test_ignore >>テーブルaaaを作成します(id int not null);
クエリは正常、影響を受けた行は 0 行 (0.19 秒)

mysql:test_ignore >>テーブルaabを作成します(id int not null);
 クエリは正常、影響を受けた行は 0 行 (0.01 秒)

mysql:test_ignore >>テーブルaacを作成します(id int not null);
 クエリは正常、影響を受けた行は 0 行 (0.00 秒)

mysql:test_ignore >>テーブルaadを作成します(id int not null);
 クエリは正常、影響を受けた行は 0 行 (0.01 秒)

mysql:test_ignore >>テーブルaaeを作成します(id int not null);
 クエリは正常、影響を受けた行は 0 行 (0.01 秒)

図書館からの眺め:

mysql:test_ignore >>テーブルを表示;
+-----------------------+
| テスト中のテーブルを無視 |
+-----------------------+
|ああ|
| アブ |
| 日本語
| アード |
| アエー |
+-----------------------+
セット内の行数は 5 です (0.00 秒)

同期されていることがわかりました。この時点で、マスターとスレーブの同期状態が確立されています。ここでマスター データベース上のテーブルを削除すると、スレーブ データベース上のテーブルも確実に削除され、これは望ましい結果ではありません。

当然、次のステップは、replicate-wild-ignore-table パラメータを構成することです。通常は、スレーブ サービスを停止して my.cnf ファイルを構成する必要があります。複数のテーブルを構成する場合は、my.cnf ファイルに複数のワイルドカード レコードを書き込む必要があります。たとえば、この例では、パラメータの値は test_ignore.aa% として設定する必要があります。ここで、% はワイルドカードを表します。つまり、test_ignore データベース内の aa% 形式のテーブル操作はフィルター処理されます。作成したテーブル aaa、aab、aac、aad、aae はすべてこの形状であるため、これらのテーブルに対する操作はスレーブ データベースに同期されません。テストしてみましょう。

まず、現在のレプリケーション ステータスを確認します。

ダブルイエスステータスは、レプリケーション関係に問題がないことを意味します

メインライブラリは以下を運営しています:

mysql :test_ignore >>テーブルaaaを削除します。
クエリは正常、影響を受けた行は 0 行 (0.01 秒)

mysql :test_ignore >>テーブルaabを削除します。
クエリは正常、影響を受けた行は 0 行 (0.00 秒)

図書館からの眺め:

mysql:test_ignore >>テーブルを表示;
+-----------------------+
| テスト中のテーブルを無視 |
+-----------------------+
|ああ|
| アブ |
| 日本語
| アード |
| アエー |
+-----------------------+
セット内の行数は 5 です (0.00 秒)

スレーブデータベースのテーブルはまだ存在しており、マスターデータベースの操作がスレーブデータベースに同期されていないことを示しています。設定したパラメータは

replicate-wild-ignore-table=test_ignore.aa%

うまくいきました。この時点で、マスター データベースにテーブルを作成すると、次のようになります。

メインライブラリ
mysql:test_ignore >>テーブルaaf(id int)を作成します。
クエリは正常、影響を受けた行は 0 行 (0.00 秒)

「ライブラリから」
mysql:test_ignore >>テーブルを表示;
+-----------------------+
| テスト中のテーブルを無視 |
+-----------------------+
|ああ|
| アブ |
| 日本語
| アード |
| アエー |
+-----------------------+
セット内の行数は 5 です (0.00 秒)

aaf もルール test_ignore.aa% に一致するため、スレーブ データベースはマスター データベースのテーブル aaf を同期していないことがわかります。

この機能を使用することで、メイン データベースが削除され、スレーブ データベースにデータが保持されるというビジネス シナリオを非常にうまく解決できます。しかし、この方法には重大な問題があり、スレーブ データベースを毎回再起動する必要があります。2 番目のルールと 3 番目のルールを設定する必要がある場合、スレーブ データベースを 2 回または 3 回再起動する必要があります。このプロセス中、スレーブ データベースはビジネス パーティには表示されません。アクセスできない場合、プログラム エラーが発生する可能性があり、これは耐えられません。

このプロセスを解決する必要がありますが、どのように解決すればよいでしょうか?ダウンタイムなしでレプリケーション フィルターを変更する方法はありますか?公式ドキュメントを探してください。

案の定、この生きている間にもマシンをシャットダウンすることは不可能です。公式文書にはこのような一文があります。

CHANGE REPLICATION FILTER REPLICATE_WILD_IGNORE_TABLE ステートメントを発行して、このようなフィルターを作成することもできます。

この文は一体何なのでしょうか? 一度も使われていないことを意味します。オンラインでコピー フィルターを変更することでフィルターを変更できます。公式ドキュメントの紹介を参照してください:

魔法のような文章を見つけたので、試してみましょう。

mysql:test_ignore >>レプリケーションフィルターを変更 replicate_wild_ignore_table=('test_ig%.aa%');
エラー 3017 (HY000): この操作は実行中のスレーブ SQL スレッドでは実行できません。まず STOP SLAVE SQL_THREAD を実行してください。

mysql:test_ignore >>スレーブを停止します。
クエリは正常、影響を受けた行は 0 行 (0.00 秒)

mysql:test_ignore >>レプリケーションフィルターを変更 replicate_wild_ignore_table=('test_ig%.aa%');
クエリは正常、影響を受けた行は 0 行 (0.00 秒)

mysql:test_ignore >>スレーブを起動します。
クエリは正常、影響を受けた行は 0 行 (0.01 秒)

直接使用すると、スレーブ sql_thread を停止する必要があるというプロンプトが表示されます。 よく考えてみると、レプリケーションを停止せずにレプリケーション ルールを変更するのは少し不適切であるように思われます。 レプリケーション全体を停止してから、レプリケーション フィルターを再度変更するだけです。 うまくいきます。 正常に実行され、レプリケーションが有効になります。 操作はスムーズです。

レプリケーション関係のステータスを見てみましょう。

無視されるテーブル ルールは test_ig%.aa% になりました。つまり、test_ig で始まるデータベース内の aa で始まるテーブルに対するすべての操作 (テーブルの変更、削除、作成操作を含む) は、スレーブ データベースに同期されません。

しかし、ここで解決策が出てきます。日次テーブルは一般的にYYYYMMDD形式であることがわかっています。日次テーブルをYYYYMM%形式でフィルタリングし、マスターデータベースで削除するだけです。この操作はスレーブデータベースに同期されないため、この問題はスムーズに解決できます。

もちろん、この解決策に加えて、次のような他の解決策もあります。

ビジネスで部分的なデータ損失が許容される場合は、binlog を閉じてテーブルを削除し、binlog を開いてスレーブ データベースがマスター データベースのドロップ操作を同期しないようにする方法も使用できます。

すべてのオンライン日次テーブル操作は無視するように構成され、トリガーを使用して日次テーブルの更新がスレーブ データベースに同期されます。

この一連の操作は、実際には問題を根本的に解決するものではありません。本質的にはビジネス設計の問題です。日次テーブルにはドットログが多すぎます。これらのドットログは適切に削減できます。ドットログについては、保存期間を決定する必要があります。期限切れのログは、サーバーの指標とパフォーマンスを確保するために、時間内にクリーンアップする必要があります。

上記は、MySQL でレプリケーション フィルターを動的に変更する方法の詳細です。MySQL でレプリケーション フィルターを動的に変更する方法の詳細については、123WORDPRESS.COM の他の関連記事に注目してください。