Skip to content

rds-cmd

create-postsql-

export VPC_ID=vpc-xxx
export VPC_CIDR="172.31.0.0/16"
export RDS_NAME=pgsql
export AWS_DEFAULT_REGION=

sudo yum install -y jq 
sudo amazon-linux-extras install -y postgresql12

SG_NAME=${RDS_NAME}-${RANDOM}
aws ec2 create-security-group  \
  --description ${SG_NAME}     \
  --group-name ${SG_NAME}      \
  --vpc-id ${VPC_ID}

export RDS_SG=$(aws ec2 describe-security-groups      \
  --filters Name=group-name,Values=${SG_NAME}         \
            Name=vpc-id,Values=${VPC_ID}              \
  --query "SecurityGroups[0].GroupId" --output text)

echo "RDS security group ID: ${RDS_SG}"

aws ec2 authorize-security-group-ingress  \
  --group-id ${RDS_SG}                    \
  --protocol tcp                          \
  --port 5432                             \
  --cidr ${VPC_CIDR}

export PUBLIC_SUBNETS_ID=$(aws ec2 describe-subnets        \
  --filters "Name=vpc-id,Values=$VPC_ID"                   \
  --query 'Subnets[?MapPublicIpOnLaunch==`true`].SubnetId' \
  --output json | jq -c .)

# create a db subnet group
aws rds create-db-subnet-group               \
  --db-subnet-group-name ${RDS_NAME}         \
  --db-subnet-group-description ${RDS_NAME}  \
  --subnet-ids ${PUBLIC_SUBNETS_ID}

# generate a password for RDS
export RDS_PASSWORD="$(date | md5sum  |cut -f1 -d' ')"
echo ${RDS_PASSWORD}  > ~/rds_password

# create RDS Postgresql instance
aws rds create-db-instance                 \
  --db-instance-identifier ${RDS_NAME}     \
  --db-name ${RDS_NAME}                    \
  --db-instance-class db.t3.micro          \
  --engine postgres                        \
  --db-subnet-group-name ${RDS_NAME}       \
  --vpc-security-group-ids ${RDS_SG}       \
  --master-username ${RDS_NAME}            \
  --publicly-accessible                    \
  --master-user-password ${RDS_PASSWORD}   \
  --backup-retention-period 0              \
  --allocated-storage 20

aws rds describe-db-instances                \
  --db-instance-identifier ${RDS_NAME}       \
  --query "DBInstances[].DBInstanceStatus"   \
  --output text

# get RDS endpoint
export RDS_ENDPOINT=$(aws rds describe-db-instances \
  --db-instance-identifier ${RDS_NAME} \
  --query 'DBInstances[0].Endpoint.Address' \
  --output text)

echo "RDS endpoint: ${RDS_ENDPOINT}"


cat > /tmp/pgsql.sql <<-EoF
CREATE TABLE welcome (column1 TEXT);
insert into welcome values ('--------------------------');
insert into welcome values ('Welcome to the rdsworkshop');
insert into welcome values ('Welcome to the rdsworkshop');
insert into welcome values ('Welcome to the rdsworkshop');
insert into welcome values ('Welcome to the rdsworkshop');
insert into welcome values ('Welcome to the rdsworkshop');
insert into welcome values ('--------------------------');
EoF

export RDS_PASSWORD=$(cat ~/rds_password)
psql postgresql://${RDS_NAME}:${RDS_PASSWORD}@${RDS_ENDPOINT}:5432/${RDS_NAME} -f /tmp/pgsql.sql

create mysql

quick create rds mysql in default vpc

right-click & open-in-new-tab: function get-default-vpc

vpc-cmd

right-click & open-in-new-tab: function create-sg

ec2-cmd

right-click & open in new tab: function get-subnets

vpc-cmd

get-default-vpc
VPC_ID=$DEFAULT_VPC
VPC_CIDR=$DEFAULT_CIDR

create-sg ${VPC_ID} ${VPC_CIDR}
echo ${SG_ID}

get-subnets ${VPC_ID} true
echo ${SUBNET_IDS}

# create a db subnet group
aws rds create-db-subnet-group \
  --db-subnet-group-name ${SG_ID} \
  --db-subnet-group-description ${SG_ID} \
  --subnet-ids ${SUBNET_IDS}

DB_ADMIN=admin
DB_PASSWORD=admin1234567890
DB_NAME=llm-$(TZ=EAT-8 date +%Y%m%d-%H%M%S)
aws rds create-db-instance \
    --db-instance-identifier ${DB_NAME} \
    --engine mysql \
    --db-instance-class db.r6g.large \
    --master-username ${DB_ADMIN} \
    --master-user-password ${DB_PASSWORD} \
    --db-subnet-group-name ${SG_ID} \
    --vpc-security-group-ids ${SG_ID} \
    --allocated-storage 100 

aws rds wait db-instance-available --db-instance-identifier ${DB_NAME}

create read replica cross region

# in china region
source_db_arn=arn:aws-cn:rds:cn-northwest-1:123456789012:db:database-1
aws rds create-db-instance-read-replica \
  --db-instance-identifier database-1-rep-from-cnnw1 \
  --region cn-north-1 \
  --source-region cn-northwest-1 \
  --source-db-instance-identifier ${source_db_arn} \
  --kms-key-id arn:aws-cn:kms:cn-north-1:123456789012:alias/aws/rds

delete

aws rds delete-db-instance --db-instance-identifier test1 \
  --skip-final-snapshot

modify

aws rds modify-db-instance --db-instance-identifier test1 \
  --no-multi-az --no-publicly-accessible

describe-instance

aws rds describe-db-instances

ssl connection

  • download (link)
  • verify with mysql (link)
    wget 'https://truststore.pki.rds.amazonaws.com/global/global-bundle.pem'
# maybe need to change surfix to pem

dbhost=xxxx
mysql -h $dbhost --ssl-ca=global-bundle.pem --ssl-mode=VERIFY_IDENTITY -P 3306 -u admin -p