切り戻しを考慮したMySQL5.7から8.0へのアップグレード
技術本部 サービスリライアビリティグループ（SRG）の鬼海（@fat47）です。
#SRG（Service Reliability Group）は、主に弊社メディアサービスのインフラ周りを横断的にサポートしており、既存サービスの改善や新規立ち上げ、OSS貢献などを行っているグループです。
なにかの役に立てば幸いです。
概要MySQL8.0のバージョンについてどの8.0バージョンをつかうかINSTANT DDLとはMySQL8.0からの変更で気をつけるポイントパラメータ差分のチェックサイト特に気をつけたいパラメータ差分アップグレードの大まかな流れDBレプリケーション構成アップグレードの詳細手順1. [DB02,DB03] MySQLのyum repoの入れ替えとGPG keyインポート2. [DB02,DB03] アップグレードチェッカー利用のためにmysql-shellをインストール3. [DB02,DB03] アップグレートチェッカーの実行4. [DB02,DB03] アップグレードチェッカーの結果でErrorsが0件なのを確認5. [DB02,DB03] MySQLの停止6. [DB02,DB03] MySQLのアップグレード7. [DB02,DB03] my.cnfの編集8. [DB02,DB03] 起動＆チェック9. [DB02,DB03]default_collation_for_utf8mb4を設定する10. アプリケーションからの書き込みを停止してDB02をマスターDBにフェイルオーバーさせる11. [DB01] レプリケーションのマスター参照先をDB02に変更してレプリスタート最終構成終わりに
概要
MySQL5.7をMySQL8.0にアップグレードを行い、万が一の際はまた5.7に切り戻しを行える環境の構築手順を紹介しています。
MySQL8.0のバージョンについて
開発ポリシーが大きくかわり8.0のマイナーバージョンでも新機能が追加されています。
そのためマイナーバージョンアップグレードでも慎重に検証が必要となります。
どの8.0バージョンをつかうか
マイナー最新Verではなく8.0.28の利用の検討する
8.0.29からINSTANT DROP COLUMN機能が追加され、ALTER TABLEのデフォルト挙動が大きく変わりました。それに付随してその機能周辺でバグレポートが多数報告されています。
現在は8.0.29は公開停止となっていて、8.0.30以降の利用が公式では推奨されていますが、すべてのバグが修正されているわけではありません。
このINSTANT 機能の利用を避けるためには、すべてのDDLに常に をつける必要があるため回避は現実的ではありません。
また一度でもINSTANT ADD/DROPしたテーブルが存在する場合、Percona Xtrabackupでバックアップが取得できません。
その場合は対象のテーブルに対してOPTIME TABLEを実行するか、ALTER TABLE ALGORITHM=COPY;を実行してテーブルデータを更新させる必要があります。
INSTANT DDLとは
ALTER TABLEで絡む追加などを行う際にメタデータ更新のみで更新完了にする機能です。
非常に高速だができる操作の制限や制約事項があります。
- インデックスオプションの変更
- テーブル名の変更
- MODIFY COLUMN
- カラム追加（8.0.28までは最後列のみ可) 8.0.29からどこでも追加可能
- カラム削除(8.0.29から)
8.0.28までの利用例はこちら
8.0.29からはALGORITHMの指定がないとデフォルトでINSTANTがつくようになりました。
MySQL8.0からの変更で気をつけるポイント
パラメータ差分のチェックサイト
MySQL Parametersというサイトを活用しましょう
特に気をつけたいパラメータ差分
| パラメータ名 | 5.7 | 8.0 |
| default_authentication_plugin | mysql_native_password | caching_sha2_password |
| default charset | latin1 | utf8mb4 |
| default collation(照合順序) | utf8mb4_general_ci | utf8mb4_0900_ai_ci |
| sql_mode | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION |
はユーザ認証部分の仕組みやパスワードの暗号方式がかわるので、バージョン混在でレプリを組むときに5.7と合わせておかないとエラーが起きます。
はlatin1からutf8mb4に変わっています。MySQL5.7以前を使っていたところでもlatin1を使っていたところは少ないと思いますが、utf8(mb3)が指定されていることも多いので気をつけましょう。
は照合順序のデフォルトがかわってしまうので、こちらも5.7からのアップグレードの場合は揃えておくのが無難です。
は NO_AUTO_CREATE_USERが削除されています。存在しないユーザに対するGRANT文を実行した際に自動でユーザ作成されるのを禁止するものですが、8.0からはGRANT文でユーザ作成はできなくなりました。
は下位互換のための変数です。my.cnfでは指定できず、SET PERSISTをつかって永続化する必要があります。詳しくは手順の中で解説しています。
アップグレードの大まかな流れ
DBレプリケーション構成
マスターDB（Source)：DB01
スレーブDB（Replica)：DB02,DB03
すべてMySQL5.7の環境です。
スレーブであるDB02、DB03で先にアップグレードを行います。
その後DB02をマスターDBに昇格させます。
DB01をアップグレードする前に、レプリケーションに追加しておくことで万が一の切り戻し用環境としても利用できます。ただしマスターよりフルバージョンをスレーブに追加する構成はMySQL公式では非推奨の構成となります。
アップグレードの詳細手順
上記のDB01~03を利用してアップグレードを行う際の詳細の手順です。
今回はDB01は切り戻し用環境として、レプリケーションに組み込むこととします。
1. [DB02,DB03] MySQLのyum repoの入れ替えとGPG keyインポート
2. [DB02,DB03] アップグレードチェッカー利用のためにmysql-shellをインストール
MySQLの本体とバージョンを合わせる必要はないので最新のmysql-shellを入れます。
3. [DB02,DB03] アップグレートチェッカーの実行
4. [DB02,DB03] アップグレードチェッカーの結果でErrorsが0件なのを確認
[DB02,DB03] アップグレードチェッカーの結果でErrorsが0件なのを確認します
アップグレードチェッカーで確認される項目について
5. [DB02,DB03] MySQLの停止
6. [DB02,DB03] MySQLのアップグレード
7. [DB02,DB03] my.cnfの編集
気をつけたいパラメータで紹介したとおり、MySQL8.0からデフォルト照合順序(collation)がutf8mb4_0900_ai_ciとなっています。MySQL5.7には存在しないものになるので、切り戻しを考慮している今回の検証では、このmy.cnfの設定でutf8mb4_general_ciを指定する必要があります。
8. [DB02,DB03] 起動＆チェック
9. [DB02,DB03]default_collation_for_utf8mb4を設定する
はMySQL8.0のデフォルトcollationを指定できる下位互換のための変数です。
このパラメータはmy.cnfで設定できず、SET PERSISTを利用して変数の変更を永続化します。
default_collation_for_utf8mb4について詳しく解説はこちら
このパラメータの指定がないと下記のような状態になります。
collation_serverがになっているので一見大丈夫そうに見えます。
特に何も指定せずテーブルを作成してみます。
ちゃんと文字コードはutf8mb4でCOLLATEはutf8mb4_general_ciです。
しかし、下記のようなパターンで意図せずCOLLATEがutf8mb4_0900_ai_ciが使用されます。
文字コードだけ指定してCOLLATEを指定していない場合です。
10. アプリケーションからの書き込みを停止してDB02をマスターDBにフェイルオーバーさせる
11. [DB01] レプリケーションのマスター参照先をDB02に変更してレプリスタート
最終構成
これで下記の構成が完成となります。
終わりに
SRG では一緒に働く仲間を募集しています。
ご興味ありましたらぜひこちらからご連絡ください。
