MySQL (MariaDB) データベース定義の差分を取得する方法

MySQL または MariaDB で2つのデータベースの定義がどう異なるのか、調べたいことってありますよね？

mysqldump を使って diff で差分を取得することをまず考えるかもしれませんが、もしその差分をなくす、つまり一方に行った変更を他方に反映したい場合に、まさかそこから手動で ALTER TABLE 文を書こうなんてエンジニアはいないと思います。

そんなわけで mysqldiff コマンドの出番です。

mysqldiff コマンドについて

mysqldiff は Python で実装された Oracle 公式の MySQL 向けユーティリティツール群である MySQL Utilities に含まれるツールのひとつです。2つの異なるデータベースを比較し、その差分を出力することができます。

Perl製のツール MySQL::Diff について

mysqldiff で検索すると同じ目的を持った Perl 製のツール MySQL::Diff が存在することがわかります。……というよりもどちらもコマンド名が mysqldiff であるため、別々のツールであるとは極めてわかりにくい状態です。

どちらを使ってもよい、と言いたいところですが MySQL::Diff を使ってみたところ「外部キー制約を伴うテーブルを正しく解釈できない」というバグがあることがわかりました。MySQL Utilities の方を使った方が無難です。

MySQL Utilities インストール手順

公式サイトから各種OS/ディストリビューション向けのパッケージをダウンロードし、インストールすることができますが、OS X の場合、罠が待っています。

もちろん OS X 向けにも dmg でインストーラが配布されているのですが、なぜか OS X 10.9 Mavericks までしかサポートされていません。Yosemite や El Capitan を使っている場合はその他の方法を使う必要があります。ここではその手順を解説します。

1. MySQL Connector/Python をインストール

MySQL Utilities は MySQL Connector/Python に依存するため、まずはこちらをインストールします。

Python 製のツールと言うことで pip を使ってインストールすることをまず考えたのですが pip でインストールできるバージョンが古い上にそもそもなぜかエラーがでてインストールできません。

ありがたいことに MySQL Connector/Python のソースコードは GitHub で公開されているので、それを使ってインストールします。

$ git clone [e-mail]:mysql/mysql-connector-python.git
$ cd mysql-connector-python
$ python ./setup.py build
$ python ./setup.py install

2. MySQL Utilities をインストール

こちらも GitHub から手動でインストールします。

$ git clone [e-mail]:mysql/mysql-utilities.git
$ cd mysql-utilities
$ python ./setup.py build
$ python ./setup.py install

インストール手順は以上です。

mysqldiff コマンドの使い方

基本のコマンド

基本的な使い方は次の通りです。詳しい使い方やオプションについては mysqldiff --help コマンドで確認してください。

$ mysqldiff \
  --server1=username:password@hostname \
  --difftype=sql \
  --changes-for=server1 \
  --force \
  db1:db2

• --server1 で接続情報を指定します。
• --difftype で差分の出力方法を指定します。sql を指定した場合は ALTER TABLE 文が出力されます。その他に unified, context, differ を指定することができます。
• --changes-for で比較の向きを指定します。「ALTER TABLE 文の反映先」だと考えるとわかりやすいです。
• --force を指定し、すべての差分を出力させます。指定しない場合、最初の差分が見つかった時点で処理が中断されます。

前半の2つは公式マニュアルでも最初に触れられており問題ないのですが、重要なのが後半2つです。

--force を指定しない場合、次のようなエラーメッセージが表示され、差分は出力されません。

# WARNING: Using a password on the command line interface can be insecure.
# server1 on localhost: ... connected.
# Comparing `db1` to `db2`                                         [FAIL]
# Object definitions differ. (--changes-for=server1)
#

--- `db1`
+++ `db2`
@@ -1 +1 @@
-CREATE DATABASE `db1` /*!40100 DEFAULT CHARACTER SET utf8mb4 */
+CREATE DATABASE `db2` /*!40100 DEFAULT CHARACTER SET utf8mb4 */
Compare failed. One or more differences found.

「1つかそれ以上の違いが見つかったので比較に失敗したよ！」とのたまいやがります。何のためのツールなのかと小一時間問い詰めたい気分になりますが、ここはそっと --force を指定してやるのが紳士のやり方です。

また mysqldiff --help を見る限り --changes-for を指定しない場合は --changes-for=server1 を指定した場合と同じ挙動になるはずですが、指定しない場合次のような警告メッセージが表示される場合があります。

# WARNING: The tables structure is the same, but the columns order is different. Use --change-for to take the order into account.

これが --changes-for=server1 を明示的に指定すると表示されなくなります。自分がなにをしているのかわかりやすくするためにも、明示した方がよさそうです。

2つのデータベースが異なるサーバーにある場合

--server2 オプションに後者のデータベースがあるサーバーへの接続情報を指定します。

$ mysqldiff \
  --server1=db1_username:db1_password@db1_hostname \
  --server2=db2_username:db2_password@db2_hostname \
  --difftype=sql \
  --changes-for=server1 \
  --force \
  db1:db2

テーブルを指定する場合

データベース名に続けてテーブル名を指定することもできます。その場合、異なるテーブル名を持つテーブル間の差分を取得することも可能です。

$ mysqldiff \
  --server1=username:password@hostname \
  --difftype=sql \
  --changes-for=server1 \
  --force \
  db1.table1:db2.table2

注意事項

1. 実行時のユーザー権限について

mysqldiff を実行するときに指定する MySQL のユーザーは次の権限を持っている必要があります。

• 比較対象とするデータベースへの SELECT 権限
• mysql データベースへの SELECT 権限

これらの権限が不足している場合、コマンドはエラーとなり差分は出力されません。

2. テーブル単位の過不足があっても表示されない

テーブル名を指定せず、データベース単位の比較を行う際に「一方のデータベースにのみ存在する TABLE や VIEW」がある場合、その差分は表示されず、次のようなメッセージが表示されるのみとなります。

# WARNING: Objects in server1.db2 but not in server1.db1:
#         VIEW: foo
#        TABLE: bar

まとめ

• Perl の MySQL::Diff は使わずに Oracle 公式の MySQL Utilities を使おう。
• OS X の場合は GitHub からソースコードを持ってきてインストールしよう。
• --force と --changes-for を指定しよう。

参考にしたサイトなど

• macでのmysql-connector-pythonインストール方法
• 公式マニュアル