今更ながら、Aurora（MySQL 5.6 互換）上のデータをutf8からutf8mb4に変換することになったので、タイトルの通りSELECT INTO OUTFILE S3とLOAD DATA FROM S3を使ってテストしてみました。
この方法を選んだ理由
端的に言えば「他に思いついた方法に問題点があったから」です。
-
ALTER TABLEで文字コードを変える→長時間更新ロックが掛かる（db.r4.2xlarge で 300GiB 未満でも 10 時間！）・不意に失敗することがある（実際にエラーが出た） - pt-online-schema-change を使う→ロック競合で詰まるのが怖い・トリガで拾えない更新がある
- gh-ost を使う→外部キーなどサポートしていない機能を使っている
-
mysqldumpしたものをリストアする→時間が掛かる -
DMS を使う→そもそも DMS が
utf8mb4をサポートしていない（注：バージョン 3.1.1 でサポートしました。但し、BLOB・TEXT列がNULL許可である必要があるなど、いくつか制約があります。） -
MySQL レプリケーションを使う→文字コードが違うテーブル間のレプリケーションはサポート外（行ベースではエラーで止まる）
- 17.4.1.9 テーブル定義が異なるマスターとスレーブでのレプリケーション（MySQL 5.6 リファレンスマニュアル）
2020/01/29 追記：
（サポート外なので）自己責任になりますが、slave_type_conversion=ALL_NON_LOSSYの設定でうまくレプリケーションできる、という話もあります。
- MySQL Casual Talks #13 文字コード変更対応をしている話（_awache さん）
utf8mb4に変換する流れ
既に文献がたくさんあるため、個々の記述は簡潔に済ませます。
SELECT INTO OUTFILE S3／LOAD DATA FROM S3のための準備については以下を参考にします。
- Amazon Aurora MySQL DB クラスターから Amazon S3 バケット内のテキストファイルへのデータの保存
- Amazon S3 バケットのテキストファイルから Amazon Aurora MySQL DB クラスターへのデータのロード
1. 移行先 Aurora クラスター／インスタンス用のパラメータグループを用意する
既存のパラメータグループをコピーして、変更を加えます。
-
DB クラスターパラメータグループの変更
innodb_file_per_table=1-
character_set_XXX=utf8mb4（client・connection・database・filesystem・results・server） -
aurora_select_into_s3_role=arn:aws:iam::nnnnnnnnnnnn:role/AuroraSelectIntoS3（後で作成するロールの ARN） -
aurora_load_from_s3_role=arn:aws:iam::nnnnnnnnnnnn:role/AuroraLoadFromS3（同上）
-
パラメータグループの変更
innodb_file_format=Barracudainnodb_large_prefix=1max_allowed_packet=【BLOB の処理に必要なサイズ＋α】
2. 移行先の Aurora クラスター／インスタンスを作成する
詳細は省略します。利用可能になったら 1. で作成したパラメータグループを適用します。
3. バックアップデータ書き出し用の S3 バケットを作成する
- 暗号化を指定
- バケットポリシーを指定
バケットポリシーの例（バケット名：abc-hmatsu47-bin01）
{
"Version": "2012-10-17",
"Id": "Policy1523337640951",
"Statement": [
{
"Sid": "Stmt1523337622177",
"Effect": "Allow",
"Principal": {
"AWS": [
"arn:aws:iam::nnnnnnnnnnnn:role/AuroraLoadFromS3（後で作成するロールの ARN）",
"arn:aws:iam::nnnnnnnnnnnn:role/AuroraSelectIntoS3（同上）"
]
},
"Action": [
"s3:AbortMultipartUpload",
"s3:DeleteObject",
"s3:GetObject",
"s3:GetObjectVersion",
"s3:ListBucket",
"s3:ListMultipartUploadParts",
"s3:PutObject"
],
"Resource": [
"arn:aws:s3:::abc-hmatsu47-bin01",
"arn:aws:s3:::abc-hmatsu47-bin01/*"
]
}
]
}
4. IAM で Aurora → S3／S3 → Aurora 用のポリシー／ロールを作成する
以下を参考にします。
・ポリシーAllowAuroraToS3
AllowAuroraToS3（例：「abc-hmatsu47-bin01」など5つのバケットを使う場合）
{
"Version": "2012-10-17",
"Statement": [
{
"Sid": "VisualEditor0",
"Effect": "Allow",
"Action": [
"s3:PutObject",
"s3:GetObject",
"s3:AbortMultipartUpload",
"s3:ListBucket",
"s3:DeleteObject",
"s3:ListMultipartUploadParts"
],
"Resource": [
"arn:aws:s3:::abc-hmatsu47-bin01",
"arn:aws:s3:::abc-hmatsu47-bin01/*",
"arn:aws:s3:::def-hmatsu47-bin02",
"arn:aws:s3:::def-hmatsu47-bin02/*",
"arn:aws:s3:::ghi-hmatsu47-bin03",
"arn:aws:s3:::ghi-hmatsu47-bin03/*",
"arn:aws:s3:::jkl-hmatsu47-text01",
"arn:aws:s3:::jkl-hmatsu47-text01/*",
"arn:aws:s3:::mno-hmatsu47-text02",
"arn:aws:s3:::mno-hmatsu47-text02/*"
]
}
]
}
※ロールAuroraSelectIntoS3に割り当てます。割り当てに一手間かかるため要注意です（詳細は前のリンクを参照）。
・ポリシーAllowAuroraFromS3
AllowAuroraFromS3（同上）
{
"Version": "2012-10-17",
"Statement": [
{
"Sid": "VisualEditor0",
"Effect": "Allow",
"Action": [
"s3:GetObject",
"s3:ListBucket",
"s3:GetObjectVersion"
],
"Resource": [
"arn:aws:s3:::abc-hmatsu47-bin01",
"arn:aws:s3:::abc-hmatsu47-bin01/*",
"arn:aws:s3:::def-hmatsu47-bin02",
"arn:aws:s3:::def-hmatsu47-bin02/*",
"arn:aws:s3:::ghi-hmatsu47-bin03",
"arn:aws:s3:::ghi-hmatsu47-bin03/*",
"arn:aws:s3:::jkl-hmatsu47-text01",
"arn:aws:s3:::jkl-hmatsu47-text01/*",
"arn:aws:s3:::mno-hmatsu47-text02",
"arn:aws:s3:::mno-hmatsu47-text02/*"
]
}
]
}
※ロールAuroraLoadFromS3に割り当てます。
5. Aurora クラスターにロールを割り当てる
移行元のクラスターにAuroraSelectIntoS3、移行先のクラスターにAuroraLoadFromS3を割り当てます。
※インスタンスではなく、クラスターに割り当てます。
6. Aurora クラスターから S3 にアクセスできるようにネットワークの設定を調整する
実はここが結構厄介だったりします。
パブリックアクセス可能な Aurora クラスターであれば比較的簡単ですが、AWS の VPC 内に Web サーバ等を配置している場合、通常はプライベートアクセスのみを受け付ける状態で DB サーバを配置しているはずです。
- セキュリティグループ（アウトバウンドのルールをデフォルト設定から変更している場合、
0.0.0.0/0または S3 の VPC エンドポイントへのHTTPSアクセスを許可する） - 接続しているサブネットのネットワーク ACL（デフォルト設定から変更している場合、アウトバウンドの
HTTPSアクセスとインバウンドのTCP:1024-65535アクセスを許可する） - NAT ゲートウェイ（プライベートアクセスの場合）
- DB があるサブネットから NAT ゲートウェイ／S3 の VPC エンドポイントへのルーティング（同上）
- S3 の VPC エンドポイント（同上・必要な場合）
を適切に設定し、Aurora クラスターの DB インスタンスから S3 にアクセスできるようにします。
7. 移行先 Aurora クラスターの DB インスタンスに DB・テーブル定義を入れる
CREATE DATABASE 【データベース名】 CHARACTER SET utf8mb4 COLLATE 【デフォルト指定したい照合順序】;
で作成したデータベースに対し、移行元からテーブル定義だけを抽出して（mysqldumpで-dオプションを付けて書き出すなど）、
DEFAULT CHARSET=utf8mb4COLLATE=【指定したい照合順序】-
ROW_FORMAT=DYNAMIC（191 文字を超える INDEX を使う場合）
を付与したCREATE TABLE文を流し込みます。
ストアド・VIEW 等も入れておきます。
8. 移行元クラスターのインスタンスから S3 へデータを書き出す
移行元クラスターの DB でSELECT INTO OUTFILE S3を使い、 S3 バケットへデータを書き出します。このコマンドを使うと、データが適切な単位で分割されるとともに、マニフェストファイルが生成されて S3 バケットに格納されます。
Aurora の場合並列処理に強いため、複数のスレッドでSELECT INTOすることで時間短縮が可能です。1 つのテーブルを主キーの範囲で分けて複数スレッドで書き出すことも可能です。
※詳細はテスト内容と結果を参照。
9. S3 に書き出したデータを移行先クラスターにロード（リストア）する
移行先クラスターの DB から、S3 バケット内のデータをLOAD DATA FROM S3で読み込みます。こちらも並列処理が可能です。並列数を増やしすぎなければ、1 つのテーブルのデータを複数スレッド並列で分割して読み込むことも可能です。
※こちらも詳細はテスト内容と結果を参照。
10. クラスター名／インスタンス名を入れ替える
データを移行し内容のチェックができたら、
- 移行元クラスター／インスタンスの名前を別のものに変更
- 移行先クラスター／インスタンスの名前を移行元の（変更前の）名前に変更
するか、Route 53 のプライベート DNS ゾーンのCNAMEレコードの書き替えを行うなどして、Web サーバ等からのアクセス先が移行先のクラスター／インスタンスに向くようにします。
移行のために設定したパラメータグループ・ロール・ネットワークなどの設定を解除したら、作業完了です。
※しばらくしたら移行用の S3 バケットと移行元の Aurora クラスターも削除します。
移行テストの内容・結果
ここから先は、テストデータを使って移行テストをしたときの結果を掲載します。
A. テストに使ったテーブル等
以下のような定義のテーブルに、テストデータを
-
BLOBテーブル 10 個（データ容量 110GiB 強） -
TEXTテーブル 20 個（同・50GiB 前後）
の合計 160GiB 強（それぞれ 1 レコード当たり平均 1,100 ～ 1,200 バイト）格納しました。
テーブル定義：BLOB#1
CREATE TABLE `bin_table_01` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`val` int(10) NOT NULL,
`bin` blog,
PRIMARY KEY (`id`),
KEY `idx_val` (`val`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
テーブル定義：TEXT#1
CREATE TABLE `text_table_01` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`str` varchar(32) NOT NULL,
`txt` text,
PRIMARY KEY (`id`),
KEY `idx_str` (`str`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
※いずれも、変換後のデータを入れるクラスタの DB はDEFAULT CHARSET=utf8mb4です。テストではCOLLATEを指定しませんでした（デフォルトの COLLATION が選択される）。
information_schema で見ると、以下のようになりました（サンプリングを元にした推計値なので正確な容量ではありませんが、1 レコード当たりのサイズはほぼ同じなので、大きく外れてはいないと思います）。
information_schema.TABLESより統計情報を抜粋（推計値）
mysql> SELECT TABLE_SCHEMA, TABLE_NAME, DATA_LENGTH, INDEX_LENGTH FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'backup_restore_test';
+---------------------+---------------+-------------+--------------+
| TABLE_SCHEMA | TABLE_NAME | DATA_LENGTH | INDEX_LENGTH |
+---------------------+---------------+-------------+--------------+
| backup_restore_test | bin_table_01 | 23036166144 | 471859200 |
| backup_restore_test | bin_table_02 | 5814353920 | 120209408 |
| backup_restore_test | bin_table_03 | 55718182912 | 1209008128 |
| backup_restore_test | bin_table_04 | 9879683072 | 164380672 |
| backup_restore_test | bin_table_05 | 2330984448 | 39403520 |
| backup_restore_test | bin_table_06 | 16157507584 | 309329920 |
| backup_restore_test | bin_table_07 | 5814353920 | 117063680 |
| backup_restore_test | bin_table_08 | 1194328064 | 19447808 |
| backup_restore_test | bin_table_09 | 2299527168 | 39403520 |
| backup_restore_test | bin_table_10 | 1194328064 | 19447808 |
| backup_restore_test | text_table_01 | 9276751872 | 556793856 |
| backup_restore_test | text_table_02 | 2317352960 | 141557760 |
| backup_restore_test | text_table_03 | 5004853248 | 294649856 |
| backup_restore_test | text_table_04 | 2363490304 | 145752064 |
| backup_restore_test | text_table_05 | 6138363904 | 331350016 |
| backup_restore_test | text_table_06 | 2292187136 | 137363456 |
| backup_restore_test | text_table_07 | 8006926336 | 472907776 |
| backup_restore_test | text_table_08 | 10602151936 | 606076928 |
| backup_restore_test | text_table_09 | 2341470208 | 141557760 |
| backup_restore_test | text_table_10 | 5713690624 | 318767104 |
| backup_restore_test | text_table_11 | 1589248 | 81920 |
| backup_restore_test | text_table_12 | 1589248 | 81920 |
| backup_restore_test | text_table_13 | 1589248 | 81920 |
| backup_restore_test | text_table_14 | 1589248 | 81920 |
| backup_restore_test | text_table_15 | 1589248 | 81920 |
| backup_restore_test | text_table_16 | 1589248 | 81920 |
| backup_restore_test | text_table_17 | 1589248 | 81920 |
| backup_restore_test | text_table_18 | 1589248 | 81920 |
| backup_restore_test | text_table_19 | 1589248 | 81920 |
| backup_restore_test | text_table_20 | 1589248 | 81920 |
+---------------------+---------------+-------------+--------------+
30 rows in set (0.07 sec)
B. テストの内容
- Aurora クラスターの DB インスタンスは db.r4.2xlarge を使う
- 比較の基準として
mysqldumpによるダンプとmysqlリストアを実行して時間計測 -
SELECT INTO OUTFILE S3→LOAD DATA FROM S3を各 1 スレッドのみ→ 2 スレッドずつ並列→ 6 スレッドずつ並列でスレッドを増やしながら実行して所要時間を比較- 6 スレッド並列のとき、3 番目の
BLOBテーブルを 2 分割して処理
- 6 スレッド並列のとき、3 番目の
- インスタンスタイプを db.r4.4xlarge に変更して 6 スレッド並列の
LOAD DATA FROM S3を試す（db.r4.2xlarge と比較）
テストに使ったシェルスクリプトと SQL は以下の通りです（一部抜粋）。
※書き出す S3 バケットは 5 つに分割しましたが、この程度の内容なら分割しなくてもそれほど速度には影響しないはずです。
select_into_single.sh（1スレッドSELECTのシェルスクリプト）
#!/bin/sh
date
mysql -u mkadmin -h test.XXXXXXXXXXXX.ap-northeast-1.rds.amazonaws.com --password=xxxxxxxxxx --default-character-set=utf8 < select_into_bin_01.sql
date
mysql -u mkadmin -h test.XXXXXXXXXXXX.ap-northeast-1.rds.amazonaws.com --password=xxxxxxxxxx --default-character-set=utf8 < select_into_bin_02.sql
date
（中略）
mysql -u mkadmin -h test.XXXXXXXXXXXX.ap-northeast-1.rds.amazonaws.com --password=xxxxxxxxxx --default-character-set=utf8 < select_into_bin_10.sql
date
mysql -u mkadmin -h test.XXXXXXXXXXXX.ap-northeast-1.rds.amazonaws.com --password=xxxxxxxxxx --default-character-set=utf8 < select_into_text_01.sql
date
mysql -u mkadmin -h test.XXXXXXXXXXXX.ap-northeast-1.rds.amazonaws.com --password=xxxxxxxxxx --default-character-set=utf8 < select_into_text_02.sql
date
（中略）
mysql -u mkadmin -h test.XXXXXXXXXXXX.ap-northeast-1.rds.amazonaws.com --password=xxxxxxxxxx --default-character-set=utf8 < select_into_text_20.sql
date
select_into_bin_01.sql（1スレッドSELECTのSQL：BLOBテーブル用）
SELECT * FROM backup_restore_test.bin_table_01 INTO OUTFILE S3 's3://abc-hmatsu47-bin01/01-single/backup_restore_test.bin_table_01' FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' MANIFEST ON OVERWRITE ON;
select_into_text_01.sql（1スレッドSELECTのSQL：TEXTテーブル用）
SELECT * FROM backup_restore_test.text_table_01 INTO OUTFILE S3 's3://jkl-hmatsu47-text01/01-single/backup_restore_test.text_table_01' FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' MANIFEST ON OVERWRITE ON;
load_data_single.sh（1スレッドLOADのシェルスクリプト）
#!/bin/sh
date
mysql -u mkadmin -h test2.XXXXXXXXXXXX.ap-northeast-1.rds.amazonaws.com --password=xxxxxxxxxx --default-character-set=utf8mb4 < load_data_bin_01.sql
date
mysql -u mkadmin -h test2.XXXXXXXXXXXX.ap-northeast-1.rds.amazonaws.com --password=xxxxxxxxxx --default-character-set=utf8mb4 < load_data_bin_02.sql
date
（中略）
mysql -u mkadmin -h test2.XXXXXXXXXXXX.ap-northeast-1.rds.amazonaws.com --password=xxxxxxxxxx --default-character-set=utf8mb4 < load_data_bin_10.sql
date
mysql -u mkadmin -h test2.XXXXXXXXXXXX.ap-northeast-1.rds.amazonaws.com --password=xxxxxxxxxx --default-character-set=utf8mb4 < load_data_text_01.sql
date
mysql -u mkadmin -h test2.XXXXXXXXXXXX.ap-northeast-1.rds.amazonaws.com --password=xxxxxxxxxx --default-character-set=utf8mb4 < load_data_text_02.sql
date
（中略）
mysql -u mkadmin -h test2.XXXXXXXXXXXX.ap-northeast-1.rds.amazonaws.com --password=xxxxxxxxxx --default-character-set=utf8mb4 < load_data_text_20.sql
date
load_data_bin_01.sql（1スレッドLOADのSQL：BLOBテーブル#1用）
SET FOREIGN_KEY_CHECKS=0; SET UNIQUE_CHECKS=0; LOAD DATA FROM S3 MANIFEST 's3://abc-hmatsu47-bin01/01-single/backup_restore_test.bin_table_01.manifest' INTO TABLE backup_restore_test.bin_table_01 FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n';
load_data_text_01.sql（1スレッドLOADのSQL：TEXTテーブル#1用）
SET FOREIGN_KEY_CHECKS=0; SET UNIQUE_CHECKS=0; LOAD DATA FROM S3 MANIFEST 's3://jkl-hmatsu47-text01/01-single/backup_restore_test.text_table_01.manifest' INTO TABLE backup_restore_test.text_table_01 FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n';
select_into_6p_01.sh（6スレッドSELECTのシェルスクリプトその1）
#!/bin/sh
date
mysql -u mkadmin -h test.XXXXXXXXXXXX.ap-northeast-1.rds.amazonaws.com --password=xxxxxxxxxx --default-character-set=utf8 < select_into_bin_01_6.sql
date
mysql -u mkadmin -h test.XXXXXXXXXXXX.ap-northeast-1.rds.amazonaws.com --password=xxxxxxxxxx --default-character-set=utf8 < select_into_bin_02_6.sql
date
select_into_6p_02.sh（6スレッドSELECTのシェルスクリプトその2）
#!/bin/sh
date
mysql -u mkadmin -h test.XXXXXXXXXXXX.ap-northeast-1.rds.amazonaws.com --password=xxxxxxxxxx --default-character-set=utf8 < select_into_bin_03_6_1.sql
date
select_into_6p_03.sh（6スレッドSELECTのシェルスクリプトその3）
#!/bin/sh
date
mysql -u mkadmin -h test.XXXXXXXXXXXX.ap-northeast-1.rds.amazonaws.com --password=xxxxxxxxxx --default-character-set=utf8 < select_into_bin_03_6_2.sql
date
select_into_bin_03_6_1.sql（6スレッドSELECTのSQL：BLOBテーブル#3前半用）
SELECT * FROM backup_restore_test.bin_table_03 WHERE id < 24999997 INTO OUTFILE S3 's3://def-hmatsu47-bin02/03-6p/backup_restore_test.bin_table_03_1' FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' MANIFEST ON OVERWRITE ON;
select_into_bin_03_6_2.sql（6スレッドSELECTのSQL：BLOBテーブル#3後半用）
SELECT * FROM backup_restore_test.bin_table_03 WHERE id > 24999996 INTO OUTFILE S3 's3://def-hmatsu47-bin02/03-6p/backup_restore_test.bin_table_03_2' FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' MANIFEST ON OVERWRITE ON;
load_data_6p_03.sh（6スレッドLOADのシェルスクリプトその3）
#!/bin/sh
date
mysql -u mkadmin -h test2.XXXXXXXXXXXX.ap-northeast-1.rds.amazonaws.com --password=xxxxxxxxxx --default-character-set=utf8mb4 < load_data_bin_03_6_2.sql
date
load_data_bin_03_6_2.sql（6スレッドLOADのSQL：BLOBテーブル#3後半用）
SET FOREIGN_KEY_CHECKS=0; SET UNIQUE_CHECKS=0; LOAD DATA FROM S3 MANIFEST 's3://def-hmatsu47-bin02/03-6p/backup_restore_test.bin_table_03_2.manifest' INTO TABLE backup_restore_test.bin_table_03 FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n';
C. 結果
| テストケース | 書き出し | 読み込み | 合計 |
|---|---|---|---|
| mysqldump→リストア／1スレッド | 01:16:48 | 03:37:08 | 04:53:56 |
| SELECT→LOAD／1スレッド | 02:00:56 | 02:25:21 | 04:26:17 |
| SELECT→LOAD／2スレッド並列 | 01:04:08 | 01:28:50 | 02:32:58 |
| SELECT→LOAD／6スレッド並列 | 00:30:03 | 00:40:21 | 01:10:24 |
| SELECT→LOAD(4xlarge)／6スレッド並列 | 00:30:03 | 00:32:34 | 01:02:37 |
mysqldump→リストアは、r4.large の EC2 インスタンスをクライアントにして行いました（ダンプデータは EBS に保存）。
1 スレッドだけで実行していたときには 4 時間半掛かっていたものが、6 スレッド並列にすることで 1/4 近くまで短縮できました（mysqldump→リストアとの比較では 1/4 以下）。
なお、mysqldumpおよびリストアを使う場合も、テーブル単位でダンプを分割して並列処理することは可能です。但し、実際に試してみたところ、他スレッドとの競合でリストア処理が進まない状態が発生し、結果として所要時間があまり縮まりませんでした（LOAD DATA FROM S3との組み合わせで並列処理する場合、LOAD DATA FROM S3側にも影響が生じる）。
そのため、1 スレッドでの書き出し速度はmysqldumpに劣るものの、並列処理で問題が生じにくいSELECT INTO OUTFILE S3／LOAD DATA FROM S3を使うほうが良さそうです。
LOAD DATA FROM S3を6スレッド並列で処理しているときの Aurora インスタンスの CPU 使用率は約 90% でした。インスタンスタイプを db.r4.4xlarge に変更すると、CPU 使用率が約 55% に下がりましたが、所要時間は 8 分弱しか縮まりませんでした。
スレッド数を増やせば高速化しそうですが、インスタンスタイプの変更に片道数分掛かることを考えると、一時的にインスタンスタイプを変えて高速化を図るのはあまり得策ではないようです。
【注意1】
今回は、かなり単純な構造のテーブルでテストしましたが、
- レコード数が多い
- INDEX 数が多い／INDEX 容量が大きい
- 外部キー制約、ユニーク制約（主キー以外）などが多数ある
などの場合は、同じデータ容量でも所要時間が延びる可能性があります。
【注意2】
今回テストした中ではあまり影響が出なかったのですが、ロード（リストア）の際は、
- バイナリログ
- スロークエリログ、一般クエリログなど
は OFF にしておくことをお勧めします。