mysql5.7系のRDSをVPS上のmysql8へリプレイスした備忘録

目的

MySQL5系のシステムをMySQL8系へ移行する

後述するが、MySQL8は5.7から大幅に仕様が変更されていて、特にリモート接続で利用するときに従来にない追加の設定が必要になる。

これまでMySQL5.7を採用して運用してきたが、MySQL8がRedhat/CentOS系やDebian/Ubuntu系でサポートされて接続アダプタも対応しはじめてきたので、MySQL8を検証して問題がなければ本番環境も移行する。

セキュリティと性能が確保できればRDSをVPS上のMySQLへ置き換える

私的なサービスでAWSのAmazonRDSを利用しているのだが、コストパフォーマンスが悪く、カタログスペック以上に性能が低い。このRDSで運用しているMySQLをAWS外のMySQLへ置き換える事を検討している。
MySQL8からリモート接続の仕様がSSL通信がデフォルトに、かつ認証アダプタがsha2のハッシュ暗号化になり、リモート接続時のレスポンス低下を回避しながらセキュリティが大幅に向上している。

---

具体的施策

事前検証

開発環境＆ステージング環境でMySQL8へアップグレードして検証

MySQL5.7からMySQL8への接続はデフォルトの認証アダプタが異なるため接続できない等の問題点が多いので、開発環境やステージング環境をMySQL8へアップグレードして検証してから、更に本番環境でもMySQL8を検証する。

MySQL接続アダプタ mysql.data.dll / mysql.data.jar 等の検証

アダプタがMySQL8以降のデフォルトになった認証アダプタ caching_sha2_password をサポートしているかどうかの情報が少ない。

利用するアダプタと同じバージョンのmysql.data.dll や mysql.data.jar を使ってmysqlclientで接続テストをしておく。

具体的な設定

MySQL8用のssl鍵を作成する

mkdir ssltmp
cd ssltmp
vim gen-mysqlsql.sh

SSL/TSL鍵の生成スクリプト

#!/bin/bash
## etc
datadir=/etc/mysql/ssl
if [ ! -d $datadir ]; then
sudo mkdir -p $datadir
sudo chown mysql: $datadir -Rf
fi
# Generate certs if needed
if [ ! -e "${datadir}/server-key.pem" ] ; then
echo mysql_ssl_rsa_setup
sudo mysql_ssl_rsa_setup --datadir="$datadir" --uid=mysql
fi

実効権限を付与してスクリプト実行

chmod +x ./gen-mysqlsql.sh
./gen-mysqlsql.sh

my.cnf相当の設定ファイルのmysqldセクションへpemを登録

• ubuntu20.x系だと/etc/mysql/mysql.conf.d/mysqld.cnf
• Centos8.x系だと /etc/my.cnf.d/mysql-server.cnf

tls_version=TLSv1.2,TLSv1.3
ssl-ca=/etc/mysql/ssl/ca.pem
ssl-cert=/etc/mysql/ssl/server-cert.pem
ssl-key=/etc/mysql/ssl/server-key.pem

MySQL8へ対象のDBとユーザーを登録する

• mysql8以降はcreate userから設定する、grantだけではユーザー設定ができない。

create user '$username'@'$hostname' identified with caching_sha2_password by '$mypassword';

• grantは疎通設定のために設定する

grant all on '$targetdb'.* to '$username'@'$hostname';

• alterでssl使用のあり／なしとcaching_sha2_passwordの認証を付与する

※ 明示的に権限付与しないとリモートから接続できない

alter user '$username'@'$hostname' identified with caching_sha2_password require ssl;

パスワード仕様も厳格化されて、英数＋記号2文字以上を含める20文字以上を設定する必要がある。mysql5系までの回避策だったglobal variables変数の上書き対策も、変数名が変更されていたり、うまく反映できない事があるので色々と面倒が発生するので、素直にデフォルトのパスワードポリシーに従った方が良い。

mysqlclientを使ってMySQL8へリモート接続する時のパラメータ

mysql -P 3306 --default-character-set=utf8mb4 --host="$hostname" -u "$username" -p"$mypassword" --ssl-mode=REQUIRED  --get-server-public-key "$targetdb"

---

検証して判明した問題

MySQL5.7以前の設定を引き継ぐと動作しないオプション

• クエリキャッシュが廃止されている
  MySQL5.7以前まで重要なカスタマイズ項目だったクエリキャッシュの設定が残っていると動かない。地味に面倒くさいが起動時のエラーを追えば解決できる。

• root接続にsudo権限が必須
  MySQL5.7でも同じ仕様が課せられているが、8へアップグレードした後で再度有効になってしまう。ローカル環境では素直に "sudo mysql -u root -p" のように起動すればいい。

• パスワード強度が必須化されていてconfでパスワード強度設定ができない
  5.7以前の設定でパスワード強度の変数を変更していても、8にアップグレードした後で認証に失敗する。alterやupdateで強制的に権限を上書きする事もできるが、8以降のポリシーに準拠させておいた方がいい。

• リモート接続にssl接続が必須
  リモート接続の場合はデフォルトでsslが必須になっているので、ssl鍵の作成も必要になる。（※SSLを無視する設定にすることも可能）

• 認証アダプタのデフォルトが caching_sha2_password になった
  ユーザーを作成すると caching_sha2_password の認証がデフォルトになる。従来の認証方式 mysql_native_password を利用する場合は、ユーザー権限を明示的に書き換えて再設定する必要がある。

インターネット上では、従来の mysql_native_password を使う情報が目立つ

セキュリティ強化のために caching_sha2_password が標準になったのだが、Webの情報では mysql_native_password で認証する手順のほうが見つけやすい。

Amazon RDS では MySQL8以降も mysql_native_password がデフォルト

本来は caching_sha2_password のほうがセキュリティ向上が見込めるのだが、AWSのAmazon RDSでは MySQL8以降も mysql_native_password をデフォルトになっている。

しかし、RDS以外のMySQL8では caching_sha2_password が認証プラグインの標準設定で、mysql_native_password にするためには権限を更新しなくてはいけないので注意が必要。

---

データ移行時の問題

mysqldump での "Couldn't execute" エラー

mysql8系のmysqldumpでmysql5.7のDBをダンプしようとすると "Couldn't execute" と表示される謎のエラーで難儀した。

• 対策1
  mysqldumpのオプションに "--column-statistics=0" と "--set-gtid-purged=OFF" を付与する。自分の場合はこれで正常に動作した。
  MySQLの仕様変更が原因らしいので、詳細はリファレンスを参照。

mysqldump --column-statistics=0 --set-gtid-purged=OFF ※以下略

RDS上のMySQL5.7でも、通常のMySQL5.7でもどちらもmysqldumpが正常に動作した。

• 対策2
  "--skip-column-statistics" オプションを付与することでANALYZE TABLEステートメントを無効化してエラー回避できるらしいのだが。自分は試していない。（自己責任で試してください）

mysqldump --skip-column-statistics ※以下略

Amazon S3へのDBスナップショットデータのエクスポート

ERROR: The request could not be satisfied

docs.aws.amazon.com

Amazon RDSにはS3へスナップショットをエクスポートできる便利な機能があるので、その機能も使ってみた

• 事前準備にKMSの管理者権限を持つIAM設定と対象鍵の生成が必要になる
• スナップショットをエクスポートする設定は簡単だが、前述のKMS権限がないとエラーが頻発してエラー内容が分かりづらいので苦労する
• 1度設定すれば、実行するのは簡単（定期実行も簡単）だが、メンテナンス時間まで待つかマニュアル実行する必要がある
• RDSのMicroのような遅いインスタンスだと20GB程度のバックアップでも数時間かかる
• RDS専用のスナップショットなので、RDSを再開させたい場合には1オペレーションで普及ができて便利（DBが破損しない限り殆ど使わない機能だと思うが）
• KMSで暗号化されているので、仮にデータが流出してもスナップショットを元に復元することはできない

mysqldumpでエクスポートしてAmazon S3へ保存

• 同じRDSインスタンスの20GBのDBを対象に、mysqldumpでバックアップgzip等で圧縮すれば5分程度でdumpが終わる。S3へのアップロードは（回線速度に依存するが）数分で終わる。
• RDS以外のMySQLでもデータ移行が可能だが、ダンプが流出したときは丸ごとデータ漏洩になる。当然だが、圧縮ファイルにパスワードを設定したり、非公開のストレージに保存した方がいい。

---

データアダプタの設定項目

MySQL :: Page Not Found

dev.mysql.com

インターネット上にもMySQL8のデータアダプタの情報は殆ど見つからないのだが、mysql.data のコード補完（intelli-sense）と照らし合わせて、プロパティを探れば必要な設定が推測できる。

※接続の検証はmysql.data version 8.024で確認している

C#での実装例

• MySqlConnection の設定には、MySqlConnectionStringBuilderを使うと便利

string server="12.34.56.78";
string dbname="mydatabase";
string username="dbuser";
string password="mypassword";
var builder = new MySqlConnectionStringBuilder
{
Server = server,
Database = dbname,
UserID = username,
Port = 3306,
Password = password,
CharacterSet = @"utf8mb4",
SslMode = MySqlSslMode.Required,
AllowPublicKeyRetrieval = true,
ConvertZeroDateTime=true,
AllowZeroDateTime =true,
Pooling = false
};
string connector_str = builder.ConnectionString;
MySqlConnection cn = new MySqlConnection(connector_str);

---

接続できない場合は

• AWSやVPSのベンダ提供のFirewallが適切に設定されているかどうか
  AWSの場合はSecurity Groupの設定、VPSの場合はベンダ提供の管理画面からFirewallの疎通設定が必要になる。標準のポート番号だと3306がWebサーバーのIPから疎通設定されているかどうか確認する。

• iptablesが有効になっていないかどうか？
  iptablesが不要であれば無効化する。
  有効化させたまま運用する場合は、適切なポートを開放する。

• インスタンス上のFirewallの設定を確認する
  ubuntu系はufw, centos系はfirewalldの設定を確認する。
  必要がなければサービスを無効化、必要であれば適切なポートを開放する。

• mysqlclientで接続できているかどうか？
  mysqlclient=mysqlコマンドで接続できない場合は、サーバー側の疎通設定に問題があるので、適切に設定をやりなおす。

• mysqlclientで正常に接続できている場合は
  接続アダプタ（mysql.data.dll, mysql.data.jar 等）が正しく設定できていないので、プログラムの設定を見直す。

---

成果

• コストが1/8以下になった
  RDSでは月額6000円(税込)以上かかっていた費用が
  ➡ 699円(税込)にコストダウン
  
  ※月額349円からのVPS NTT-Indigo
  
  
  

  ※さくらVPS(ストレージの大容量プランが割安)
  
  
  

• パフォーマンス向上
  1GBのメモリで1CPU、CPUのバーストも殆ど効果がない(実質1コアの20～30%しか使えない)最小規模のRDSだったが、
  ➡ 2CPU/2GBメモリでCPUが100%利用できるデータベースになり体感速度も向上

• 経路のセキュリティ向上
  VPCとセキュリティグループに依存したセキュリティをMySQL準拠のTLS通信へ
  ➡ TLSの暗号化通信に対応

• VPCのセキュリティを廃止してFirewallのセキュリティへ統一
  VPC内の経路のセキュリティ(Endpointx2は別料金、NATGWx2も別料金)を廃止
  ➡ Firewallのセキュリティへ統一
  ※予算に余裕があれば、VPC内のルーティングとAZ,Subnet,Endpoint,NATGW,ALB,RDSを冗長化して設計したほうが良い