# MySQL 8.0 の RDS インスタンスを作成
aws rds create-db-instance \
  --db-instance-identifier my-mysql-db \
  --db-instance-class db.r6g.large \
  --engine mysql \
  --engine-version 8.0.35 \
  --master-username admin \
  --master-user-password 'SecureP@ssw0rd!' \
  --allocated-storage 100 \
  --storage-type gp3 \
  --storage-encrypted \
  --kms-key-id alias/rds-key \
  --multi-az \
  --vpc-security-group-ids sg-0abc123def456 \
  --db-subnet-group-name my-db-subnet-group \
  --backup-retention-period 7 \
  --preferred-backup-window "03:00-04:00" \
  --preferred-maintenance-window "Mon:04:00-Mon:05:00" \
  --auto-minor-version-upgrade \
  --enable-performance-insights \
  --performance-insights-retention-period 731 \
  --monitoring-interval 60 \
  --monitoring-role-arn arn:aws:iam::123456789012:role/rds-monitoring-role \
  --enable-cloudwatch-logs-exports '["audit","error","general","slowquery"]' \
  --copy-tags-to-snapshot \
  --deletion-protection \
  --tags Key=Environment,Value=production Key=Team,Value=backend
 
# 作成状態の確認
aws rds wait db-instance-available \
  --db-instance-identifier my-mysql-db
 
# エンドポイントの取得
aws rds describe-db-instances \
  --db-instance-identifier my-mysql-db \
  --query 'DBInstances[0].Endpoint.{Address:Address,Port:Port}'

resource "aws_db_instance" "main" {
  identifier     = "app-mysql-prod"
  engine         = "mysql"
  engine_version = "8.0.35"
  instance_class = "db.r6g.large"
 
  # ストレージ
  allocated_storage     = 100
  max_allocated_storage = 500   # オートスケーリング上限
  storage_type          = "gp3"
  storage_encrypted     = true
  kms_key_id            = aws_kms_key.rds.arn
 
  # ネットワーク
  db_subnet_group_name   = aws_db_subnet_group.main.name
  vpc_security_group_ids = [aws_security_group.rds.id]
  publicly_accessible    = false
 
  # 高可用性
  multi_az = true
 
  # 認証
  username = "admin"
  password = var.db_password  # Secrets Manager 推奨
 
  # バックアップ
  backup_retention_period = 7
  backup_window          = "03:00-04:00"
  maintenance_window     = "Mon:04:00-Mon:05:00"
 
  # パラメータ
  parameter_group_name = aws_db_parameter_group.mysql80.name
 
  # 削除保護
  deletion_protection = true
  skip_final_snapshot = false
  final_snapshot_identifier = "app-mysql-prod-final"
 
  # 監視
  performance_insights_enabled          = true
  performance_insights_retention_period = 731
  monitoring_interval                   = 60
  monitoring_role_arn                   = aws_iam_role.rds_monitoring.arn
  enabled_cloudwatch_logs_exports       = ["audit", "error", "slowquery"]
 
  tags = {
    Environment = "production"
  }
}
 
resource "aws_db_parameter_group" "mysql80" {
  family = "mysql8.0"
  name   = "app-mysql80-params"
 
  parameter {
    name  = "character_set_server"
    value = "utf8mb4"
  }
 
  parameter {
    name  = "collation_server"
    value = "utf8mb4_unicode_ci"
  }
 
  parameter {
    name  = "slow_query_log"
    value = "1"
  }
 
  parameter {
    name  = "long_query_time"
    value = "1"
  }
 
  parameter {
    name  = "log_output"
    value = "FILE"
  }
 
  parameter {
    name         = "innodb_buffer_pool_size"
    value        = "{DBInstanceClassMemory*3/4}"
    apply_method = "pending-reboot"
  }
}
 
resource "aws_db_subnet_group" "main" {
  name       = "app-db-subnet-group"
  subnet_ids = var.private_subnet_ids
 
  tags = {
    Name = "app-db-subnet-group"
  }
}
 
resource "aws_security_group" "rds" {
  name_prefix = "rds-"
  vpc_id      = var.vpc_id
 
  ingress {
    from_port       = 3306
    to_port         = 3306
    protocol        = "tcp"
    security_groups = [var.app_security_group_id]
    description     = "MySQL from application layer"
  }
 
  tags = {
    Name = "rds-security-group"
  }
}

# 既存インスタンスにストレージ自動スケーリングを追加
aws rds modify-db-instance \
  --db-instance-identifier my-mysql-db \
  --max-allocated-storage 500 \
  --apply-immediately
 
# gp2 から gp3 への移行（コスト削減）
aws rds modify-db-instance \
  --db-instance-identifier my-mysql-db \
  --storage-type gp3 \
  --iops 3000 \
  --storage-throughput 125 \
  --apply-immediately
 
# ストレージ使用量の監視（CloudWatch）
aws cloudwatch get-metric-statistics \
  --namespace AWS/RDS \
  --metric-name FreeStorageSpace \
  --dimensions Name=DBInstanceIdentifier,Value=my-mysql-db \
  --start-time 2026-02-10T00:00:00Z \
  --end-time 2026-02-11T00:00:00Z \
  --period 3600 \
  --statistics Average \
  --unit Bytes
 
# ストレージ使用量のアラーム設定（残り 10GB で通知）
aws cloudwatch put-metric-alarm \
  --alarm-name "RDS-FreeStorage-Low" \
  --metric-name FreeStorageSpace \
  --namespace AWS/RDS \
  --dimensions Name=DBInstanceIdentifier,Value=my-mysql-db \
  --statistic Average \
  --period 300 \
  --evaluation-periods 3 \
  --threshold 10737418240 \
  --comparison-operator LessThanThreshold \
  --alarm-actions "arn:aws:sns:ap-northeast-1:123456789012:alerts" \
  --unit Bytes

# フェイルオーバーの手動実行（テスト用）
aws rds reboot-db-instance \
  --db-instance-identifier my-mysql-db \
  --force-failover
 
# フェイルオーバーイベントの確認
aws rds describe-events \
  --source-type db-instance \
  --source-identifier my-mysql-db \
  --duration 60
 
# マルチ AZ 状態の確認
aws rds describe-db-instances \
  --db-instance-identifier my-mysql-db \
  --query 'DBInstances[0].{MultiAZ:MultiAZ,AZ:AvailabilityZone,SecondaryAZ:SecondaryAvailabilityZone}'
 
# EventBridge でフェイルオーバーを検知
aws events put-rule \
  --name rds-failover-notification \
  --event-pattern '{
    "source": ["aws.rds"],
    "detail-type": ["RDS DB Instance Event"],
    "detail": {
      "EventCategories": ["failover"],
      "SourceType": ["DB_INSTANCE"]
    }
  }'
 
aws events put-targets \
  --rule rds-failover-notification \
  --targets '[{
    "Id": "sns-target",
    "Arn": "arn:aws:sns:ap-northeast-1:123456789012:alerts"
  }]'

# リードレプリカの作成
aws rds create-db-instance-read-replica \
  --db-instance-identifier my-mysql-db-read1 \
  --source-db-instance-identifier my-mysql-db \
  --db-instance-class db.r6g.large \
  --availability-zone ap-northeast-1c \
  --storage-type gp3 \
  --max-allocated-storage 500 \
  --enable-performance-insights \
  --performance-insights-retention-period 731 \
  --monitoring-interval 60 \
  --monitoring-role-arn arn:aws:iam::123456789012:role/rds-monitoring-role
 
# 2 台目のリードレプリカ
aws rds create-db-instance-read-replica \
  --db-instance-identifier my-mysql-db-read2 \
  --source-db-instance-identifier my-mysql-db \
  --db-instance-class db.r6g.large \
  --availability-zone ap-northeast-1d
 
# クロスリージョンリードレプリカ（DR 用）
aws rds create-db-instance-read-replica \
  --db-instance-identifier my-mysql-db-us-read \
  --source-db-instance-identifier arn:aws:rds:ap-northeast-1:123456789:db:my-mysql-db \
  --db-instance-class db.r6g.large \
  --region us-east-1
 
# レプリケーション遅延の監視
aws cloudwatch get-metric-statistics \
  --namespace AWS/RDS \
  --metric-name ReplicaLag \
  --dimensions Name=DBInstanceIdentifier,Value=my-mysql-db-read1 \
  --start-time "$(date -u -v-1H +%Y-%m-%dT%H:%M:%SZ)" \
  --end-time "$(date -u +%Y-%m-%dT%H:%M:%SZ)" \
  --period 60 \
  --statistics Average
 
# レプリケーション遅延アラーム
aws cloudwatch put-metric-alarm \
  --alarm-name "RDS-ReplicaLag-High" \
  --metric-name ReplicaLag \
  --namespace AWS/RDS \
  --dimensions Name=DBInstanceIdentifier,Value=my-mysql-db-read1 \
  --statistic Average \
  --period 60 \
  --evaluation-periods 3 \
  --threshold 30 \
  --comparison-operator GreaterThanThreshold \
  --alarm-actions "arn:aws:sns:ap-northeast-1:123456789012:alerts"
 
# リードレプリカをスタンドアロン DB に昇格
aws rds promote-read-replica \
  --db-instance-identifier my-mysql-db-read1

import pymysql
from contextlib import contextmanager
import random
import time
 
class DatabaseRouter:
    """読み書き分離を行うデータベースルーター"""
 
    def __init__(self):
        self.writer_config = {
            'host': 'my-mysql-db.xxxx.ap-northeast-1.rds.amazonaws.com',
            'user': 'admin',
            'password': 'secret',
            'database': 'myapp',
            'charset': 'utf8mb4',
            'connect_timeout': 5,
            'read_timeout': 30,
        }
        self.reader_configs = [
            {
                'host': 'my-mysql-db-read1.xxxx.ap-northeast-1.rds.amazonaws.com',
                'user': 'readonly',
                'password': 'secret',
                'database': 'myapp',
                'charset': 'utf8mb4',
                'connect_timeout': 5,
                'read_timeout': 30,
            },
            {
                'host': 'my-mysql-db-read2.xxxx.ap-northeast-1.rds.amazonaws.com',
                'user': 'readonly',
                'password': 'secret',
                'database': 'myapp',
                'charset': 'utf8mb4',
                'connect_timeout': 5,
                'read_timeout': 30,
            },
        ]
        self._reader_index = 0
 
    def _connect_with_retry(self, config, max_retries=3):
        """リトライ付きの接続"""
        for attempt in range(max_retries):
            try:
                return pymysql.connect(**config)
            except pymysql.OperationalError as e:
                if attempt == max_retries - 1:
                    raise
                time.sleep(0.5 * (attempt + 1))
 
    @contextmanager
    def writer(self):
        """書き込み用コネクション"""
        conn = self._connect_with_retry(self.writer_config)
        try:
            yield conn
            conn.commit()
        except Exception:
            conn.rollback()
            raise
        finally:
            conn.close()
 
    @contextmanager
    def reader(self):
        """読み取り用コネクション（ラウンドロビン）"""
        config = self.reader_configs[self._reader_index]
        self._reader_index = (self._reader_index + 1) % len(self.reader_configs)
        conn = self._connect_with_retry(config)
        try:
            yield conn
        finally:
            conn.close()
 
    @contextmanager
    def consistent_reader(self):
        """一貫性が必要な読み取り（プライマリから読む）"""
        conn = self._connect_with_retry(self.writer_config)
        try:
            yield conn
        finally:
            conn.close()
 
# 使用例
db = DatabaseRouter()
 
# 書き込みはプライマリへ
with db.writer() as conn:
    with conn.cursor() as cur:
        cur.execute("INSERT INTO users (name, email) VALUES (%s, %s)",
                    ("Taro", "taro@example.com"))
 
# 読み取りはリードレプリカへ
with db.reader() as conn:
    with conn.cursor() as cur:
        cur.execute("SELECT * FROM users WHERE id = %s", (1,))
        user = cur.fetchone()
 
# 書き込み直後の読み取り（レプリケーション遅延回避）
with db.consistent_reader() as conn:
    with conn.cursor() as cur:
        cur.execute("SELECT * FROM users WHERE email = %s", ("taro@example.com",))
        user = cur.fetchone()

# RDS Proxy の作成
aws rds create-db-proxy \
  --db-proxy-name my-app-proxy \
  --engine-family MYSQL \
  --auth '[{
    "AuthScheme": "SECRETS",
    "SecretArn": "arn:aws:secretsmanager:ap-northeast-1:123456789012:secret:my-db-creds",
    "IAMAuth": "DISABLED"
  }]' \
  --role-arn arn:aws:iam::123456789012:role/rds-proxy-role \
  --vpc-subnet-ids subnet-aaa subnet-bbb subnet-ccc \
  --vpc-security-group-ids sg-proxy123 \
  --require-tls \
  --idle-client-timeout 1800
 
# ターゲットグループの登録
aws rds register-db-proxy-targets \
  --db-proxy-name my-app-proxy \
  --db-instance-identifiers my-mysql-db
 
# Proxy エンドポイントの取得
aws rds describe-db-proxies \
  --db-proxy-name my-app-proxy \
  --query 'DBProxies[0].Endpoint'
 
# Proxy のリーダーエンドポイントを作成（読み取り用）
aws rds create-db-proxy-endpoint \
  --db-proxy-name my-app-proxy \
  --db-proxy-endpoint-name my-app-proxy-reader \
  --target-role READ_ONLY \
  --vpc-subnet-ids subnet-aaa subnet-bbb subnet-ccc

# 復元可能な最新時刻の確認
aws rds describe-db-instances \
  --db-instance-identifier my-mysql-db \
  --query 'DBInstances[0].LatestRestorableTime'
 
# 特定時刻の状態にリストア（新インスタンスとして作成）
aws rds restore-db-instance-to-point-in-time \
  --source-db-instance-identifier my-mysql-db \
  --target-db-instance-identifier my-mysql-db-restored \
  --restore-time "2026-02-10T15:30:00Z" \
  --db-instance-class db.r6g.large \
  --db-subnet-group-name my-db-subnet-group \
  --vpc-security-group-ids sg-0abc123def456 \
  --multi-az \
  --storage-type gp3 \
  --copy-tags-to-snapshot
 
# 最新の復元可能な時点にリストア
aws rds restore-db-instance-to-point-in-time \
  --source-db-instance-identifier my-mysql-db \
  --target-db-instance-identifier my-mysql-db-latest \
  --use-latest-restorable-time \
  --db-instance-class db.r6g.large
 
# 手動スナップショットの作成
aws rds create-db-snapshot \
  --db-instance-identifier my-mysql-db \
  --db-snapshot-identifier my-mysql-db-snap-20260211
 
# スナップショットからの復元
aws rds restore-db-instance-from-db-snapshot \
  --db-instance-identifier my-mysql-db-from-snap \
  --db-snapshot-identifier my-mysql-db-snap-20260211 \
  --db-instance-class db.r6g.large
 
# スナップショットの別リージョンへのコピー（DR 用）
aws rds copy-db-snapshot \
  --source-db-snapshot-identifier arn:aws:rds:ap-northeast-1:123456789012:snapshot:my-mysql-db-snap-20260211 \
  --target-db-snapshot-identifier my-mysql-db-snap-us-copy \
  --region us-east-1 \
  --kms-key-id alias/rds-dr-key
 
# スナップショットの別アカウントへの共有
aws rds modify-db-snapshot-attribute \
  --db-snapshot-identifier my-mysql-db-snap-20260211 \
  --attribute-name restore \
  --values-to-add "987654321098"

# 自動バックアップの別リージョンへのレプリケーション
aws rds start-db-instance-automated-backups-replication \
  --source-db-instance-arn arn:aws:rds:ap-northeast-1:123456789012:db:my-mysql-db \
  --backup-retention-period 7 \
  --kms-key-id alias/rds-dr-key \
  --region us-east-1
 
# レプリケーション状態の確認
aws rds describe-db-instance-automated-backups \
  --db-instance-automated-backups-arn arn:aws:rds:us-east-1:123456789012:auto-backup:xxx \
  --region us-east-1

# Performance Insights を有効化
aws rds modify-db-instance \
  --db-instance-identifier my-mysql-db \
  --enable-performance-insights \
  --performance-insights-retention-period 731 \
  --performance-insights-kms-key-id alias/rds-pi-key \
  --apply-immediately
 
# トップ待機イベントの取得
aws pi get-resource-metrics \
  --service-type RDS \
  --identifier db-XXXXXXXXXXXXXXXXXXXX \
  --metric-queries '[{
    "Metric": "db.load.avg",
    "GroupBy": {
      "Group": "db.wait_event",
      "Limit": 5
    }
  }]' \
  --start-time 2026-02-10T00:00:00Z \
  --end-time 2026-02-11T00:00:00Z \
  --period-in-seconds 3600
 
# トップ SQL の取得
aws pi get-resource-metrics \
  --service-type RDS \
  --identifier db-XXXXXXXXXXXXXXXXXXXX \
  --metric-queries '[{
    "Metric": "db.load.avg",
    "GroupBy": {
      "Group": "db.sql",
      "Limit": 10
    }
  }]' \
  --start-time 2026-02-10T00:00:00Z \
  --end-time 2026-02-11T00:00:00Z \
  --period-in-seconds 3600
 
# Enhanced Monitoring の有効化（OS レベルのメトリクス）
aws rds modify-db-instance \
  --db-instance-identifier my-mysql-db \
  --monitoring-interval 60 \
  --monitoring-role-arn arn:aws:iam::123456789012:role/rds-monitoring-role \
  --apply-immediately

# RDS 監視ダッシュボードの作成
aws cloudwatch put-dashboard \
  --dashboard-name "RDS-Monitoring" \
  --dashboard-body '{
    "widgets": [
      {
        "type": "metric",
        "properties": {
          "title": "CPU Utilization",
          "metrics": [
            ["AWS/RDS", "CPUUtilization", "DBInstanceIdentifier", "my-mysql-db"]
          ],
          "period": 300,
          "stat": "Average",
          "region": "ap-northeast-1",
          "yAxis": {"left": {"min": 0, "max": 100}}
        },
        "width": 12, "height": 6, "x": 0, "y": 0
      },
      {
        "type": "metric",
        "properties": {
          "title": "Database Connections",
          "metrics": [
            ["AWS/RDS", "DatabaseConnections", "DBInstanceIdentifier", "my-mysql-db"]
          ],
          "period": 300,
          "stat": "Average",
          "region": "ap-northeast-1"
        },
        "width": 12, "height": 6, "x": 12, "y": 0
      },
      {
        "type": "metric",
        "properties": {
          "title": "IOPS",
          "metrics": [
            ["AWS/RDS", "ReadIOPS", "DBInstanceIdentifier", "my-mysql-db"],
            ["AWS/RDS", "WriteIOPS", "DBInstanceIdentifier", "my-mysql-db"]
          ],
          "period": 300,
          "stat": "Average",
          "region": "ap-northeast-1"
        },
        "width": 12, "height": 6, "x": 0, "y": 6
      },
      {
        "type": "metric",
        "properties": {
          "title": "Replica Lag",
          "metrics": [
            ["AWS/RDS", "ReplicaLag", "DBInstanceIdentifier", "my-mysql-db-read1"],
            ["AWS/RDS", "ReplicaLag", "DBInstanceIdentifier", "my-mysql-db-read2"]
          ],
          "period": 60,
          "stat": "Average",
          "region": "ap-northeast-1"
        },
        "width": 12, "height": 6, "x": 12, "y": 6
      }
    ]
  }'

# DB サブネットグループの作成
aws rds create-db-subnet-group \
  --db-subnet-group-name my-db-subnet-group \
  --db-subnet-group-description "Private subnets for RDS" \
  --subnet-ids subnet-aaa111 subnet-bbb222 subnet-ccc333
 
# セキュリティグループの作成
SG_ID=$(aws ec2 create-security-group \
  --group-name rds-mysql-sg \
  --description "Security group for RDS MySQL" \
  --vpc-id vpc-xxx \
  --query 'GroupId' --output text)
 
# アプリケーション層からのみ MySQL ポートを許可
aws ec2 authorize-security-group-ingress \
  --group-id $SG_ID \
  --protocol tcp \
  --port 3306 \
  --source-group sg-app-layer
 
# Bastion / SSM からの接続も許可（管理者用）
aws ec2 authorize-security-group-ingress \
  --group-id $SG_ID \
  --protocol tcp \
  --port 3306 \
  --source-group sg-bastion

# IAM 認証の有効化
aws rds modify-db-instance \
  --db-instance-identifier my-mysql-db \
  --enable-iam-database-authentication \
  --apply-immediately
 
# MySQL で IAM 認証ユーザーの作成
# mysql> CREATE USER 'iam_user'@'%' IDENTIFIED WITH AWSAuthenticationPlugin AS 'RDS';
# mysql> GRANT SELECT ON myapp.* TO 'iam_user'@'%';
 
# IAM ポリシーの例
# {
#   "Version": "2012-10-17",
#   "Statement": [{
#     "Effect": "Allow",
#     "Action": "rds-db:connect",
#     "Resource": "arn:aws:rds-db:ap-northeast-1:123456789012:dbuser:cluster-xxx/iam_user"
#   }]
# }
 
# IAM 認証トークンの取得と接続
TOKEN=$(aws rds generate-db-auth-token \
  --hostname my-mysql-db.xxxx.ap-northeast-1.rds.amazonaws.com \
  --port 3306 \
  --username iam_user)
 
mysql -h my-mysql-db.xxxx.ap-northeast-1.rds.amazonaws.com \
  -u iam_user \
  --password=$TOKEN \
  --ssl-mode=REQUIRED \
  --ssl-ca=global-bundle.pem

# 暗号化済みインスタンスの確認
aws rds describe-db-instances \
  --db-instance-identifier my-mysql-db \
  --query 'DBInstances[0].{StorageEncrypted:StorageEncrypted,KmsKeyId:KmsKeyId}'
 
# 非暗号化インスタンスの暗号化（スナップショット経由）
# 1. スナップショット取得
aws rds create-db-snapshot \
  --db-instance-identifier my-mysql-db-unencrypted \
  --db-snapshot-identifier my-mysql-db-unenc-snap
 
# 2. 暗号化コピーを作成
aws rds copy-db-snapshot \
  --source-db-snapshot-identifier my-mysql-db-unenc-snap \
  --target-db-snapshot-identifier my-mysql-db-encrypted-snap \
  --kms-key-id alias/rds-key
 
# 3. 暗号化スナップショットから復元
aws rds restore-db-instance-from-db-snapshot \
  --db-instance-identifier my-mysql-db-encrypted \
  --db-snapshot-identifier my-mysql-db-encrypted-snap
 
# 4. アプリの接続先を切り替え後、旧インスタンスを削除
 
# SSL/TLS 接続の強制（パラメータグループ）
# MySQL: require_secure_transport = 1
# PostgreSQL: rds.force_ssl = 1

# RDS インスタンスの一時停止（最大 7 日間）
aws rds stop-db-instance \
  --db-instance-identifier my-dev-db
 
# 7 日後に自動起動されるため、継続的に停止したい場合はスクリプトが必要
 
# RDS インスタンスの起動
aws rds start-db-instance \
  --db-instance-identifier my-dev-db
 
# Lambda で開発環境の定時停止・起動を自動化
# EventBridge ルール: 平日 20:00 に停止、翌朝 8:00 に起動
aws events put-rule \
  --name stop-dev-rds-nightly \
  --schedule-expression "cron(0 11 ? * MON-FRI *)" \
  --description "Stop dev RDS at 20:00 JST"
 
aws events put-rule \
  --name start-dev-rds-morning \
  --schedule-expression "cron(0 23 ? * SUN-THU *)" \
  --description "Start dev RDS at 8:00 JST"

AWSTemplateFormatVersion: '2010-09-09'
Description: Production RDS MySQL with Multi-AZ and Read Replica
 
Parameters:
  VpcId:
    Type: AWS::EC2::VPC::Id
  PrivateSubnetIds:
    Type: List<AWS::EC2::Subnet::Id>
  AppSecurityGroupId:
    Type: AWS::EC2::SecurityGroup::Id
  DBPassword:
    Type: String
    NoEcho: true
    Description: Master password (Secrets Manager recommended)
 
Resources:
  # KMS キー
  RDSKey:
    Type: AWS::KMS::Key
    Properties:
      Description: RDS encryption key
      KeyPolicy:
        Version: '2012-10-17'
        Statement:
          - Sid: AllowRootAccount
            Effect: Allow
            Principal:
              AWS: !Sub 'arn:aws:iam::${AWS::AccountId}:root'
            Action: 'kms:*'
            Resource: '*'
 
  # DB サブネットグループ
  DBSubnetGroup:
    Type: AWS::RDS::DBSubnetGroup
    Properties:
      DBSubnetGroupDescription: Private subnets for RDS
      SubnetIds: !Ref PrivateSubnetIds
 
  # セキュリティグループ
  DBSecurityGroup:
    Type: AWS::EC2::SecurityGroup
    Properties:
      GroupDescription: RDS MySQL Security Group
      VpcId: !Ref VpcId
      SecurityGroupIngress:
        - IpProtocol: tcp
          FromPort: 3306
          ToPort: 3306
          SourceSecurityGroupId: !Ref AppSecurityGroupId
          Description: MySQL from app layer
 
  # パラメータグループ
  DBParameterGroup:
    Type: AWS::RDS::DBParameterGroup
    Properties:
      Family: mysql8.0
      Description: Custom MySQL 8.0 parameters
      Parameters:
        character_set_server: utf8mb4
        collation_server: utf8mb4_unicode_ci
        slow_query_log: '1'
        long_query_time: '1'
        require_secure_transport: '1'
 
  # プライマリインスタンス
  DBInstance:
    Type: AWS::RDS::DBInstance
    DeletionPolicy: Snapshot
    Properties:
      DBInstanceIdentifier: !Sub '${AWS::StackName}-mysql'
      Engine: mysql
      EngineVersion: '8.0.35'
      DBInstanceClass: db.r6g.large
      AllocatedStorage: 100
      MaxAllocatedStorage: 500
      StorageType: gp3
      StorageEncrypted: true
      KmsKeyId: !Ref RDSKey
      MultiAZ: true
      MasterUsername: admin
      MasterUserPassword: !Ref DBPassword
      DBSubnetGroupName: !Ref DBSubnetGroup
      VPCSecurityGroups:
        - !Ref DBSecurityGroup
      DBParameterGroupName: !Ref DBParameterGroup
      BackupRetentionPeriod: 7
      PreferredBackupWindow: '03:00-04:00'
      PreferredMaintenanceWindow: 'Mon:04:00-Mon:05:00'
      AutoMinorVersionUpgrade: true
      DeletionProtection: true
      CopyTagsToSnapshot: true
      EnablePerformanceInsights: true
      PerformanceInsightsRetentionPeriod: 731
      MonitoringInterval: 60
      MonitoringRoleArn: !GetAtt MonitoringRole.Arn
      EnableCloudwatchLogsExports:
        - audit
        - error
        - slowquery
 
  # リードレプリカ
  ReadReplica:
    Type: AWS::RDS::DBInstance
    DependsOn: DBInstance
    Properties:
      DBInstanceIdentifier: !Sub '${AWS::StackName}-mysql-read1'
      SourceDBInstanceIdentifier: !Ref DBInstance
      DBInstanceClass: db.r6g.large
      StorageType: gp3
      EnablePerformanceInsights: true
      PerformanceInsightsRetentionPeriod: 731
      MonitoringInterval: 60
      MonitoringRoleArn: !GetAtt MonitoringRole.Arn
 
  # Enhanced Monitoring 用 IAM ロール
  MonitoringRole:
    Type: AWS::IAM::Role
    Properties:
      AssumeRolePolicyDocument:
        Version: '2012-10-17'
        Statement:
          - Effect: Allow
            Principal:
              Service: monitoring.rds.amazonaws.com
            Action: sts:AssumeRole
      ManagedPolicyArns:
        - arn:aws:iam::aws:policy/service-role/AmazonRDSEnhancedMonitoringRole
 
  # CPU 使用率アラーム
  CPUAlarm:
    Type: AWS::CloudWatch::Alarm
    Properties:
      AlarmName: !Sub '${AWS::StackName}-rds-cpu-high'
      MetricName: CPUUtilization
      Namespace: AWS/RDS
      Dimensions:
        - Name: DBInstanceIdentifier
          Value: !Ref DBInstance
      Statistic: Average
      Period: 300
      EvaluationPeriods: 3
      Threshold: 80
      ComparisonOperator: GreaterThanThreshold
      AlarmActions:
        - !Sub 'arn:aws:sns:${AWS::Region}:${AWS::AccountId}:alerts'
 
Outputs:
  PrimaryEndpoint:
    Value: !GetAtt DBInstance.Endpoint.Address
    Description: Primary DB endpoint
  PrimaryPort:
    Value: !GetAtt DBInstance.Endpoint.Port
    Description: Primary DB port
  ReadReplicaEndpoint:
    Value: !GetAtt ReadReplica.Endpoint.Address
    Description: Read replica endpoint

import * as cdk from 'aws-cdk-lib';
import * as rds from 'aws-cdk-lib/aws-rds';
import * as ec2 from 'aws-cdk-lib/aws-ec2';
import * as kms from 'aws-cdk-lib/aws-kms';
import * as cloudwatch from 'aws-cdk-lib/aws-cloudwatch';
import * as sns from 'aws-cdk-lib/aws-sns';
import * as cw_actions from 'aws-cdk-lib/aws-cloudwatch-actions';
import * as secretsmanager from 'aws-cdk-lib/aws-secretsmanager';
import { Construct } from 'constructs';
 
interface RdsStackProps extends cdk.StackProps {
  vpc: ec2.IVpc;
  appSecurityGroup: ec2.ISecurityGroup;
}
 
export class RdsStack extends cdk.Stack {
  public readonly dbInstance: rds.DatabaseInstance;
  public readonly readReplica: rds.DatabaseInstanceReadReplica;
 
  constructor(scope: Construct, id: string, props: RdsStackProps) {
    super(scope, id, props);
 
    // KMS キー
    const encryptionKey = new kms.Key(this, 'RdsKey', {
      description: 'RDS encryption key',
      enableKeyRotation: true,
    });
 
    // セキュリティグループ
    const dbSg = new ec2.SecurityGroup(this, 'DbSg', {
      vpc: props.vpc,
      description: 'RDS MySQL Security Group',
      allowAllOutbound: false,
    });
    dbSg.addIngressRule(
      props.appSecurityGroup,
      ec2.Port.tcp(3306),
      'MySQL from app layer'
    );
 
    // パラメータグループ
    const parameterGroup = new rds.ParameterGroup(this, 'Params', {
      engine: rds.DatabaseInstanceEngine.mysql({
        version: rds.MysqlEngineVersion.VER_8_0_35,
      }),
      parameters: {
        character_set_server: 'utf8mb4',
        collation_server: 'utf8mb4_unicode_ci',
        slow_query_log: '1',
        long_query_time: '1',
        require_secure_transport: '1',
      },
    });
 
    // プライマリインスタンス
    this.dbInstance = new rds.DatabaseInstance(this, 'Primary', {
      engine: rds.DatabaseInstanceEngine.mysql({
        version: rds.MysqlEngineVersion.VER_8_0_35,
      }),
      instanceType: ec2.InstanceType.of(
        ec2.InstanceClass.R6G,
        ec2.InstanceSize.LARGE
      ),
      vpc: props.vpc,
      vpcSubnets: { subnetType: ec2.SubnetType.PRIVATE_WITH_EGRESS },
      securityGroups: [dbSg],
      multiAz: true,
      allocatedStorage: 100,
      maxAllocatedStorage: 500,
      storageType: rds.StorageType.GP3,
      storageEncrypted: true,
      storageEncryptionKey: encryptionKey,
      parameterGroup,
      backupRetention: cdk.Duration.days(7),
      preferredBackupWindow: '03:00-04:00',
      preferredMaintenanceWindow: 'Mon:04:00-Mon:05:00',
      deletionProtection: true,
      removalPolicy: cdk.RemovalPolicy.SNAPSHOT,
      copyTagsToSnapshot: true,
      enablePerformanceInsights: true,
      performanceInsightRetention: rds.PerformanceInsightRetention.MONTHS_25,
      monitoringInterval: cdk.Duration.seconds(60),
      cloudwatchLogsExports: ['audit', 'error', 'slowquery'],
      credentials: rds.Credentials.fromGeneratedSecret('admin', {
        secretName: 'rds/mysql/prod/credentials',
      }),
    });
 
    // リードレプリカ
    this.readReplica = new rds.DatabaseInstanceReadReplica(this, 'ReadReplica', {
      sourceDatabaseInstance: this.dbInstance,
      instanceType: ec2.InstanceType.of(
        ec2.InstanceClass.R6G,
        ec2.InstanceSize.LARGE
      ),
      vpc: props.vpc,
      vpcSubnets: { subnetType: ec2.SubnetType.PRIVATE_WITH_EGRESS },
      securityGroups: [dbSg],
      storageType: rds.StorageType.GP3,
      enablePerformanceInsights: true,
      performanceInsightRetention: rds.PerformanceInsightRetention.MONTHS_25,
      monitoringInterval: cdk.Duration.seconds(60),
    });
 
    // CPU 使用率アラーム
    const alertTopic = sns.Topic.fromTopicArn(
      this, 'AlertTopic',
      `arn:aws:sns:${this.region}:${this.account}:alerts`
    );
 
    const cpuAlarm = this.dbInstance.metricCPUUtilization({
      period: cdk.Duration.minutes(5),
    }).createAlarm(this, 'CpuAlarm', {
      threshold: 80,
      evaluationPeriods: 3,
      alarmDescription: 'RDS CPU utilization > 80%',
    });
    cpuAlarm.addAlarmAction(new cw_actions.SnsAction(alertTopic));
 
    // レプリケーション遅延アラーム
    const replicaLagAlarm = new cloudwatch.Alarm(this, 'ReplicaLagAlarm', {
      metric: new cloudwatch.Metric({
        namespace: 'AWS/RDS',
        metricName: 'ReplicaLag',
        dimensionsMap: {
          DBInstanceIdentifier: this.readReplica.instanceIdentifier,
        },
        period: cdk.Duration.minutes(1),
        statistic: 'Average',
      }),
      threshold: 30,
      evaluationPeriods: 3,
      alarmDescription: 'RDS ReplicaLag > 30 seconds',
    });
    replicaLagAlarm.addAlarmAction(new cw_actions.SnsAction(alertTopic));
 
    // 出力
    new cdk.CfnOutput(this, 'PrimaryEndpoint', {
      value: this.dbInstance.dbInstanceEndpointAddress,
    });
    new cdk.CfnOutput(this, 'ReadReplicaEndpoint', {
      value: this.readReplica.dbInstanceEndpointAddress,
    });
    new cdk.CfnOutput(this, 'SecretArn', {
      value: this.dbInstance.secret!.secretArn,
    });
  }
}

# Blue/Green デプロイメントの作成
aws rds create-blue-green-deployment \
  --blue-green-deployment-name mysql-upgrade \
  --source arn:aws:rds:ap-northeast-1:123456789012:db:my-mysql-db \
  --target-engine-version 8.0.36 \
  --target-db-parameter-group-name new-params
 
# 状態の確認
aws rds describe-blue-green-deployments \
  --blue-green-deployment-identifier bgd-xxx
 
# 切り替え実行
aws rds switchover-blue-green-deployment \
  --blue-green-deployment-identifier bgd-xxx \
  --switchover-timeout 300