MySQL::diffでデプロイ時のDBスキーマ更新を自動化!

前回のエントリ DBテーブルの差分を出力するMySQL::diffをインストールする の続きです。

前回のおさらいを少しすると、 MySQL::diff は二つのデータベーススキーマを比較して差分の ALTER SQL を出力してくれる perl 製スクリプトツールです。

例として以下のコマンドは、database1 データベースを database2 データベースのスキーマに追従する ALTER SQL が生成されます。DB スキーマの更新に便利です。

mysqldiff -u user -p password -i database1 database2

このツールのメリットはフレームワークに依存しないスタンドアロンなスクリプトであることだと思います。デメリットは最近の MySQL の機能に対応していなかったりすることだと思います。例えば PARTITION には対応していません。中身は perl で書かれているので、数箇所ソースをいじってメンテナンスしつつ運用しています、、。perl を読めると役立つことがあるかも。

GUI で差分を生成したいならこのツールよりも MySQL Workbench の方が便利です。

MySQL::diff の事例紹介

今回は、現在携わっているプロジェクトで実際に MySQL::diff を migration ツールとして利用したので事例を紹介します。

前提として、WebAP と DB は二つで一つで、両方が噛み合っていないとシステムに不具合が生じるため、同じリポジトリでバージョン管理しています。また更新は WebServer 上のシェルスクリプトで行います。

  • /var/www/sousaku-memo.net: gitで管理しているディレクトリ
  • /var/www/sousaku-memo.net/web: ドキュメントルート (WebAP)
  • /var/www/sousaku-memo.net/database: DDL SQLを配置 (DB)

デプロイ用のシェルスクリプト例です。おおまかな流れとしては、このようなことをやっています。

  1. git から最新の WebAP と DDL を取得
  2. 最新の DDL を使って一時的な比較用 DB を作成
  3. 現行の古い DB を最新の DB に追従する ALTER SQL 生成
  4. ALTER SQL を実行
#!/bin/sh -eu

<h1>バックアップ</h1>

cp -rfpv "/var/www/sousaku-memo.net" "/var/www/sousaku-memo.net_$(date '+%Y%m%d_%H%M')"

<h1>git から最新の WebAP と DDL を取得</h1>

cd "/var/www/sousaku-memo.net"
git fetch origin
git reset --hard origin/master
git clean -fdx

<h1>コミットにデプロイの目印をつけるためタグ付け</h1>

tag_name="deploy-$(date '+%Y%m%d-%H%M')"
git tag ${tag_name}
git push origin ${tag_name}

<h1>データベース接続情報</h1>

readonly MYSQL_USER='user'
echo "Enter password for '${MYSQL_USER}' MySQL account:"
read -s mysql_password
readonly MYSQL_COMMAND="mysql -u ${MYSQL_USER} -p${mysql_password}"
readonly MYSQL_DIFF_COMMAND="mysqldiff -u ${MYSQL_USER} -p ${mysql_password} -i"

<h1>比較用の一時データベース(temp_global)を作成</h1>

cd ~
{
  echo "NOWARNING;"
  echo "SET NAMES utf8;"
  echo "DROP DATABASE IF EXISTS temp_global;"
  echo "CREATE DATABASE temp_global DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;"
  echo "USE temp_global;"
  echo "SOURCE /var/www/sousaku-memo.net/database/ddl.sql;"
} >generate_temp_db.sql

${MYSQL_COMMAND} &lt;generate_temp_db.sql

<h1>作成した最新の temp_global と 稼働中の global データベースの差分をとる</h1>

<h1>alter_up_global.sql がスキーマアップグレード用クエリ</h1>

<h1>alter_down_global.sql がダウングレード用クエリ</h1>

${MYSQL_DIFF_COMMAND} global temp_global >alter_up_global.sql
${MYSQL_DIFF_COMMAND} temp_global global >alter_down_global.sql

<h1>スキーマアップグレード前確認</h1>

cat alter_up_global.sql
echo 'スキーマを最新にしてもいいですか?:'
read

<h1>スキーマアップグレード</h1>

{
  echo "NOWARNING;"
  echo "SET NAMES utf8;"
  echo "USE global;"
  echo "SOURCE alter_up_global.sql;"
} >>alter_execute.sql

${MYSQL_COMMAND} &lt;alter_execute.sql
echo '更新を完了しました'

実際はもっと細かい処理があるのですが、雰囲気こういう感じということで。

シェルスクリプトの補足説明

データベースのバックアップコマンドは省いていますが、ALTER 実行前にはやっておいた方がいいと思います。それと、ALTER SQL ファイルはデプロイの度に世代管理するようにするのがおすすめです。後でスキーマのダウングレードやアップグレードを出来るようにしておくためです。

git から最新のファイルを取得する部分は pull ではなく fetch + reset –hard でやっています。

mysqldiff コマンドは -i オプションをつけていますが、これはオートインクリメント値やデフォルト値を無視してくれるオプションです。

参考サイト

2014/01/30編集: シェルスクリプトの fetch コマンドを修正しました。

コメントを残す

メールアドレスが公開されることはありません。

計算問題(認証) * Time limit is exhausted. Please reload the CAPTCHA.