WEBサイトを運用をしている場合、仕様の追加変更やパフォーマンス対策など、様々な理由で、テーブルの構造を途中で変更する必要性が出てきます。
このような場合、通常では一時的にWEBサイトを停止し、メンテナンス期間中にDBにALTERをします。
しかし、このような機会が頻繁に得られない場合などは、稼働中に変更しなければならないケースもあるかと思います。
そこで今回は、EC2でMySQL(VP編 VPってなんじゃ？)で紹介したVPを利用して、
停止することなくテーブルにALTERをする方法を紹介したいと思います。
初期のテーブルが以下の通りとしますと、
create table gift( id int auto_increment, gift_name varchar(255), description text, created_at datetime not null, primary key(id) )engine=InnoDB;
これに削除日(deleted_at)を追加し、下記のように変更したいとします。
create table gift( id int auto_increment, gift_name varchar(255), description text, created_at datetime not null, deleted_at datetime, primary key(id) )engine=InnoDB;
それでは、VPを利用して元のテーブルと新規テーブルを入れ替えてみます。
まず、初期イメージは以下の通りです。
新しいスキーマのテーブルと、入れ替え用のダミーのテーブルを用意し、VPテーブルで新スキーマテーブルとダミーテーブルを繋げておきます。
mysql> create table gift_new( id int auto_increment, gift_name varchar(255), description text, created_at datetime not null, deleted_at datetime, primary key(id) )engine=InnoDB; Query OK, 0 rows affected (0.03 sec) mysql> create table gift_dummy like gift; Query OK, 0 rows affected (0.01 sec) mysql> create table gift_vp( id int auto_increment, gift_name varchar(255), description text, created_at datetime not null, primary key(id) )engine=vp comment 'table_name_list "gift_dummy gift_new", cit "2", cil "2", ctm "1", ist "1", zru "1"'; Query OK, 0 rows affected (0.01 sec)
VPテーブルには、Spiderと同様、基本設定以外にも様々なオプションがあり、ここでは、cit、cil、ctm、ist、zruというオプション値を指定しています。
これらはそれぞれ以下のような意味があります。(マニュアルから抜粋)
- choose_ignore_table_list(cit)
検索時に利用するテーブルの選択から指定した番号のテーブルを除外する。
デフォルト値は指定なし。- choose_ignore_table_list_for_lock(cil)
ロック付検索時に利用するテーブルの選択から指定した番号のテーブルを除外する。
更新は行われる。
デフォルト値は指定なし。- choose_table_mode(ctm)
検索時に利用するテーブルの選択モード。
サーバパラメータvp_choose_table_modeが設定されている場合は、そちらが優先される。
0：最適化モード。
1：「table_list」の前からの記載順に利用するテーブルを決定する。
デフォルト値は 0- infomation_source_table(ist)
テーブルステータス取得のモード。
0 ：全ての子テーブルからテーブルステータス取得を行う。
1-：指定した子テーブルからのみテーブルステータス取得を行う。
デフォルト値は 0- zero_record_update_mode(zru)
0件更新の際のモード。
0：何もしない。
1：choose_ignore_table_list_for_lockの対象子テーブルであった場合は、insertする。
デフォルト値は 0
つまり、cit：2、cil：2、ctm：1、ist：1、zru：1という設定の意味は、
VPテーブルに対して検索されたときにgift_newは使用しない
VPテーブルに対して検索されたときにgift_dummyを使用する
VPテーブルのステータスチェックにはgift_dummyのステータスを使用する
VPテーブルの更新対象が0件であった場合は、INSERTする
ということを示します。
次に、各テーブルをリネームします。
gift_dummyは必要なくなったのでgift_deleteに、元のgiftをVPの接続先であるgift_dummyに、VPテーブルのgift_vpをgiftにそれぞれ変更します。
これにより、VPテーブルの接続先テーブルが、オリジナルのテーブルと新スキーマのテーブルに切り替わり、アプリケーションからのアクセスはVPテーブルに瞬時に切り替わります。
mysql> rename table gift_dummy to gift_delete, gift to gift_dummy, gift_vp to gift; Query OK, 0 rows affected (0.03 sec)
vp_copy_tables()を実行します。
vp_copy_tablesはVPに付属されているUDFで、VPテーブルを介して、子テーブル間でデータコピーを 行うことができます。
コピー元テーブルリストとコピー先テーブルリストのテーブルが、指定した親テーブルからのみ更新される場合、
コピー中にそのVPテーブルに行われた更新もコピー先にコピーされるため、テーブルへの更新を止めずにコピーすることができます。
mysql> select vp_copy_tables('gift', 'gift_dummy', 'gift_new');
そして、コピーが完了したら、テーブルのリネームを再度行います。
新スキーマのgift_newをgiftに、giftだったVPテーブルをgift_vpに変更します。
mysql> rename table gift to gift_vp, gift_new to gift;
最後に、必要のなくなったテーブルを削除します。
mysql> drop table gift_dummy, gift_vp, gift_delete;
このように、データアクセスをさせたまま、テーブル定義の変更とデータ移行をすることができました。
こちらの記事はなかの人（memorycraft）監修のもと掲載しています。
元記事は、こちら