Posted inMySQL

MySQL Replication Filters: スレーブを「救済」するためのデータベースとテーブルのフィルタリング術

MySQL tutorial - IT technology blog
MySQL tutorial - IT technology blog

実体験：全データの「コピー＆ペースト」がもたらした惨劇

少し前、データ分析チームのためにレプリカサーバーを構築するタスクを担当しました。当時のマスター（Master）システムは、50ものマイクロサービスから集まる約1TBのデータを抱えていました。

私の最大の失敗は、デフォルト設定のままにしたことです。つまり、マスターにあるものはすべてスレーブ（Slave）にも同期される状態でした。わずか2週間後、スレーブのハードディスクは赤色表示（ディスク使用率95%）になりました。分析チームが全く使わない大量のゴミデータで埋め尽くされていたのです。さらに悪いことに、巨大なログテーブルの同期によってSeconds_Behind_Masterが3600秒まで跳ね上がり、I/Oのボトルネックでスレーブが頻繁にフリーズする事態に陥りました。

最終的には、ある日の深夜3時に起きて、フリーズしたスレーブのデータベースをクリーンアップする羽目になりました。この教訓は明白です。本番環境において、100%のデータを同期することは、時に深刻なリソースの無駄遣いになります。そこで必要になるのがMySQL Replication Filtersです。

なぜすべてを同期すべきではないのか？

同期データのフィルタリングには、主に3つの大きなメリットがあります。

  • コスト削減: スレーブに必要なデータだけを保持させることで、ディスク容量を1TBから200GBに削減できるなど、クラウドコストを大幅に節約できます。
  • セキュリティ: user_passwordsやcredit_cardsのような機密情報が、レポート作成用のサーバーに同期されるのを防ぎます。
  • クエリ速度の向上: I/O負荷を軽減することで、スレーブでのSQL実行速度が向上し、同期遅延をほぼゼロに抑えることができます。

2つのアプローチ：マスターでフィルタリングするか、スレーブでするか？

2つの選択肢がありますが、それぞれに「落とし穴」があるため注意が必要です。

1. マスター側でのフィルタリング (Binary Log Filters)

マスターは、binlog-do-dbまたはbinlog-ignore-dbで指定されたデータベースの変更のみをバイナリログ（Binlog）に記録します。

# マスター側の設定 (my.cnf)
[mysqld]
binlog-do-db=db_important

警告: この方法はお勧めしません. MySQLは現在USEされているデータベースに基づいてフィルタリングを行います。もしUSE db_otherを実行している状態でUPDATE db_important.tableを実行した場合、その変更は無視されてしまいます。結果として、気づかないうちにスレーブのデータ整合性が失われる危険があります。

2. スレーブ側でのフィルタリング (Replication Filters) – 最も安全な方法

マスターはすべてのログを送信し、スレーブ側で実行すべきものを選択します。この方法はマスターの元データを損なうことがないため、より安全です。

覚えておくべき主なパラメータ：

  • replicate-do-db: 指定したデータベースのみを同期する。
  • replicate-wild-do-table: パターン（例：sales_%）に基づいて同期する。これが最も推奨される選択肢です。

本番環境向けの標準設定

運用経験に基づき、データベースのコンテキストに依存するエラーを避けるため、常にワイルドカードフィルタリングの使用を優先しています。

ステップ 1: スレーブの設定ファイルを編集する

スレーブの/etc/mysql/my.cnfを開き、以下の設定を追加します。

[mysqld]
# ecommerceデータベースのみを同期
replicate-wild-do-table=ecommerce.%

# analyticsデータベース内のレポート関連テーブルのみを同期
replicate-wild-do-table=analytics.report_%

# 一時的なログテーブルを除外
replicate-wild-ignore-table=ecommerce.temp_logs

ステップ 2: 変更を適用する

MySQLを再起動して設定を反映させることもできますが、ダウンタイムを避けたい場合は、MySQLシェルで直接実行します。

STOP SLAVE SQL_THREAD;
CHANGE REPLICATION FILTER REPLICATE_WILD_DO_TABLE = ('ecommerce.%', 'analytics.report_%');
START SLAVE SQL_THREAD;

ステップ 3: 確認

SHOW SLAVE STATUS\Gコマンドを実行します。Replicate_Wild_Do_Tableの行を確認し、フィルタが期待通りに動作しているかチェックしてください。

システムを壊さないための「鉄則」

  1. ワイルドカードを優先する: replicate-do-dbではなく、常にreplicate-wild-do-tableを使用してください。これにより、非常に厄介な「クロスデータベース更新」によるエラーを回避できます。
  2. リレーログの管理: スレーブがデータをフィルタリングして捨てたとしても、マスターからは全ログファイルが送られてきます。relay_log_purge = 1を設定しましょう。スレーブは処理が終わったログを自動的に削除し、一時ファイルによるディスクフルを防ぎます。
  3. 定期的な監視: pt-table-checksumを使用して、マスターとスレーブの間のデータ差分を確認してください。過信は禁物です。タイポなどのミスで重要なテーブルがフィルタリングから漏れている可能性があります。

このフィルタリング技術をマスターすることは、システムを円滑に稼働させるだけでなく、エンジニアとしての最適化能力を証明することにも繋がります。もしあなたのスレーブがデータ過多で「息切れ」しているなら、今すぐ試してみてください！

Share:
Tags: