MySQL バイナリログをマスキングするツールを作ってみた

このエントリーは MySQL Advent Calendar 2020 の 12/20 のエントリーです。

問題を再現させるためにバイナリログが必要

MySQL は OSS です。誰でも無料で自由に使うことができます。

一方、Oracle や Percona といったデータベースを専門とする会社にお願いして、有償サポートを受けることも可能です。有償サポートはその道のプロフェッショナルの方が、問題解決にあたってくれるわけですが、彼らも魔法使いではありません。 問題の再現性がなければ、有効な回答がもらえないことがあります。

問題の再現性を高めるために、必要になってくるのがバイナリログです。MySQL のバイナリログにはデータベースに対する更新履歴が記録されています。 バイナリログをサポートに提出することで、より問題を詳細に解析してもらうことが可能になります。

しかし、バイナリログにはデータが含まれていますから、契約を結んでいるサポート先とはいえ、外に出せない場合も多いです。そこで、バイナリログに含まれるデータのマスキングについて考えてみました。

バイナリログのフォーマット

バイナリログは名前のとおり、バイナリ形式で書かれています。そのままでは人間が読めません。まず、人間が扱えるよう、mysqlbinlog コマンドでバイナリログ形式からテキスト形式へ変換します。

$ sudo mysqlbinlog -vv --base64-output=DECODE-ROWS   /var/lib/mysql/mysqld-bin.000002 

バイナリログのフォーマットは非常にシンプルです。更新内容が @数字=値 の形式で記録されています (ROWフォーマット前提)。

mysql> INSERT INTO test.t (c1, c2, c3), ('THIS IS SECRET STRING', 1000, now());

#201213  5:45:26 server id 1  end_log_pos 819 CRC32 0x2388d887  Write_rows: table id 109 flags: STMT_END_F
### INSERT INTO `test`.`t`
### SET
###   @1=1 /* LONGINT meta=0 nullable=0 is_null=0 */
###   @2='THIS IS SECRET STRING' /* VARSTRING(255) meta=255 nullable=0 is_null=0 */
###   @3=1000 /* INT meta=0 nullable=1 is_null=0 */
###   @4='2020-12-13 05:45:26' /* DATETIME(0) meta=0 nullable=1 is_null=0 */

適当な文字に置換するだけであれば、非常に簡単です。しかし、それではデバッグには向かないでしょう。

デバッグに必要な要素を可能な限り保持しつつ、マスクする必要があります。

ツールを作ってみた

バイナリログをマスキングするスクリプトを書いてみました。

github.com

これが、

### INSERT INTO `test`.`t`
### SET
###   @1=1 /* LONGINT meta=0 nullable=0 is_null=0 */
###   @2='THIS IS SECRET STRING' /* VARSTRING(255) meta=255 nullable=0 is_null=0 */
###   @3=1000 /* INT meta=0 nullable=1 is_null=0 */
###   @4='2020-12-13 05:45:26' /* DATETIME(0) meta=0 nullable=1 is_null=0 */

こなります↓

$ sudo mysqlbinlog -vv --base64-output=DECODE-ROWS   /var/lib/mysql/mysqld-bin.000002 | ./mita2-binlog-mask.py --preserve=test.t.1
<snip>
### INSERT INTO `test`.`t`
### SET
###   @1=1 /* LONGINT meta=0 nullable=0 is_null=0 */
###   @2='EFIA7R01TBPE9ADWOYH' /* VARSTRING(255) meta=255 nullable=0 is_null=0 */
###   @3=2140268343 /* INT meta=0 nullable=1 is_null=0 */
###   @4='2020-12-25 21:17:29' /* DATETIME(0) meta=0 nullable=1 is_null=0 */

以下のような特徴があります。

• マスキングの対象外とするカラムの指定が出来る

--preserve でどのテーブルの何番目のカラムをマスキングの対象外にするか指定します。 ロックにまつわる問題の場合は、どの行に更新があったかが重要になってきます。そのようなときに、主キーだけマスキングの対象外にすることを想定しています。

• 文字列長を維持してマスクする

文字列型は文字列長を維持してランダムな文字列にマスクします。

• 日付型はシフトする

日付型も完全にランダムに置き換えるのではなく、スクリプト開始時にランダムな秒数を決め、すべてのレコードでそのランダム秒数ぶん同じようにシフトするとしました。 レコードどうしの日付の相対値を維持します。

例えば、10:10 のレコードと 10:15 のレコードがあった場合、10:40 (+30秒) と 10:45 (こちらも+30秒）というような値に置換します。

もっとやれそうなことは、ありそうですが、日曜大工ではここまで・・・

MySQL Advent Calendar 2020

以上、日曜大工の紹介でした。明日の Advent Calendar は hamtsu47 さんです。