こんにちは、インフラエンジニア の 加藤(@kuromitsu_ka)です。
この記事は Enigmo Advent Calendar 2021 の 24 日目の記事です。
今回は、オンプレミスのMySQLを、Auroraへ移行する際、困ったことと対応したことを記載します。
移行方式をざっくりいうと、オンプレミスのMySQLより取得した、論理バックアップ(mysqldump)とバイナリログを使用してAuroraへ移行しました。移行のため、リストア環境と、リストア後のデータのチェック環境を構築したので先にその説明を記載して、困ったところと対応を記載していきます。
リストア環境
オンプレミスMySQLサーバから取得した、バックアップファイルをAuroraへ適用する環境を作りました。EC2のMySQLと、Auroraとでレプリケーションを貼り、EC2のMySQLにデータを投入してリストアしました。バックアップファイルのダウンロードや、リストアジョブのスクリプト実行は、リストアジョブサーバから実行します。
リストア後のデータのチェック環境
Auroraへのデータのリストア後に、データの差分確認をする環境としてMemcachedも用意しました。それぞれのテーブルのデータの合計チェックサム値をMemcachedに入れて比較しました。こちらも、チェックのジョブスクリプト実行は、リストアジョブサーバから実行します。
困ったこといくつか。
本題のAuroraへの移行で困ったことは、5個あり、順を追って記載します。
- やんごとなき理由で、Auroraへのリストアに物理バックアップが使えず困った。
- Auroraの仕様上、バイナリログを直接適用できずに困った。
- Auroraへのリストアで、バイナリログの適用に2日間もかかって困った。
- リストア環境を作ったものの、バイナリのログ適用がコケて困った。
- リストア後、バイナリログ適用したデータの時間がなぜか9時間ずれて困った。
困ったこと その1
やんごとなき理由で、Auroraへのリストアに物理バックアップが使えず困った。
Auroraへのリストアには、物理バックアップが、サポートされていました。こちらが使えると安心だったのですが、問題があり使用できませんでした。結果的に論理バックアップから、リストアすることとなりました。
物理リストアできなかった原因
オンプレミスのMySQLには、パラメータ(innodb_undo_tablespace)が設定されていました。Auroraでは、こちらが変更不能になっているため、物理バックアップでは、Auroraへのリストアでコケていました。パラメータ変更には、MySQLサービスの再起動が必要なため、論理バックアップを使用することとなりました。
[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
- 公式ドキュメント
Aurora MySQL 設定パラメータ
| パラメータ名 | 変更可能 |
|---|---|
| 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
- 公式ドキュメント
13.7.6.1 BINLOG Statement
Amazon Aurora MySQL DB クラスターと外部の MySQL データベースを同期する
外部のソースインスタンスを使用したバイナリログファイル位置のレプリケーションの設定
困ったこと その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のMySQLもJSTに設定していました。
+------------------+--------+ | 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移行は、たいへんでしたが楽しかったです。
明日の記事の担当は 人事総務 の 廣島 さんです。お楽しみに。
株式会社エニグモ すべての求人一覧
