はじめに
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側で以下のパラメータ設定が必要です。
|
1 2 3 4 5 6 7 |
log_bin log_slave_updates binlog_format=ROW log_bin_use_v1_row_events=ON slave_sql_verify_checksum = OFF binlog_checksum = NONE binlog_row_image=FULL |
なお、上記の問題は binlog_format = STATEMENTであれば回避ができます。しかし、以下のマニュアルの記述の通りSTATEMENT形式のレプリケーションにはいくつか注意点があるため、事前のチェック・検証は必要となります。
レプリケーション用にステートメントベースのロギングを使用している場合、ステートメントの設計方法が、データ変更が非決定的である (つまりクエリーオプティマイザの意向に委ねられるようになっている) ときに、マスターとスレーブのデータが異なることがあります。
バイナリログ適用テスト
上記の設定を有効にした状態で本当にバイナリログが適用できるのか、実際に試してみます。
今回は、dbdeployerを使って検証環境を用意しました。
MySQLのバージョンは、それぞれ 5.5.62 / 5.6.44 / 5.7.27 を使用しています。
|
1 2 3 |
$ dbdeployer deploy single 5.7.27 --master $ dbdeployer deploy single 5.6.44 --master $ dbdeployer deploy single 5.5.62 --master |
5.6.44 と 5.7.27 では log_slave_updatesを有効にしておきましょう。
|
1 2 3 4 5 |
$ vi msb_5_6_44/my.sandbox.cnf ### "log_slave_updates"を追加 $ msb_5_6_44/restart $ vi msb_5_7_27/my.sandbox.cnf ### "log_slave_updates"を追加 $ msb_5_7_27/restart |
[1] 3サーバでレプリケーションを設定
まずは、5.5.62 → 5.6.44 → 5.7.27 の順でレプリケーションを設定します。
|
1 2 3 4 5 6 7 8 9 10 |
$ msb_5_5_62/use -u root mysql [localhost:5562] {root} ((none)) > CREATE USER repl_user@localhost IDENTIFIED BY "replpass"; mysql [localhost:5562] {root} ((none)) > GRANT REPLICATION SLAVE ON *.* TO repl_user@localhost; mysql [localhost:5562] {root} ((none)) > SHOW MASTER STATUS\G *************************** 1. row *************************** File: mysql-bin.000001 Position: 2419 Binlog_Do_DB: Binlog_Ignore_DB: |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
$ msb_5_6_44/use -u root mysql [localhost:5645] {root} ((none)) > CREATE USER repl_user@localhost IDENTIFIED BY "replpass"; mysql [localhost:5645] {root} ((none)) > GRANT REPLICATION SLAVE ON *.* TO repl_user@localhost; mysql [localhost:5645] {root} ((none)) > SHOW MASTER STATUS\G *************************** 1. row *************************** File: mysql-bin.000002 Position: 433 Binlog_Do_DB: Binlog_Ignore_DB: Executed_Gtid_Set: mysql [localhost:5645] {root} ((none)) > CHANGE MASTER TO MASTER_HOST="127.0.0.1", MASTER_PORT=5562, MASTER_LOG_FILE="mysql-bin.000001", MASTER_LOG_POS=2419; mysql [localhost:5645] {root} ((none)) > START SLAVE USER="repl_user" PASSWORD="replpass"; |
|
1 2 3 4 |
$ msb_5_7_27/use -u root mysql [localhost:5727] {root} ((none)) > CHANGE MASTER TO MASTER_HOST="127.0.0.1", MASTER_PORT=5645, MASTER_LOG_FILE="mysql-bin.000002", MASTER_LOG_POS=433; mysql [localhost:5727] {root} ((none)) > START SLAVE USER="repl_user" PASSWORD="replpass"; |
なお、公式では「1つのトポロジー内に3つ以上のバージョンが混在するレプリケーション」はサポートの対象外となりますので、想定外の挙動が発生する可能性がある点に注意してください。
17.4.2 MySQL バージョン間のレプリケーション互換性
2 つを超える MySQL Server バージョンを使用することは、マスターまたはスレーブ MySQL サーバーの数にかかわらず、複数のマスターを使用するレプリケーションセットアップでサポートされません。
[3] 5.5.62 でテストデータを作成
5.5.62 でテストデータを作成し、5.7.27にレプリケーションさせます。
|
1 2 3 4 5 6 7 8 9 10 11 |
$ msb_5_5_62/use -u root -e "CREATE DATABASE d1; CREATE TABLE d1.t1 (id int)" $ msb_5_5_62/use -u root -e "INSERT INTO d1.t1 VALUES (1),(2),(3)" $ msb_5_7_27/use -u root -e "SELECT * FROM d1.t1" +------+ | id | +------+ | 1 | | 2 | | 3 | +------+ |
[4] 5.7.27 でレプリケーション停止
5.7.27でレプリケーションを停止します。実際の移行作業（バージョンアップ）であれば、これ以降クライアントからの更新は 5.7.27 に対して発行される形になります。
また、バイナリログの切り替わりを分かりやすくするため、あわせてログのローテートも実行すると良いでしょう。
|
1 2 3 4 5 6 7 8 9 10 |
$ msb_5_7_27/use -u root -e "STOP SLAVE" $ msb_5_7_27/use -u root -e "FLUSH BINARY LOGS" $ msb_5_7_27/use -u root -e "SHOW MASTER LOGS" +------------------+-----------+ | Log_name | File_size | +------------------+-----------+ | mysql-bin.000001 | 4112 | | mysql-bin.000002 | 771 | | mysql-bin.000003 | 154 | |
[5] 5.7.27 でテストデータを作成
バージョンアップ後を想定して、いくつか 5.7.27 上でテストデータを作成します。
もちろん、この更新内容は旧環境（5.5.62）には伝播しません。
|
1 |
$ msb_5_7_27/use -u root -e "INSERT INTO d1.t1 VALUES (10),(20),(30)" |
[6] バイナリログの適用を試してみる
この状態で 5.7.27 のバイナリログを 5.5.62 に適用しようとすると、以下のsyntaxエラーが発生します。
※ バイナリログの適用の仕方は公式マニュアルを参考にしてください
※ MySQL5.7のバイナリログにはGTID情報が含まれており、MySQL5.5には同機能が実装されていないため–skip-gtidsオプションを指定する必要があります
※ 5.5環境では binlog_format = ROW or MIXED である必要があります
|
1 2 3 4 5 |
$ ~/opt/mysql/5.7.27/bin/mysqlbinlog --skip-gtids msb_5_7_27/data/mysql-bin.000003 > /tmp/57_binlog_default.sql $ msb_5_5_62/use -u root -e "SET GLOBAL binlog_format=MIXED" $ msb_5_5_62/use -u root < /tmp/57_binlog_default.sql ERROR 1149 (42000) at line 33: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use |
[7] 必要な設定を有効にしてリトライ
5.7.27 の my.cnf に必要な設定を追加して、バイナリログの適用を再度試してみます。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
$ vi msb_5_7_27/my.sandbox.cnf ### 以下を追加 log_bin_use_v1_row_events=ON slave_sql_verify_checksum = OFF binlog_checksum = NONE binlog_row_image=FULL $ msb_5_7_27/restart $ msb_5_7_27/use -u root -e "STOP SLAVE" $ msb_5_7_27/use -u root -e "SHOW MASTER LOGS" +------------------+-----------+ | Log_name | File_size | +------------------+-----------+ | mysql-bin.000001 | 4112 | | mysql-bin.000002 | 771 | | mysql-bin.000003 | 434 | | mysql-bin.000004 | 150 | +------------------+-----------+ |
別のテストデータを追加して、バイナリログを適用してみます。
|
1 2 3 4 5 |
$ msb_5_7_27/use -u root -e "INSERT INTO d1.t1 VALUES (100),(200),(300)" $ ~/opt/mysql/5.7.27/bin/mysqlbinlog --skip-gtids msb_5_7_27/data/mysql-bin.000004 > /tmp/57_binlog_arrange.sql $ msb_5_5_62/use -u root < /tmp/57_binlog_arrange.sql |
テーブル内を確認すると、バイナリログ内の更新クエリが正しく反映されていることが確認できます。
これを使えば、MySQL5.7 から MySQL5.5 への切り戻しも可能になります。
|
1 2 3 4 5 6 7 8 9 10 11 |
$ msb_5_5_62/use -u root -e "SELECT * FROM d1.t1" +------+ | id | +------+ | 1 | | 2 | | 3 | | 100 | | 200 | | 300 | +------+ |
切り戻しできないケース
しかし、上記の方法でも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 に適用しようとすると以下のエラーが発生します。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
$ msb_5_7_27/use -u root -e "FLUSH BINARY LOGS" $ msb_5_7_27/use -u root -e "CREATE TABLE d1.t2 (col1 DATETIME)" $ msb_5_7_27/use -u root -e "INSERT INTO d1.t2 VALUES ('2019-04-10 09:00:00')" $ ~/opt/mysql/5.7.27/bin/mysqlbinlog --skip-gtids msb_5_7_27/data/mysql-bin.000005 > /tmp/57_binlog_datetime.sql $ msb_5_5_62/use -u root < /tmp/57_binlog_datetime.sql ERROR 1105 (HY000) at line 40: Unknown error $ msb_5_5_62/use -u root < /tmp/57_binlog_datetime.sql ### CREATE TABLEは成功するため重複エラー ERROR 1050 (42S01) at line 26: Table 't2' already exists $ tail msb_5_5_62/data/msandbox.err ... 190722 16:41:18 [ERROR] Slave SQL: Column 0 of table 'd1.t2' cannot be converted from type '<unknown type>' to type 'datetime', Error_code: 1677 |
このエラーを回避するには、5.7.27で binlog_format=STATEMENT を有効にするしかありませんが、別の切り戻し手順を使用することで回避することができます。
切り戻し手順（別解）
binlog_rows_query_log_events変数を有効にすると、binlog_format=ROWであってもバイナリログ内に実際の更新クエリがコメントとして記録されるようになります。
これを利用して、MySQL5.7のバイナリログから更新クエリのみを抽出し、5.5向けのSQLファイルを作成することができます。
|
1 2 3 4 5 6 7 8 9 10 |
$ msb_5_7_27/use -u root -e "SET GLOBAL binlog_rows_query_log_events=ON" $ msb_5_7_27/use -u root -e "FLUSH BINARY LOGS" $ msb_5_7_27/use -u root -e "CREATE TABLE d1.t3 (col1 DATETIME)" $ msb_5_7_27/use -u root -e "INSERT INTO d1.t3 VALUES ('2019-07-20 12:00:00')" $ msb_5_7_27/use -u root -e "INSERT INTO d1.t3 VALUES ('2019-08-20 15:00:00')" $ msb_5_7_27/use -u root -e "INSERT INTO d1.t3 VALUES ('2019-09-25 20:00:00')" $ ~/opt/mysql/5.7.27/bin/mysqlbinlog --skip-gtids -vvv --base64-output=DECODE-ROWS msb_5_7_27/data/mysql-bin.000 006 > /tmp/57_binlog_datetime_arrange.sql |
2つのオプション（-vvv と –base64-output=DECODE-ROWS）をつけることで、mysqlbinlogコマンドの出力結果は以下のようになります。★★★印をつけた箇所が、binlog_rows_query_log_events変数によって出力された実際の更新クエリです。
※ “### “で始まるINSERT文は -v オプションによってROW形式のイベントを解釈したものであり、カラム名が @1, @2, … で置換されているため、SQLとしてそのまま実行することができません
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
$ less /tmp/57_binlog_datetime_arrange.sql # at 370 #190722 17:12:32 server id 5727 end_log_pos 434 Query thread_id=14 exec_time=0 error_code=0 SET TIMESTAMP=1563783152/*!*/; BEGIN /*!*/; # at 434 #190722 17:12:32 server id 5727 end_log_pos 502 Rows_query # INSERT INTO d1.t3 VALUES ('2019-07-20 12:00:00') ★★★ # at 502 #190722 17:12:32 server id 5727 end_log_pos 542 Table_map: `d1`.`t3` mapped to number 110 # at 542 #190722 17:12:32 server id 5727 end_log_pos 577 Write_rows: table id 110 flags: STMT_END_F ### INSERT INTO `d1`.`t3` ### SET ### @1='2019-07-20 12:00:00' /* DATETIME(0) meta=0 nullable=1 is_null=0 */ # at 577 |
この『57_binlog_datetime_arrange.sql 』を実際に実行できる形に編集していきます。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 |
1) "### " で始まるコメント行を削除する $ sed -i '/^### /d' /tmp/57_binlog_datetime_arrange.sql 2) INSERT文のコメントアウトを外し、末尾にクエリを終わりを示す ; を付与する $ sed -i 's/^# INSERT/INSERT/g' /tmp/57_binlog_datetime_arrange.sql $ sed -i '/^INSERT/s/$/;/g' /tmp/57_binlog_datetime_arrange.sql 3) UPDATE文のコメントアウトを外し、末尾にクエリを終わりを示す ; を付与する $ sed -i 's/^# UPDATE/UPDATE/g' /tmp/57_binlog_datetime_arrange.sql $ sed -i '/^UPDATE/s/$/;/g' /tmp/57_binlog_datetime_arrange.sql 4) DELETE文のコメントアウトを外し、末尾にクエリを終わりを示す ; を付与する $ sed -i 's/^# DELETE/DELETE/g' /tmp/57_binlog_datetime_arrange.sql $ sed -i '/^DELETE/s/$/;/g' /tmp/57_binlog_datetime_arrange.sql 5) BEGIN / COMMIT / ROLLBACK / SET の末尾に ; を付与する $ sed -i '/^BEGIN/s/$/;/g' /tmp/57_binlog_datetime_arrange.sql $ sed -i '/^COMMIT/s/$/;/g' /tmp/57_binlog_datetime_arrange.sql $ sed -i '/^ROLLBACK/s/$/;/g' /tmp/57_binlog_datetime_arrange.sql $ sed -i '/^SET/s/$/;/g' /tmp/57_binlog_datetime_arrange.sql |
編集した『57_binlog_datetime_arrange.sql』を 5.5.62 にインポートします。
|
1 2 3 4 5 6 7 8 9 |
$ msb_5_5_62/use -u root < /tmp/57_binlog_datetime_arrange.sql $ msb_5_5_62/use -u root -e "SELECT * FROM d1.t3" +---------------------+ | col1 | +---------------------+ | 2019-07-20 12:00:00 | | 2019-08-20 15:00:00 | | 2019-09-25 20:00:00 | +---------------------+ |
まとめ
MySQLに限らず、稼働中のシステムでバージョンアップを実施する場合は常に切り戻しを考慮する必要があります。今回の記事で取り上げたように、MySQLではメジャーバージョンが上がるにつれ機能が増えるため、複数のメジャーバージョンを跨いでの切り戻しが難しくなることがあります。
そういった未来の苦労を少しでも避けるため、バージョンアップ作業はこまめに実施することをオススメします。
