最近,環境ごとのデータベーススキーマの差分をチェックする機会があった.プロダクション環境とステージング環境ならまだしも,開発環境だと検証のために追加したインデックスがそのままになっていたり,開発が途中で止まってしまって日の目を見ることがなかったテーブルが残っていたり,そういうことって比較的あるのではないかなと思う.特に今の環境だと,マイグレーションの仕組みが整っていないという課題もあり,より一層,データベーススキーマに差分が出やすくなってしまっている.
今回は MySQL から公式に提供されている mysqldiff
というツールを使ってデータベーススキーマの差分をチェックした.
mysqldiff をインストールする
mysqldiff
は MySQL Utilities という MySQL の管理ツールパッケージの中に同梱されている.現在だと v1.6 が最新になっている.
- MySQL :: MySQL Utilities 1.6 Manual
- MySQL :: MySQL Utilities 1.6 Manual :: 5.10 mysqldiff — Identify Differences Among Database Objects
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
だけでなく ENGINE
や CHARSET
の差分も無視してしまう.ここは少し微妙だなと思った.
--- 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 として継続的に差分をチェックするべき