プラットフォーム基盤推進室 DBRE Group https://www.bizreach.co.jp/ mysql casual talks#13 文字コード変更対応をしている話

➠ この資料は @_awache という個人の独断と偏見により作成したものです。 ➠ 所属する組織の意見を代表するものではありません。 ➠ 合法性や安全性、情報の正確性についても保証できません。 カジュアルな会(のはず)なので内容もノリも軽めです。 気軽に楽しんでいただければと思います。 README

_awache mysql > SELECT * FROM me \G *********** 1. row ********** name: 粟田 啓介 nickname: あわっち company: 株式会社 ビズリーチ title: DBRE twitter: @_awache 1 rows in set (0.00 sec) 自己紹介

Database 文字コード変更対応

文字コード変更 ➠ ミッション ↳ 夜間メンテの数時間でメンテ作業が完了すること ↳ チェックをしたい時にチェックができること ↳ 稼働中のサービスそのものに影響を与えないこと とある Database の文字コードを utf8mb4 に変換！

文字コード変更 ➠ とあるサービスではまだ utf8 を利用している ↳ ユーザー利便性の低下 ↳ 当たり前だけど特定の文字が挿入できない ↳ フリーテキストに頑張ってユーザーが入力してくれた情報もたった1文字のためにエラー ↳ 大多数のユーザーはその1文字が何なのか分からない(と思う) ERROR 1366 (HY000): Incorrect string value: '\xF0\xA6\x9A\xB0' for column 'txt' at row 1 ↳ そのままにしておくとユーザーは離れてしまう ↳ 技術的負債 ↳ 例えば他サービスのデータを取り込む仕組みなど ↳ データ取り込み元が utf8mb4 で、データ入れ先が utf8 だった場合、エラーが頻発する ↳ サービスを運用するエンジニアも疲弊 ↳ 今後 utf8 は無くなっていく流れ ↳ 直前で焦るより、今対応をする方が心理的安全性も遥かに高い ↳ 焦らず様々な方針を模索して対応するなら今がチャンス RDS 上での utf8 -> utf8mb4 対応

移行方法検討 ➠ ALTER TABLE ➠ mysqldump -> import ➠ unload -> load ➠ RDS replica いくつか検討したやり方

移行方法検討 ➠ サービス停止は許容するものの、停止時間をできる限り短くする ↳ サイズが大きめのテーブルの存在 ↳ ALTER TABLE, dump -> import, unload -> load いずれも相当時間がかかってしまった ↳ 1テーブルで ALTER TABLE が 40時間超 ↳ 流石にサービス停止許容と言えどもこれは無理が。。 ↳ RDS replica ↳ テーブルの型違いが吸収できず断念。。 ➠ (COLLATION は utf8mb4_0900_ai_ci を選択) 検討事項

binlog を使った文字コード変更対応

移行方法検討 ➠ binlog からクエリを生成してコピーしたインスタンスに流せばいける？ ↳ binlog2sql (https://github.com/danfengcao/binlog2sql) を使ってみた ↳ binlog ファイルからクエリを生成 binlog_format = row_ binlog_row_image = full ↳ なんかうまくいきそうなので全体戦略を検討 ↳ 移行にかかる時間を最小限にする ↳ 一時的にであればインスタンス数が倍になったとしても許容する ↳ 作り込みすぎない ↳ シンプルに binlog が rotate されたらそのファイルを取り込んで実行 binlog から戻す方法

移行方法検討 ➠ 文字コード変更に必要な時間を極小化 ↳ サービスを停止できる時間は長くても夜間数時間程度 ↳ この間に確実に文字コード変更が行えること ➠ チェックをしたいときに定点が取れる仕組みであること ↳ データを扱う上で定点を取ってデータを確認できる状態にすることはとても重要 ↳ データの不整合はサービスの死に直結 ➠ サービス継続をするためのインスタンス群に与える影響が小さい ↳ START SLAVE の際に一気にデータが流れ込むこととIOのクレジットだけ気にすればいい？ ↳ オンプレなら SQL_THREAD だけ止めれた。。 ↳ mysql > STOP SLAVE SQL_THREAD; ↳ RDS だと出来なそう(調べられてないだけ？) ↳ とは言え、START SLAVE と STOP SLAVE を負荷を見ながら交互に実行する形でも対応可能 実現したかったこと(MUST)

プロダクトアカウント 構成 稼働中 RDS Master 全体イメージ

プロダクトアカウント 構成 稼働中 RDS Master New Replica 全体イメージ RDS Replication

プロダクトアカウント 構成 稼働中 RDS Master New Replica 全体イメージ RDS Replication Create Snapshot

プロダクトアカウント 構成 稼働中 RDS Master New Replica 全体イメージ RDS Replication Create Snapshot Restore Snapshot

プロダクトアカウント 構成 稼働中 RDS Master New Replica 全体イメージ RDS Replication Change Charset New Master utf8mb4 に変換済みのスキーマ

プロダクトアカウント 構成 稼働中 RDS Master New Replica 全体イメージ New Master utf8mb4 に変換済みのスキーマ RDS Replication - 3分毎に cloudwatch イベントから ECS を起動(RDS はデフォルトで5分毎にbinlog ローテート) - 初期データ投入時は処理が15分以内に終わらなかったため Lambda だけではできず - binlog のローテートのタイミングで新しい binlog を取り込み - SQL Statement を New Master に対して実行

プロダクトアカウント 構成 稼働中 RDS Master New Replica 全体イメージ New Master utf8mb4 に変換済みのスキーマ STOP SLAVE - 3分毎に cloudwatch イベントから ECS を起動(RDS はデフォルトで5分毎にbinlog ローテート) - 初期データ投入時は処理が15分以内に終わらなかったため Lambda だけではできず - binlog のローテートのタイミングで新しい binlog を取り込み - Statement を New Master に対して実行 この更新を止めれば New Replica には更新 STATEMENT が伝搬 されてこないので New Replica <-> New Master 間で定点が取得 できている状態になる

Initial データ作成

Initial データ作成 ➠ 割と飛ばされることが多いこの部分 ↳ 単純に文字コード変更をすればいいといえばそれまで ↳ とはいえなんだかんだ一番大変だったので軽く紹介 Initial データの作成に関して

プロダクトアカウント 構成 稼働中 RDS Master New Replica 1. Create New Replica Initial データ作成方法

プロダクトアカウント 構成 稼働中 RDS Master New Replica 1. Create New Replica 2. STOP SLAVE Initial データ作成方法

プロダクトアカウント 構成 稼働中 RDS Master New Replica 1. Create New Replica 2. STOP SLAVE Initial データ作成方法 3. Create Snapshot

プロダクトアカウント 構成 稼働中 RDS Master New Replica 1. Create New Replica 2. STOP SLAVE Initial データ作成方法 New Master (utf8) 4. Restore Snapshot 3. Create Snapshot

プロダクトアカウント 構成 Initial データ作成方法 New Master (utf8) 1. GET TARGET SCHEMA mysql > SELECT______________________________________________________________________________ SCHEMA_NAME_____________________________________________________________________ FROM________________________________________________________________________________ information_schema.SCHEMATA_____________________________________________________ WHERE_______________________________________________________________________________ SCHEMA_NAME NOT IN (’information_schema’, ’mysql’, ’sys’, ’performance_schema’); New Master (utf8mb4) New Master に対して ALTER(15パラ) で変更

プロダクトアカウント 構成 Initial データ作成方法 New Master (utf8) 1. GET TARGET SCHEMA mysql > SELECT______________________________________________________________________________ SCHEMA_NAME_____________________________________________________________________ FROM________________________________________________________________________________ information_schema.SCHEMATA_____________________________________________________ WHERE_______________________________________________________________________________ SCHEMA_NAME NOT IN (’information_schema’, ’mysql’, ’sys’, ’performance_schema’); 2. GET TARGET COLUMNS mysql > SELECT______________________________________________________________________________ TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME___________________________________________ FROM________________________________________________________________________________ information_schema.COLUMNS _____________________________________________________ WHERE_______________________________________________________________________________ CHARACTER_SET_NAME = ’utf8’ AND ________________________________________________ SCHEMA_NAME IN (’SCHEMA_NAME’, ...)_____________________________________________ GROUP BY____________________________________________________________________________ TABLE_NAME, TABLE_SCHEMA;≈≈≈____________________________________________________ New Master (utf8mb4) New Master に対して ALTER(15パラ) で変更

プロダクトアカウント 構成 Initial データ作成方法 New Master (utf8) 1. GET TARGET SCHEMA mysql > SELECT______________________________________________________________________________ SCHEMA_NAME_____________________________________________________________________ FROM________________________________________________________________________________ information_schema.SCHEMATA_____________________________________________________ WHERE_______________________________________________________________________________ SCHEMA_NAME NOT IN (’information_schema’, ’mysql’, ’sys’, ’performance_schema’); 2. GET TARGET COLUMNS mysql > SELECT______________________________________________________________________________ TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME___________________________________________ FROM________________________________________________________________________________ information_schema.COLUMNS _____________________________________________________ WHERE_______________________________________________________________________________ CHARACTER_SET_NAME = ’utf8’ AND ________________________________________________ SCHEMA_NAME IN (’SCHEMA_NAME’, ...)_____________________________________________ GROUP BY____________________________________________________________________________ TABLE_NAME, TABLE_SCHEMA;≈______________________________________________________ 3. CREATE SCHEMA CHANGE DDL 1 で取得したレスポンスを元に ALTER DATABASE SCHEMA_NAME CHARACTER SET utf8mb4; 2 で取得したレスポンスを元に ALTER TABLE TABLE_NAME CONVERT TO CHARACTER SET utf8mb4; New Master (utf8mb4) New Master に対して ALTER(15パラ) で変更

プロダクトアカウント 構成 Initial データ作成方法 New Master (utf8) 1. GET TARGET SCHEMA mysql > SELECT______________________________________________________________________________ SCHEMA_NAME_____________________________________________________________________ FROM________________________________________________________________________________ information_schema.SCHEMATA_____________________________________________________ WHERE_______________________________________________________________________________ SCHEMA_NAME NOT IN (’information_schema’, ’mysql’, ’sys’, ’performance_schema’); 2. GET TARGET COLUMNS mysql > SELECT______________________________________________________________________________ TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME___________________________________________ FROM________________________________________________________________________________ information_schema.COLUMNS _____________________________________________________ WHERE_______________________________________________________________________________ CHARACTER_SET_NAME = ’utf8’ AND ________________________________________________ SCHEMA_NAME IN (’SCHEMA_NAME’, ...)_____________________________________________ GROUP BY____________________________________________________________________________ TABLE_NAME, TABLE_SCHEMA;≈______________________________________________________ 3. CREATE SCHEMA CHANGE DDL 1 で取得したレスポンスを元に ALTER DATABASE SCHEMA_NAME CHARACTER SET utf8mb4; 2 で取得したレスポンスを元に ALTER TABLE TABLE_NAME CONVERT TO CHARACTER SET utf8mb4; 4. Execute ALTER DDL (15 パラ) New Master (utf8mb4) New Master に対して ALTER(15パラ) で変更

Initial データ作成 ➠ これで utf8 のテーブルの文字コードが utf8mb4 に変更 ↳ パラレル度をあげたところで結局一番大きなテーブルが時間かかった ↳ 自分たちの環境で効率が一番よかった15パラを選択 ↳ とはいえ42時間くらい時間がかかる ↳ メンテナンス 42時間は流石に長すぎ ↳ convert to を使って行なった場合 ↳ text -> mediumtext など可変長型は convert to で型変更されてしまう ↳ アプリケーションとスキーマの型が密結合しているような ORM を使っている場合は要注意 ↳ スキーマ定義書などを別管理している場合はその変更を更新しておかないと忘れた頃にびっくりする(カモ) Initial データの作成に関して

Initial データ作成 ➠ 大きな落とし穴が。。 ↳ 一部カラムにて BINARY を明示している箇所の存在 ↳ 下記のクエリ結果を New Replica 側から取得 ↳ mysql > SELECT______________________________________________________________________________ TABLE_NAME, COLUMN_NAME, COLUMN_TYPE, COLLATION_NAME, IS_NULLABLE,______________ COLUMN_DEFAULT, COLUMN_COMMENT, CHARACTER_SET_NAME______________________________ FROM________________________________________________________________________________ information_schema.COLUMNS _____________________________________________________ WHERE_______________________________________________________________________________ COLLATION_NAME = ’utf8_bin’ AND ________________________________________________ SCHEMA_NAME = 'SCHEMA_NAME';____________________________________________________ ↳ 下記のクエリを New Replica 側で取得してから ALTER TABLE 文を動的に生成、実行 ↳ mysql > ALTER TABLE_________________________________________________________________________ schema_name.table_name__________________________________________________________ MODIFY _____________________________________________________________________________ COLUMN_NAME ___________________________________________________________________ COLUMN_TYPE ___________________________ ___________________________ ____________ CHARACTER SET utf8mb4 __________________________________________________________ COLLATE utf8mb4_bin ____________________________________________________________ IS_NULLABLE ____________________________________________________________________ COLUMN_DEFAULT _________________________________________________________________ COLUMN_COMMENT; ________________________________________________________________ Initial データの作成に関して

プロダクトアカウント 構成 稼働中 RDS Master New Replica ここまでできたら Replication 開始して想定していた構成が完成 New Master utf8mb4 に変換済みのスキーマ RDS Replication - 3分毎に cloudwatch イベントから ECS を起動(RDS はデフォルトで5分毎にbinlog ローテート) - 初期データ投入時は処理が15分以内に終わらなかったため Lambda だけではできず - binlog のローテートのタイミングで新しい binlog を取り込み - SQL Statement を New Master に対して実行

その後

とある勉強会の帰りに神様がいて ➠ slave_type_conversions に関して ↳ Master - Slave 間の型の違いをある意味吸収してくれるオプション ↳ Master - Slave 間でスキーマに違いがあるなんて考えたくは無いけど ↳ この時ばかりは神オプション ↳ ALL_NON_LOSSY で対応 ↳ 詳しくはこちらをご確認ください ↳ https://gihyo.jp/dev/serial/01/mysql-road-construction-news/0040 「それ slave_type_conversions でいけません？」

ここで現実に戻ってみる ➠ RDS to RDS で binlog を使った Replication できるのか？ ↳ 正直やったことも考えたこともなかったので検証してみた ↳ 割とサクッとできた！ ↳ mysql > CALL mysql.rds_set_external_master (__________ 'new replica_endpoint',___________________________ 3306,_____________________________________________ 'master_user_name',_______________________________ 'master_password',________________________________ 'binlog_file_name',_______________________________ binlog_position___________________________________ ):____________________________________________________ 僕たちの環境は RDS RDS Master RDS Slave Start Replication

プロダクトアカウント 構成 稼働中 RDS Master New Replica Replication 構成がただの多段レプリに！ New Master (utf8mb4) Replication

自分たちの環境に充ててみる ➠ 基本のキができてないやつ ↳ あえて晒す ↳ log_slave_updates 忘れてた ↳ MySQL 業界みんな助けてくれる ↳ 皆様、本当にありがとうございますｍｍ ↳ 僕もお返しできるように頑張りますｍｍ ここでも神様登場

プロダクトアカウント Check 稼働中 RDS Master New Replica Replication Stop Slave をするだけで定点を取得 New Master (utf8mb4) Replication

プロダクトアカウント Check 稼働中 RDS Master New Replica Stop Slave をするだけで定点を取得 New Master (utf8mb4) Replication アプリケーションからのリクエストが来ないため、 全量チェック、ランダムチェック、Count チェックなど 安全に行える 稼働中の Master で発生した Slow Query を流すなどして パフォーマンス検証 (& 暖気)が行える

Master 昇格 ➠ RESET SLAVE ↳ New Replica と New Master の Replication を停止 ↳ mysql > CALL mysql.rds_stop_replication;_ ↳ New Master の Slave 情報を Reset ↳ mysql > CALL mysql.rds_reset_external_master;_ ↳ パラメータグループを変更(MySQL の my.cnf みたいなもの) ↳ slave_type_conversions を戻すなど Replication の対応のために使ったパラメータを戻す RDS の Master 昇格は割とシンプル

プロダクトアカウント Master 昇格 稼働中 RDS Master New Replica Replication Stop Slave とアプリケーションの向き先変更 New Master (utf8mb4) Replication

プロダクトアカウント Master 昇格 稼働中 RDS Master New Replica Replication Stop Slave とアプリケーションの向き先変更 New Master (utf8mb4)

結果 ➠ ミッション ↳ 夜間メンテの数時間でメンテ作業が完了すること ↳ 事前に文字コード変更を行なった Database に対して Replication をしておくことで メンテ当日作業は切り替え作業のみで対応可能となった ↳ チェックをしたい時にチェックができること ↳ 稼働中の Master と Replication している Slave を停止することで定点の取得が可能になった ↳ 稼働中のサービスそのものに影響を与えないこと ↳ 稼働中の Database と切り離した環境を作ることでサービスへの影響を極小化 とある Database の文字コードを utf8mb4 に変換可能な状態に！

今のステータス ➠ プロダクトに所属していない僕たちが出来るところは一旦ここまで ↳ アプリケーション修正 ↳ 他のアプリケーションとの連携周りの調査、改修 などなど、プロダクト側ではまだやることがある ➠ `今のところ` この対応で問題にはなってない ↳ これから問題が出てくるかもしれないが出てきたところで地道に対応していく ↳ 出てきたらまたどこかで報告しますｍｍ RDS の文字コード変更に関してはクリア

まとめ

まとめ ➠ RDS もオンプレも対応方法は変わらなかった ↳ Slave 立てて slave_type_conversions を ALL_NON_LOSSY に ↳ 普通にレプリケーションして対応 ↳ 時に使い捨て、と割り切ってインスタンスを立てれる MySQL は楽しい ➠ MySQL のコミュニティには神々がいる ↳ 困ったらコミュニティの人たちに頼ることも大切 ↳ 同時に自分もちゃんとお返しできるように勉強する ➠ 北川さん(@keny_lala さん)はすごい人 DB の文字コード変更

Thank you!