RDS for MySQLやAuroraでは、2015年後半から2016年前半にかけて、相次いでタイムゾーンの設定ができるようになりました。
UTCではなく日本時間(Asia/Tokyo)を選ぶ場合、AWS内で閉じた環境で使う分には良いのですが、オンプレミス環境などとの間でレプリケーションを行う際に、タイムゾーンのずれが問題になることがあります。
MySQLのタイムゾーン
こちらに説明があります。
10.6 MySQL Server でのタイムゾーンのサポート(MySQL 5.6 リファレンスマニュアル)
これによると、MySQLのタイムゾーン(サーバー側設定)には2種類あります。
- システムタイムゾーン
- サーバーの現在のタイムゾーン
Linux環境において、前者は、
- mysqld_safeの起動オプションで「--timezone」を指定するか、my.cnfの[mysqld_safe]セクションで「timezone」を指定していれば、その値(例:「UTC」「JST」)
- 特に指定をしなければ、OSのTZ環境変数の値
が、後者は、
- mysqldの起動オプションで「--default-time-zone」を指定するか、my.cnfの[mysqld]セクションで「default-time-zone」を指定していれば、その値(例:「Asia/Tokyo」「+09:00」)
- 特に指定をしなければ、「SYSTEM」(=システムタイムゾーンと同じ、という意味)
が入ります。
※ほかに、mysqlクライアントの接続毎のタイムゾーンも指定できます。
オンプレミス環境で、OSを普通に日本のタイムゾーンで設定し、my.cnfや起動オプションに何も指定しない形でMySQLを設定した場合、通常は以下のようになります。
mysql> show variables like '%time_zone%';
+------------------+--------+
| Variable_name | Value |
+------------------+--------+
| system_time_zone | JST |
| time_zone | SYSTEM |
+------------------+--------+
2 rows in set (0.00 sec)
一方、AWSでRDS for MySQLやAuroraインスタンスを起動し、パラメータグループでtime_zoneを「Asia/Tokyo」に設定しインスタンスに適用した場合は、以下のようになります。
mysql> show variables like '%time_zone%';
+------------------+------------+
| Variable_name | Value |
+------------------+------------+
| system_time_zone | UTC |
| time_zone | Asia/Tokyo |
+------------------+------------+
2 rows in set (0.00 sec)
問題が発生するケース
このように、
- オンプレミス側がマスターになる
- システムタイムゾーンがマスター/スレーブ間で異なる
- オンプレミス側のサーバーのタイムゾーンが「SYSTEM」(無指定=システムタイムゾーンと同じ/バイナリログに「SET @@session.time_zone='SYSTEM'/!/;」が記録される)
かつ、
- マスター側サーバーのバイナリログを「MIXED」または(通常は指定しないですが)「STATEMENT」にしている
場合は、
- DATE/TIME/DATETIME型カラムに、NOW()関数などによってセットした値(時刻)
がずれます(「JST」と「UTC」の組み合わせであれば9時間)。
※オンプレミス側のOS時間を(RTCの設定ミスなどで)「UTCを指定しているのに時刻をJST相当で設定・運用している場合」は、TIMESTAMP型カラムの値のほうがずれます。
このようなレプリケーションは、平常運用時にはあまり行わないと思いますが、オンプレミス⇒AWSの移行準備で行うことがあると思います(移行にDMSを使わないケース)。
問題の回避
システムタイムゾーンを合わせられれば良いのですが、残念ながらAWS側で設定できるのはサーバーの(現在の)タイムゾーン(time_zone)のほうだけですので、オンプレミス側の同オプションパラメータをmy.cnfに記述し、mysqldを再起動することで対応します。
[mysqld]
default-time-zone='+09:00'
※以下のように(mysqlスキーマ内の)タイムゾーンテーブルをロードしている場合は、RDS for MySQL/Auroraと同様、「Asia/Tokyo」形式での指定も可能です。
4.4.6 mysql_tzinfo_to_sql — タイムゾーンテーブルのロード(MySQL 5.6 リファレンスマニュアル)
なお、RDS for MySQL/Aurora側をマスターにする場合、バイナリログには「Asia/Tokyo」形式でタイムゾーンが記録されることもありますので、トラブルを避けるために、オンプレミス側でタイムゾーンテーブルをロードしておいたほうが良いでしょう。
注意点
マスター/スレーブの2階層ではなく、マスター/中継(スレーブ&マスター)/スレーブの3階層でレプリケーションを行う場合、
- スレーブ側では、「そのSQLの発行元サーバー」のタイムゾーンと自サーバーのタイムゾーンを比較して値を決定する
という動作をするため、中継サーバーだけ設定を変更・反映(再起動)しても意味がありません。
必ず、マスターを含め、全てのサーバーのタイムゾーンの関係性が合うように設定を変更・反映(再起動)します。
【おまけ】
Amazon Aurora関連投稿記事へのリンクを集めました。