MySQL
RDS
Aurora

オンプレMySQL~RDS for MySQL/Aurora間のレプリケーションにおけるタイムゾーン設定

More than 1 year has passed since last update.

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(オンプレ・日本時間)
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」に設定しインスタンスに適用した場合は、以下のようになります。

RDS(MySQL)/Aurora(日本時間)
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を再起動することで対応します。

my.cnf(部分)
[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関連投稿記事へのリンクを集めました。