EC2でMySQL(運用編 VPで無停止ALTER)

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）監修のもと掲載しています。
元記事は、こちら