MySQL 企业实操：私网 Multi-AZ、故障切换与独立恢复

region                = "REPLACE_REGION"
name                  = "orders-mysql-preprod"
vpc_id                = "REPLACE_VPC_ID"
private_subnets       = ["REPLACE_SUBNET_A", "REPLACE_SUBNET_B"]
client_security_group = "REPLACE_OPERATIONS_SG"
engine_version        = "REPLACE_SUPPORTED_8_4_PATCH"
final_snapshot_id     = "orders-mysql-final-REPLACE_CHANGE_ID"

aws sts get-caller-identity
aws rds describe-db-engine-versions --engine mysql --query 'DBEngineVersions[?starts_with(EngineVersion, `8.4`)].EngineVersion'
terraform init
terraform plan -out=approved.tfplan
terraform apply approved.tfplan

mysql --host=REPLACE_RDS_ENDPOINT --user=dbowner --password --ssl-mode=VERIFY_IDENTITY --ssl-ca=global-bundle.pem orders

CREATE TABLE accounts(id BIGINT PRIMARY KEY, balance_cents BIGINT NOT NULL);
CREATE TABLE transfers(request_id VARCHAR(64) PRIMARY KEY, amount_cents BIGINT NOT NULL);
INSERT INTO accounts VALUES (1,10000),(2,0);
START TRANSACTION;
INSERT INTO transfers VALUES ('dr-001',1200);
UPDATE accounts SET balance_cents=balance_cents-1200 WHERE id=1 AND balance_cents>=1200;
UPDATE accounts SET balance_cents=balance_cents+1200 WHERE id=2;
COMMIT;
SELECT * FROM accounts ORDER BY id;
SELECT COUNT(*),SUM(amount_cents) FROM transfers;
SELECT SUM(balance_cents) FROM accounts;

export SOURCE_DB=orders-mysql-preprod
export SNAPSHOT=orders-mysql-dr-REPLACE_CHANGE_ID
export RESTORE_DB=orders-mysql-restore-REPLACE_CHANGE_ID
export VPC_ID=REPLACE_VPC_ID
export VERIFY_CLIENT_SG=REPLACE_ISOLATED_VALIDATOR_SG
export RESTORE_SG=$(aws ec2 create-security-group --group-name "$RESTORE_DB" --description "Isolated restore validation" --vpc-id "$VPC_ID" --query GroupId --output text)
aws ec2 authorize-security-group-ingress --group-id "$RESTORE_SG" --protocol tcp --port 3306 --source-group "$VERIFY_CLIENT_SG"
aws rds create-db-snapshot --db-instance-identifier "$SOURCE_DB" --db-snapshot-identifier "$SNAPSHOT"
aws rds wait db-snapshot-available --db-snapshot-identifier "$SNAPSHOT"
aws rds restore-db-instance-from-db-snapshot --db-instance-identifier "$RESTORE_DB" --db-snapshot-identifier "$SNAPSHOT" --db-instance-class db.m6g.large --db-subnet-group-name "$SOURCE_DB" --vpc-security-group-ids "$RESTORE_SG" --db-parameter-group-name "$SOURCE_DB" --multi-az --no-publicly-accessible --deletion-protection
aws rds wait db-instance-available --db-instance-identifier "$RESTORE_DB"
aws rds describe-db-instances --db-instance-identifier "$RESTORE_DB" --query 'DBInstances[0].{Endpoint:Endpoint.Address,Secret:MasterUserSecret.SecretArn,Public:PubliclyAccessible}'

aws rds reboot-db-instance --db-instance-identifier "$SOURCE_DB" --force-failover
aws rds wait db-instance-available --db-instance-identifier "$SOURCE_DB"