MySQLとは
MySQLはOracle社が提供するRDBMSのひとつで、オープンソースソフトウェアであるため導入コストの低さから社内のデータベースとして採用している企業もあるかと思います。ですが近年クラウド型のDWH（データウェアハウス）サービスが次々と登場し、安価な運用コストながらデーベースの管理コストをベンダー側に負担してもらうことができ、またデータ量の増加に伴うデータベースの拡張も容易になってきました。
ただし従来のMySQLをベースにした運用から、DWHサービスを中心とした運用へのシフトにはMySQLからDWHへのデータ統合が簡単に行えなければあまり意味がありません。
また、一度データパイプラインが構築できたとしてもデータの更新のたびにMySQLのデータを丸ごと転送していてはMySQL、DWH双方のパフォーマンスを低下させます。
概要
そこで今回はtrocco®のCDC形式転送機能を利用し、MySQLbinlogのうち変更が生じたデータだけをGoogle BigQueryへ自動で転送、データベースをアップデータとするという仕組みをほとんどノーコードでやってみたいと思います。
1-0. CDCとは？
今回はMySQLのbinlogをCDC形式（Change Data Capture = 変更データキャプチャ）で転送します。
CDC形式の転送により、初回の転送のみデータを全件転送するもののそれ以後はMySQL上に生じた差分データのみ転送するため転送量の削減・転送の高速化を図ることができます。
データ転送に伴うデータベースへの負荷を抑えつつバッチ処理でデータ同期を行いたいときには、CDC形式での転送がベストではないでしょうか。
なおCDC形式の形式の転送と全件転送との比較はを表にすると以下のようになります。
| 全件転送 | CDC | |
|---|---|---|
| trocco転送量(初回実行時) | ソーステーブルの全データ | ソーステーブルの全データ |
| trocco転送量(2回目以降) | ソーステーブルの全データ | 更新ログのみ(※1) |
| スキーマ変更発生時 (列追加等) | 自動追従 | 自動追従 |
| データ削除(DELETE)の挙動 | 物理削除 | 論理削除 |
| データ更新(UPDATE)の挙動 | 物理更新 | 物理更新 |
| BigQueryスキャン量 | なし | 現在のBigQuery上のテーブル全量+更新ログ |
| データソースDBへの負荷 | 高 | 低（初回転送時のみ全量のため高負荷） |
※1 INSERT,DELETE,DDL文については1行文、UPDATE文については更新前後の2行分
trocco®でCDC転送を行う際には
- trocco®がMySQLのbinlogをGoogle BigQuery上に転送
- binlogのデータから重複を排除し、それぞれのレコードの最新のレコードの集合を作成
- ↑の集合を現在のGoogle BigQueryテーブルとマージし、マージ後のテーブルで置換する
という手順を踏んでいます。
CDC転送の詳細な手順についてはこちらのドキュメントの「テクニカルオーバービュー」の項をご参照ください。
2-1. MySQLのパラメータ設定
trocco®のドキュメントを参照しつつ、MySQLのパラメータ設定を行います。
binlogの設定を反映させるためにはDBの再起動が必要です。
DBを再起動して次のステップに進みます。
2-2. MySQLの権限設定
trocco®のドキュメントを参照しつつ、MySQLの権限設定を行います。
ここではSELECTとREPLICATION CLIENTの２つの権限を設定します。
2-3. Primary Keyの設定
転送元のテーブルにはPrimary Keyが設定されている必要があります。
ここでは既に設定されているものとして、割愛します。
その他、trocco®で転送する際の必須条件などについてはこちらのドキュメントを参照してください。
これにてMySQL側での設定は完了です。
続いてtrocco®の設定を行います。
3-0. trocco®に登録
はじめにtrocco®のアカウントが必要です。
無料トライアルも実施しているので、前もって申し込み・登録をしておきましょう。
https://trocco.io/lp/index.html
（申し込みの際にこちらの記事を見たという旨を記載して頂ければスムーズにご案内できます）
3-1. 転送元・転送先を決定
trocco®にアクセスし、ダッシュボード画面から「転送設定を作成」のボタンを押します。
転送元にMySQL binlog、転送先にGoogle BigQueryを選択し、「この内容で作成」ボタンを押します。
設定画面になるので、必要な情報を入力していきます。
3-2. MySQLとの連携設定
転送設定の名前とメモを入力します。
転送設定の名前を決めたら、「転送元の設定」内の「接続情報を追加」ボタンを押し、MySQLの接続情報の設定を行います。
接続設定の名前・ホスト・ポート・ユーザー名・パスワードを入力します。
接続確認をしたのち、設定の保存をします。
再度転送設定画面に戻り、「接続情報を読み込む」ボタンを押すと、作成した接続情報が選択できるかと思います。
3-3. MySQLからのデータ抽出設定
これでMySQLとの連携は完了です。
次にMySQLの取得データを設定します。
まずは必須項目の「データベース名」「テーブル」をセレクトボックスの中から選択します。
スキーマ追従の有効/無効を指定します。
スキーマ追従が有効になっている場合、
- クエリにRENAME COLUMNが含まれている場合、変更後の名前でデータ連携される
- クエリにADD COLUMNが含まれている場合、追加したテーブルが自動で転送されるようになる
といった挙動になります。
最後に接続確認が通るか確認します。
MySQLとの接続が確認できました。以上でMySQL側の設定は完了です。
次は転送先のGoogle BigQueryの設定を行っていきましょう。
3-4. 転送先Google BigQueryの設定
基本的には転送元と同じ要領です。
「接続情報を追加」ボタンからGoogle BigQueryの接続設定を行い、データベース・テーブル・データセットのロケーションを指定します。
MySQLとの接続同様Google BigQueryについても接続テストを行います。
接続が確認できたためこれで入力は完了です。保存して次に進みましょう。
3-5. カラム名・データ型の確認
「データプレビュー」の画面でカラム名・データ型の確認を行います。
MySQLの各データはtrocco®の方でデータ型を判断し、自動的にGoogle BigQueryのデータ型に変換されます。
データ型についてはこちらのドキュメントをご参照ください。
問題なければ転送設定の詳細画面に移ります。
3-6. 初回転送
CDCは変更データのみを転送する方式ですが、初回だけは全件転送をする必要があります。
このステップでは、手動で全件転送を行います。
右上にある「実行」ボタンを押します。
「全件転送を行う」にチェックマークを入れて、「ジョブを実行」を押します。
転送完了まで待ちます。
転送が成功しました。これで初回の全件転送は完了です。
続いてCDC転送の設定をします。
転送設定の詳細画面に戻りましょう。
3-7. スケジュール設定
「スケジュール・トリガー設定」タブを開きます。
以下のように実行スケジュールを設定することで、転送を自動化することが出来ます。
3-8. 通知設定
必須の設定ではないですが、ジョブの実行ステータスに応じてEmailやSlackに通知を行うことが出来ます。転送の成功/失敗時の通知だけでなく、転送ジョブが不自然に時間がかかっている場合や、転送は成功したもののデータの転送件数がゼロになってしまっている場合など様々なエラー管理に対応しています。
これで全ての設定が完了しました。
以後は設定したスケジュールに合わせてCDC転送が実行され、差分のデータがGoogle BigQueryに統合されるようになります。
4-1. 転送結果の確認
trocco®での設定のみで転送が完了しているため、Google BigQuery側で操作は特に必要ありません。
最後に設定に従ってGoogle BigQueryにデータの統合ができているか確認してみます。
両方とも同じデータが入っていることが確認できます。
元のテーブルにいくつか変更を加えて、Gogle BigQuery側でどのように表示されるのかを確認してみましょう。
- id=1 のcommentをUPDATE
- id=2 の行を削除
- 新たに「NewColumn」のカラムを追加
この３点が変更されています。
このようにMySQLのテーブルに変更を加えたうえでCDC転送を行い、Google BigQuery側のコンソール画面から再度データを確認します。
先ほどのクエリで変更した部分がGoogle BigQueryでも反映されていることが分かります。
また削除したカラムは_trocco_deletedカラムがtrueになっています。
今回はtrocco®の設定で「スキーマ追従」の設定を有効にしているのでカラムの追加が反映されていますが、スキーマ追従が無効の場合には反映されないのでご注意ください。
まとめ
いかがでしたでしょうか。trocco®を利用することで簡単にMySQLのデータを取得し、DWH（今回はGoogle BigQuery）に統合することが出来ました。
MySQLを活用している企業の方はぜひデータtrocco®をご検討ください。
trocco®では、クレジットカード不要のフリープランをご案内しています。ご興味がある方はぜひこの機会に一度お試しください。
