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 インストール手順
[公式サイト](http://dev.mysql.com/downloads/utilities/1.5.html)から各種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 git@github.com:mysql/mysql-connector-python.git
$ cd mysql-connector-python
$ python ./setup.py build
$ python ./setup.py install
```

### 2. MySQL Utilities をインストール
こちらも GitHub から手動でインストールします。

```
$ git clone git@github.com: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インストール方法](http://qiita.com/PallCreaker/items/0b02c5f42be5d1a14adb)
- [公式マニュアル](https://dev.mysql.com/doc/mysql-utilities/1.5/en/mysqldiff.html)