I needed a script to backup a mysql database daily and send it to Amazon s3 automatically. Here is a tutorial of how to do it.
Backing up a database can be done in many ways. Since the project I was working on is a Drupal site there are modules for this. However I wanted to keep the backup handling out of Drupal so that backups would be ran even if problems would occur on the site.
I started with reading my friend Tobias notes on how to backup databases with cron. Before you start you need to have an Amazon s3 account and both the Access and Secret Key.
Set up the cron job
Edit an existing or create a new cron file and put the following in it. In my case /etc/cron.d/mysql-backup.
59 7 * * * root /root/mysqlbackup.sh
This tells cron to run the script /root/mysqlbackup.sh as root user at 7:59 every day.
Database credentials
Keep the credentials safe in a separate file. Create /var/mysql.cfn and set permissions $ chmod 400 /root/mysqlbackup.sh. Put the following in the file.
[client]
user="myuser"
password="mypassword"
Set up s3 command line tools
Install s3 command line tools $ sudo aptitude install s3cmd. Run the configuration as root user $ sudo -u root s3cmd --configure. You will be asked to put in your keys and there are also a couple of other options which you can skip (I decided to send the backups over https). When the configuration is set up you can create a new bucket $ s3cmd mb mybucket-for-databases check in the s3 UI that the bucket got created.
Bash script
Create the file /root/mysqlbackup.sh. Edit the file.
#!/bin/bash
S3BUCKET="mys3bucket"
DBNAME="mydatabase"
FILE=$DBNAME-`date "+%Y%m%d-%H%M"`.sql.gz
mysqldump --defaults-extra-file=/var/mysql.cfn $DBNAME | gzip -9 > /var/backups/$FILE
/usr/bin/s3cmd --config /root/.s3cfg put /var/backups/$FILE s3://$S3BUCKET/ >> /var/log/mysqlback.log
sleep 5
rm /var/backups/$FILE
The script creates the filename, dumps the database using our credentials in mysql.cfn and moves the database to s3 with the put command. The last line removes the file from our server.
That's it, you should now have a new backup named mydatabase-YYYYMMDD-HHMM.sql.gz daily in your bucket. Under Properties > Lifecycle in the s3 UI for the bucket you can set up rules for how long the backups should be kept in s3.
If it doesn't work start with checking the syslog $ tail -f /var/log/syslog when the cron job is executed.