Help us understand the problem. What is going on with this article?

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

More than 3 years have 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関連投稿記事へのリンクを集めました。

hmatsu47
名古屋で士業向けWebサービスのインフラ構築管理、たまにアプリケーション開発をやっています。 業務利用しているもの、個人研究など、気長にのんびり投稿していきます。ニッチ狙いが多めです。 IPA RISS(001158)・NW・DB/日商・大商2級コレクター?(簿記・ビジネス法務・ビジネス会計)。
https://hmatsu47.hatenablog.com/
infra-workshop
インフラ技術を勉強したい人たちのためのオンライン勉強会です
https://wp.infra-workshop.tech/
Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away