ダウンタイムなしでRDSのMySQL5.7インスタンスをMySQL8.0インスタンスへ移行した備忘録

ファクター

ダウンタイムなしで可用性を確保しつつ、
- この記事
スループットで非機能要件を保ちつつ
- 新旧のベンチ観測とメトリクスを観察
  - SysBenchベンチ結果はMYSQLネイティブでスループットが向上した 5 < 8
リソース消費は 5 < 8 と増加したのでインスタンスタイプを1ランクスケールアップした
ユーザーインターフェースはなにひとつ変化のない完全性を保つ
- 全クエリレベルとE2Eでクエリ由来の全ページでHTML差分検証した
  - Orderステートメントのあいまいなところでちょいちょい差分が出たので修正
  - テーブル名が一部8系の予約後にひっかかってエラーが出たので修正

全体像

事前検証編

MySQLバージョンネイティブな問題・課題を集めるために、まずはdockerコンテナで MySQL5のダンプファイルをMySQL8にレストアしてアプリケーションから繋いでみる

認証プラグイン

PHP5系のPDOドライバで接続すると認証エラーがでる

SQLSTATE[HY000] [2054] The server requested authentication method unknown to the client**

MySQL8からクライアント認証方式default_authentication_pluginのデフォルトがハッシュ強制caching_sha2_passwordに変更になった。
PHP5以前のPDOはcaching_sha2_passwordに対応していないため、MySQLサーバーのdefault_authentication_pluginとユーザーのIDENTIFIED WITHで認証プラグインをmysql_native_password(平文)にデグレードする
RDSのMySQL8デフォルトパラメーターグループではdefault_authentication_plugin=mysql_native_passwordなので対応不要であった
MySQLとアプリケーションのトラフィックにインターネットは経由しないのでOK。

よって、開発環境では認証プラグインをmysql_native_passwordに変更した。
phpでThe server requested authentication method unknown to the client. - それマグで！
要件 - Manual

my.cnf

[mysqld]
default_authentication_plugin=mysql_native_password

ユーザーの認証プラグインを変更

sql

-- ユーザ作成時にプラグインを指定
CREATE USER `user`@`%` IDENTIFIED WITH mysql_native_password  BY 'パスワード'; 
-- あるいは更新
ALTER USER `user`@`%` IDENTIFIED WITH mysql_native_password BY 'パスワード';

utf8はutf8mb3になった

MySQL8においてはutf8はutf8mb3に名詞変更された

MySQL5から取得したダンプファイルのCRATE TABLE ....ステートメントのキャラセットutf8と照合順序utf8_general_ciをMySQL8にレストアするとキャラセットutf8mb3照合順序utf8mb3_general_ciになる

utf8mb4にする

utfmb3はデプレケイト予定でいずれ移行は避けられないならutf8mb4に対応していく。
MySQL 8 のデフォルト文字セット系をすべて utf8mb4 にする cnf の書き方メモ

注意点として、キャラクタセットutf8mb4かつ照合順序が未定義のテーブルをMySQL8にインポートするとデフォルトでは照合順序にutf8mb4_0900_ai_ciが充てられるが、旧世代(php5系)のPDOでは解釈できない。これはMVCフレームワークを使っている場合に、ORM層がMySQLにメタデータをリクエストしてPDOにパスしたときにエラーとして顕在化する。

そもそも環境影響を受けないためにもテーブル定義ではutf8mb4_general_ciなど旧世代でも互換性のある照合順序をセットで定義してやる。

つまり、CREATE TABLE文にDEFAULT CHARSET=utf8mb4のみ記述すると、collation_serverシステム変数に設定されているサーバーのデフォルトcollationでもデータベースレベルのcollationでもない、MySQLのデフォルトcollationが設定されてしまうのです。CREATE TABLE文を実行するときは予期せぬトラブルを避けるため、DEFAULT CHARSET=xx COLLATE=xxを省略せずに記述するのが良いでしょう。
第157回　MySQLのデフォルトcollationの注意点

ということでテーブルをはじめ、未定義のエスレーション先である、データベース > サーバーに対してもキャラセットにutf8mb4と照合順序にutf8mb4_general_ciをがっつり設定していく。

テーブル

ダンプファイルのすべてのCREATE TABLEステートメントに対して次の正規表現で「DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;」に変更する

.sql

# `DEFAULT CHARSET`句を正規表現で
DEFAULT CHARSET=[^(COLLATE)].*$

# まとめて置換する
DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

データベースのデフォルト

sql

CREATE DATABASE `データベース名` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

MySQLサーバーのデフォルト

my.cnf

; mysqlサーバー [mysqld]
; キャラクタセット character-set-server=utf8mb4
; 照合順序 collation-server=utf8mb4_general_ci 
; mysqlクライアントも一応
[mysql] default-character-set=utf8mb4

MySQLサーバーのキャラクタセットを確認

サーバーデフォルトがutfmb4に統一された
character_set_systemはMySQLサーバーシステムのキャラクタセットなのでutf8mb3(3バイト)でOK

sql

mysql> show variables like 'character%';
+--------------------------+--------------------------------+
| Variable_name            | Value                          |
+--------------------------+--------------------------------+
| character_set_client     | utf8mb4                        |
| character_set_connection | utf8mb4                        |
| character_set_database   | utf8mb4                        |
| character_set_filesystem | binary                         |
| character_set_results    | utf8mb4                        |
| character_set_server     | utf8mb4                        |
| character_set_system     | utf8mb3                        |
| character_sets_dir       | /usr/share/mysql-8.0/charsets/ |
+--------------------------+--------------------------------+
8 rows in set (0.00 sec)

MySQLバージョン違いのレプリケーション

ブルーグリーン移行に備えて移行先インスタンスを現行インスタンスとミラーリングホットスタンバイ状態にしておくため、RDSインスタンス間でマニュアルレプリケーションが組めるか調査

そもそもMySQLネイティブでバージョン違いのレプリケーションはできるのか

できる

次を除いて、1世代(5=>8) までのレプリケーションがサポートされている
MySQL :: MySQL 8.0 リファレンスマニュアル :: 17.5.2 MySQL バージョン間のレプリケーション互換性

バイナリログフォーマットがステートメントベースではないことMySQL&MariaDBのbinlog formatの話
後方互換性のない定義を含まないこと(たとえば、64 文字を超える外部キー名は、MySQL 8.0 からサポートされない)

RDSインスタンス間でマニュアルレプリケーションが組めるか

組める

マルチAZ構成にしているからかバイナリログを出力しているし、MySQLネイティブレベルのチューニングが可能
レプリケーション絡みのコマンドは権限がないため蹴られるが、次の代替のストアドでCHANGE MASTER TO ...相当の設定ができるのでインスタンスレベルでレプリケーションが可能
RDSをAuroraのスレーブにする

sql

CALL mysql.rds_set_external_master ('host.to.endpoint', 3306, 'repl', 'repl', 'mysql-bin-changelog.000003', 481507, 0);

ダンプとバイナリログ座標の取得

ダンプファイルとバイナリログ座標をどこから取るか。

mysqldumpはI/O停止が発生するため稼働中の移行元5.7のプライマリインスタンスには打ちたくない。
マルチAZセカンダリインスタンスはエンドポイント非公開なので打てない。
スナップショット復元インスタンスは立ち上げた瞬間から別系統なのでダンプもバイナリログもどう動いているかわからずレプリケーションのソースにならない。
リードレプリカはマスタのレプリケーション状態で起動するので、レプリケーションを停止すればダンプファイルと&マスタのバイナリログ座標をキャプチャできる。
ただし、リードレプリカの起動にはスナップショットを必要とするためシングルAZではI/O停止が発生するが、マルチAZ配置ならスナップショットをセカンダリからとってくれるのでサービス影響の心配はない。
RDSリードレプリカのメンテナンスでダウンタイムを発生させない方法｜スクショはつらいよ
 DB インスタンスのリードレプリカの操作 - Amazon Relational Database Service
MySQL リードレプリカの使用 - Amazon Relational Database Service
Amazon RDS for MySQL または MariaDB での mysqldump エラーの解決
RDSインスタンスではmysqldumpのバイナリログ座標オプション--master-dataは使用不可。
mysqldumpはREAD LOCKを要求するが、RDSではREAD LOCKに必要なユーパーユーザ権限は制限されているための次のエラーをくらう。

mysqldump: Couldn't execute 'FLUSH TABLES WITH READ LOCK': Access denied for user 'user'@'%' (using passw
ord: YES) (1045)

--master-data オプションは FLUSH TABLES WITH READ LOCK を取得します。これには、Amazon RDS マスターのユーザーが持っていない SUPER 権限が必要です。また、Amazon RDS は GLOBAL READ LOCK をサポートしていません。

よって、リードレプリカから--master-dataオプションなしでダンプファイルを取得して、バイナリログの座標はリードレプリカのスレーブコンディションから取得する。

sql

SHOW SLAVE STATUS 

Master_Log_Fille ...
Exec_Master_Log_Pos ...

移行ロードマップ

ソースインスタンス(5.7プライマリ)のチューニング
- バイナリログの保持期間を延長する
- マニュアルレプリケーション用のユーザを作成する
リードレプリカから起点となるダンプファイルとバイナリログ座標を取得する
- 起動する
- リードレプリカのレプリケーションを停止する
- ダンプファイルを取得する
- バイナリログの座標を取得する
- キャラクタセットと照合順序をutf8mb4に変更する
移行先インスタンス MySQL8.0.34を構築する
- パラメータグループ
- アプリクライント用のMySQLユーザ作成
- MySQL8インスタンスにダンプファイルをレストアする
- レプリケーション
  - MySQL5
    - レプリーション用ユーザーを作成する
    - バイナリログの保存期間を延長する
  - MySQL8
    - パラメタチューニング
    - レプリケーションを開始
プライベートホストゾーンで移行する

1. ソースインスタンス(5.7プライマリ)のチューニング

Amazon RDSのMySQLバイナリログ保持時間を設定する。 #AWS - Qiita

バイナリログの保持期間を延長する

マスターインスタンスのバイナリログはレプリケーションのストリームに送るとデフォルトでは即時で破棄されるため、リードレプリカ→ダンプ→移行先から移行元へのレプリケーション開始時にはロストしてしまう。そのためバイナリログの保持期間を一時的に延長する。バイナリログはインスタンスのストレージを食うので[[レプリケーション作業が完了したら元に戻す。

次のストアドで保持期間の確認と設定ができる

sql

-- バイナリログの保持期間を確認する
CALL mysql.rds_show_configuration

-- 時間(h)で指定
CALL mysql.rds_set_configuration('binlog retention hours', 24);

マニュアルレプリケーション用のユーザを作成する

sql

-- レプリケーション用ユーザを作成
CREATE USER 'repl'@'%' IDENTIFIED BY 'passrepl';

-- レプリケーション権限を付与
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';

2.リードレプリカから起点となるダンプファイルとバイナリログ座標を取得する

Amazon RDS for MySQL または MariaDB での mysqldump エラーの解決

起動する

シングルAZ、バックアップなど不要なオプションは落として、基本ソースインスタンスとコンディションを揃えて起動

リードレプリカのレプリケーションを停止する

ダンプを取得する前にレプリカでレプリケーションを停止してダンプとバイナリログ座標を固定する。

sql

-- レプリケーション停止ストアド
CALL mysql.rds_stop_replication;

ダンプファイルを取得する

　注意ポイント

マルチAZのRDSインスタンスはGTIDで全DBをセカンダリにレプリケーションしているため、単体DBを抜き出そうとすると対象外のGTIDも含まれるそうなので、-set-gtid-purgeed=OFFオプションでmysqldumpからGTIDを落とす
マルチAZのmysqldumpでWarningが出た
mysqldumpエラー
- 公式のOracleLinux7.9×MySQL5コンテナのmysqldumpを使うとunknow option --connect-expired-passwordエラーで動かなかったので、OracleLinux8.8×MySQL8コンテナからやりました。
==--master-dataオプションは使用不可であった==バイナリログとポジションを把握するために--master-dataオプションをつけるとdumpファイルにCHANGE MASTER TO ...が入ってしまうので、--master-data=2でコメント化させる
MySQL on EC2 → Aurora にレプリケーションをしてみた - kakakakakku blog

-- `OracleLinux8.8`×`MySQL8`コンテナからEC2にsshポートフォワードしてます
mysqldump
-h {ソースインスタンスのエンドポイント}
-u {ユーザ名}
-p{パスワード}
--set-gtid-purged=OFF -- GTIDなし
{データベース名} > mysqldump.sql

バイナリログの座標を取得する

SHOW SLAVE STATUSでレプリカごしにダンプファイル時点のバイナリログ座標のスタンプをとる

sql

SHOW SLAVE STATUS;

> Master_Log_File ...
> Exec_Master_Log_Pos ...

charsetとcollateを補完する

ダンプファイルの全CREATE TABLEステートメントにcharsetとcollateを当てる

dump.sql

-- `DEFAULT CHARSET`句を正規表現で
DEFAULT CHARSET=[^(COLLATE)].*$

-- まとめて置換する
DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

3. 移行先インスタンス MySQL8.0.34を構築する

　パラメータグループ

パラメータグループファミリーはmysql8.0を使用する

認証プラグイン
- default_authentication_plugin → mysql_native_password
  → 変更不可でが適応済
キャラクタセットを変更する
- character_set_client → utf8mb4
- character_set_connection → utf8mb4
- character_set_database → utf8mb4
- character_set_filesystem → binary
- character_set_result → utf8mb4
- character_set_server → utf8mb4
照合順序を変更する
- collation_server → utf8mb4_general_ci
- collation_connection → utf8mb4_general_ci
タイムゾーンを変更する
- time_zone → Asia/Tokyo
クライアントオプションを変更する
- クライアントのキャラクタセット指定を無視 init_connect → SET NAMES utf8mb4
- クライアントのキャラクタセット指定を無視 skip-character-set-client-handshake → 1
レプリケーションの非不可逆スケールを許可する
- 非不可逆スケールを許可する slave_type_conversions → ALL_NON_LOSSY

キャラセットと照合順序を指定してデータベースを作成する

sql

CREATE DATABASE `データベース` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

アプリクライアント用のMySQLユーザを作成する

sql

-- ユーザ作成
CREATE USER `ユーザ`@`%` IDENTIFIED BY 'パスワード';
-- ユーザ権限とフラッシュ
GRANT ALL ON `データベース`.* TO `ユーザ`@`%`; FLUSH PRIVILEGES;

4. ダンプファイルで移行先インスタンスをレストアする

気になっていたこと

log_slave_updatesが有効になっている?
→ パラメータグループでデフォルト1になっていた
server_idがソースインスタンスと被っていない?
→ select @@server_idで確認可能。パラメータグループで自動的にユニークになっている
MySQL :: MySQL 8.0 Reference Manual :: 17.1.2.2 Setting the Replica Configuration

# SSHトンネル経由でレストア
mysql -u root -p -P 3307 -h 127.0.0.1 レストア先のデータベース名 < ダンプファイル

5. 移行先インスタンスから移行元インスタンスへレプリケーションを開始する

レプリケーション系のストアド

RDSではCHANGE MASTER TO ...とかレプリケーション関連コマンドは制限されていて、かわりに次のプロシージャが用意されているMySQL on EC2 → Aurora にレプリケーションをしてみた - kakakakakku blog
- mysql.rds_set_external_master → CHANGE MASTER TO
- mysql.rds_start_replication → START SLAVE
- mysql.rds_stop_replication → STOP SLAVE

レプリケーションを開始する

sql

mysql> CALL mysql.rds_set_external_master ('5.7インスタンスのエンドポイント', 3306, 'repluser', 'password', 'bin.000001', 12345, 0);
mysql> CALL mysql.rds_start_replication;
mysql> SHOW SLAVE STATUS\G

6. プライベートホストゾーンでインスタンスのエンドポイントを切り替える

-- DNS解決が安定するまで見守る
watch -n 5 dig エンドポイントのDNS