kakakakakku blog

Weekly Tech Blog: Keep on Learning!

mysqldiff を使って継続的に MySQL のデータベーススキーマの差分をチェックする

最近,環境ごとのデータベーススキーマの差分をチェックする機会があった.プロダクション環境とステージング環境ならまだしも,開発環境だと検証のために追加したインデックスがそのままになっていたり,開発が途中で止まってしまって日の目を見ることがなかったテーブルが残っていたり,そういうことって比較的あるのではないかなと思う.特に今の環境だと,マイグレーションの仕組みが整っていないという課題もあり,より一層,データベーススキーマに差分が出やすくなってしまっている.

今回は MySQL から公式に提供されている mysqldiff というツールを使ってデータベーススキーマの差分をチェックした.

mysqldiff をインストールする

mysqldiff は MySQL Utilities という MySQL の管理ツールパッケージの中に同梱されている.現在だと v1.6 が最新になっている.

yum で簡単にインストールすることができる(リポジトリは適宜指定すること).

$ sudo yum install -y mysql-utilities

$ which mysqldiff
/usr/bin/mysqldiff

$ mysqldiff --version
MySQL Utilities mysqldiff version 1.6.5
License type: GPLv2

mysqldiff を使う

基本的には以下のように使う.データベースだけではなく,テーブルを指定して限定的な差分をチェックすることもできる.

  • --server1 … 比較するデータベースの接続設定
  • --server2 … 比較するデータベースの接続設定
  • --force … デフォルトだと,差分が1件出ると止まってしまうため,最後まで流すために指定する
  • --skip-table-options … テーブルのオプションに差分があっても無視するために指定する
  • --changes-for=server2 … デフォルトだと server1 を修正する形で認識されるため,server1 を起点として server2 を修正する形にする
$ mysqldiff --server1=root@localhost --server2=root@localhost database_a:database_b --force --skip-table-options
$ mysqldiff --server1=root@localhost --server2=root@localhost database_a.users:database_b.users --force --skip-table-options

サンプルとして users テーブルの差分をチェックしてみた.以下の例では email_UNIQUE 制約が存在しないことをチェックできている.

$ mysqldiff --server1=root@localhost --server2=root@localhost database_a.users:database_b.users --force --skip-table-options --changes-for=server2
# server1 on localhost: ... connected.
# server2 on localhost: ... connected.
# Comparing database_a.users to database_b.users           [FAIL]
# Transformation for --changes-for=server2:
#

--- database_a.users
+++ database_b.users
(中略)
   `email` varchar(255) DEFAULT NULL,
(中略)
   `updated_at` int(10) unsigned DEFAULT NULL,
   `deleted_at` int(10) unsigned DEFAULT NULL,
   PRIMARY KEY (`id`),
-  UNIQUE KEY `email_UNIQUE` (`email`),
# Compare failed. One or more differences found.

完全に一致している場合は,以下のように [PASS] と表示される.

$ mysqldiff --server1=root@localhost --server2=root@localhost database_a.users:database_b.users --force --skip-table-options --changes-for=server2
# server1 on localhost: ... connected.
# server2 on localhost: ... connected.
# Comparing database_a.users to database_b.users    [PASS]
# Success. All objects are the same.

差分を SQL 形式で出力する

--difftype=sql を指定すると,データベーススキーマを一致させるために必要な SQL を出力することができる.

$ mysqldiff --server1=root@localhost --server2=root@localhost database_a.users:database_b.users --force --skip-table-options --changes-for=server2 --difftype=sql
# server1 on localhost: ... connected.
# server2 on localhost: ... connected.
# Comparing database_a.users to database_b.users           [FAIL]
# Transformation for --changes-for=server2:
#

ALTER TABLE `database_b`.`users`
  ADD UNIQUE INDEX email_UNIQUE (email);

# Compare failed. One or more differences found.

微妙なところ : AUTO_INCREMENT だけを無視することができない

比較対象のデータベースが TRUNCATE されてなく,AUTO_INCREMENT を保持している場合,デフォルトだと差分として検知されてしまう.そのために --skip-table-options を使っているが,これだと AUTO_INCREMENT だけでなく ENGINECHARSET の差分も無視してしまう.ここは少し微妙だなと思った.

--- database_a.users
+++ database_b.users
(中略)
-) ENGINE=InnoDB AUTO_INCREMENT=123 DEFAULT CHARSET=utf8
+) ENGINE=InnoDB DEFAULT CHARSET=utf8
# Compare failed. One or more differences found.

微妙なところ : --difftype=sql は完璧じゃない

今回試したケースだと,MySQL の PARTITION の定義が抜けている場合に,データベーススキーマの差分としては検知できるものの,--difftype=sql では検知されなかった.現状だと --difftype=sql は完璧じゃなく,参考程度に使うのが良いと思う.

CI として継続的に差分をチェックする

日々開発を進めている中で,データベーススキーマに差分が生まれてしまうことはあると思う.よって今回は mysqldiff を使ったデータベーススキーマの差分チェックを CI として継続的に実行できるように自動化した.よって,意図しない差分が出た場合は,容易に気付くことができるようになった.

補足 : mysqldump --no-data を使う

mysqldiff の紹介をメインに書いたため省略してしまったけど,実際に使う場合は環境ごとにネットワークが分離されていることも多いと思う.まさに今回もそのような環境下で試していたものの,mysqldump --no-data でデータベーススキーマを取得して,S3 にアップロードすることによって,CI を実行するサーバに各環境のデータベーススキーマを集約することができる.補足として書いておく.

まとめ

  • mysqldiff を使うと,MySQL のデータベーススキーマの差分をチェックすることができる
  • オプションが多くあるため,ドキュメントを確認すること
  • AUTO_INCREMENT の差分が出る場合は --skip-table-options オプションを使うが,CHARSET などの差分も無視されてしまう
  • --difftype=sql は完璧じゃないので,参考程度に使う
  • 一回実行して終えるのではなく,CI として継続的に差分をチェックするべき