AWS RDSのタイムゾーンについて

AWSでRDS(Relational Database Service)のMySQLを利用されている方は少なくないと思いますが、こちらはTimezoneを変更できないという制約があります。

いろいろと調査した結果、下記のワークアラウンドで解決しました。(2013/4/30現在)

ワークアラウンド概要

概要は以下の通りです。ここでは、設定したいTimezoneは ‘Asia/Tokyo’ であると仮定します。

  • “init_connect” パラメータで、接続時に必ずTimezoneを変更するコマンドを実行するよう、設定します。ただしこれは、接続したMySQLユーザーが「rdsadmin」以外の場合にのみ、有効になるものとします。

具体的には、以下のようなストアドプロシージャを実行するよう “init_connect”パラメータに設定します。

IF NOT (POSITION(‘rdsadmin@’ IN CURRENT_USER()) = 1) THEN

SET SESSION time_zone = ‘Asia/Tokyo’;

END IF

MySQLユーザーが「rdsadmin」以外である場合にのみ、「SET SESSION time_zone = ‘Asia/Tokyo’;」を実行するというのがポイントです。

AmazonはRDSを管理するために、「rdsadmin」という専用のMySQLユーザーを用意しています。どうやら、この「rdsadmin」ユーザーのTimezoneがUTC以外の場合に不都合が起こるようです。従って、他のユーザーが接続した場合にだけ、「SET SESSION time_zone = ‘Asia/Tokyo’;」を実行するような、条件分岐付きストアドプロシージャを作成すれば良いわけです。

MySQLはcurrent_user()関数を利用することで、現在のMySQLセッションのユーザー名・ホスト名を取得することが出来ます。IF文とcurrent_user()関数を組み合わせることで、このストアドプロシージャを実現します。

本ワークアラウンドの実現に際し、以下のblogを参考にさせていただきました。

Setting up non-GMT timezone on AWS RDS

手順の説明

具体的な手順は以下の通りです。

手順の実行、前提条件は以下の通りです。

  • RDSインスタンスが既に構築済みであり、適切なSecurityGroupが設定されていること。
    つまり、適切なEC2インスタンスから「mysql」コマンドで接続できる状態になっていることを前提とします。
  • 作業に利用するEC2インスタンスのTimezoneが、目的のものになっていることを前提とします。
    (要所要所で時刻・Timezoneを確認する際に、EC2インスタンスの時刻と比較します。 )
  • 接続に利用するMySQLユーザーはストアドプロシージャを実行できる権限があること。
    RDSインスタンス作成時に生成されるユーザー以外で接続する場合は、後述するGRANT文で権限を付与する必要があります。

手順の流れは以下の様になります。

    1. ストアドプロシージャを作成・確認する
    2. パラメータグループを作成する
    3. RDSインスタンスへ、パラメータグループを適用する
    4. RDSインスタンスを再起動する
    5. mysqlコマンドで接続して動作確認を行う

手順に関連する値は以下の通りです。環境に応じて適宜変更し、手順を読み替えてください。

  • MySQLバージョン:5.5
  • RDSインスタンス名:rdstest1
  • RDS Endpoint:rdstest1.cchpjrugqqdj.ap-northeast-1.rds.amazonaws.com
  • MySQLユーザー名:rdstest1
  • 設定したいTimezone:Asia/Tokyo
  • ストアドプロシージャ名:mysql.adjust_time_zone ()
  • パラメータグループ名:newparam

具体手順

それでは、実際の手順を見ていきましょう。

ストアドプロシージャを作成・確認する

まず、適切なEC2インスタンスにログインします。その後、mysqlコマンドで対象のRDSインスタンスに接続します。

#  mysql -h rdstest1.cchpjrugqqdj.ap-northeast-1.rds.amazonaws.com -u rdstest1 -p

下記クエリを実行し、ストアドプロシージャを作成します。ストアドプロシージャ内に「;」が含まれるので、DELIMITER句で一旦デリミタを変更するのがポイントです。作成先のデータベース名やストアドプロシージャ名は、環境に併せて適宜変更してください。

DELIMITER |

CREATE PROCEDURE mysql.adjust_time_zone ()

IF NOT (POSITION(‘rdsadmin@’ IN CURRENT_USER()) = 1) THEN

SET SESSION time_zone = ‘Asia/Tokyo’;

END IF |

DELIMITER ;

作成したストアドプロシージャを実行してエラーが出ないことを確認します。

CALL mysql.adjust_time_zone;

その後、now()関数の実行結果を確認し、Timezoneの変更が反映されているか確認します。

SELECT NOW();

mysqlコンソールを抜けた後、dateコマンドの結果とnow()関数の結果と比較します。now()関数で表示された時刻が、意図したものになっていればOKです。これでストアドプロシージャの作成は完了です。

参考までに上記内容を実行した際のスクリーンショットを載せておきます。

1-1 ストアドプロシージャを作成・確認する

パラメータグループを作成する

AWS Consoleにログインします。「Services」→「RDS」を選択し、Amazon RDS Console Dashboardを開きます。

左ペインから「DB Parameter Groups」を選択します。その後、右上ペインの上方の「Create DB Parameter Group」ボタンを押します。

2-1 パラメータグループを作成する1

「Create DB Parameter Group」というポップアップウィンドウが出てきますので、対象のMySQLバージョン及びパラメータグループ名を入力します。ここでは「newparam」という名称で作成します。

2-2 パラメータグループを作成する2

右上ペインに、作成したパラメータグループが存在することを確認し、クリックします。すると、右下ペインにパラメータ一覧が表示されます。その後、「Viewing:」に「init_connect」と入力し、表示させるパラメータをフィルタします。

「init_connect」行を選択した後、[Edit Parameters」ボタンを押します。

2-3 パラメータグループを作成する3

「init_connect」パラメータを編集するためのウィンドウがポップアップしますので、設定するパラメータを入力し「Save Changes」を押します。ここでは「CALL mysql.adjust_time_zone」と入力します。作成したストアドプロシージャ名に併せて変更してください。

2-4 パラメータグループを作成する4

「init_connect」パラメータのValueに「CALL mysql.adjust_time_zone」と表示されていることを確認します。

2-5パラメータグループを作成する5

以上で、パラメータグループの作成は終了です。

RDSインスタンスへ、パラメータグループを適用する

引き続きAWS Console上の操作となります。左ペインから「DB Parameter Groups」を選択します。右上ペインの上方の「Create DB Parameter Group」ボタンを押します。変更対象のRDSインスタンスを選択し、[DB Parameter Group:」の現在値を確認します。変更していなければ、恐らくデフォルトの「default.mysql5.5 (in-sync)」となっている筈です。(MySQL 5.5のインスタンスの場合)

3-1 RDSインスタンスへ、パラメータグループを適用する1

右上ペインから「Instance Actions」プルダウンメニューを開き、「Modify」を選択します。

3-2 RDSインスタンスへ、パラメータグループを適用する2

RDSインスタンスを編集するためのポップアップウィンドウが開きます。「Parameter Group: 」のプルダウンメニューから、先程作成したパラメータグループ名を選択します。ここでは「newparam」を選択します。併せて「Apply Immediately:」にチェックを入れ、直ぐに設定が反映されるようにします。入力が完了したら「Continue」ボタンを押します。

3-3 RDSインスタンスへ、パラメータグループを適用する3

確認画面が表示されますので、「Parameter Group: 」が先程変更した「newparam」になっていることを確認します。問題が無ければ「Modify DB Instance」ボタンを押し、変更を反映させます。

3-4 RDSインスタンスへ、パラメータグループを適用する4

RDSインスタンスの一覧画面に戻りますので、変更したインスタンスの「DB Parameter Group:」が「newparam (applying)」になっていることを確認します。適用まで時間がかかるので暫く待ちます。

3-5 RDSインスタンスへ、パラメータグループを適用する5

暫く待ってリロードした後に「DB Parameter Group:」が「newparam (pending-reboot)」になったことを確認します。

3-6 RDSインスタンスへ、パラメータグループを適用する6

RDSインスタンスを再起動する

設定したパラメータグループを反映させるためには、RDSインスタンスの再起動が必要になります。

右上ペインから対象のRDSインスタンスを右クリックし、「Reboot」を選択します。

4-1 RDSインスタンスを再起動する1

確認のポップアップウィンドウが表示されるので、対象インスタンスが間違っていないことを確認し「Yes, reboot」を押します。

4-2 RDSインスタンスを再起動する2

対象のRDSインスタンスの「Status」が「rebooting」になったことを確認します。再起動が完了するまでには時間がかかりますので、暫く待ちます。

4-3 RDSインスタンスを再起動する3

暫く待ってリロードした後に、「Status」が「rebooting」に、「DB Parameter Group:」が「newparam (in-sync)」になったことを確認します。

4-4 RDSインスタンスを再起動する4

以上で、RDSインスタンスのパラメータグループが更新されました。

mysqlコマンドで接続して動作確認を行う

最後に、対象RDSインスタンスに接続し、Timezoneが自動的に変更されることを確認します。

適切なEC2インスタンスにログインし、mysqlコマンドで対象のRDSインスタンスに接続します。

#  mysql -h rdstest1.cchpjrugqqdj.ap-northeast-1.rds.amazonaws.com -u rdstest1 -p

接続後、now()関数の実行結果を確認します。

SELECT NOW();

mysqlコマンドを終了させ、EC2インスタンス上で「date」コマンドを実行します。now()関数の結果と「date」コマンドの実行結果を比較し、RDSに意図したTimezoneが設定されたことを確認します。

# date

 

5-1 mysqlコマンドで接続して動作確認を行う

なお、別のMySQLユーザーで 接続する場合は、対象ストアドプロシージャを実行できるよう、権限を付与することを忘れないようにします。典型的には、以下のようなGRANT文を実行します。環境やセキュリティ要件に応じて、必要な権限を付与してください。

 GRANT EXECUTE ON PROCEDURE `mysql`.`adjust_time_zone` TO ‘newuser’@’%’;

 

以上で、RDSのタイムゾーン変更作業は完了です。必要に応じ、スナップショットの作成や再度の再起動を実行し、不具合が起こらないかどうかを確認します。