MySQL のデータをCDC を使ってSnowflake に統合するには？MySQL のCDC 機能の解説も

こんにちは、プロダクトチームの宮本です。

CDataSync ではここのところ、SQL Server, PostgreSQL でのChange Data Capture (以下、CDC) 機能に対応してきていましたが、ついにログベースによるMySQL CDC にも対応しました！

ちなみに前回のPostgreSQL CDC の記事はこちら↓↓
https://www.cdata.com/jp/blog/postgresql-sync-cdc

個人的には待望の機能という感じで、というのも SQL Server や PostgreSQL より「MySQL でのCDC には対応していますか？？」というお問合せをかれこれ毎年のように受けており、そのたびに「まだ未対応・・・ですが、更新日付を条件に入れたクエリベースなら・・・」と心苦しい回答していました。

ではMySQL CDC とは？をはじめ、CData Sync でのMySQL CDC によるデータレプリケーションを行う方法を紹介していきたいと思います。

MySQL Change Data Capture (CDC) とは？

CDC とはその名のとおり、変更データを取得するための機能でして、毎回の全件取得を行わずとも連携先に最新情報を転送できるようになります。ちなみにこのCDC 機能は何種類かの取得方法があるのが現状です。
一般的な取得方法としては下記が挙げられます。

1. クエリベースでの変更データキャプチャ
2. トリガーベースでの変更データキャプチャ
3. ログベースでの変更データキャプチャ

これらはそれぞれにメリット／デメリットがありますが、総じてログべースが変更データキャプチャとしては扱いやすいものとなっています。

１．クエリベースでの変更データキャプチャ

クエリベースは、対象テーブルにタイムスタンプで検索して変更データを抽出する方式を指しています。

例えば、SQLのWhere句に更新日付の条件を入れることで、差分データを抽出できるようになる

Select * from Account Where updated_at> ‘yyyy-MM-dd(最終更新日時)’

この方式のメリット：

• DB側の設定が不要ですぐに実行することができる
• クエリを指定できるサービスやツールであればすぐに実現できる

この方式のデメリット：

• 普通のクエリを実行するだけなので、保持しているデータ量によってはDB全体に負荷を掛けてしまう
• 対象テーブルで更新日時を持つ必要がある
• 削除レコードは検知できない
  
  

２．トリガーベースでの変更データキャプチャ

トリガーによるCDCでは、変更が行われたタイミングで別テーブルに変更情報を連携する方式となるため、
「Insert、Update、Delete」での更新内容を保持することができます。

この方式のメリット：

• 削除も含めて全変更情報を取得可

この方式のデメリット：

• 別テーブルの管理で運用が複雑化
• トリガー処理がプラスされる＝元のステートメントの実行時間が増える
• テーブルのスキーマ変更時は手動対応する必要がある

３．ログベースでの変更データキャプチャ

トランザクションログを利用した差分データ抽出の方式となるので、すべての変更イベントをリアルタイムで検知することができます。MySQLの場合はBinlog からの抽出を行います。

この方式のメリット：

• 管理するリソースがないので楽
• ログを直接参照するだけなので、DBへのパフォーマンスに影響与えない
• スキーマ変更も気にしなくて良い
  
  

この方式のデメリット：

• 古いバージョンのDBでは未対応であることがある

ということでそれぞれの方式を表で比較してみましたが、やはりログベースが圧倒的に利用しやすいところになりますね。CDC について理解を揃えたところで、次はCData Sync での利用手順をみていきましょう。

手順

MySQL CDC 設定：バイナリログの有効化

最初にMySQL 側の設定になりますが、利用しているMySQL のバージョンが8.0 以降だとバイナリログはデフォルトで有効化になっています。
有効化になっているかは以下のコマンドで確認できます。

mysql> SHOW GLOBAL VARIABLES LIKE 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin | ON |
+---------------+-------+
1 row in set, 1 warning (0.03 sec)

バイナリログを無効にしたい場合
今回のCDC の検証はできませんが、もしバイナリログをOFFにしたいという場合には、MySQL を停止後（Windows 環境の場合）デフォルトで下記パスにあるmy.ini ファイルを直接編集します。
C:\ProgramData\MySQL\MySQL Server 8.0\my.ini

開いたら「log-bin」パラメータを検索します。これがbinlog このパラメータが有効にしているものになるのですが、コメントアウトにしただけでは無効化とはならないので、その下にskip-log-bin というパラメータを指定します。
そうすることでバイナリログを無効化することができます。

mysql> SHOW GLOBAL VARIABLES LIKE 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin | OFF |
+---------------+-------+
1 row in set, 1 warning (0.01 sec)

CData Sync のインストール

CData Sync は Java 版、.NET版、AMI のクラウド版があります。今回はローカルのWindows マシンを使用していることから .NET 版をダウンロードします。(30日間の無償トライアルが可能です)
Data Sync Apps | CData Software Japan

ダウンロードしたインストーラーを実行するとセットアップウィザードが表示されるので、そのまま次へと最後まで進んでください。

インストールが完了したらスタートメニューからCDataSync を起動し、ログイン画面が表示されたらOKです。あとはログインユーザーを作成してログインするだけです。

接続情報の作成

最初にデータソース側のMySQL の接続設定を行っていきます。接続ボタン → データソース → MySQL の順に進んでいきます。

MySQL への接続情報を入力して作成します。

続いてSnowflake の接続設定を行いますので、下記順序で進みます。

ジョブの作成

ジョブ設定画面の右上にある「ジョブを作成」をクリックし、MySQL → Snowflake 連携のジョブの枠を作成します。
この際、レプリケーションの種類では「変更データキャプチャ」を選択します。

今回はサンプルで入っているsakila.actor テーブルを選択します。

元の画面に戻るとこのように連携クエリが作成されます。

これでジョブの設定が完了しました。

ジョブの実行

ジョブの実行は左側のチェックボックスにチェックを入れて実行ボタンをクリックするだけです。
ジョブが完了するとステータスに連携したレコード件数が表示されます。

Snowflake 側をみるとactor でテーブルが作成され、MySQL のレコードが入っていました。

以上が通常のDB to DB のレプリケーションになります。
次はバイナリログでのCDC レプリケーションで差分更新を確認してみたいと思います。

CDC 機能の確認

MySQL のレコードを変更する前の状態を確認します。
今回はactor_id = 2 の first_name をNICK から JOHN に変更してみたいと思います。

下記SQLでJOHNに変更しました。
Update sakila.actor set first_name='JOHN' where actor_id = 2;

この状態でジョブを実行してみます。通常だと全件レプリケーションして、Snowflake 側でMerge される形になるのですが、下にある結果をみるとちゃんと変更された件数のみが連携されました。

Snowflake を見てみるとMySQL 側で変更した内容で反映されていました。

次はMySQL 側でレコードを3件削除してみたいと思います。

delete from sakila.actor where actor_id IN (1,2,3);
select * from sakila.actor order by 1;

ではこのタイミングでまたジョブを実行してみますと、削除した件数がステータスに表示されました。

Snowflake 側にもこのように論理削除で反映されました。

物理削除したい場合は、変換機能でジョブ終了後に _cdatasync_deleted = TRUE で削除レコードを削除する内容で用意しておくなどで対応できます。

Binlog が削除されていたときは？

MySQL のレコードを適当に更新します。

ここでバイナリログの保存期間を変更します。デフォルトでは30 日となっていますが、それを60 秒に変更します。

まずは現在の保持期間を確認

mysql> show variables like 'binlog_expire_logs_seconds';
+----------------------------+---------+
| Variable_name | Value |
+----------------------------+---------+
| binlog_expire_logs_seconds | 2592000 |
+----------------------------+---------+
1 row in set, 1 warning (0.00 sec)

60 秒に変更

mysql> SET GLOBAL binlog_expire_logs_seconds =60;
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> show variables like 'binlog_expire_logs_seconds';
+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
| binlog_expire_logs_seconds | 60 |
+----------------------------+-------+
1 row in set, 1 warning (0.00 sec)

これで保持期間を60秒に変更しました。
ちなみに現在参照先のバイナリログは以下のバージョンになります。

mysql> show master status;
+----------------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+----------------------------+----------+--------------+------------------+-------------------+
| DESKTOP-H8P4FBP-bin.000009 | 4450 | | | |
+----------------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

60 秒経過後、MySQL を再起動して改めて参照先のバイナリログを確認してみると、
DESKTOP-H8P4FBP-bin.000010 に繰り上がっていました。

mysql> show master status;
+----------------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+----------------------------+----------+--------------+------------------+-------------------+
| DESKTOP-H8P4FBP-bin.000010 | 157 | | | |
+----------------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

CData Sync 内部ではDESKTOP-H8P4FBP-bin.000009 から参照するようバイナリバージョンを保持している状態でジョブを実行すると、エラーになってしまいます。

ですので、バイナリログの有効期間内でジョブを実行するようスケジューリングしてください。

おわりに

いかがでしたでしょうか？今回はMySQL のバイナリログでのCDC レプリケーションの方法をご紹介しました。
色々書きましたが、MySQL 8.0 ではデフォルトで有効ですので、Cdata Sync v22 をインストールしていただければ設定不要ですぐに利用できるようになっています。

このCData Sync は どこにでもホスティングできるアプリケーションですので、クラウド環境だけではなく、オンプレミス環境でもご利用いただけます。また、30 日間の無償トライアルも可能ですので是非以下のリンクから製品をダウンロードしてお試しください！
Data Sync Apps | CData Software Japan

関連コンテンツ