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:
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.