鬼気迫るMYSQL5サポート終了の対応
ダウンタイムなしでRDSのMySQL5.7インスタンスをMySQL8.0インスタンスへ移行した備忘録
ファクター
- ダウンタイムなしで可用性を確保しつつ
- この記事
- スループットで非機能要件を保ちつつ
- 新旧のベンチ観測とメトリクスを観察
- SysBenchベンチ結果はMYSQLネイティブでスループットが向上した 5 < 8
- 新旧のベンチ観測とメトリクスを観察
- リソース消費は 5 < 8 と増加したのでインスタンスタイプを1ランクスケールアップした
- ユーザーインターフェースはなにひとつ変化のない完全性を保つ
- 全クエリレベルとE2Eでクエリ由来の全ページでHTML差分検証した
- Orderステートメントのあいまいなところでちょいちょい差分が出たので修正
- テーブル名が一部8系の予約後にひっかかってエラーが出たので修正
- 全クエリレベルとE2Eでクエリ由来の全ページでHTML差分検証した
全体像
事前検証編
MySQLバージョンネイティブな問題・課題を集めるために、まずはdockerコンテナで 5 → 7 にダンプファイルをレストアしてアプリケーションをつないでみる
認証方式をプレーンに変更する(5.7互換)
phpでThe server requested authentication method unknown to the client. - それマグで！
要件 - Manual
開発環境に影響あり
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。
開発環境ではサーバーとユーザの認証方式を次のように変更した
/etc/my.cnf
[mysqld]
default_authentication_plugin=mysql_native_password
-- ユーザ作成時にプロトコルを指定
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を
8にレストアするとキャラセット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;」に変更する
# `DEFAULT CHARSET`句を正規表現で
DEFAULT CHARSET=[^(COLLATE)].*$
# まとめて置換する
DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
データベースのデフォルト
CREATE DATABASE `データベース名` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
MySQLサーバーのデフォルト
; mysqlサーバー
[mysqld]
; キャラクタセット
character-set-server=utf8mb4
; 照合順序
collation-server=utf8mb4_general_ci
; mysqlクライアントも一応
[mysql]
default-character-set=utf8mb4
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)
- サーバーデフォルトがutfmb4に統一された
character_set_systemはMySQLサーバーシステムのキャラクタセットなのでutf8mb3(3バイト)でOK
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 ...相当の設定ができるのでインスタンスレベルでレプリケーションが可能
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オプションなしでダンプファイルを取得して、バイナリログの座標はリードレプリカのスレーブコンディションから取得する。
SHOW SLAVE STATUS
Master_Log_Fille ...
Exec_Master_Log_Pos ...
移行ロードマップ
- ソースインスタンス(5.7プライマリ)のチューニング
- バイナリログの保持期間を延長する
- マニュアルレプリケーション用のユーザを作成する
- リードレプリカから起点となるダンプファイルとバイナリログ座標を取得する
- 起動する
- リードレプリカのレプリケーションを停止する
- ダンプファイルを取得する
- バイナリログの座標を取得する
- キャラクタセットと照合順序をutf8mb4に変更する
- 移行先インスタンス MySQL8.0.34を構築する
- パラメータグループ
- アプリクライント用のMySQLユーザ作成
- MySQL8インスタンスにダンプファイルをレストアする
- レプリケーション
- MySQL5
- レプリーション用ユーザーを作成する
- バイナリログの保存期間を延長する
- MySQL8
- パラメタチューニング
- レプリケーションを開始
- MySQL5
- プライベートホストゾーンで移行する
1. ソースインスタンス(5.7プライマリ)のチューニング
Amazon RDSのMySQLバイナリログ保持時間を設定する。 #AWS - Qiita
バイナリログの保持期間を延長する
マスターインスタンスのバイナリログはレプリケーションのストリームに送るとデフォルトでは即時で破棄されるため、リードレプリカ→ダンプ→移行先から移行元へのレプリケーション開始時にはロストしてしまう。そのためバイナリログの保持期間を一時的に延長する。バイナリログはインスタンスのストレージを食うので[[レプリケーション作業が完了したら元に戻す。
次のストアドで保持期間の確認と設定ができる
-- バイナリログの保持期間を確認する
CALL mysql.rds_show_configuration
-- 時間(h)で指定
CALL mysql.rds_set_configuration('binlog retention hours', 24);
マニュアルレプリケーション用のユーザを作成する
-- レプリケーション用ユーザを作成
CREATE USER 'repl'@'%' IDENTIFIED BY 'passrepl';
-- レプリケーション権限を付与
-- 余談だが、データベース単位の指定は不可だった
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';
2. リードレプリカから起点となるダンプファイルとバイナリログ座標を取得する
Amazon RDS for MySQL または MariaDB での mysqldump エラーの解決
起動する
シングルAZ、バックアップなど不要なオプションは落として、基本ソースインスタンスとコンディションを揃えて起動
リードレプリカのレプリケーションを停止する
ダンプを取得する前にレプリカでレプリケーションを停止してダンプとバイナリログ座標を固定する。
-- レプリケーション停止ストアド
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コンテナからやりました。
- 公式の
# OracleLinux8.8×MySQL8コンテナからEC2にsshポートフォワードしてます
mysqldump
-h {ソースインスタンスのエンドポイント}
-u {ユーザ名}
-p{パスワード}
set-gtid-purged=OFF -- GTIDなし
{データベース名} > mysqldump.sql
バイナリログの座標を取得する
SHOW SLAVE STATUSでレプリカごしにバイナリログ座標のスタンプをとる
# OracleLinux8.8×MySQL`コンテナからEC2にsshポートフォワードしてます
SHOW SLAVE STATUS;
> Master_Log_File ...
> Exec_Master_Log_Pos ...
charsetとcollateを補完する
ダンプファイルの全CREATE TABLEステートメントにcharsetとcollateを当てる
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→utf8mb4character_set_connection→utf8mb4character_set_database→utf8mb4character_set_filesystem→binarycharacter_set_result→utf8mb4character_set_server→utf8mb4
- 照合順序を変更する
collation_server→utf8mb4_general_cicollation_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
- 非不可逆スケールを許可する
キャラセットと照合順序を指定してデータベースを作成する
CREATE DATABASE `データベース` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
アプリクライアント用のMySQLユーザを作成する
-- ユーザ作成
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
- mysql.rds_set_external_master →
レプリケーションを開始する
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
0 Comments:
コメントを投稿