角待ちは対空

おもむろガウェイン

time_zone設定の違うMySQLのレプリケーションについて

Tech

結論としてはできない。正確にはレプリケーションの設定自体はできるがデータが適切に複製されないので設定を変える必要がある。

これはMySQL5.6 -> Aurora(MySQL5.6互換)移行の際、レプリケーションを組んだが、時刻周りで上手くいかなかった問題と解決の記録。

そして、はてなエンジニア Advent Calendar 2018の2日目の記事です。

qiita.com

前提

件の移行の際のmaster以下のような設定だった。

mysql> show variables like '%time_zone%';
+------------------+--------+
| Variable_name    | Value  |
+------------------+--------+
| system_time_zone | JST    |
| time_zone        | SYSTEM |
+------------------+--------+
2 rows in set (0.00 sec)

またバイナリログ形式はMIXEDであった。

対して、Auroraのデフォルト設定は、system_time_zone = UTCでtime_zone = SYSTEMである。

mysql> show variables like ‘%time_zone%‘;
+------------------+------------+
| Variable_name    | Value      |
+------------------+------------+
| system_time_zone | UTC        |
| time_zone        | SYSTEM     |
+------------------+------------+
2 rows in set (0.00 sec)

2018/11/29現在、Auroraはtime_zoneの設定を変更することはできるがsystem_time_zoneをデフォルトから変更することは出来ない。

最初はAuroraのtime_zoneをAsia/Tokyoに変更するだけでokかと思ったが、それでは新規の(Auroraへの)書き込み分とmysqldumpをリストア分は上手く行くがレプリケーションで生成されるデータ(NOW()の返す値)がmasterとは異なることがわかった。

以下、必要になる基礎的な知識を解説した後、僕が行った解決を示す。

MySQLのタイムゾーン設定

MySQL(server)にはタイムゾーン設定が2つありsystem_time_zoneとtime_zoneがある。

mysql> show variables like '%time_zone%';
+------------------+--------+
| Variable_name    | Value  |
+------------------+--------+
| system_time_zone | JST    |
| time_zone        | SYSTEM |
+------------------+--------+
2 rows in set (0.00 sec)

SYSTEMはsystem_time_zoneを参照せよという意味でこの場合JSTとなる。SYSTEM以外にもAsia/Tokyoといった名前付きタイムゾーンや+09:00といった値が使える。サマータイムなどを考慮しないで良い日本では両者は同じ状態である。

system_time_zoneはサーバーの起動時に一度設定されオンラインの変更は不可。time_zoneはオンラインで変更可能となっている。

またtime_zone設定に限らないがMySQLの設定にはセッションとグローバルの2つのスコープがある。 細かい挙動はここでは解説しないが、大まかに言ってグローバルなものが使われるがクライアントから特別に設定するとグローバルとは違う値が設定できる。

TIMESTAMP型とDATETIME型

TIMESTAMP型は内部的には1970-01-01 00:00:00 UTCからの経過秒で値を保持している(unixtime)。つまりMySQLは挿入時に現在のタイムゾーンをUTCに変換してからunixtimeで保存し、参照時は逆のことをする。なので挿入時と参照時のtime_zoneが異なっていると時刻の値もことなることになる。というよりはtime_zoneによっていい感じに表示してくれる。

これに対してDATETIME型は単なる文字列がそのまま挿入され参照されるのでタイムゾーンという概念がない。time_zone設定の影響をうけることもない。

レプリケーションセーフ

レプリケーションセーフとはレプリケーション先で正しくデータが複製されるかどうかを表す概念である。たとえばMySQLには非決定性関数というものがあり、これらの関数はステートメントベースのバイナリログではレプリケーションセーフではない。レプリケーションセーフにしたければ行ベースのバイナリログ形式を選ぶ必要がある。

NOW()について考えてみると、ステートメントベースでもレプリケーションセーフである。仕組みとしては、NOW()が呼ばれるとバイナリログにはTIMESTAMP変数が記録される。NOW()はTIMESTAMP変数を参照し、それをDATETIMEリテラルに変換しNOW()の返す値として使う。したがってNOW()は現在の時刻を返す関数でありながらステートメントベースのバイナリログ形式でも基本的にはレプリケーションセーフとなる。

逆に言うとステートメントベースのバイナリログ形式でもレプリケーションセーフなため、バイナリログ形式がMIXEDの場合、行ベースのバイナリログ形式へはフォールバックされない。

基本的にはレプリケーションセーフと書いたのは条件があり、masterとslaveのtime_zoneが同じことである。

masterとslaveのtime_zoneが違う場合のレプリケーション

  • master
    • system_time_zone = JST
    • time_zone = SYSTEM
  • slave(Aurora)
    • system_time_zone = UTC
    • time_zone = Asia/Tokyo

のような場合を考える。

masterとしては

  • DATETIME型はJSTを想定したDATETIME文字列
  • TIMESTAMP型は挿入時にJSTとしてDATETIME文字列内->unixtimeに変換された内部表現 が記録されている。

レプリケーションの事を考えなければ、これでも正しく動く。つまり、mysqldumpをリストした分と新規にAuroraに書き込まれる分は整合性を保てる。

ではレプリケーションでの複製はどうか。これは最初に書いたようにうまくいかない。なにが起こっているかというと、masterのtime_zoneがSYSTEMであるため吐かれるバイナリログは以下のようなものになる。

SET @@session.time_zone='SYSTEM'/*!*/;
BEGIN
/*!*/;
# at 215
#181015  0:17:01 server id xxxx  end_log_pos xxxx CRC32 0x9e0534e1         Query   thread_id=25800848      exec_time=0     error_code=0
use `hoge_table`/*!*/;
SET TIMESTAMP=1539530221/*!*/;
replace hoge set id = xxxxxx,  entrytime = NOW()
/*!*/;
# at 711
#181015  0:17:01 server id xxxx  end_log_pos xxxx CRC32 0xdad0c08a         Xid = 5435778
COMMIT/*!*/;

@@session.time_zoneがSYSTEMであるため、NOW()のタイムゾーンはsystem_time_zone依存になりmaster/slaveのsystem_time_zoneが異なっていた場合DATETIME型のカラムはレプリケーションアンセーフとなる。ちなみにTIMESTAMP型の場合はうまくいく(master/slaveで同じデータが書き込まれる)。

解決策

1. slaveのsystem_time_zoneをJSTに変える

設定を揃えるのが王道だが、今回はslaveがAuroraだったためsystem_time_zoneを揃えるのは不可能である。

2. masterのtime_zoneをAsia/Tokyoにする

system_time_zone経由でUTCが参照されてしまうのが原因であるため、明示的にAsia/Tokyoにしてあげれば問題は起こらない。

3. バイナリログ形式を行ベースにする

おそらくこれでもうまくいくはず？原理的にはうまくいくと思っているが、試してはいない。詳しい人いたら補足ください。

どうしたか

結局2を選んだ。masterとなるMySQLでSET GLOBAL time_zone = '+9:00';し、再起動に備えてmy.confも該当箇所を書き換えた。(+09:00な理由は名前付きタイムゾーンのデータがロードされていなかったから)

アプリケーションの変更は特に行っていない(クライアントからtime_zoneを設定している場合などは必要)。

やってわかったこと

  • グローバル変数の書き換えしたあとは一度再接続しないとセッション変数は書き換わらない
    • わかってたつもりだけど普段やらないので切り替わらん...って焦った
  • time_zoneの設定はレプリケーションへは伝搬はしない
    • すると書いてあった気がしたけど(どこに？)しなかった

timez_zoneが書き換わった後に接続してきたクライアントが発行したクエリにはSET @@session.time_zone='+09:00'/*!*/;がつき、コネクションがそのままのクライアントのクエリにはSET @@session.time_zone='SYSTEM'/*!*/;がつく。 やがて全部SET @@session.time_zone='+09:00'/*!*/;になる。つまり、ファイルの先頭に一度だけSET @@session.time_zone='+09:00'/*!*/;と書かれたバイナリログが出来上がる。

ちなみに

レプリケーションを多段で組んでいる場合は大本となる(該当のクエリを直接受けている)サーバーのタイムゾーンを変えないと意味がない。 バイナリログに記録されているクエリは再生したものをそのまま記録するので一番大本のタイムゾーンを書き換えない限りSET @@session.time_zone='SYSTEM'/*!*/;はそのままになる。

終わり

MySQL -> Auroraのレプリケーションを組む際ハマるであろうポイントであるタイムゾーンの設定について自分で調べて解決したことをまとめました。最近はDBのタイムゾーンはUTCに揃えることが多いと思いますが、そうでないシステムもたくさんあると思うので同じ問題で困ってる人の参考になれば幸いです。また、なにか間違っていたり、不正確な部分があれば指摘を貰えると嬉しいです。

ちなみに現在はmasterの切り替え作業も終わり完全にAuroraに移行され、元気に動いています。

参考にしたもの