RDS - MySQL5.7から8.0へブルーグリーン移行する

<img src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/417736/b5be5d9e-c83c-2d7b-9886-f88c0e72b515.png" alt="mysql" width="156">

<img src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/417736/8b520cb1-e769-afaf-ced6-a632170ca162.png" alt="aws" width="80">


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

## ファクター

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

## 全体像

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/417736/17aabfe4-82a9-fba8-a8fc-ae5ac2a57508.png)

## 事前検証編

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

### 認証プラグイン

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

```log
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. - それマグで！](https://takuya-1st.hatenablog.jp/entry/2022/06/13/224123#google_vignette)
[要件 - Manual](https://www.php.net/manual/ja/mysqli.requirements.php)


```ini:my.cnf
[mysqld]
default_authentication_plugin=mysql_native_password
```

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

```sql: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 の書き方メモ](https://oki2a24.com/2018/10/21/how-to-set-mysql-8-cnf-to-utf8mb4/)

注意点として、キャラクタセット`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の注意点](https://gihyo.jp/dev/serial/01/mysql-road-construction-news/0157)

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

#### テーブル

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

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

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

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

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

#### MySQLサーバーのデフォルト

```ini: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: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 バージョン間のレプリケーション互換性](https://dev.mysql.com/doc/refman/8.0/ja/replication-compatibility.html)

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

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

**組める**

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

```sql: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リードレプリカのメンテナンスでダウンタイムを発生させない方法｜スクショはつらいよ](https://chariosan.com/2019/07/21/readreplica_-maintenance/)
[DB インスタンスのリードレプリカの操作 - Amazon Relational Database Service](https://docs.aws.amazon.com/ja_jp/AmazonRDS/latest/UserGuide/USER_ReadRepl.html)
[MySQL リードレプリカの使用 - Amazon Relational Database Service](https://docs.aws.amazon.com/ja_jp/AmazonRDS/latest/UserGuide/USER_MySQL.Replication.ReadReplicas.html)
[Amazon RDS for MySQL または MariaDB での mysqldump エラーの解決](https://repost.aws/ja/knowledge-center/mysqldump-error-rds-mysql-mariadb)
- RDSインスタンスではmysqldumpのバイナリログ座標オプション`--master-data`は使用不可。
  mysqldumpはREAD LOCKを要求するが、RDSではREAD LOCKに必要なユーパーユーザ権限は制限されているための次のエラーをくらう。

```sh:sh
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: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](https://qiita.com/kooohei/items/90a1ad7d3e83b1e941ec)

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

![image 2.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/417736/9722cb00-8eef-d399-b1d9-e0047b620a44.png)

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

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

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

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

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

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

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

### 2.リードレプリカから起点となるダンプファイルとバイナリログ座標を取得する
[Amazon RDS for MySQL または MariaDB での mysqldump エラーの解決](https://repost.aws/ja/knowledge-center/mysqldump-error-rds-mysql-mariadb)

![image 3.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/417736/63e83d0f-fc42-d4b0-a42e-6985fc2f0ba6.png)

#### 起動する

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

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

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

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

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

![image 4.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/417736/8deddfa7-2e62-123c-2708-bfab14a1f9ab.png)

##### 　注意ポイント

- マルチAZのRDSインスタンスはGTIDで全DBをセカンダリにレプリケーションしているため、単体DBを抜き出そうとすると対象外のGTIDも含まれるそうなので、`-set-gtid-purgeed=OFF`オプションでmysqldumpからGTIDを落とす
  [マルチAZのmysqldumpでWarningが出た](https://dev-labo.com/aws/multi-az-rds-mysqldump/)
- 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](https://kakakakakku.hatenablog.com/entry/2017/02/03/223834)

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

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

![image 5.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/417736/f54ba996-9408-b60f-758d-b70161e9f364.png)

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

```sql:sql
SHOW SLAVE STATUS;

> Master_Log_File ...
> Exec_Master_Log_Pos ...
```

#### charsetとcollateを補完する

![image 6.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/417736/7be7a819-7b9a-24a6-67d0-a6d7a8f4a1d9.png)

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

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

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

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

![image 7.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/417736/69750e03-da7f-cfc3-e4b4-07e14231d4e9.png)

#### 　パラメータグループ

パラメータグループファミリーは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:sql
CREATE DATABASE `データベース` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
```

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

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

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

![image 8.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/417736/4c0288e9-fca7-0fba-28dc-0bc2d9493d0c.png)

気になっていたこと

- `log_slave_updates`が有効になっている?
  → パラメータグループでデフォルト1になっていた
- `server_id`がソースインスタンスと被っていない?
  → `select @@server_id`で確認可能。パラメータグループで自動的にユニークになっている
  [MySQL :: MySQL 8.0 Reference Manual :: 17.1.2.2 Setting the Replica Configuration](https://dev.mysql.com/doc/refman/8.0/en/replication-howto-slavebaseconfig.html)

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

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

![image 9.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/417736/9f113ef1-57d6-947b-4f80-0638ccd42202.png)

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

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

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

```sql: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. プライベートホストゾーンでインスタンスのエンドポイントを切り替える
---

![image 10.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/417736/81a1165a-c3a6-52ae-ef6e-074b5d953c15.png)

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