More than 1 year has passed since last update.

EC2上のMySQLをRDSに移行してみた

Posted at 2022-01-04

はじめに

先日、業務で使用している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

You get articles that match your needs
You can efficiently read back useful information
You can use dark theme

What you can do with signing up