Aurora Blue/Green 実践検証 — Aurora MySQL での挙動の違いを確認する

# サブネットグループ作成
aws rds create-db-subnet-group \
  --db-subnet-group-name bg-test-mysql-subnet \
  --db-subnet-group-description "Subnet group for MySQL Blue/Green test" \
  --subnet-ids '["subnet-xxxxx","subnet-yyyyy","subnet-zzzzz"]' \
  --region ap-northeast-1
 
# カスタムパラメータグループ作成（binlog 有効化）
aws rds create-db-cluster-parameter-group \
  --db-cluster-parameter-group-name bg-test-mysql8-params \
  --db-parameter-group-family aurora-mysql8.0 \
  --description "Custom params for MySQL Blue/Green test"
aws rds modify-db-cluster-parameter-group \
  --db-cluster-parameter-group-name bg-test-mysql8-params \
  --parameters "ParameterName=binlog_format,ParameterValue=ROW,ApplyMethod=pending-reboot"
 
# Aurora MySQL 3.08.0 クラスター作成（パラメータグループ指定）
aws rds create-db-cluster \
  --db-cluster-identifier bg-test-mysql \
  --engine aurora-mysql \
  --engine-version 8.0.mysql_aurora.3.08.0 \
  --master-username admin \
  --master-user-password '<your-password>' \
  --db-subnet-group-name bg-test-mysql-subnet \
  --db-cluster-parameter-group-name bg-test-mysql8-params \
  --storage-encrypted \
  --no-deletion-protection \
  --region ap-northeast-1
 
# Writer / Reader インスタンス（パブリックアクセス有効）
aws rds create-db-instance \
  --db-instance-identifier bg-test-mysql-writer \
  --db-cluster-identifier bg-test-mysql \
  --db-instance-class db.r6g.large \
  --engine aurora-mysql \
  --publicly-accessible \
  --region ap-northeast-1
 
aws rds create-db-instance \
  --db-instance-identifier bg-test-mysql-reader \
  --db-cluster-identifier bg-test-mysql \
  --db-instance-class db.r6g.large \
  --engine aurora-mysql \
  --publicly-accessible \
  --region ap-northeast-1
 
# SG に MySQL ポートを追加
SG_ID=$(aws rds describe-db-clusters --db-cluster-identifier bg-test-mysql \
  --query 'DBClusters[0].VpcSecurityGroups[0].VpcSecurityGroupId' \
  --output text --region ap-northeast-1)
MY_IP=$(curl -s https://checkip.amazonaws.com)
aws ec2 authorize-security-group-ingress \
  --group-id "${SG_ID}" \
  --protocol tcp --port 3306 \
  --cidr "${MY_IP}/32" \
  --region ap-northeast-1

# Java 21 + Maven のインストール（Ubuntu）
sudo apt-get install -y openjdk-21-jdk maven
 
# プロジェクト作成
mkdir -p bg-mysql-test/src/main/java/bgtest
cd bg-mysql-test
# pom.xml と MysqlSwitchoverTest.java を配置（下記参照）
 
# ビルド
mvn package -q

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <groupId>bgtest</groupId>
    <artifactId>bg-mysql-test</artifactId>
    <version>1.0</version>
    <properties>
        <maven.compiler.source>21</maven.compiler.source>
        <maven.compiler.target>21</maven.compiler.target>
    </properties>
    <dependencies>
        <dependency>
            <groupId>com.mysql</groupId>
            <artifactId>mysql-connector-j</artifactId>
            <version>9.2.0</version>
        </dependency>
        <dependency>
            <groupId>software.amazon.jdbc</groupId>
            <artifactId>aws-advanced-jdbc-wrapper</artifactId>
            <version>2.6.4</version>
            <exclusions>
                <exclusion>
                    <groupId>org.slf4j</groupId>
                    <artifactId>slf4j-api</artifactId>
                </exclusion>
            </exclusions>
        </dependency>
        <dependency>
            <groupId>com.zaxxer</groupId>
            <artifactId>HikariCP</artifactId>
            <version>6.2.1</version>
        </dependency>
        <dependency>
            <groupId>org.slf4j</groupId>
            <artifactId>slf4j-api</artifactId>
            <version>2.0.16</version>
        </dependency>
        <dependency>
            <groupId>org.slf4j</groupId>
            <artifactId>slf4j-simple</artifactId>
            <version>2.0.16</version>
        </dependency>
    </dependencies>
    <build>
        <plugins>
            <plugin>
                <groupId>org.apache.maven.plugins</groupId>
                <artifactId>maven-jar-plugin</artifactId>
                <version>3.4.2</version>
                <configuration>
                    <archive><manifest><mainClass>bgtest.MysqlSwitchoverTest</mainClass></manifest></archive>
                </configuration>
            </plugin>
            <plugin>
                <groupId>org.apache.maven.plugins</groupId>
                <artifactId>maven-dependency-plugin</artifactId>
                <version>3.8.1</version>
                <executions>
                    <execution>
                        <id>copy-dependencies</id>
                        <phase>package</phase>
                        <goals><goal>copy-dependencies</goal></goals>
                        <configuration><outputDirectory>${project.build.directory}/lib</outputDirectory></configuration>
                    </execution>
                </executions>
            </plugin>
        </plugins>
    </build>
</project>

package bgtest;
 
import java.sql.*;
import java.time.Instant;
import java.time.Duration;
import java.util.Properties;
import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;
 
public class MysqlSwitchoverTest {
    static final int CONNECT_TIMEOUT_MS = 3000;
    static final int QUERY_TIMEOUT_SEC = 3;
    static final String QUERY = "SELECT @@hostname";
 
    public static void main(String[] args) throws Exception {
        if (args.length < 3) {
            System.err.println("Usage: MysqlSwitchoverTest <plain|hikari|wrapper> <endpoint> <password> [intervalMs] [maxQueries]");
            System.exit(1);
        }
        String mode = args[0], endpoint = args[1], password = args[2];
        int intervalMs = args.length > 3 ? Integer.parseInt(args[3]) : 1000;
        int maxQueries = args.length > 4 ? Integer.parseInt(args[4]) : 400;
 
        System.out.println("timestamp,query_num,status,latency_ms,server_host,error");
        switch (mode) {
            case "plain"   -> runPlain(endpoint, password, intervalMs, maxQueries);
            case "hikari"  -> runHikari(endpoint, password, intervalMs, maxQueries);
            case "wrapper" -> runWrapper(endpoint, password, intervalMs, maxQueries);
        }
    }
 
    static void runPlain(String ep, String pw, int interval, int max) throws Exception {
        String url = "jdbc:mysql://" + ep + ":3306/mysql?connectTimeout=" + CONNECT_TIMEOUT_MS
                + "&socketTimeout=" + (QUERY_TIMEOUT_SEC * 1000);
        int ok = 0, fail = 0;
        for (int n = 1; n <= max; n++) {
            Instant s = Instant.now();
            try (Connection c = DriverManager.getConnection(url, "admin", pw);
                 Statement st = c.createStatement(); ResultSet r = st.executeQuery(QUERY)) {
                r.next(); long ms = Duration.between(s, Instant.now()).toMillis();
                System.out.println(Instant.now()+","+n+",OK,"+ms+","+r.getString(1)+","); ok++;
            } catch (Exception e) {
                long ms = Duration.between(s, Instant.now()).toMillis();
                String err = e.getMessage().replace('\n',' ');
                System.out.println(Instant.now()+","+n+",FAIL,"+ms+",,"+err.substring(0,Math.min(100,err.length()))); fail++;
            }
            Thread.sleep(interval);
        }
        System.err.println("=== Plain: OK="+ok+" FAIL="+fail+" ===");
    }
 
    static void runHikari(String ep, String pw, int interval, int max) throws Exception {
        HikariConfig cfg = new HikariConfig();
        cfg.setJdbcUrl("jdbc:mysql://"+ep+":3306/mysql");
        cfg.setUsername("admin"); cfg.setPassword(pw);
        cfg.setMaximumPoolSize(5); cfg.setConnectionTimeout(CONNECT_TIMEOUT_MS);
        HikariDataSource ds = new HikariDataSource(cfg);
        int ok = 0, fail = 0;
        for (int n = 1; n <= max; n++) {
            Instant s = Instant.now(); boolean success = false;
            for (int a = 1; a <= 3 && !success; a++) {
                try (Connection c = ds.getConnection();
                     Statement st = c.createStatement(); ResultSet r = st.executeQuery(QUERY)) {
                    r.next(); long ms = Duration.between(s, Instant.now()).toMillis();
                    System.out.println(Instant.now()+","+n+",OK,"+ms+","+r.getString(1)+","+(a>1?"retry="+a:"")); success = true; ok++;
                } catch (Exception e) {
                    if (a == 3) { long ms = Duration.between(s, Instant.now()).toMillis(); String err = e.getMessage().replace('\n',' ');
                        System.out.println(Instant.now()+","+n+",FAIL,"+ms+",,"+err.substring(0,Math.min(100,err.length()))); fail++;
                    } else Thread.sleep(1000);
                }
            }
            Thread.sleep(interval);
        }
        ds.close(); System.err.println("=== HikariCP: OK="+ok+" FAIL="+fail+" ===");
    }
 
    static void runWrapper(String ep, String pw, int interval, int max) throws Exception {
        String url = "jdbc:aws-wrapper:mysql://"+ep+":3306/mysql";
        Properties p = new Properties();
        p.setProperty("user","admin"); p.setProperty("password",pw);
        p.setProperty("wrapperPlugins","bg,failover2,efm2");
        p.setProperty("wrapperDialect","aurora-mysql");
        p.setProperty("bgdId","bg-mysql-demo");
        p.setProperty("bgSwitchoverTimeoutMs","600000");
        p.setProperty("blue-green-monitoring-connectTimeout","20000");
        p.setProperty("blue-green-monitoring-socketTimeout","20000");
        p.setProperty("connectTimeout",String.valueOf(CONNECT_TIMEOUT_MS));
        p.setProperty("socketTimeout",String.valueOf(QUERY_TIMEOUT_SEC*1000));
        p.setProperty("wrapperLoggerLevel","fine");
        int ok = 0, fail = 0;
        for (int n = 1; n <= max; n++) {
            Instant s = Instant.now();
            try (Connection c = DriverManager.getConnection(url, p);
                 Statement st = c.createStatement(); ResultSet r = st.executeQuery(QUERY)) {
                r.next(); long ms = Duration.between(s, Instant.now()).toMillis();
                System.out.println(Instant.now()+","+n+",OK,"+ms+","+r.getString(1)+","); ok++;
            } catch (Exception e) {
                long ms = Duration.between(s, Instant.now()).toMillis(); String err = e.getMessage().replace('\n',' ');
                System.out.println(Instant.now()+","+n+",FAIL,"+ms+",,"+err.substring(0,Math.min(100,err.length()))); fail++;
            }
            Thread.sleep(interval);
        }
        System.err.println("=== Wrapper: OK="+ok+" FAIL="+fail+" ===");
    }
}

# クラスパス構築（MySQL 版）
CP="target/bg-mysql-test-1.0.jar"
for jar in target/lib/*.jar; do CP="$CP:$jar"; done
 
ENDPOINT="bg-test-mysql.cluster-xxxxx.ap-northeast-1.rds.amazonaws.com"
 
# 3パターンを同時起動（クラス名が MysqlSwitchoverTest に変わる点に注意）
java -cp "$CP" bgtest.MysqlSwitchoverTest plain  "$ENDPOINT" '<password>' 1000 400 > mysql-plain.log  2>&1 &
java -cp "$CP" bgtest.MysqlSwitchoverTest hikari "$ENDPOINT" '<password>' 1000 400 > mysql-hikari.log 2>&1 &
java -cp "$CP" bgtest.MysqlSwitchoverTest wrapper "$ENDPOINT" '<password>' 1000 400 > mysql-wrapper.log 2>&1 &
 
# Switchover 実行
aws rds switchover-blue-green-deployment \
  --blue-green-deployment-identifier bgd-xxxxx \
  --switchover-timeout 300 \
  --region ap-northeast-1

aws rds delete-blue-green-deployment --blue-green-deployment-identifier bgd-xxxxx --region ap-northeast-1
aws rds delete-db-instance --db-instance-identifier bg-test-mysql-reader-old1 --skip-final-snapshot
aws rds delete-db-instance --db-instance-identifier bg-test-mysql-writer-old1 --skip-final-snapshot
aws rds delete-db-instance --db-instance-identifier bg-test-mysql-reader --skip-final-snapshot
aws rds delete-db-instance --db-instance-identifier bg-test-mysql-writer --skip-final-snapshot
aws rds delete-db-cluster --db-cluster-identifier bg-test-mysql-old1 --skip-final-snapshot
aws rds delete-db-cluster --db-cluster-identifier bg-test-mysql --skip-final-snapshot
aws rds delete-db-cluster-parameter-group --db-cluster-parameter-group-name bg-test-mysql8-params
aws rds delete-db-subnet-group --db-subnet-group-name bg-test-mysql-subnet
aws ec2 revoke-security-group-ingress --group-id sg-xxxxx --protocol tcp --port 3306 --cidr <your-ip>/32