オールアバウトTech Blog

株式会社オールアバウトのエンジニアブログです。

All Aboutの基幹DBのMySQLバージョンを5.7から8.0に上げた話

こんにちは。株式会社オールアバウト エンジニアの@hideです。

私たちのチームでは、2022年3〜6月にかけて、オールアバウトの基幹DB（All Aboutの記事やガイドの情報が格納してあるDB）のMySQLバージョンを5.7から8.0に上げる対応を実施しました。

当記事ではその際の移行手順や実際の作業を通して得た知見をまとめます。

これからシステムのMySQLバージョンを上げたいと思っている方の参考になれば幸いです。

環境情報

弊社の主な環境は以下の通りです。

今回はCloud SQLのMySQLバージョンを5.7から8.0に上げる対応を実施しました。

MySQLバージョンアップの動機について

今回MySQLバージョンアップを行った理由は以下の二つです。

セキュリティ担保のため

一つ目はセキュリティを担保するためです。

MySQL（Cloud SQL for MySQL）にはサポート期限があり、期限が切れてしまうと新規のバグやエラーの修正が行われなくなってしまいます。

バグやエラーはシステムの脆弱性に繋がる可能性があるため、なるべく早くバージョンアップを実施する必要がありました。

All Aboutのリアーキテクチャに向けた準備

二つ目はAll Aboutのリアーキテクチャに向けた準備です。

私たちのチームでは、現在All Aboutのアプリケーション・インフラ構成を設計レベルから作り直す、リアーキテクチャプロジェクトを進めています。

このプロジェクトは数年単位で行う予定のため、本格的に走り出すとMySQLのバージョンアップに時間を割く余裕が無くなってしまいます。

そのため、プロジェクトが本格的に走り出す前の初期段階で対応しておくことになりました。

移行作業の振り返りと得た学び

次に、移行の流れと得た学びを時系列でまとめていきます。

1. MySQL8の変更点を確認

まず最初に行ったのは、MySQL8での主な変更点を確認することです。

チームで定期的に時間を設けて調査を行い、MySQL8の主な変更点を洗い出しました。

特に参考になった記事は以下の二つです。

一つが公式が出している記事で、もう一つがCybozu様がMySQL8への移行を行った際の記事です。

どちらもMySQL8の変更点を知る上でとても参考になりました。

2. 影響範囲の洗い出し

次に、影響範囲（対象のDBと接続しているアプリ）の洗い出しを行いました。

調査の結果、他部署が管理しているシステムも含めて、合計で8つのアプリと接続していることが分かりました。

3. 各アプリのテストケースを作成

影響範囲の洗い出しが終わった後は、各アプリで動作担保用のテストケースを作成しました。

今回は通常の機能追加のテストケースとは違ってどこでエラーが発生するかが分からなかったため、全ての機能を網羅する必要がありました。

4. ステージング環境で動作テストを実施して問題点の洗い出し＋エラーの解消

次に、作成したテストケースを使ってステージング環境で各アプリのテストを実施しました。

ここでは、ステージングのDBを使うのではなく、本番環境のDBのデータをステージング用に一部加工したうえでMySQL8化してテストを実施しました。

なぜなら、移行対象のDBは、本番環境とステージング環境でデータの内容や量がかなり異なっていたからです。

データ量によってクエリの動作が変わる場合もあるため、実際の本番環境に近い状態でテストすることはとても重要でした。（実際、メモリ不足起因のエラーは発生しました）


ステージング環境でテストを実施したことで、MySQL8化により各アプリで発生するエラーの洗い出しをすることができました。

発生した主なエラーは以下の2つです。

  • MySQL8で削除されたSQLモードに起因するエラー
  • データソート時のメモリ不足に起因するエラー

それぞれの原因と解消方法を紹介します。

MySQL8で削除されたSQLモードに起因するエラー

一つ目は、MySQL8で削除されたSQLモードに起因するエラーです。

Laravelで作られたバッチシステムで以下のエラーが発生しました。

SQLSTATE[42000]: Syntax error or access violation: 1231 Variable 'sql_mode' can't be set to the value of 'NO_AUTO_CREATE_USER'

これは、公式ドキュメントにもある通り、MySQL8からNO_AUTO_CREATE_USERというSQLモードが削除されたために発生するようになったエラーです。

具体的には、LaravelではMySQLのstrictモードのオン・オフを設定できるようになっており、オンの場合は以下のようにSQLモードをセットするのですが、MySQL8からはNO_AUTO_CREATE_USERが削除されたためセットできずにエラーとなっていました。

protected function strictMode()
{
  return "set session 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'";
}
// /vendor/laravel/framework/src/Illuminate/Database/Connectors/MySqlConnector.php

これについては、Laravelでパッチが出ていたため、Laravelのパッチバージョンを上げることで解消することができました。

パッチバージョンを上げた後は、以下のようにMySQLのバージョンによってSQLモードを変えるため、MySQL8にしてもエラーが発生しなくなっています。

protected function strictMode(PDO $connection)
{
  if (version_compare($connection->getAttribute(PDO::ATTR_SERVER_VERSION), '8.0.11') >= 0) {
      return "set session sql_mode='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION'";
  }

  return "set session 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'";
}

データソート時のメモリ不足に起因するエラー

二つ目は、データソート時のメモリ不足に起因するエラーです。

Laravelで作られた管理画面の契約書一覧ページで以下のエラーが発生しました。

SQLSTATE[HY001]: Memory allocation error: 1038 Out of sort memory, consider increasing server sort buffer size

これは、MySQL8からデータのソート方式が変わった（正確にはソートの選択方式が変わった）ことに起因するエラーと考えています。

具体的には、以前はレコードのソートをする際に、特定のカラム（IDなど）でソートをした後にデータを取得していたのですが、MySQL8（正確には8.0.20）からはパフォーマンスの観点から、常に指定したカラムのデータをまず全て取得した上でメモリ上で一括でソートするようになりました。

そのため、ソート時のデータ量が増加してしまい、sort buffer sizeが同じ値でもメモリ不足によりエラーが発生したと考えられます。

このエラーの対応方針としては、エラーメッセージにある通り素直にsort buffer sizeを上げても良かったのですが（実際、sort buffer sizeを上げてエラーが解消されることは確認しました）、DB全体のメモリ消費量の増加に繋がる可能性がありました。

そのため今回はクエリチューニングで対応しました。

具体的には、クラスタインデックス（主キーインデックス）を効かせるようクエリを変更することで、データ取得時にソート処理が発生しない（ソート時にメモリが消費されない）ようにしました。


今回紹介した二つのエラーはあくまで弊社の事例であり、他社のシステムで発生するとは限りません。

どういったエラーが発生するかは（ある程度の予測はできるかもしれませんが）断定はできないので、結局は「徹底的にテストをして問題点を洗い出す」がバージョンアップの際の原則なのかなと感じています。

5. データ移行方法の検討と決定

ステージングでの動作検証と並行して、データの移行方法（MySQLのバージョンアップ方法）の調査と検証も行っていました。

調査により、Cloud SQLでMySQLのバージョンを上げる方法としては主に以下の二つがあることが分かりました。

  1. Database Migration Service（DMS）を使用する方法
  2. 現在のCloud SQLインスタンスのコンソールからデータをExportし、新しいMySQLバージョンのインスタンスにImportする方法

※Database Migration Service（DMS）とは、別の環境からCloud SQLにデータベースを移行するためのGCPのサービスです。ここでの「別の環境」とは、AWS・Azure・オンプレ等のGCP以外の環境だけでなく、別バージョンのCloud SQLインスタンスからの移行も含んでいます。DMSの概要についてはこちらの動画も参考になるでしょう

これらの二つの方法について動作検証をしつつ比較を行い、結論としてはDMSを用いて移行することになりました。

主な理由は以下の3つです。

  • 移行にかかる時間がDMSの方が短い
  • 移行当日の作業量がDMSの方が少ない
  • DMSではトリガー等の設定が移行される

移行にかかる時間がDMSの方が短い

2通りの方法で移行を試した結果、DMSを用いた場合は1時間半程度でデータのExportからImportまでが完了しました。

しかし、DMSを使わない場合はExportからImportまで8時間以上かかりました。（特にImportに時間がかかった）

当たり前の話としてサービスのダウンタイムは短ければ短いほど良いため、この時点でDMSを使うことはほぼ確定しました。

移行当日の作業量がDMSの方が少ない

他の理由としては、移行当日の作業量がDMSの方が圧倒的に少ないことも挙げられます。

DMSの場合は、事前に「移行ジョブ」を作成しておけば、当日は「移行開始ボタン」を押すだけで移行ができます。

しかしDMSを使わない場合は、Export,Importを手動で行う必要があるのに加えて、後述するトリガー等の設定も全て手動で移行する必要がありました。

当日の作業量が増えると思わぬエラーやミスで時間が延びる可能性もあるため、このDMSの作業量の少なさは大きな魅力の一つと考えました。

DMSではトリガー等の設定が移行される

検証の結果、DMSを用いた場合はトリガーの設定も含めて移行されますが、DMSを使わない場合はトリガーの設定は移行されないことが分かりました。

前述したように、当日の作業量はなるべく減らしたかったため、トリガー等の設定を自動で移行できるDMSの方が有効と考えました。

ちなみに、DMSで移行が保証されているものはこちらの記事にまとめられてあります。

ストアドプロシージャ、ビュー、外部キー制約等は漏れなく移行されます。

ただ、一部DMSでは移行されない設定もあったため、次の項目（本番環境でデータ担保のテストを実施）で紹介します。

6. 本番環境でデータ担保のテストを実施

ステージングでのテストの実施により、エラーの特定と解消は行えました。

しかし、あくまで「マスキングしたデータ」を用いてテストをしたため、「移行前後でデータが変化していないことの担保」はできていませんでした。

そのため、本番環境でコピーインスタンスを作成してDMSを用いたデータ移行を行い、移行前後で同じSQLを流してデータが変化していないことを確認しました。

このとき、移行前後でデータベースに保存されている時間（記事の作成日時など）が変化していることに気付きました。

これは、DMSではCloud SQLのデータベースフラグの設定は移行されないため、移行後のDBのタイムゾーン設定（default_time_zone）がUTC時間になっていたことが原因でした。

※MySQLのTIMESTAMP型の取得時の値はデータベースのタイムゾーン設定に依存する（DATETIME型は依存しない）

これについては、移行元インスタンスと同一のフラグを移行先にも設定することで解消できました。

7. ステージング環境でリハーサルを実施

データ担保のテストを実施した後は、ステージング環境でハーサルを実施しました。

ここでのポイントは、ステージングテストと同様、本番と同じデータ量のDBを使用したことです。

なぜなら、データ量が違うとDMSの移行時間が大きく変わってしまうため、移行時間の正確な計測が難しくなってしまうからです。

具体的には、データ容量に比例してDMSの移行時間も増えていました。

以下、ストレージ使用量の違うDBでそれぞれDMS移行を試した際にかかった時間の比較です。

  • 使用量60GiBのDB
    • 1時間半程度
  • 使用量20GiBのDB
    • 30分程度

このリハーサル結果を基に、本番環境での移行手順の作成と作業時間の見積もりを行いました。

また、影響範囲の大きい作業となるため、早めに広く告知することも意識しました。（オールアバウトの場合社外のガイドにも告知が必要）

8. 本番環境で移行作業を実施

最後に本番環境で移行作業を実施しました。

ここで意識したことは、なるべく最小時間で作業が終わるようリリースの順序を工夫することです。

例えばDMS移行の待ち時間の間に各アプリのDB接続情報の変更やリリース準備を進めておくなど、無駄な時間ができるだけ発生しないよう工夫しました。

結果としては、予定より早く3時間半程度で全ての移行作業を完了することができました。

移行を進めるにあたり意識したこと

移行作業を進めるにあたり意識した事は、まずは「ミスをしない」ことです。

DBの移行は一歩間違うと長時間DB（システム）が停止する可能性もあるため、慎重に行う必要がありました。

具体的には、厚くテストをするのはもちろんのこと、リリースの際にも切り戻し用のプランを用意しておくなど、常に悪い状況を想定して準備を行いました。

また、今回は自チームだけでなく複数のチームが連携して動く必要があったため、動き方の認識を合わせるミーティングを開いたり、都度作業の進捗を確認するなどして全体の作業がスムーズにが進むよう調整することも意識しました。

おわりに

個人的にはMySQLのバージョンアップは初めての経験だったため、最初はどこから手を付けていいかも分かりませんでした。

しかし、周りの手を借りたり個人的に勉強しつつ進めることで、何とか大きなミスなく移行作業を終えることができました。

また、同時にDMSやクエリチューニングなどDB周りの知見も多く増やすことができました。

今後は今回の作業で得た知見を日々の業務に活かすのはもちろんのこと、よりDB周りの知見を増やしていけるよう日々精進していきたいと考えています。

最後まで読んで頂きありがとうございました。

All Aboutリビルド：エンジニア募集のお知らせ

オールアバウトでは、2001年にサービスをスタートしたメディアAll Aboutのリビルドに取り組むエンジニアを募集しています。

オールアバウトとしてこれまでメインでは使ってこなかったTypeScriptの採用等、技術的に大きな取り組みにチャレンジできるプロジェクトです。

興味のある方は以下のリンクからお申し込みください。

https://hrmos.co/pages/allaboutgroup/jobs/engineer_rebuild

沢山のご応募をお待ちしております！