LoginSignup
0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

@bigbridge(大橋 正明)

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

Posted at
mysql aws

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

ファクター

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

全体像

image.png

事前検証編

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);

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

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

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
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

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

image 2.png

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

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

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 エラーの解決

image 3.png

起動する

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

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

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

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

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

image 4.png

　注意ポイント

  • マルチ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

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

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

image 5.png

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

sql
SHOW SLAVE STATUS;

> Master_Log_File ...
> Exec_Master_Log_Pos ...

charsetとcollateを補完する

image 6.png

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

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

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

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

image 7.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
CREATE DATABASE `データベース` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

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

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

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

image 8.png

気になっていたこと

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

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

image 9.png

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

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

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

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

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?