AWS Aurora MySQL Database cloning using CLI

Question

I want to create a copy of my production aurora mysql database on a weekly basis. The copies will be used for development.

I like the clone feature of Aurora MySQL but unfortunately, the instructions to create these clones from AWS CLI are not clear.

Following the docs, I am able to create another Aurora cluster, but it doesn't create the DBs. It just creates an empty cluster. I am not able to figure out the commands to create a new Db inside this cluster from a snapshot of the Db in the source cluster as the restore-db-instance-from-db-snapshot is not supported for Aurora MySQL.

Please let me know the commands to clone the Aurora Cluster along with the DBs inside it.

Answer 1

According to the AWS documentation, this is a two phase process.

When you create a new cluster with:

aws rds restore-db-cluster-to-point-in-time \ 
  --source-db-cluster-identifier arn:aws:rds:eu-central-1:AAAAAAAAAAA:cluster:BBBBBBBBBB-cluster \ 
  --db-cluster-identifier YYYYYYYYYY-cluster \ 
  --restore-type copy-on-write \ 
  --use-latest-restorable-time

When this completes, data store has been created and is ready to be used but there are no aurora instances running.

The second step would be to create one (or more) instances:

aws rds create-db-instance \
  --db-cluster-identifier YYYYYYYYYY-cluster \ 
  --db-instance-class <value> \
  --engine <value>
  (other optional values)

Answer 2

The answer is correct, one important detail, not mentioned and causing me thinking it didn't work, is that not necessarily security policy will be the same, so in order to make the DB available you need to set the the same or appropriate plus make the DB public. I am providing some snippet for Java API:

private final AmazonRDS rds;
rds.restoreDBClusterToPointInTime(
        new RestoreDBClusterToPointInTimeRequest()
            .withSourceDBClusterIdentifier("sourceClusterIdentifier")
            .withDBClusterIdentifier("targetName")
            .withRestoreType("copy-on-write")
            .withVpcSecurityGroupIds("vpc_group_id_to_be_found")  //important
            .withUseLatestRestorableTime(true));

DBInstance instanceOfDb = rds.createDBInstance(new CreateDBInstanceRequest()
    .withDBClusterIdentifier("targetName")
    .withDBInstanceIdentifier("targetName-cluster")
    .withEngine("aurora-postgresql")
    .withDBInstanceClass("db.r4.large")
    .withPubliclyAccessible(true) //important
    .withMultiAZ(false)
);

rds.waiters().dBInstanceAvailable()
    .run(new WaiterParameters<>(new DescribeDBInstancesRequest()
        .withDBInstanceIdentifier(instanceOfDb.getDBInstanceIdentifier()))
        .withPollingStrategy(new PollingBuilder().delay(30).maxWait(30, TimeUnit.MINUTES).build()));

Answer 3

The concept of Aurora DB cloning took me a while to get my head around. With Aurora the data is actually part of the cluster. The database instance gets its data from the cluster. To clone one Aurora cluster to another you need to clone the cluster, then create a DB instance in the new cluster. The db instance you create in the new cluster will get its data from the cluster in which it is created. Phew! That was a long explanation. Anyway the shell script below is something I run from a cron and it works for me (so far). The security group ids below are fake for this example obviously.

#!/bin/bash

start=$(date +%s)
NOW_DATE=$(date '+%Y-%m-%d-%H-%M')
SOURCE_CLUSTER_INSTANCE_ID=source-aurora-cluster
TARGET_CLUSTER_INSTANCE_ID=target-aurora-cluster
TARGET_CLUSTER_INSTANCE_CLASS=db.r3.large
TARGET_ENGINE="aurora-mysql"
NEW_MASTER_PASS=setyourpasshere
SECURITY_GROUP_ID=sg-0cbc97f44ed74d652
SECURITY_GROUP_ID_DEV=sg-0b36b590347ba8796
SECURITY_GROUP_ID_ADMIN=sg-04032188f428031fd
BACKUP_RETENTION=7


echo -e "\e[93mDeleting existing RDS instance ${TARGET_CLUSTER_INSTANCE_ID} ..."
aws rds delete-db-instance --db-instance-identifier $TARGET_CLUSTER_INSTANCE_ID --skip-final-snapshot
echo -e "\e[93mWaiting for database deletion to complete..."
sleep 10
aws rds wait db-instance-deleted --db-instance-identifier $TARGET_CLUSTER_INSTANCE_ID
echo -e "\e[92mFinished deleting old ${TARGET_CLUSTER_INSTANCE_ID} RDS instance."

EXISTING_CLUSTER_INSTANCE=$(aws rds describe-db-instances --db-instance-identifier $TARGET_CLUSTER_INSTANCE_ID --query 'DBInstances[0].[DBInstanceIdentifier]' --output text)

echo -e "\e[93mDeleting existing cluster instance ${TARGET_CLUSTER_INSTANCE_ID} ..."
aws rds delete-db-cluster --db-cluster-identifier $TARGET_CLUSTER_INSTANCE_ID --skip-final-snapshot
echo -e "\e[93mWaiting for cluster deletion to complete..."

status="available"
while [ "$status" == "available" ] || [ "$status" == "deleting" ]; do
    sleep 10
    status=$(aws rds describe-db-clusters --db-cluster-identifier $TARGET_CLUSTER_INSTANCE_ID --query "*[].{DBClusters:Status}" --output text)

    echo " status = $status "
done

echo -e "\e[92mFinished deleting old ${TARGET_CLUSTER_INSTANCE_ID} cluster."

echo -e "\e[93mRestoring cluster ${SOURCE_CLUSTER_INSTANCE_ID} to new cluster ${TARGET_CLUSTER_INSTANCE_ID} ..."

CRUSTERRESTORECOMMAND="

aws rds restore-db-cluster-to-point-in-time \
--source-db-cluster-identifier $SOURCE_CLUSTER_INSTANCE_ID \
--db-cluster-identifier $TARGET_CLUSTER_INSTANCE_ID \
--restore-type copy-on-write \
--use-latest-restorable-time "

eval $CRUSTERRESTORECOMMAND

status=unknown
while [ "$status" != "available" ]; do
    sleep 10
    status=$(aws rds describe-db-clusters --db-cluster-identifier $TARGET_CLUSTER_INSTANCE_ID --query "*[].{DBClusters:Status}" --output text)
done

echo -e "\e[93mModifying cluster ${TARGET_CLUSTER_INSTANCE_ID} settings..."

CREATECLUSTERCOMMAND="
aws rds modify-db-cluster \
    --db-cluster-identifier $TARGET_CLUSTER_INSTANCE_ID \
    --master-user-password $NEW_MASTER_PASS \
    --vpc-security-group-ids $SECURITY_GROUP_ID $SECURITY_GROUP_ID_DEV $SECURITY_GROUP_ID_ADMIN \
    --backup-retention-period $BACKUP_RETENTION \
    --apply-immediately "

eval $CREATECLUSTERCOMMAND

status_modify=unknown
while [ "$status_modify" != "available" ]; do
    sleep 10
    status_modify=$(aws rds describe-db-clusters --db-cluster-identifier $TARGET_CLUSTER_INSTANCE_ID --query "*[].{DBClusters:Status}" --output text)

    echo -e "\e[92mModifications to ${TARGET_CLUSTER_INSTANCE_ID} complete."

done

echo " create RDS instance within new cluser ${TARGET_CLUSTER_INSTANCE_ID}."

CREATEDBCOMMAND="
aws rds create-db-instance \
--db-cluster-identifier $TARGET_CLUSTER_INSTANCE_ID \
--db-instance-identifier $TARGET_CLUSTER_INSTANCE_ID \
--db-instance-class $TARGET_CLUSTER_INSTANCE_CLASS \
--publicly-accessible
--engine $TARGET_ENGINE "

eval $CREATEDBCOMMAND

# neeed to wait until the new db is in an available state

while [ "${exit_status3}" != "0" ]; do
    echo -e "\e[93mWaiting for ${TARGET_CLUSTER_INSTANCE_ID} to enter 'available' state..."
    aws rds wait db-instance-available --db-instance-identifier $TARGET_CLUSTER_INSTANCE_ID
    exit_status3="$?"

    INSTANCE_STATUS=$(aws rds describe-db-instances --db-instance-identifier $TARGET_CLUSTER_INSTANCE_ID --query 'DBInstances[0].[DBInstanceStatus]' --output text)
    echo -e "\e[92m${TARGET_CLUSTER_INSTANCE_ID} is now ${INSTANCE_STATUS}."
    echo -e "\e[92mCreation of ${TARGET_CLUSTER_INSTANCE_ID} complete."
done

echo -e "\e[92mFinished clone of ${SOURCE_DB_INSTANCE_ID} to ${TARGET_CLUSTER_INSTANCE_ID}!"


end=$(date +%s)
runtime=$((end - start))
displaytime=$(displaytime runtime)

echo -e "\e[92mFinished clone of '${SOURCE_DB_INSTANCE_ID}' to '${TARGET_CLUSTER_INSTANCE_ID}"
echo -e "\e[92mThe script took ${displaytime} to run."
exit 0