How to: Backup MySQL database and send to Amazon S3

Here's how to use Amazon S3 to keep your MySQL backups.

Make a file e.g.: mysqltos3.sh in your server, for example inside your home directory like: /home/mysqltos3.sh and paste the following code:

#!/bin/bash
S3_BUCKET=S3-BUCKETNAME
DATE=`date +%d%m%Y_%H%M`
BACKUP_LOC=/home/$DATE

mysql_backup(){
mkdir $BACKUP_LOC
mysqldump -uDBADMIN -pDBPASSWORD DBNAME > $BACKUP_LOC/sqlbackup_$DATE.sql

   s3cmd ls s3://$S3_BUCKET/sqlbackup/$DATE > /tmp/log.txt
   grep -lr "$DATE" /tmp/log.txt
  if [ $? -ne 0 ]
    then
    mkdir /tmp/$DATE
    s3cmd put -r /tmp/$DATE s3://$S3_BUCKET/sqlbackup/
    s3cmd sync -r $BACKUP_LOC s3://$S3_BUCKET/sqlbackup/
  else
    s3cmd sync -r $BACKUP_LOC s3://$S3_BUCKET/sqlbackup/
  fi
}
mysql_backup
exit 0


You will need to replace S3-BUCKETNAME with your Amazon bucket name and
DBADMIN DBPASSWORD DBNAME with the username password and name or the database.


If you want to backup ALL databases, replace DBNAME with --all-databases

Give the file 755 permissions:

# chmod 755 /home/mysqltos3.sh

Now, the next time you want to make a backup just run:

# sh /home/mysqltos3.sh

----------------------------------