スマートスタイル TECH BLOG

データベース&クラウド技術情報

MySQL5.7のバイナリログをMySQL5.5に適用する方法

はじめに

Oracleの資料(※)の通り、MySQL5.5は Extended Support が2018年12月で終了し、完全にサポートが切れた状態です。そのため、現在も同バージョンを使用している方は、安定稼働版である MySQL5.7 もしくは MySQL8.0 へのアップグレードが推奨されます。

こちらのサイトで公開されている『ライフタイム・サポート: Oracle Technology Products 』に記載されています

しかし、いざバージョンアップを成功させたとしても、MySQL5.7に移行したものの様々な事情でMySQL5.5に戻さなくてはいけないケースも発生するかと思います。そうしたケースで必要となるのは「切り戻し」です。

MySQL5.7 → MySQL5.5 への切り戻しには、バイナリログを使用するのが一般的かと思います。ですが、この時いくつかの課題に直面します。本記事では、その課題と対策について説明したいと思います。

バイナリログの互換性

MySQL5.6から、ROW形式(binlog_format=ROW)のバイナリログイベントのフォーマットが version.2 になりました。そのため、MySQL5.7 で出力されたバイナリログを、MySQL5.5に直接読み込ませることができません。

log-bin-use-v1-row-events変数(デフォルト:OFF)をONにすれば、MySQL5.5以前のフォーマット(version.1)に変更することができるため、切り戻しを前提とする場合はMySQL5.7では同変数を設定すると良いでしょう。なお、log-bin-use-v1-row-events変数は動的変更が不可能なため、ON/OFFの切り替えにはmysqldの再起動が伴います。

また、バイナリログに関しては以下のパラメータも変更する必要があります。

  • slave_sql_verify_checksum
  • binlog_checksum
  • binlog_row_image

まとめると、MySQL5.7側で以下のパラメータ設定が必要です。

なお、上記の問題は binlog_format = STATEMENTであれば回避ができます。しかし、以下のマニュアルの記述の通りSTATEMENT形式のレプリケーションにはいくつか注意点があるため、事前のチェック・検証は必要となります。

5.2.4.2 バイナリログ形式の設定

レプリケーション用にステートメントベースのロギングを使用している場合、ステートメントの設計方法が、データ変更が非決定的である (つまりクエリーオプティマイザの意向に委ねられるようになっている) ときに、マスターとスレーブのデータが異なることがあります。

バイナリログ適用テスト

上記の設定を有効にした状態で本当にバイナリログが適用できるのか、実際に試してみます。
今回は、dbdeployerを使って検証環境を用意しました。

MySQLのバージョンは、それぞれ 5.5.62 / 5.6.44 / 5.7.27 を使用しています。

5.6.44 と 5.7.27 では log_slave_updatesを有効にしておきましょう。

[1] 3サーバでレプリケーションを設定

まずは、5.5.62 → 5.6.44 → 5.7.27 の順でレプリケーションを設定します。

なお、公式では「1つのトポロジー内に3つ以上のバージョンが混在するレプリケーション」はサポートの対象外となりますので、想定外の挙動が発生する可能性がある点に注意してください。

17.4.2 MySQL バージョン間のレプリケーション互換性

2 つを超える MySQL Server バージョンを使用することは、マスターまたはスレーブ MySQL サーバーの数にかかわらず、複数のマスターを使用するレプリケーションセットアップでサポートされません。

[3] 5.5.62 でテストデータを作成

5.5.62 でテストデータを作成し、5.7.27にレプリケーションさせます。

[4] 5.7.27 でレプリケーション停止

5.7.27でレプリケーションを停止します。実際の移行作業(バージョンアップ)であれば、これ以降クライアントからの更新は 5.7.27 に対して発行される形になります。
また、バイナリログの切り替わりを分かりやすくするため、あわせてログのローテートも実行すると良いでしょう。

[5] 5.7.27 でテストデータを作成

バージョンアップ後を想定して、いくつか 5.7.27 上でテストデータを作成します。
もちろん、この更新内容は旧環境(5.5.62)には伝播しません。

[6] バイナリログの適用を試してみる

この状態で 5.7.27 のバイナリログを 5.5.62 に適用しようとすると、以下のsyntaxエラーが発生します。

※ バイナリログの適用の仕方は公式マニュアルを参考にしてください
※ MySQL5.7のバイナリログにはGTID情報が含まれており、MySQL5.5には同機能が実装されていないため–skip-gtidsオプションを指定する必要があります
※ 5.5環境では binlog_format = ROW or MIXED である必要があります

[7] 必要な設定を有効にしてリトライ

5.7.27 の my.cnf に必要な設定を追加して、バイナリログの適用を再度試してみます。

別のテストデータを追加して、バイナリログを適用してみます。

テーブル内を確認すると、バイナリログ内の更新クエリが正しく反映されていることが確認できます。
これを使えば、MySQL5.7 から MySQL5.5 への切り戻しも可能になります。

切り戻しできないケース

しかし、上記の方法でもMySQL5.7のバイナリログをMySQL5.5に適用できないケースも存在します。
それは、DATETIME型のカラムに対する更新クエリが含まれていた場合です。

これは、MySQL5.6から datetime 型の内部構造が変更されたことに起因します。

2.11.1.3 MySQL 5.5 から 5.6 へのアップグレード

互換性のない変更: TIME、DATETIME、および TIMESTAMP カラムについては、MySQL 5.6.4 より前に作成されたテーブルに必要なストレージは、5.6.4 以降で作成されたテーブルに必要なストレージとは異なります。これは、5.6.4 で、これらの時間型が少数部を持つことを許可するように変更されたためです。

実際に試してみると、5.7.27 におけるDATETIME型へのROW形式イベントを 5.5.62 に適用しようとすると以下のエラーが発生します。

このエラーを回避するには、5.7.27で binlog_format=STATEMENT を有効にするしかありませんが、別の切り戻し手順を使用することで回避することができます。

切り戻し手順(別解)

binlog_rows_query_log_events変数を有効にすると、binlog_format=ROWであってもバイナリログ内に実際の更新クエリがコメントとして記録されるようになります。

これを利用して、MySQL5.7のバイナリログから更新クエリのみを抽出し、5.5向けのSQLファイルを作成することができます。

2つのオプション(-vvv と –base64-output=DECODE-ROWS)をつけることで、mysqlbinlogコマンドの出力結果は以下のようになります。★★★印をつけた箇所が、binlog_rows_query_log_events変数によって出力された実際の更新クエリです。

※ “### “で始まるINSERT文は -v オプションによってROW形式のイベントを解釈したものであり、カラム名が @1, @2, … で置換されているため、SQLとしてそのまま実行することができません

この『57_binlog_datetime_arrange.sql 』を実際に実行できる形に編集していきます。

編集した『57_binlog_datetime_arrange.sql』を 5.5.62 にインポートします。

まとめ

MySQLに限らず、稼働中のシステムでバージョンアップを実施する場合は常に切り戻しを考慮する必要があります。今回の記事で取り上げたように、MySQLではメジャーバージョンが上がるにつれ機能が増えるため、複数のメジャーバージョンを跨いでの切り戻しが難しくなることがあります。

そういった未来の苦労を少しでも避けるため、バージョンアップ作業はこまめに実施することをオススメします。

MySQL
Return Top