新人エンジニアのつぶやき

日々の学びをアウトプットするためのブログです

【EOL対応】MySQLのバージョンアップについての備忘録

この記事について

  • MySQL ５系のEOLが迫っています。Oracleは過ぎていますね。AWS RDS はあと数ヶ月です。

~ Oracle ~

MySQL 5.7は2023年10月21日にサポート終了 (EOL) を迎えます。これはMySQLの背後にある企業であるOracle社が、MySQL 5.7の公式アップデート、バグ修正、セキュリティパッチを提供しなくなることを意味します。

~ AWS RDS ~

5.7系 RDS 標準サポート終了日 2023 年 12 月

  • 業務でチーム内のMySQLのバージョンを５系から８系にバージョンアップしました。手順や苦労、MySQL８系になったらできることをまとめようと思います。

先に要点を３つ

  • DBのバックアップは必ず取りましょう。
  • 予約語でマイグレーションしている既存テーブルがないか確認しましょう。あれば、テーブル名を変更しましょう。
  • デフォルトのUTF8の設定はutf8mb4_0900_ai_ciになるので設定ファイルを見直しましょう。

ローカル環境での移行作業

移行手順

１：mysqlのバージョン確認を確認する

$ docker exec -it <コンテナ名またはコンテナID> mysql --version

２：dumpをとる

$ docker exec -it <コンテナ名またはコンテナID> mysqldump -u root --set-gtid-purged=OFF --add-drop-database -B < DBの名前 > > 保存先のパス/mysql_backup_2023XXXX.sql

３：コンテナを停止する

$ docker stop <コンテナ名またはコンテナID>

４：dockerコンテナ上のmysqlのバージョンを上げるためにdockerfileを修正する // dockerfile

- FROM mysql:5.7
+ FROM mysql:8.0

５：volumesにあたるディレクトリを削除し、再度作成し、起動する

$ docker compose up -d

６：正常に起動していることを確認する

$ docker ps -a

  CONTAINER ID   IMAGE     COMMAND                  CREATED          STATUS          PORTS                               NAMES
  28972eccefe0   コンテナイメージ名    "docker-entrypoint.s…"   35 minutes ago   Up 44 seconds   0.0.0.0:3306->3306/tcp, 33060/tcp   コンテナ名

７：dumpをリストアする

  • パスワードはmysql 8.0以降は以下を使う
    • SET PASSWORD = '任意の文字列';

  • ローカル上でダンプファイルの実行がうまくいかなかったので以下の手順で実施

    1: ダンプファイルをコンテナ内にコピー

      $ docker cp mysql_backup_2023XXXX.sql コンテナ名:/tmp

    2: docker上のディレクトリにファイルが存在するかチェックする

      $ docker exec -it コンテナ名 bash
  $ ls /tmp

    3: mysqlサーバーにサクセスし以下のコマンドを実行

      $ docker exec -it pts-db-1 mysql -u root
  $ mysql> source /tmp/mysql_backup_2023XXXX.sql

動作確認

  • サーバー側をビルドし、正しくDBからデータが取得できるかを確認する
  • ビルド時に予約語の関係でエラーとなるため、テーブル名を変更する 
      RENAME TABLE `変更対象のテーブル名` TO `変更後のテーブル名`;
    • テーブル名の変更に伴い、アプリケーション側のコードもテーブルやクラス名を変更する
      • ここは注意して作業を進めるべき
      • アプリが動かない恐れがあるため

AWSでの移行作業

MySQL 5.7 から 8.0 へのアップグレードの事前確認

  • 2.11.5 アップグレード用のインストールの準備に記載されている以下の項目をチェックした
    • テーブルで古いデータ型や関数を使用してはいけない。
    • 孤立した *.frm ファイルがあってはいけません。
    • トリガーの definer が欠落しているか、空である、またはトリガーに無効な作成コンテキストが含まれていてはいけない
    • ネイティブのパーティショニングをサポートしていないストレージエンジンを使用するパーティショニングされたテーブルがあってはいけない
      • 以下のクエリでチェックできる sql SELECT TABLE_SCHEMA, TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE ENGINE NOT IN ('innodb', 'ndbcluster') AND CREATE_OPTIONS LIKE '%partitioned%';
    • キーワードや予約語に違反してはいけない。MySQL 8.0 では、以前に予約されていなかったキーワードもある。詳細については、MySQL ドキュメントの「キーワードと予約語」を参照。
    • MySQL 5.7 の mysql システムデータベースに、MySQL 8.0 データディクショナリで使用されるテーブルと同じ名前のテーブルがあってはいけない。

    • データディクショナリテーブルを確認した

        SELECT 
      TABLE_SCHEMA, TABLE_NAME
  FROM 
      INFORMATION_SCHEMA.TABLES
  WHERE 
      LOWER(TABLE_SCHEMA) = 'mysql'
  and 
      LOWER(TABLE_NAME) IN
      (
          'catalogs',
          'character_sets',
          'check_constraints',
          'collations',
          'column_statistics',
          'column_type_elements',
          'columns',
          'dd_properties',
          'events',
          'foreign_key_column_usage',
          'foreign_keys',
          'index_column_usage',
          'index_partitions',
          'index_stats',
          'indexes',
          'parameter_type_elements',
          'parameters',
          'resource_groups',
          'routines',
          'schemata',
          'st_spatial_reference_systems',
          'table_partition_values',
          'table_partitions',
          'table_stats',
          'tables',
          'tablespace_files',
          'tablespaces',
          'triggers',
          'view_routine_usage',
          'view_table_usage'
      );

    • sql_mode システム可変設定で、古い SQL モードを定義してはいけない。

      SQL モードの設定MySQL 8.0 のデフォルトの SQL モードには、次のモードが含まれます: ONLY_FULL_GROUP_BY, STRICT_TRANS_TABLES, NO_ZERO_IN_DATE, NO_ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO および NO_ENGINE_SUBSTITUTION。

      SQL モードの設定を参照

    • 長さが 255 文字または 1,020 バイトを超える ENUM または SET 列要素をそれぞれ持つテーブルまたはストアドプロシージャがあってはいけない。
    • MySQL 8.0.13 以降にアップグレードする前に、共有 InnoDB テーブルスペースに存在するテーブルパーティションがあってはいけない。
    • ASC 句に DESC または GROUP BY 修飾子を使用する、MySQL 8.0.12 以前のクエリおよびストアドプログラム定義があってはならない。
    • MySQL 8.0でサポートされていない機能を MySQL 5.7 のインストールで使用することはできません。 詳細については、MySQL ドキュメントの「MySQL 8.0 で削除された機能」を参照する。
    • 64 文字を超える外部キーの制約名があってはいけない。
    • Unicode サポートが向上するように、utf8mb3 文字セットを使用するように、utf8mb4 文字セットを使用するオブジェクトを変換することを検討してください。utf8mb3 文字セットは廃止されました。また、utf8mb4 の代わりに utf8 を文字セット参照に使用することを検討してください。現在 utf8 は utf8mb3 文字セットの別名であるためです。詳細については、MySQL ドキュメントの「utf8mb3 文字セット (3 バイトの UTF-8 Unicode エンコード)」を参照してください。

上記を確認した後、アップグレードする

MySQL DB インスタンスをアップグレードする

  1. ダンプをとる(念の為)
    mysqldump -u admin -p -h "DBサーバー" --skip-column-statistics --set-gtid-purged=OFF --add-drop-database -B "DB名" > /var/tmp/mysql_backup_202307XX.sql
  1. MySQL DB エンジンのアップグレード アップグレードの概要を参照し手順を確認する
  2. DB スナップショットの作成
  3. DB インスタンスのアップデートを適用するでAWSコンソール、もしくはAWS CLI上から進める

MySQL 5.7 から 8.0 へのアップグレードに失敗した後のロールバック

  • AWSのドキュメントより

失敗した時点でAmazon RDS は、アップグレードに対して実行された変更をロールバックします。ロールバック後、MySQL DB インスタンスは MySQL バージョン 5.7 を実行しています。アップグレードが失敗してロールバックされると、Amazon RDS は、イベント ID RDS-EVENT-0188 のイベントを生成します。

切り戻しについて

  • 何かしらの要因で切り戻しが発生した場合はAWS RDSのスナップショットをAWSのコンソール上で復元するようにした

MySQL8系での変更点およびできること

変更点

  • GROUP BYによる暗黙のソートがされなくなるためORDER BY + ASC OR DESCを使用することが推奨されている
  • テーブルやカラムを作る時にutf8mb4の設定がデフォルトでutf8mb4_0900_ai_ciとなってしまう
    • 文字列の区別ができない場合が想定される
      • 「は」と「ぱ」は等しいとして評価されてしまう
      • 大文字と小文字の違いを無視するため「あ」と「ぁ」は等しいと評価されてしまう など
    • 対策として、以下の設定値を変更する
      • SET GLOBAL collation_server=utf8mb4_general_ci;

できること

  • MySQL 8.0: MySQL 5.7よりも最大2倍高速
  • データ分析がしやすくなった
  • JSONデータを受け取り、それをリレーショナルテーブルとして返すJSON_TABLE関数など、多数の機能を追加された
  • WITH句を使用し、サブクエリを切り出すことができるようになった
  • GISであるSRS(Spatial Reference Systems)をサポートするようになった
    • 位置情報に関する処理がしやすくなった

最後に

  • 移行作業はとても緊張したが、データが消えなくて良かったと思っています。バックアップ、スナップショットを取得することは本当に大事です。。。