オンプレミスMySQLをAuroraへ移行する際に、困ったこととその対応

こんにちは、インフラエンジニア の 加藤(@kuromitsu_ka)です。

この記事は Enigmo Advent Calendar 2021 の 24 日目の記事です。

今回は、オンプレミスのMySQLを、Auroraへ移行する際、困ったことと対応したことを記載します。

移行方式をざっくりいうと、オンプレミスのMySQLより取得した、論理バックアップ(mysqldump)とバイナリログを使用してAuroraへ移行しました。移行のため、リストア環境と、リストア後のデータのチェック環境を構築したので先にその説明を記載して、困ったところと対応を記載していきます。

リストア環境

オンプレミスMySQLサーバから取得した、バックアップファイルをAuroraへ適用する環境を作りました。EC2のMySQLと、Auroraとでレプリケーションを貼り、EC2のMySQLにデータを投入してリストアしました。バックアップファイルのダウンロードや、リストアジョブのスクリプト実行は、リストアジョブサーバから実行します。 f:id:enigmo7:20211220161725p:plain

リストア後のデータのチェック環境

Auroraへのデータのリストア後に、データの差分確認をする環境としてMemcachedも用意しました。それぞれのテーブルのデータの合計チェックサム値をMemcachedに入れて比較しました。こちらも、チェックのジョブスクリプト実行は、リストアジョブサーバから実行します。 f:id:enigmo7:20211222002116p:plain

困ったこといくつか。

本題のAuroraへの移行で困ったことは、5個あり、順を追って記載します。

  1. やんごとなき理由で、Auroraへのリストアに物理バックアップが使えず困った。
  2. Auroraの仕様上、バイナリログを直接適用できずに困った。
  3. Auroraへのリストアで、バイナリログの適用に2日間もかかって困った。
  4. リストア環境を作ったものの、バイナリのログ適用がコケて困った。
  5. リストア後、バイナリログ適用したデータの時間がなぜか9時間ずれて困った。

困ったこと その1

やんごとなき理由で、Auroraへのリストアに物理バックアップが使えず困った。

Auroraへのリストアには、物理バックアップが、サポートされていました。こちらが使えると安心だったのですが、問題があり使用できませんでした。結果的に論理バックアップから、リストアすることとなりました。

物理リストアできなかった原因

オンプレミスのMySQLには、パラメータ(innodb_undo_tablespace)が設定されていました。Auroraでは、こちらが変更不能になっているため、物理バックアップでは、Auroraへのリストアでコケていました。パラメータ変更には、MySQLサービスの再起動が必要なため、論理バックアップを使用することとなりました。

  • エラーログ
    Auroraのerror/mysql-error-running.log、error/mysql-error.logより確認したエラーログ
[ERROR] InnoDB: Unable to open undo tablespace './/undo001'.
[ERROR] InnoDB: Plugin initialization aborted with error Generic error
[ERROR] Plugin 'InnoDB' init function returned error.
[ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
[ERROR] Failed to initialize builtin plugins.
[ERROR] Aborting
  • 問題のパラメータ
    undo ログが分割するテーブルスペース数を設定するパラメータ(innodb_undo_tablespace
物理リストアできなかった値
- innodb_undo_tablespace = 2
物理リストアできる値
+ innodb_undo_tablespace = 0
パラメータ名 変更可能
innodb_undo_tablespace いいえ

困ったこと その2

Auroraの仕様上、バイナリログを直接適用できずに困った。

Auroraへは、直接バイナリログ適用できない仕様で、リストア環境を用意することになりました。BINLOGコマンドは、スーパーユーザーの権限での実行が必要なのですが、Aurora では、スーパーユーザー権限を利用することはできないそうで、コケてしまいました。そのため、リストア環境としては、EC2にMySQLを作成して、レプリケーションを貼りました。
※他社事例では、バイナリログをデコードして生のクエリを直接Auroraに適用する方法もありましたが、移行作業をしていた当時は見ていませんでした...。

  • 適用コマンド
# mysqlbinlog --no-defaults --database=${DB_NAME} --start-datetime=${START_TIME} --stop-datetime=${STOP_TIME} bin-log.00xxx 
  | mysql -h ${AURORA_ENDPOINT} -P 3306 -u admin -p
  • エラーログ
ERROR 1227 (42000) at line 7: Access denied; you need (at least one of) the SUPER privilege(s) for this operation

困ったこと その3

Auroraへのリストアで、バイナリログの適用に時間がかかって困った。

Auroraへのリストアでは、バイナリログ適用(1桁GB程度でも)に2日間ほどかかりました。こちらは、AuroraのDBインスタンスのマシンリソースの増強と、Auroraのパラメータを変更したところ高速化できました。結果、バイナリログ適用は、20分程度に納まるようにできました。

  • 対応したこと
    AuroraのCPU、メモリ使用率を見つつインスタンスタイプを変更
サーバ インスタンスタイプ CPU メモリ(GB)
オンプレミスMySQL 12 252
EC2のMySQL(リソース増強前) db.t3.small 2 2
EC2のMySQL(リソース増強後) db.r6g.4xlarge 16 128
  • Auroraのパラメータより、バイナリログ出力を一時的にOFFに変更
Binlog_format = OFF

困ったこと その4

リストア環境を作ったものの、バイナリのログ適用がコケて困った。

バイナリログ適用がタイムアウトしたり、2,3割適用できたところでコネクションエラーになったりもしました。デバッグの際も、タイムアウトとコネクションエラーしか出ず、原因がぱっと見ではわからずで困りました。結果として、リストア環境のMySQLパラメータを変更することで、解決しました。

  • エラーログ
ERROR -- : Lost connection to MySQL server during query (Mysql2::Error::ConnectionError)

MySQLパラメータ変更

リストア環境のEC2のMySQLで、パラメータチューニングを行いました。ひとまず、タイムアウト関係のパラメータを操作しましたが、一部バイナリログデータを適用できず止まってしまうものもありました。続いて、パケットサイズのパラメータも変更しましたが、エラーは続いていました。最終的に、プロセスが、十分なメモリを確保できていないのかなと考えて、メモリキャッシュ関係のパラメータを変更して解決しました。

connect_timeout = 172800
net_write_timeout = 172800
net_read_timeout = 172800
wait_timeout = 172800
interactive_timeout = 172800
  • パケットの最大サイズのMySQLパラメータ(最大値に設定)
max_allowed_packet = 1073741824
  • メモリキャッシュ関係のパラメータ(最大値に設定)
    ※変更したパラメータ「innodb_buffer_pool_size」は、読み込み、書き込みのパフォーマンス向上にも使われるパラメータです。
innodb_buffer_pool_size = 1G

困ったこと その5

リストア後、バイナリログ適用したデータのうち、タイムスタンプ関係のデータで、なぜか9時間ズレが発生して困った。

バイナリログで適用したデータのみ、何故か9時間ズレるという問題も経験しました。MySQLタイムゾーン関係の、パラメータを変更して対応しましたが、解消しませんでした。結果として、バックアップ取得元の、バイナリログフォーマットのパラメータを変更して解決しました。

タイムゾーンのパラメータ
オンプレミスのMySQLも、Auroraも、リストア環境のEC2のMySQLJSTに設定していました。

  • バイナリログ取得元のMySQLと、EC2に作成したMySQLのパラメータ
+------------------+--------+
| Variable_name    | Value  |
+------------------+--------+
| system_time_zone | JST    |
| time_zone        | SYSTEM |
+------------------+--------+
  • Auroraのパラメータ
    Auroraの場合、パラメータ「time_zone」が、データベースのデフォルト値になります。
+------------------+------------+
| Variable_name    | Value      |
+------------------+------------+
| system_time_zone | UTC        |
| time_zone        | Asia/Tokyo |
+------------------+------------+

バイナリログフォーマットのパラメータ

  • binlog_format」をMIXEDから、ROW変更
    レプリケーション元の binlog_format を ROW とすることで、バイナリログ適用の際、クエリでなく実行結果がレプリケーションされるようになるそうでした。バックアップ取得元の、オンプレミスのMySQLにてパラメータ変更した結果、無事、データが一致しました。
バイナリログを適用した、データのタイムスタンプのデータが9時間ズレたパラメータ
- binlog_format = 'MIXED'
バイナリログを適用した、データで時間のズレがなかったパラメータ
+ binlog_format = 'ROW'

感想

オンプレミスMySQLのAurora移行は、たいへんでしたが楽しかったです。

明日の記事の担当は 人事総務 の 廣島 さんです。お楽しみに。


株式会社エニグモ すべての求人一覧

hrmos.co