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 コマンドを修正しました。

DBテーブルの差分を出力するMySQL::diffをインストールする

二つの MySQL データベースを比較し差分 ALTER SQL 文を出力してくれる perl 製ツール、MySQL::diffのインストール方法です。

このツールは、稼働中のデータベースのテーブルスキーマを最新に更新する場合など使えます。

Usage: mysqldiff [ options ] <database1> <database2>

今回はサーバデプロイ時のスキーマ更新作業を自動化するためにインストールしてみました。上手く使うと DDL のスマートなバージョン管理に一役買ってくれると思います。

コマンドラインツールなので GUI で分かりやすく差分を出力したい場合には不向きです。そういった場合はこのツールよりも MySQLWorkbench を使ったほうが便利でしょう。

MySQL::diff のインストール

MySQL::diff は mysqldiff – search.cpan.org で公開されています。

インストール先を /usr/local/mysqldiff にしました。

wget http://search.cpan.org/CPAN/authors/id/A/AS/ASPIERS/MySQL-Diff-0.43.tar.gz
tar xzvf MySQL-Diff-0.43.tar.gz
mv MySQL-Diff-0.43 /usr/local/mysqldiff

/usr/local/bin/mysqldiff にシンボリックリンクをはります。

ln -s /usr/local/mysqldiff/bin/mysqldiff /usr/local/bin/mysqldiff

これでインストール完了と言いたいところなのですが、このままだとパスが通っておらず以下のエラーが起こります。

Can't locate MySQL/Diff.pm in @INC (@INC contains: /usr/local/mysqldiff/bin /usr/lib64/perl5/site_perl/5.8.8/x86_64-linux-thread-multi /usr/lib/perl5/site_perl/5.8.8 /usr/lib/perl5/site_perl /usr/lib64/perl5/vendor_perl/5.8.8/x86_64-linux-thread-multi /usr/lib/perl5/vendor_perl/5.8.8 /usr/lib/perl5/vendor_perl /usr/lib64/perl5/5.8.8/x86_64-linux-thread-multi /usr/lib/perl5/5.8.8 .) at /usr/local/bin/mysqldiff line 112.
BEGIN failed--compilation aborted at /usr/local/bin/mysqldiff line 112.

このエラーを修正するために、ソースコードの冒頭に use lib 文を一行追記します。

vi /usr/local/mysqldiff/bin/mysqldiff
#!/usr/bin/perl -w
use lib '/usr/local/mysqldiff/lib';

=head1 NAME

また、CentOS の場合では CPAN で File::Slurp をインストールする必要がありました。

cpan
cpan > install File::Slurp

mysqldiff コマンドを空打ちしてみて、usage が表示されれば無事にインストール完了です。

次のエントリでこのツールを使って差分を出力しデプロイ自動化を試みます。CI しましょう:)

Apacheのモジュールでコンテンツの帯域制限をかける

Apache 2.xでコンテンツの転送速度制限をかける方法のメモです。共用レンタルサーバや従量課金の AWS などを使っていてサイトの転送量を抑えたい場合など使えると思います。

mod_bw モジュール導入

リポジトリを指定し yum でインストールします。

yum --enablerepo=epel install mod_bw

設定ファイルのバックアップを取り、設定ファイルの設定を行います。

mv /etc/httpd/conf.d/mod_bw.conf /etc/httpd/conf.d/mod_bw.conf.orig
vi /etc/httpd/conf.d/mod_bw.conf

同じクライアントからのアクセスを2Mbps(256kbytes/s)に制限する場合の mod_bw.conf の内容です。BandWidth は転送可能な秒間バイト数です。

<IfDefine HAVE_BW>
  <IfModule !mod_bw.c>
    LoadModule bw_module    extramodules/mod_bw.so
  </IfModule>
</IfDefine>

<IfModule mod_bw.c>
  BandWidthModule On
  ForceBandWidthModule On
  BandWidth all 256000
</IfModule>

128bytes 以上の .flv と .avi ファイルに対して2Mbps(256kbytes/s)に制限する場合の例です。LoadModule は省略。

<IfModule mod_bw.c>
  BandWidthModule On
  ForceBandWidthModule On
  BandWidth all 256000
  LargeFileLimit .flv 128 256000
  LargeFileLimit .avi 128 256000
</IfModule>

設定が終了したらサーバを再起動します。

/etc/init.d/httpd restart

帯域制限がかかっていることを確認

ab コマンドなどでコンテンツの転送速度が制限なされていること、Apache のエラーログにエラーが出ないことを確認します。

ab -n 1 -c 1 http://example.com/hoge.flv