無停止でALTERできるPercona-Toolkitのonline-schema-change

MySQL5.5をまだまだ絶賛利用中な私です。
サービスを運用しているとINDEX追加やカラム追加を行いたい要望が多々発生します。
それも無停止で。

MySQL5.6からは無停止でINDEX追加やカラム追加ができるオンラインDDLが実装されました。
カラム追加はまだ色々制約があるみたいですが…。
http://dev.mysql.com/doc/refman/5.6/en/innodb-create-index-overview.html

pt-online-schema-change
MySQL5.5でも似たようなことをやりたいので、
有名な「Percona-Toolkit」に同梱されているpt-online-schema-changeを利用しています。
http://www.percona.com/software/percona-toolkit

通常はロックが掛かってしまうALTER TABLEをロックなしで実行できるようになるツールです。
今までカラム追加の為に行っていた短時間のメンテナンスが必要なくなります。

online-schema-changeの仕組み
このような流れで実現されています。

1.　対象テーブルと同じ構造をした作業用テーブルを作成する

2.　作業用テーブルに変更するALTER文を適用する

3.　3つトリガーを作成して、対象テーブルへの挿入・削除・更新が作業用テーブルに反映されるようにする

4. 対象テーブルから作業用テーブルへデータをコピーしてくる

5. RENAMEして対象テーブルと作業用テーブルを入れ替える

6. 入れ替え後の古いテーブルとトリガーを削除する

インストール方法

yum install perl-DBD-MySQL perl-Time-HiRes perl-IO-Socket-SSL
wget http://www.percona.com/downloads/percona-toolkit/2.2.7/RPM/percona-toolkit-2.2.7-1.noarch.rpm
rpm -ivh percona-toolkit-2.2.7-1.noarch.rpm

利用方法

/usr/bin/pt-online-schema-change --execute --set-vars="SET VARIABLES" --alter="xxxxxx" h=HOSTNAME,D=DATABASE,t=TABLE_NAME,u=USER

--set-vars=	SET hoge〜のようにオプション値指定したい時に
--alter=	ALTER文の中身を書く
h=	接続先DBホスト
d=	DB名
t=	テーブル名
u=	DBユーザ名

例えば以下の様な操作をしたい場合は、

mysql -uroot -h localhost
use hoge;
set sql_log_bin=0;
alter table hoge_table engine=InnoDB;

このようなコマンドになります。

/usr/bin/pt-online-schema-change --execute --set-vars="sql_log_bin=0" --alter="engine=InnoDB" h=localhost,D=hoge,t=hoge_table,u=root

利用時の注意点
■テーブルコピーを伴うのでIOとCPUかなり食う
■本番と同等スペックの検証環境で確認するべき
■普通にALTER打つよりは遅い
■レプリケーション組んでいる時に普通に実行するとレプリ遅延が発生する
■Ctrl + Cで途中で止めた場合は、トリガーと作業テーブルの削除が行われないのでゴミ掃除する
その際は絶対に先にトリガーを削除する。テーブル消してしまうとエラーになる。

レプリ遅延回避の方法
プロダクトによっては、レプリ遅延の発生を許容できる作りになっていない場合も有ると思います。
オンラインでカラム追加を行った時に、絶対にスレーブのレプリケーション遅延を発生させたくない場合は、
以下の様な手順でレプリ遅延の発生を回避できます。

1.　先にスレーブ全台でonline-schema-changeを実行する。その際のオプションとして--set-vars="sql_log_bin=0"で変更binlogに出さないようにする。

2.　スレーブ全台で完了後、マスターでonline-schema-changeを実行する。この時も--set-vars="sql_log_bin=0"を忘れずに。

※カラム追加などでテーブル構造を変更した場合、INSERTやUPDATEのクエリの書き方によってはアプリケーション側でエラー出る可能性もあるので、
その辺考慮しましょう。INDEX追加とかなら多分大丈夫です。

仕組みを理解した上で利用するには便利なツールなので、
みなさんガンガンつかっていきましょう。