Backup your MySQL to Amazon S3

Motivation: If ever database gets corrupted, or the MySQL goes boink, the data loss can be minimized.
Prerequisites: 

1. Familiarity with shell script
2. S3 bucket and credentials
3. Any S3 tool that you're familiar with. Here I have used s3cmd . You may use weapon of your choice, may be JetS3t or anything else.

Steps:

1. Create a clean directory where all the operations will take place. Call it staging_dir
2. Take mysqldump of desired database. Or all the database. Learn here what you want: mysqldump usage document
   

   mysqldump --user MYSQL_USER --password=MYSQL_PASSWORD --add-locks --flush-privileges \
   --add-drop-table --complete-insert --extended-insert --single-transaction \
   --database DB_TO_BACKUP > FILENAME_FOR_SQL_STATEMENTS

   
   where
• --add-locks: Surround each table dump with LOCK TABLES and UNLOCK TABLES statements. This results in faster inserts when the dump file is reloaded.
• --flush-privileges: Send a FLUSH PRIVILEGES statement to the server after dumping the mysql database. This option should be used any time the dump contains the mysql database and any other database that depends on the data in the mysql database for proper restoration.
• --add-drop-table: Add a DROP TABLE statement before each CREATE TABLE statement.
• --complete-insert: Use complete INSERT statements that include column names. 
• --extended-insert: Use multiple-row INSERT syntax that include several VALUES lists. This results in a smaller dump file and speeds up inserts when the file is reloaded.
  
  you may also want to use -h HOSTNAME, if your cron job is going to run on a remote machine.
3.  Make a tarball of this file.
   

   tar cf TAR_FILENAME --directory=STAGING_DIR DUMPED_SQL_FILE_NAME
   
   

4. Upload to S3, (I assume you have configured your S3 tool whatever you use). With s3cmd, it's simple. s3cmd put FILENAME s3://BUCKETNAME/SUB/DIRECTORY/
5. The tricky part is how would you know, what file was uploaded X days ago? With richer (scripting) languages like Python, or Groovy, it's lot more easy to enumerate the files in the backup folder. And if files are more than X, delete the excessive files. To me, it's tricky to this in shell script. So, I ended up in having smart filename instead.
   
   I name the files as www.mywebsite.com.YYYY-MM-DD.sql.tar. So, to delete the file that was created X days ago. All I have to do it to generate the date of X day ago and place in similar filename structure and then call s3cmd del s3://BUCKETNAME/SUB/DIRECTORY/OLD_FILENAME

Here is the complete script:

#!/bin/bash
set -e
is_quiet="false"

mysql_user="naishe"
mysql_pass="mysqlp@ssword"
mysql_db="appdb"

staging_dir="/tmp/mysql_bkp"
logfile="/var/log/mysql.bkp"

s3_bucket="backupbucket/www/mysql"
aws_access_key=""
aws_secret_key=""

keep_for_days=30
today=$( date '+%Y-%m-%d' )
remove_bkp_on=$( date -d "${today} -${keep_for_days} days" '+%Y-%m-%d' )
filename="www.naishe.in"
write_to="${filename}.${today}.sql"

delete_file="${filename}.${remove_bkp_on}.sql.tar"
fullpath_sqlfile="${staging_dir}/${write_to}"
fullpath_tarfile="${fullpath_sqlfile}.tar"

# conditinal printing
function echome(){
    if [ "${is_quiet}" != "true" ] ; then 
        echo -e "\033[0;34m\xE2\x9C\x93 $1\033[0m"
    fi
}

function start_bkp(){
    echome "--- MySQL Backup on $( date '+%b %d, %Y' ) ---"
    #delete staging dir if any
    echome "Cleaning staging directory: ${staging_dir}"
    rm -rf ${staging_dir}
    mkdir ${staging_dir}

    #take a MySQL dump
    echome "Starting MySQL backup..."
    echome "mysqldump --user ${mysql_user} --password=${mysql_pass} --add-locks --flush-privileges --add-drop-table --complete-insert --extended-insert --single-transaction --database ${mysql_db} > ${fullpath_sqlfile}"

    mysqldump --user ${mysql_user} --password=${mysql_pass} --add-locks --flush-privileges --add-drop-table --complete-insert --extended-insert --single-transaction --database ${mysql_db} > ${fullpath_sqlfile}

    #tar
    echome "Creating tar file..."
    echome "tar cf ${fullpath_tarfile} --directory=${staging_dir} ${write_to}"
    tar cf ${fullpath_tarfile} --directory=${staging_dir} ${write_to}

    #upload to S3
    echome "Uploading to S3..."
    echome "s3cmd put ${fullpath_tarfile} \"s3://${s3_bucket}/\""
    s3cmd put ${fullpath_tarfile} "s3://${s3_bucket}/"

    echome "Deleting the file uploaded ${keep_for_days} days ago..."
    echome "s3cmd del \"s3://${s3_bucket}/${delete_file}\""
    s3cmd del "s3://${s3_bucket}/${delete_file}"

    #delete staging folder
    echome "Removing staging directory: ${staging_dir}"
    rm -rf ${staging_dir}
    
    echome "--- MySQL Backup Completed ---"
    echome "   "
}

start_bkp >> ${logfile}

Setting up Cron job: Place this script somewhere safe. Provide appropriate permission to it. I kept it under /opt/scripts/mysql_backup.sh, and appended this line to crontab -e

0 4 * * * /opt/scripts/mysql_backup.sh

this runsthe script daily at 4AM.