はじめに

先日、業務で使用しているDBをEC2上のMySQLからRDSに移行しました
その際にMySQL周りの知識を色々学べたのでまとめました

前提

MySQLバージョン：5.6

※ RDSではMySQL5.6のサポートを2022/2/1で終了するのでこれから新しくDBを作成するなら5.7以降が推奨です。
(参考：https://aws.amazon.com/jp/blogs/news/amazon-rds-for-mysql-5-6-extending/)
※ バージョンを5.6から5.7にする手順は別記事でまとめます。

RDS移行手順の概要

RDS作成
バックアップ取得
バックアップデータをRDSへインポート
古いDBインスタンス停止
DB接続情報の修正
phpmyadminの設定ファイル修正
DBバックアップ処理の設定ファイル修正（※ productionのみ）
Jenkinsのジョブ修正（※ stagingのみ）

RDS作成

前提

運用環境はマルチAZで作成しフェイルオーバーを利用する。
AZはap-northeast-1c と ap-northeast-1d を利用する。
サブネットはプライベートネットワークとする

RDS作成手順の概要

サブネットグループを作成する
セキュリティーグループを作成する
パラメーターグループを作成する
DBを作成する
データを取得する
ターゲットのDBにインポートする
障害復旧の動作確認をする

サブネットグループを作成する

サブネットを作成する

VPC > サブネットページ右上の「サブネットを作成」をクリックして作成ページに遷移する。
ap-northeast-1cのサブネットを作成する
ap-northeast-1dのサブネットを作成する
サブネットの一覧ページに一覧される。

サブネットグループを作成する

RDS > サブネットグループページ右上の「DBサブネットグループを作成」をクリックして作成ページに遷移する。
名称入力、作成したサブネットを選択してサブネットグループを作成する.
RDS > サブネットグループに作成されたグループが表示される。

セキュリティーグループを作成する

VPC > セキュリティー > セキュリティーグループに遷移し、ページ右上「セキュリティーグループを作成」をクリップして作成ページに遷移する。
許可したいインスタンスのIPアドレスをインバウンドルールに追加。アウトバウンドルールは何もしない。
「セキュリティーグループを作成」をクリックする。

パラメーターグループを作成する

RDS > パラメータグループに遷移し、画面右上「パラメータグループを作成」をクリックし作成ページに遷移する。
パラメータグループはDBの規模に応じて作成する

■ 注意

インポートするデータの容量が大きいとデータインポートでエラーが発生する
max_allowed_packetを 4194304(4MB)から16777216(16MB)に引き上げることでエラーを回避

■ デバッグ履歴

インポート実行
cat test_edited.sql | mysql -u root  -pxxx　-h rdsエンドポイント xxx_db_production
エラー発生
ERROR 1153 (08S01) at line 1124: Got a packet bigger than 'max_allowed_packet' bytes

設定されているパケットの上限に引っかかってるのが原因っぽい
mysql> show variables like 'max_allowed_packet';
+--------------------+---------+
| Variable_name      | Value   |
+--------------------+---------+
| max_allowed_packet | 4194304 |
+--------------------+---------+

上限を以下コマンドで引き上げようとしたらエラー発生
mysql> SET GLOBAL max_allowed_packet=16777216;
ERROR 1227 (42000): Access denied; you need (at least one of) the SUPER privilege(s) for this operation

RDSだと権限がたりないのが原因っぽい。RDSのDBパラメータグループのマネージメントコンソールからmax_allowed_packetを変更した。
参考： https://qiita.com/gremito/items/5f7ee3377194312c7c81

RDS再起動しなくても反映された
mysql> show variables like 'max_allowed_packet';
+--------------------+----------+
| Variable_name      | Value    |
+--------------------+----------+
| max_allowed_packet | 16777216 |
+--------------------+----------+
1 row in set (0.00 sec)

インポート実行したら成功
約20分ほどかかった（RDSのCloudWatchメトリクスみるとそれぐらいだった）
cat testk_edited.sql | mysql -u root  -pxxx -h rdsエンドポイント xxx_db_production

DBを作成する

RDS > データベースに遷移し、ページ右上「データベースの作成」をクリップして作成ページに遷移する。
作成したサブネットグループ/セキュリティーグループ/パラメーターグループを指定して「データベースを作成」ボタンをクリックして処理を開始する。

障害復旧の動作確認

以下2つの動作確認を行う

バックアップ取得

# RDSにアクセスできるインスタンスにssh接続
ssh -i ~/.ssh/xxxx.pem ec2-user@10.100.0.xxxxx

# RDSへの接続確認
mysql -u root -pxxx -h rdsのエンドポイント
# 接続できたらexit
exit

# dumpファイル作成
mysqldump --skip-column-statistics -u root -pxxx -h rdsのエンドポイント xxx_db_production | sed -e 's/DEFINER[ ]*=[ ]*[^*]*\*/\*/' > test.sql

# dumpファイルの不要部分を削除
cat test.sql| sed -e 's/SET @@SESSION.SQL_LOG_BIN= 0;//' | grep -v "SET @@GLOBAL\.GTID_PURGED" | grep -v "SET @@SESSION.SQL_LOG_BIN" > test_edited.sql

※ MySQL5.7以前の場合 --skip-column-statistics オプション入れないとmysqldumpでエラーになる(参考: https://blog.pinkumohikan.com/entry/mysqldump-disable-column-statistics)

バックアップデータをRDSにインポート

# 既存DBを削除
mysql -u root -pxxx -h rdsエンドポイント  -e 'drop database xxx_db_production;'

# DBを作成
mysql -u root -pxxx -h rdsエンドポイント -e 'create database xxx_db_production;'

# ユーザーを作成する
mysql -u root -pxxx -h rdsエンドポイント
CREATE USER 'user_production'@'%' IDENTIFIED BY 'passdb_production';

# 作成したユーザーに権限付与
GRANT SELECT, UPDATE, DELETE, INSERT, CREATE, DROP, INDEX, ALTER, LOCK TABLES, EXECUTE, CREATE TEMPORARY TABLES,TRIGGER, CREATE VIEW, SHOW VIEW, EVENT ON xxx_db_production.* TO 'user_production'@'%';

# データをインポートする
cat test_edited.sql | mysql -u root  -pxxx -h rdsエンドポイント xxx_db_production

# データインポートできたか確認。データが返ってくればOK
mysql -u root -pxxx -h rdsエンドポイント
show databases;
use xxx_db_production;
select name from users where id = 1;

古いDBインスタンス停止

RDSへの移行が完了したら古いDB(EC2上のMySQL)を停止する

DB接続情報の修正

DB接続情報の記載されている.bashrcファイルと/etc/nginx/conf.d/myapp.confファイルを修正する。
その後RDSへの接続テストを行う。

# 踏み台サーバーにssh接続
ssh -i ~/.ssh/xxx.pem ec2-user@10.100.0.xxx
sudo su -

# RDSと接続するインスタンスにssh接続
ssh -i ~/.ssh/xxx.pem centos@10.150.0.xxx

# DB接続情報が格納されている設定ファイルのコピーを作成
cp /etc/nginx/conf.d/myapp.conf /etc/nginx/conf.d/myapp_bp.conf
vi /etc/nginx/conf.d/myapp.conf
# 以下の情報に変更
  passenger_env_var DATABASE_USERNAME user_productuon;
  passenger_env_var DATABASE_PASSWORD passdb_production;
  passenger_env_var DATABASE_HOST rdsエンドポイント;
  passenger_env_var DATABASE_NAME xxx_db_production;
# 修正できたか確認
cat /etc/nginx/conf.d/myapp.conf
# 修正内容を反映
sudo service nginx reload
# Redirecting to /bin/systemctl reload nginx.service と返ってくればOK

# rootユーザーで.bashrcの環境変数を編集する
sudo su -
# 設定ファイルのコピー作成
cp .bashrc .bashrc_bp
# ファイル開く
vi .bashrc
# 以下の情報に変更
export DATABASE_USERNAME user_productuon
export DATABASE_PASSWORD passdb_production
export DATABASE_HOST rdsエンドポイント
export DATABASE_NAME xxx_db_production
# 修正できたか確認
cat .bashrc
# 修正内容を反映
source ~/.bashrc

# RDS接続確認へ続く

RDS接続確認

DBに変更が加わるような操作をブラウザ側から行う
ターミナルからRDSへログインしてデータが更新されたか確認

ssh -i ~/.ssh/xxx.pem  centos@10.100.0.xxx
mysql -u root -pxxx -h rdsエンドポイント
use xxx_db_production;
SQL発行して更新内容確認

以上でRDS周りの設定は終わりです！

その他設定ファイルの修正

開発環境周りで使用しているツールの設定ファイルも見直しましょう
具体例は以下の通りです

phpmyadmin
CloudWatchアラート
Jenkins
Lambda