MySQL5.7化移行作業で発生したトラブル紹介

はじめに

皆様はじめまして。IT基盤部の竹内と申します。
主に担当している業務内容は、ゲームタイトルやプラットフォーム、オートモーティブ事業等のインフラ運用等となります。
今回のブログでは先日実施したMySQLの移行作業において発生したトラブルを紹介したいと思います。
同じようなトラブルが発生した方の参考になれば幸いです。

なぜMySQLの移行作業を実施したか

時代と共にガラパゴス化しつつある運用環境を標準技術を使って一新したい
MySQL5.6 から利用できるようになった GTID を利用したい (※ GTIDとは
現在DeNAでは新しいインフラの共通ツールセットを開発しておりその一環で MySQL5.7 を利用したい

というような上記事情があり MySQL5.1 環境を MySQL5.7 に移行させる必要がありました。
ですが MySQL5.1 から MySQL5.7 への移行は大きくバージョンアップするため仕様が大分異なりトラブルが多発しました。
そのため当初は下記のような構成で移行する予定でしたが、結果として最終的に想定と違う構成となりました。

構成

移行前の構成

MySQL5.1(master) -> MySQL5.1(slave)

移行後の構成(当初の想定)

MySQL5.7(master) -> MySQL5.7(slave)
                 -> MySQL5.1(slave)

移行後の構成(最終版)

MySQL5.7(master) -> MySQL5.7(slave)
                 -> MySQL5.6(slave) -> MySQL5.1(slave)

そこでなぜ上記のような構成になったか実際に発生したトラブルの一部と実施した内容をいくつかピックアップして紹介します。

発生したトラブル

MySQL5.7 と MySQL5.1 でレプリケーション構成が組めない

MySQL5.1 から MySQL5.7 に移行した後クエリの形式も変わるため、何かあったときに切り戻せるよう MySQL5.1 は残す必要がありました。
ですがMySQL5.7(master) -> MySQL5.1(slave)でレプリケーション構成を組んだ場合下記のようなレプリケーションエラーが発生するという問題があります。

Last_IO_Errno: 1236
Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'Slave can not handle replication events with the checksum that master is configured to log; the first event 'bin.000001' at '

上記エラーの対策として下記設定を追加しstart slaveしたところI/Oスレッド及びSQLスレッドは正常に起動することは確認できました。

binlog-checksum=none

ですが master にて更新文を発行すると今度は下記エラーが発生してしまいました。

Last_SQL_Errno: 1594
Last_SQL_Error: Relay log read failure: Could not parse relay log event entry. The possible reasons are: the master's binary log is corrupted (you can check this by running 'mysqlbinlog' on the binary log), the slave's relay log is corrupted (you can check this by running 'mysqlbinlog' on the relay log), a network problem, or a bug in the master's or slave's MySQL code. If you want to check the master's binary log or slave's relay log, you will be able to know their names by issuing 'SHOW SLAVE STATUS' on this slave.

そこでMySQL5.7 と MySQL5.6及びMySQL5.6 と MySQL5.1間ではレプリケーション構成を組んでもエラーが発生しないことが判明したため、下記構成にすることで対応しました。

MySQL5.7(master) -> MySQL5.6(slave) -> MySQL5.1(slave)

多段構成のレプリケーションになってしまいますが、この場合レプリケーション構成が組めないという問題は解決できました。

レプリカセット内で、 binlog_format の混在はできない

master と slave の binlog_format に差異があると下記のようなレプリケーションエラーが発生します。

Last_SQL_Errno: 1666
Last_SQL_Error: Error executing row event: 'Cannot execute statement: impossible to write to binary log since statement is in row format and BINLOG_FORMAT = STATEMENT.'

上記の場合 ROW(master) と STATEMENT(slave) でレプリケーション構成を組んでしまったため発生してしまいました。

それぞれの MySQL Server は、サーバー独自のバイナリロギング形式のみを設定できます (binlog_format がグローバルスコープまたはセッションスコープのいずれで設定される場合にも当てはまります)。
これは、レプリケーションマスターのロギング形式を変更しても、スレーブがそのロギング形式を一致するように変更するわけではないことを意味しています。
(STATEMENT モードを使用中の場合、binlog_format システム変数はレプリケーションされず、MIXED または ROW ロギングモードを使用中の場合、レプリケーションされますが、スレーブによって無視されます。)
レプリケーションの進行中にマスター上のバイナリロギング形式を変更したり、スレーブ上でそれを変更しなかったりすると、予期しない結果を招いたり、レプリケーションの失敗を招くことがあります。

と公式のドキュメントに記載があるように master と slave で binlog_format を合わせて修正する必要があります。
my.cnfに追記して修正する場合は下記のように

binlog-format=statement
binlog-format=row
binlog-format=mixed

グローバルに形式を指定するには下記のように

mysql> SET GLOBAL binlog_format = 'STATEMENT';
mysql> SET GLOBAL binlog_format = 'ROW';
mysql> SET GLOBAL binlog_format = 'MIXED';

指定することで設定が可能となります。
binlog_format のデフォルト値が MySQL5.1,MySQL5.6,MySQL5.7で変わっているため、図らずも混在が生じる可能性があり注意が必要になります。

binlog_format=MIXED の MySQL5.6 と MySQL5.1 でレプリケーションを構成を組むと binlog が読み出せなくなる

MySQL5.6 からバージョン2バイナリロギングが使用されているため、MySQL5.1 では ROW or MIXEDの binlog を読み出すことができず下記のようなエラーが発生します。

Last_Errno: 1594
Last_Error: Relay log read failure: Could not parse relay log event entry. The possible reasons are: the master's binary log is corrupted (you can check this by running 'mysqlbinlog' on the binary log), the slave's relay log is corrupted (you can check this by running 'mysqlbinlog' on the relay log), a network problem, or a bug in the master's or slave's MySQL code. If you want to check the master's binary log or slave's relay log, you will be able to know their names by issuing 'SHOW SLAVE STATUS' on this slave.

この場合下記のパラメータでバージョン1バイナリログに変更すれば、MySQL5.1 でも読み出せるようになります。

log_bin_use_v1_row_events=1

MySQL5.7 と MySQL5.6 でレプリケーションが正常に行われない

MySQL5.7(master) -> MySQL5.6(slave)で発生した問題となります。
下記のような状態になっておりました。

show slave statusの結果 error 表示はない
I/Oスレッド及びSQLスレッドは正常に起動している
Master_Log_File や Exec_Master_Log_Posなど正常に更新されているように見える
テーブルの中身を見ると更新されていない

結果としては MySQL5.6 の bug で 5.6.16 や5.6.20で発生し5.6.43以降では発生しないことを確認しました。

最後に

MySQL5.1 から MySQL5.7などアップグレードを実施する場合、仕様の変更などでトラブルが発生することが多々あるかと思います。
上記には書ききれない細かいクエリの形式のエラーなども発生したりしています。今回は特に問題となった部分をピックアップさせていただきました。
皆様の作業の参考になりましたら幸いです。