Go to Qiita Advent Calendar 2024 Top
LoginSignup
13
10

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 1 year has passed since last update.

エムスリーキャリア Advent Calendar 2023

Day 24
@amita0031

さようならMySQL5系 こんにちはMySQL8系

Last updated at Posted at 2023-12-24

背景

AWS RDS が 2024年2月29日にMySQL5系の標準サポートを終了することになり、新規のバグやエラーの修正が行われなくなり、脆弱性に繋がる可能性が有ります。
サポートが終了すると、2024/3/1 ~ 2024/3/31 の間で自動でMySQL 8.0 にアップグレードされるようになっています。

MySQL 5.7 で RDS 延長サポートに登録すれば、最大3年間延命することができるようですが、遅かれ早かれアップグレードする必要があるため、今回　5.7.42 から 8.0.35 へ移行することにしました。

今年中にアップグレードする人が多いのかMySQLアップグレードの記事が増えていますが、
この記事では、今後駆け込みでアップグレードする方向けに、

  • アップグレードを実施するまでにやったこと
  • MySQL5.7から8系への変更点
  • 文字セットと照合順序の変更
  • アップグレードの手順
  • アップグレードする上で気をつける点。
  • アップグレード実行後に問題になったこと

をまとめました。
この記事だけ参考にしてアップグレードを進めて頂いても、高度なSQL職人芸を使用していない限り大丈夫だと思います。ご参考ください。

アップグレードを実施するまでにやったこと

1.MySQL 5.7 から 8 への変更点と影響可能性の調査&まとめ

調査結果は、MySQL5.7から8系への変更点と文字セットと照合順序の変更にまとめました。

2.MySQLのアップグレードを行うサービスについて、ローカル環境にてバージョンアップを実施し動作確認を行う。

アップグレード方法は環境によりますが、docker、docker composeを使って環境を構築している場合、 docker-compose.ymlで指定しているMySQLのイメージのバージョンを上げるだけです。

また、mysql8以降のイメージはマルチアーキテクチャに対応しています。
Dockerホストのアーキテクチャに合わせて適切なイメージをPullするが、MySQL5系ではApple sillicon に対応したアーキテクチャのイメージがありませんでした。
条件分岐している場合は、不要になります。

3.検証環境でのDBアップグレードの手順の作成

手順の詳細な流れは後述します。

4.検証環境でのDBアップグレード実施。時間計測を含める

作成した手順書に沿って、アップグレードを実施します。
時間計測を行い、本番環境のアップグレードにかかる時間を予想しておきます。

5.検証

QA環境にてひたすらテストを行います。
今回は、サービスで行える操作を全てQAの方に確認していただきました。
また、後述しますがSQLの実行計画に変化がないかも合わせて確認しています。

6.本番環境へのDBアップグレード手順の作成

検証環境での手順を本番環境用に書き換えます。手順の詳細については後述します。

7.本番環境でのテストケースの作成

QA環境のテストほどガッツリやりませんが、コア機能に絞って検証を行いました。

8.関係者への連絡

今回のアップグレードは、サービスを閉鎖した状態で行ったため、サービス利用者や社内の運用者に対して告知を行いました。
これに関しては、本番環境への適用タイミングの目処がついた時点で行いました。

MySQL5.7から8系への変更点4点

1.パフォーマンスの違いと新規追加機能

公式ドキュメントで出ているMySQL8の新規追加機能：https://dev.mysql.com/doc/refman/8.0/en/upgrading-from-previous-series.html

AWS 公式で出しているMySQL8の新規追加機能：https://aws.amazon.com/jp/about-aws/whats-new/2018/10/amazon-rds-now-supports-mysql-8/

から重要なものだけを引用しています。

  • ウインドウ関数
  • CTE
    • WITH句のことで、一時的に結果セット作成し、クエリ内で複数回参照できるというもの
  • CHECK制約
  • パフォーマンス: MySQL 5.7 の 2 倍のパフォーマンス。InnoDB で読み書きの、IO バウンドで、コンテンションの高い「ホットスポット」のワークロードのパフォーマンスを最適化。レプリケーションのパフォーマンス向上。降順インデックス。バッファプールのコンテンツに対応したコストモデルの改善。リソースグループでユーザーによるワークロードに応じたリソース使用の制御と優先付けを実現。
  • AUTO_INCREMENT の不具合修正
  • MySQL 8.0 で追加、非推奨または削除されたサーバーおよびステータスの変数とオプション
    • RDSのパラメータグループを確認して、編集済み(つまり使用中)のものが↑に含まれていないか確認しましょう。
    • 旧環境でチューニングしていた内容を反映することを忘れるなどパラメータの差異は見逃さないようにしましょう。参考
  • 国際化とモバイルのサポート: デフォルトの文字コードに utf8mb4 を使用。
    • 絵文字やいくつかの特殊な文字を保存することができるようになった。
    • 文字コードについては注意点で詳細をまとめています。

2.互換性を持たない変更

引用元：https://docs.aws.amazon.com/ja_jp/AmazonRDS/latest/UserGuide/USER_UpgradeDBInstance.MySQL.html#USER_UpgradeDBInstance.MySQL.57to80Prechecks

MySQL 8.0 には MySQL 5.7 との非互換性がいくつかあります。このような非互換性が原因で MySQL 5.7 から MySQL 8.0 へのアップグレード中に問題が生じる可能性があります。
そのため、アップグレードを成功させるには、データベースで何らかの準備が必要になる場合があります。このような非互換性の一般的なリストを以下に示します。

  • テーブルで古いデータ型や関数を使用してはいけません。
  • 孤立した *.frm ファイルがあってはいけません。
  • トリガーの definer が欠落しているか、空である、またはトリガーに無効な作成コンテキストが含まれていてはいけません。
  • ネイティブのパーティショニングをサポートしていないストレージエンジンを使用するパーティショニングされたテーブルがあってはいけません。
  • キーワードや予約語に違反してはいけません。MySQL 8.0 では、以前に予約されていなかったキーワードもあります。詳細については、MySQL ドキュメントの「キーワードと予約語」を参照してください。
  • MySQL 5.7 の mysql システムデータベースに、MySQL 8.0 データディクショナリで使用されるテーブルと同じ名前のテーブルがあってはいけません。
  • sql_mode システム可変設定で、古い SQL モードを定義してはいけません。
  • 長さが 255 文字または 1,020 バイトを超える ENUM または SET 列要素をそれぞれ持つテーブルまたはストアドプロシージャがあってはいけません。
  • MySQL 8.0.13 以降にアップグレードする前に、共有 InnoDB テーブルスペースに存在するテーブルパーティションがあってはいけません。
  • ASC 句に DESC または GROUP BY 修飾子を使用する、MySQL 8.0.12 以前のクエリおよびストアドプログラム定義があってはなりません。
  • GROUP BY 句の非推奨の ASC 修飾子または DESC 修飾子は削除されます。 以前に GROUP BY ソートに依存していたクエリーでは、以前の MySQL バージョンとは異なる結果が生成される場合があります。 特定のソート順序を生成するには、ORDER BY 句を指定します。
    • 担当しているサービスでは、GROUP BY にソートを組み合わせて使用していないので問題ありませんでした。
  • MySQL 8.0でサポートされていない機能を MySQL 5.7 のインストールで使用することはできません。
  • 64 文字を超える外部キーの制約名があってはいけません。
  • Unicode サポートが向上するように、utf8mb3 文字セットを使用するように、utf8mb4 文字セットを使用するオブジェクトを変換することを検討してください。utf8mb3 文字セットは廃止されました。また、utf8 の代わりに utf8mb4 を文字セット参照に使用することを検討してください。現在 utf8 は utf8mb3 文字セットの別名であるためです。
    • 詳細については、MySQL ドキュメントの「utf8mb3 文字セット (3 バイトの UTF-8 Unicode エンコード)」を参照してください。
    • 今回は、バージョンアップに伴い、utf8mb3を使っている部分は、全てutf8mb4に変更しました。詳細は後述します。

Amazon RDS では、MySQL 5.7 から 8.0へアップグレード時に上記の項目を自動でチェック(pre-upgrade checks)してくれます。自動チェックに引っかかった場合、アップグレードは自動でキャンセルされます。

これらは検証環境のアップグレード時にチェックしてくれるので、全て手作業で確認する必要はありませんでした。検証環境のアップグレードでエラーが出る場合、確認しましょう。

3.文字コードやコレーションの変更

charset：記号とエンコーディングのセット。(別名：character set、文字セット)

collation : 照合順序 (別名：collation、コレーション)

charsetとcollation はサーバー(システム環境変数)、データベース、テーブル、カラムそれぞれ設定可。

前述しましたが、charsetやcollateのデフォルトが変わりました。

項目 MySQL5.7 MySQL8 備考
Default character set
(システム環境変数@@character_set_database)		 latin1 utf8mb4
Default Collation
(システム環境変数@@collation_database)		 latin1_swedish_ci utf8mb4_0900_ai_ci
utf8mb4 の デフォルトコレーション utf8mb4_general_ci utf8mb4_0900_ai_ci
システム環境変数default_collation_for_utf8mb4の存在 なし あり utf8mb4のデフォルトコーレション変更の経過措置としてこの環境変数が追加された。
例えばutf8mb4_general_ci にセットし、データベースやテーブル、カラムに対してコレーションを指定せずに utf8mb4 を使った場合、 utf8mb4_general_ci を使ってくれる。

4.Select count(*) が遅くなる。という話

SELECT COUNT(*) は、テーブルフルスキャン(PKインデックスのフルスキャン)を行います。

MySQL 8.0.14 から、parallel clustered index reads という機能が追加されました。インデックスを並列読み込みする機能です。これは、PKのインデックスのスキャンに影響があり、↑ のテーブルフルスキャンに影響があるというものです。これが影響して遅くなる可能性がありました。

今回は仮にSELECT COUNT(*)がスロークエリになった場合、Sentryで補足するようにしました。

*参考

文字セットと照合順序の変更

(注：この話だけでもう１記事書けそうなぐらいのボリュームです。)

背景

さて、AWS RDSのpre checkedで前述したように
utf8mb3 文字セットは廃止されました。(正確には、公式で非推奨になってます。)

MySQL公式の引用

utf8mb3 文字セットは非推奨であり、将来の MySQL リリースで削除される予定です。 かわりに utf8mb4 を使用してください。
utf8 は現在 utf8mb3 のエイリアスですが、ある時点では utf8 が utf8mb4 への参照になることが予想されます。 utf8 の意味があいまいにならないように、utf8 ではなく文字セット参照に utf8mb4 を明示的に指定することを検討してください。

またutf8 (utfmb3)を設定しているカラムに、難読漢字を入れようとした結果不具合が発生した事例もありました。(当社の話)

将来的に廃止される + 取り扱っているサービスの文字セットと照合順序がそれぞれutf8(utf8mb4)用でかつ環境ごとに違っていたことから、アップグレードのタイミングに合わせて文字セットと照合順序をutf8mb4に統一するように変更しました。

utf8 は utf8mb3 のエイリアスです。

文字セットutf8mb3(utf8)とutf8mb4の違い

utf8mb3では文字ごとに1バイトから3バイトを使用します。そのため、4バイト文字が使えません。
utf8mb4では文字ごとに1バイトから4バイトを使用します。そのため、4バイト文字が使えます。

utf8mb3 では Basic Multilingual Plane (BMP) だけサポートしている。utf8mb4ではBMPに加え、補助文字もサポートしています。(補助文字とは絵文字とか難読漢字みたいなものです。)

utf8mb3とutf8mb4の互換性ですが、

  • BMP文字の場合、utf8mb4 と utf8mb3 それぞれのコード値、エンコーディング、バイト数が同じ
  • 補助文字の場合、utf8mb3には補助文字がそもそもない

これら2つの理由から問題ありません。

utf8mb4をサポートする照合順序 utf8mb4_general_ciとutf8mb4_unicode_ciの違い

general_ci と unicode_ci の違いを簡単にまとめると、

半角全角の比較 大文字小文字の比較 ひらがなカタカナの比較
unicode_ci できない できない できない
general_ci できる できない できる

もっと詳しい違いはこちらにまとまっています。

過去は、utf8mb3_general_ciを使っていたので、今回は utf8mb4_general_ci を採用しました。

文字コードや照合順序を変更する場合の注意点4点

その1.予期しないカラムの型変換

VARCHAR や TEXT 系のカラムの文字コードを変換した際、新しいカラムが元のカラムの文字列を格納できることを保証するためにtext型がmediumtext型に変換されます。そのため、文字コードの変換クエリを実行する場合、既存のtext型を明示して実行する必要があります。

For a column that has a data type of VARCHAR or one of the TEXT types, CONVERT TO CHARACTER SET changes the data type as necessary to ensure that the new column is long enough to store as many characters as the original column.

しかし、文字コードのサイズが3byteから4byteに変更した場合、
TEXT型のバイト数65,535バイトを超える文字数(65535byte ÷ 4(byte/文字数) ≒ 16383(文字) )を保存しているカラムがあると溢れるため、MEDIUM TEXT型に変換しましょう。

引用元

その2.インデックスの最大キー長の制限

　MySQLでは、文字列型カラム（varchar型やchar型など）に対してインデックスを作成する場合に、インデックスを作成するカラムの最大キー長は767バイトに決められています。

つまり、MySQLではデータ長が767バイト以上のカラムに対してはインデックスを貼れないということです。

Railsのマイグレーションでstring型を指定した場合、デフォルトで255文字のVARCHAR(255)の型でカラムが作成されます。

1文字当たりのバイト数が3バイトのutf8mb3の場合
255(文字)×3(byte/文字数)=765(byte) < 767(byte)
で問題なくインデックスを作成できます。

一方、1文字当たりのバイト数が4バイトであるutf8mb4の場合、
255(文字)×4(byte/文字数)=1020(byte) > 767(byte)
となりオーバーします。

収めるためには、
191(文字)×4(byte/文字数)=764(byte) < 767(byte)

つまり、191 文字までカラムの文字数を減らす必要があります。

結論として255文字のVARCHAR かつ インデックスが振られているカラムの文字数を191まで減らすクエリを実行する必要があります。

参考文献

VARCHAR(255)文字のカラムの存在は、以下のクエリで調べられます。

SELECT
	C.DATA_TYPE,
    C.TABLE_SCHEMA,
	C.TABLE_NAME,
    C.COLUMN_NAME,
    COLUMN_TYPE 
    FROM
	    INFORMATION_SCHEMA.`COLUMNS` C
    INNER JOIN information_schema.STATISTICS S
    	ON C.TABLE_NAME = S.TABLE_NAME 
    	AND C.COLUMN_NAME = S.COLUMN_NAME
    WHERE
		C.TABLE_SCHEMA = <スキーマ名>
		AND C.DATA_TYPE = 'varchar';
その3.Railsのデフォルトで作成されるテーブルの扱い

ar_internal_metadata テーブルのkeyカラムとschema_migrationsのversion カラムについては、文字コードがutf8mb3かつ VARCHAR(255) でインデックスも貼られていました。

が、Rails管理下のテーブルなのでどういった影響が出るかわからないといったリスクがあるため、今回は対象外としました。

その4.Gemfileの確認

Gemfile.lock : 'mysql2' が '>= 0.3.12b4' （utf8mb4 に対応しているバージョン)であることを確認して下さい。

アップグレードの大まかな流れ

公式ドキュメントによるMySQLのアップグレード方法: https://dev.mysql.com/doc/refman/8.0/en/upgrading.html
AWS公式：https://docs.aws.amazon.com/ja_jp/AmazonRDS/latest/UserGuide/USER_UpgradeDBInstance.MySQL.html

検証環境と本番環境の前準備

  1. DB インスタンスがカスタムDBパラメータグループに関連付けられている場合は、新しいメジャーバージョンと互換性のある既存の設定で新しいDBパラメータグループを作成しておく。

検証環境と本番環境でのアップグレードの簡単な流れ

各サービスによって詳細なアップグレード手順は変わるためここでは簡単に流れだけ説明します。

  1. ALBなどでメンテナンス画面を表示させる
  2. RDSのスナップショットを取得
  3. あれば、文字セットと照合順序を変更するクエリを実行する。(RailsであればマイグレーションでやってもOK)
  4. AWS マネジメントコンソールからRDSインスタンスのアップグレードの実行
  5. 動作確認
  6. メンテナンス画面の解除

アップグレードする上で気をつける点。

アップグレードの手順は猿でもできるぐらい詳細に書き尽くす。

ぶっちゃけアップグレードは緊張するので、脳死状態でも実行できるぐらい詳細に書いた方が身のためです。

また万が一、アップグレードの失敗した時のために、スナップショットからの復元方法も詳細に書いておきましょう。また、検証環境で切り戻しのシミュレーションはしておきましょう。

アップグレード後のパフォーマンス調査は怠らないこと

以下のツールを使って一ヶ月間パフォーマンスのチェックを行っています。
RDS Performance Insight
Sentry

アップグレード実行後に問題になったこと2点

1. インデックスが上手く使えていないクエリが余計遅くなった。

こちらについては、sentryで捕捉出来ており、今後対応する予定です。(簡単にいうと Using FileSortが発生しており、こちらを改善する必要があります。)

2. RDSのログに出てきた新しいwarning

 Plugin mysql_native_password reported: ''mysql_native_password' is deprecated and will be removed in a future release. Please use caching_sha2_password instead'

というWarningが出るようになった。

これはMySQL8.0になって、デフォルトのユーザー認証方式が新規に導入された caching_sha2_password を推奨しているということです。

しかし、Amazon RDS for MySQL 8.0 のデフォルトのユーザー認証は mysql_native_password のままのため、特別な対応は不要なようです。また、パラメーターグループでも編集できないようになっているため今回は、静観しています。

13
10
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
13
10

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?