Export MySQL dump from AWS RDS via AWS Command Line

Question

I am trying to get a MySQL backup from the AWS RDS automated. I figured using AWS Command Line would be beneficial and I could use crontab on EC2 Red Hat to automate the trigger of the event.

Now the problem: How can I connect to the RDS, backup the MySQL, place it on EC2 or make a copy to S3 and let it run each night.

I am new to the AWS Command Line. Please be free to drop of suggestions and code snippets.

Thanks!

Possible duplicate of [PHP regular backup of mysql data](http://stackoverflow.com/questions/38916163/php-regular-backup-of-mysql-data) — e4c5, Sep 10 '16 at 10:08

score 7 · Answer 1 · edited Aug 23 '17 at 11:40

On EC2 instance make a cron.sh file and put below content.

mysqldump -h RDS_ENPOINT -u MASTER_USER_DATABASE -p DATABASE_NAME > /backup/bkp.$(date +%Y%m%d).sql

Create Other file move_to_s3.sh and put below content

    #!/bin/bash
    echo "starting upload to s3 ..."
    TODAY=$(date +%Y%m%d);
    month=$(date +"%m");
    year=$(date +"%Y");
    bucket="mybkp"
    file="$year/$month/bkp.$TODAY.tar"
    filepath="/backup/bkp.$TODAY.tar"
    resource="/${bucket}/${file}"
    contentType="application/x-compressed-tar"
    dateValue=`date -R`
    stringToSign="PUT\n\n${contentType}\n${dateValue}\n${resource}"
    s3Key=AKIAI7BE3RKNSsdfsdfASF
    s3Secret=sdfksdfkJsdfgd76sdfkljhdfsdfsdfsdf
    signature=`echo -en ${stringToSign} | openssl sha1 -hmac ${s3Secret} -binary | base64`;

    RESPONSE=$(curl -w "%{http_code}" -s -X PUT -T "${filepath}" \
            -H "Host: ${bucket}.s3.amazonaws.com" \
            -H "Date: ${dateValue}" \
            -H "Content-Type: ${contentType}" \
            -H "Authorization: AWS ${s3Key}:${signature}" \
            https://${bucket}.s3.amazonaws.com/${file} -o /dev/null $1);

    echo $RESPONSE;
    if [ $RESPONSE -ne 200 ] ; then
        echo "There was an issue in transfering DB dbbackup file to S3. Noticed Error Code: $RESPONSE" | mail -s "Issue on transfer to S3" test@gmail.com;
    else
        rm $filepath;
    fi
    echo "finished upload."`

Set both in cron in half hour distance!

Hope it helps :)

score 3 · Answer 2 · answered Sep 07 '16 at 07:35

you can make backups directly from EC2 using mysqldump.

Edit Security Group of your RDS instance, Inbound rules:

Type: Mysql/Aurora

Protocol: TCP

Port Range: 3306

Source: Custom EC2_SECURITY_GROUP_ID

Example (Source: Custom sg_451caa43)
Connect to EC2 instance with SSH:

[MacBook-Pro: user]$ ssh -i keypair.pem ec2-user@PUBLIC_IP
In EC2 instance install mysql-client:

[ec2-user@ip-170-10-20-30]$ sudo yum install mysql
Try mysqldump command

[ec2-user@ip-170-10-20-30]$ mysqldump -h RDS_ENPOINT -u MASTER_USER_DATABASE -p DATABASE_NAME > backup.sql

[ec2-user@ip-170-10-20-30]$ mysqldump -h db_test.cdsludsd.us-west-2.rds.amazonaws.com -u admin -p my_database > backup_my_database.sql

Create cron job.

Will this be fast enough? I have around 100GB of data on a single Database and i have around 5 Databases like this how long do you think will this take ? — Krish, May 03 '18 at 18:26

score 0 · Answer 3 · answered Oct 06 '22 at 05:24

We should avoid exporting and uploading to s3 in 2 steps.

For larger DB, I would suggest using the below command to upload file to S3 directly without occupying the local space

mysqldump -h [db_hostname] -u [db_user] -p[db_passwd] [databasename] | aws s3 cp - s3://[s3_bucketname]/[mysqldump_filename]

e.g.

mysqldump -h $HOST -u $USER -p $PASSWORD $DB_NAME --routines --single-transaction | gzip | aws s3 cp - s3://bucket/exportfile.sql.gz

Export MySQL dump from AWS RDS via AWS Command Line

3 Answers3