RDS 데이터베이스 - 정답 및 해설

# VPC ID 확인
VPC_ID=$(aws ec2 describe-vpcs \
  --filters "Name=tag:Name,Values=ShopEasy-VPC" \
  --query "Vpcs[0].VpcId" \
  --output text \
  --region ap-northeast-2)
echo "VPC ID: $VPC_ID"

# EC2 보안 그룹 ID 확인
EC2_SG_ID=$(aws ec2 describe-security-groups \
  --filters "Name=group-name,Values=ShopEasy-EC2-SG" \
            "Name=vpc-id,Values=$VPC_ID" \
  --query "SecurityGroups[0].GroupId" \
  --output text \
  --region ap-northeast-2)
echo "EC2 SG ID: $EC2_SG_ID"

# RDS 보안 그룹 생성
RDS_SG_ID=$(aws ec2 create-security-group \
  --group-name ShopEasy-RDS-SG \
  --description "ShopEasy RDS MySQL Security Group" \
  --vpc-id $VPC_ID \
  --query "GroupId" \
  --output text \
  --region ap-northeast-2)
echo "RDS SG ID: $RDS_SG_ID"

# 인바운드 규칙 추가 - EC2 보안 그룹을 소스로 지정
aws ec2 authorize-security-group-ingress \
  --group-id $RDS_SG_ID \
  --protocol tcp \
  --port 3306 \
  --source-group $EC2_SG_ID \
  --region ap-northeast-2

# 태그 추가
aws ec2 create-tags \
  --resources $RDS_SG_ID \
  --tags Key=Name,Value=ShopEasy-RDS-SG \
  --region ap-northeast-2

aws ec2 describe-security-groups \
  --group-ids $RDS_SG_ID \
  --query "SecurityGroups[0].{Name:GroupName,ID:GroupId,Ingress:IpPermissions}" \
  --region ap-northeast-2

# 프라이빗 서브넷 ID 확인 (이름 태그 기준)
PRIV_SUBNET_1=$(aws ec2 describe-subnets \
  --filters "Name=vpc-id,Values=$VPC_ID" \
            "Name=cidr-block,Values=10.0.100.0/24" \
  --query "Subnets[0].SubnetId" \
  --output text \
  --region ap-northeast-2)

PRIV_SUBNET_2=$(aws ec2 describe-subnets \
  --filters "Name=vpc-id,Values=$VPC_ID" \
            "Name=cidr-block,Values=10.0.101.0/24" \
  --query "Subnets[0].SubnetId" \
  --output text \
  --region ap-northeast-2)

echo "Private Subnet 1: $PRIV_SUBNET_1"
echo "Private Subnet 2: $PRIV_SUBNET_2"

aws rds create-db-subnet-group \
  --db-subnet-group-name shopeasy-db-subnet-group \
  --db-subnet-group-description "ShopEasy RDS DB Subnet Group" \
  --subnet-ids $PRIV_SUBNET_1 $PRIV_SUBNET_2 \
  --region ap-northeast-2

aws rds describe-db-subnet-groups \
  --db-subnet-group-name shopeasy-db-subnet-group \
  --query "DBSubnetGroups[0].{Name:DBSubnetGroupName,Status:SubnetGroupStatus,Subnets:Subnets[*].{AZ:SubnetAvailabilityZone.Name,SubnetId:SubnetIdentifier}}" \
  --region ap-northeast-2

# RDS MySQL 인스턴스 생성
aws rds create-db-instance \
  --db-instance-identifier shopeasy-db \
  --db-instance-class db.t3.micro \
  --engine mysql \
  --engine-version 8.0 \
  --master-username admin \
  --master-user-password "ShopEasy2024!" \
  --allocated-storage 20 \
  --storage-type gp2 \
  --db-subnet-group-name shopeasy-db-subnet-group \
  --vpc-security-group-ids $RDS_SG_ID \
  --db-name ecommerce \
  --no-publicly-accessible \
  --no-multi-az \
  --backup-retention-period 0 \
  --no-deletion-protection \
  --no-storage-encrypted \
  --region ap-northeast-2

# 상태 확인
aws rds describe-db-instances \
  --db-instance-identifier shopeasy-db \
  --query "DBInstances[0].{Status:DBInstanceStatus,Endpoint:Endpoint.Address,Port:Endpoint.Port}" \
  --region ap-northeast-2

# 엔드포인트 가져오기
RDS_ENDPOINT=$(aws rds describe-db-instances \
  --db-instance-identifier shopeasy-db \
  --query "DBInstances[0].Endpoint.Address" \
  --output text \
  --region ap-northeast-2)
echo "RDS Endpoint: $RDS_ENDPOINT"

# 한 줄로 접속 테스트 (비밀번호도 인라인으로)
mysql -h $RDS_ENDPOINT -u admin -p"ShopEasy2024!" -e "SHOW DATABASES;"

+--------------------+
| Database           |
+--------------------+
| ecommerce          |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+

cd ~/ecommerce-app/api-server

# DB_TYPE 변경
sed -i 's/^DB_TYPE=sqlite/DB_TYPE=mysql/' .env

# DB_HOST 설정
sed -i "s/^DB_HOST=.*/DB_HOST=$RDS_ENDPOINT/" .env

# DB_PORT 설정
sed -i 's/^DB_PORT=.*/DB_PORT=3306/' .env

# DB_USER 설정
sed -i 's/^DB_USER=.*/DB_USER=admin/' .env

# DB_PASSWORD 설정
sed -i 's/^DB_PASSWORD=.*/DB_PASSWORD=ShopEasy2024!/' .env

# DB_NAME 설정
sed -i 's/^DB_NAME=.*/DB_NAME=ecommerce/' .env

# 변경 확인
cat .env | grep DB_

# 한 번에 실행
cd ~/ecommerce-app/api-server && \
npm install mysql2 && \
node seed.js && \
pm2 restart shopeasy-api || pm2 start server.js --name shopeasy-api

# EC2 퍼블릭 IP 가져오기
EC2_IP=$(aws ec2 describe-instances \
  --filters "Name=tag:Name,Values=ShopEasy-EC2" \
            "Name=instance-state-name,Values=running" \
  --query "Reservations[0].Instances[0].PublicIpAddress" \
  --output text \
  --region ap-northeast-2)
echo "EC2 IP: $EC2_IP"

# 회원가입
curl -X POST http://$EC2_IP:5000/api/auth/signup \
  -H "Content-Type: application/json" \
  -d '{"username":"cliuser","email":"cli@test.com","password":"test1234"}'

echo ""

# 로그인
curl -X POST http://$EC2_IP:5000/api/auth/login \
  -H "Content-Type: application/json" \
  -d '{"email":"test@test.com","password":"password123"}'

echo ""

# 상품 조회
curl -s http://$EC2_IP:5000/api/products | python3 -m json.tool | head -30