Zenn
📑

Aurora MySQL v2をv3に切り替え（blue/green deploy） 是正

2024/10/14に公開
AWS
Terraform
aurora
tech

過去ログ

1回目

https://zenn.dev/shigeru_oda/articles/57d24dc357f2a0

2回目

https://zenn.dev/shigeru_oda/articles/9ed9194a6384dd

3回目

https://zenn.dev/shigeru_oda/articles/e777bff6db46bc

やりたいこと

  • DB、TABLE、COLUMEから予約語削除
  • SnapShoot取得
  • Aurora MySQL v2からv3に更新（blue/green deploy）
  • Replication中にDMLの挙動確認

構成図

github

v4参照
https://github.com/shigeru-oda/aurora-mysql-blue-green

terraform変更点

https://github.com/shigeru-oda/aurora-mysql-blue-green/blob/main/terraform/v4/rds.tf#L28-L41

mysql変更点

予約語の削除

手順

local

# AWS credentialsは設定済であること
git clone https://github.com/shigeru-oda/aurora-mysql-blue-green.git
cd aurora-mysql-blue-green/terraform/v4
terraform init
terraform apply

Cloudshell

DB再作成

git clone https://github.com/shigeru-oda/aurora-mysql-blue-green.git
cd aurora-mysql-blue-green/sql/v4

sudo yum update -y
sudo yum install mariadb -y
mysql --version

ENDPOINT=$(aws rds describe-db-clusters --db-cluster-identifier aurora-mysql-blue-green-cluster --query 'DBClusters[0].Endpoint' --output text)
PASSWORD=your_password
mysql -h $ENDPOINT -u admin -p$PASSWORD

DROP DATABASE cube;

source 1.databse.sql
source 2.table.sql
source 3.setup_data.sql
source 4.check.sql
source 5.crud.sql

データは作成しますが、予約語は消しています

local

SnapShoot取得

前回のSnapshot削除

aws rds delete-db-cluster-snapshot \
--db-cluster-snapshot-identifier aurora-mysql-blue-green-cluster

Snapshotの作成

aws rds create-db-cluster-snapshot \
    --db-cluster-snapshot-identifier aurora-mysql-blue-green-cluster \
    --db-cluster-identifier aurora-mysql-blue-green-cluster

確認
Statusがavailableになるまで待機

aws rds describe-db-cluster-snapshots \
    --db-cluster-identifier aurora-mysql-blue-green-cluster \
    --query "DBClusterSnapshots[*].Status" \
    --output text

Blue/Greenの実行

Blue/Greenデプロイメントの作成
https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/blue-green-deployments-creating.html

aws rds create-blue-green-deployment \
    --blue-green-deployment-name blue-green-deployment \
    --source arn:aws:rds:ap-northeast-1:xxxxxxxxxxxx:cluster:aurora-mysql-blue-green-cluster \
    --target-engine-version 8.0.mysql_aurora.3.05.2 \
    --target-db-parameter-group-name aurora-mysql-db-param-group-80 \
    --target-db-cluster-parameter-group-name aurora-mysql-cluster-param-group-80 \
    --no-upgrade-target-storage-config

Blue/Greenデプロイメントの表示
https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/blue-green-deployments-viewing.html

aws rds describe-blue-green-deployments \
    --filters Name=blue-green-deployment-name,Values=blue-green-deployment
  • "Status"が"AVAILABLE" または "COMPLETED" になることを確認

ログファイル確認

aws rds download-db-log-file-portion \
    --db-instance-identifier aurora-mysql-write-green-8flpxi \
    --log-file-name upgrade-prechecks.log \
     --output text

前回のErrorが消えていることを確認

Replication中にDMLの挙動確認

INSERT/SELECT

blue側で以下DMLを実行。SELECTはgreeng側でも実行

INSERT INTO table1 (name, age) VALUES ("blue/green",1);
SELECT * FROM table1 WHERE name = "blue/green";

結果 blue/green同じ結果

MySQL [db1]> SELECT * FROM table1 WHERE name = "blue/green";
+------+------------+------+---------------------+
| id   | name       | age  | created_at          |
+------+------------+------+---------------------+
| 1001 | blue/green |    1 | 2024-10-14 07:00:32 |
+------+------------+------+---------------------+
  • blue側 general log
  • green側 general log
    • INSERTの情報が消えてる
  • blue側 audit log
  • green側 audit log
    • INSERTの情報が消えてる

UPDATE/SELECT

blue側で以下DMLを実行。SELECTはgreeng側でも実行

UPDATE table1 set name = "blue/green/UPDATE" WHERE name = "blue/green";
SELECT * FROM table1 WHERE name = "blue/green/UPDATE";

結果 blue/green同じ結果

MySQL [db1]> SELECT * FROM table1 WHERE name = "blue/green/UPDATE";
+------+-------------------+------+---------------------+
| id   | name              | age  | created_at          |
+------+-------------------+------+---------------------+
| 1001 | blue/green/UPDATE |    1 | 2024-10-14 07:00:32 |
+------+-------------------+------+---------------------+

  • blue側 general log

  • green側 general log

    • UPDATEの情報が消えてる

  • blue側 audit log

  • green側 audit log

    • UPDATEの情報が消えてる

UPDATE/SELECT

blue側で以下DMLを実行。SELECTはgreeng側でも実行

DELETE FROM table1 WHERE name = "blue/green/UPDATE";
SELECT * FROM table1 WHERE name = "blue/green/UPDATE";

結果 blue/green同じ結果

MySQL [db1]> SELECT * FROM table1 WHERE name = "blue/green/UPDATE";
Empty set (0.002 sec)

  • blue側 general log

  • green側 general log

    • DELETEの情報が消えてる

  • blue側 audit log

  • green側 audit log

    • DELETEの情報が消えてる

replication

blue/greenで論理レプリケーションしているので、green側ログにINSERT、UPDATE、DELETEが発行されるかと思ったけど出ないのね。逆にSELECTは残るのか

Discussion