Mysqldump Specific Tables From An RDS Database and Archive To S3

I was recently tasked with doing a daily backup of specific tables from an RDS database and storing that backup in date formatted S3 bucket. I made use of the awesome s3cmd cli tool.

The first thing I did was manually dump the desired tables from the database to get the correct syntax.


mysqldump -h database.dcolon.org -u dbuser -pABCD1234 table1 table2 table3 > dump.sql

This worked as expected. The dump.sql file contains table1, table2, and table3. Next I created a shell script and defined a number of variables. The format for the date in the S3 bucket is year/month/day. Today is 3/24/2014 so the date format for the bucket s3://net.dcolon.backups/mysql/ is:


s3://net.dcolon.backups/mysql/2014/03/24

Using the date command I get each of the values that I need and store them in a variable in the script. I take the mysqldump and store it locally and verify that the process completed without an error. After copying the mysqldump I rename the local copy appending the date. You can also add some logic to keep a certain number of recent copies on local disk and delete everything older.

Here is the complete script:


#!/bin/bash

export PATH=/bin:/usr/bin

DBHOST=db.dcolon.net
DBUSER=dbuser
DBPASSWD=ABCD1234
DATABASE=somedb
TABLES="table1 table2 table3"
YEAR=$(date +"%Y")
MONTH=$(date +"%m")
DAY=$(date +"%d")
S3BUCKET="s3://net.dcolon.backups/mysql/$YEAR/$MONTH/$DAY/"
DUMPFILE="/storage/backups/dump.sql"

mysqldump -h $DBHOST -u $DBUSER -p$DBPASSWD $DATABASE $TABLES > $DUMPFILE 

# if successful copy dump.sql to S3            
if [ $? -eq 0 ]; then
        s3cmd put $DUMPFILE $S3BUCKET
fi

mv $DUMPFILE $DUMPFILE.$YEAR$MONTH$DAY

Note: There is an inherent security risk of storing the password in clear text in a script or configuration file. mysqldump will mask your password while the process is running so another user can’t get the password from the process list.


dcolon    4668  0.0  0.0  22816  1776 pts/3    R+   00:45   0:00 mysqldump -u root -px xxxxxxxxxxxxxxxxxx zm

This post shows how to use mysql_config_editor to generate a config file with your password encrypted. Note that this requires MySQL 5.6 is greater.

If you have any questions, please ask below.

  • Walter

    Hello David,

    Thanks for sharing your code. I need a slightly different version which dumps all db’s in the RDS Instance. Would you help me with this change? Cheers!