AutomatedDbBackup

Automated database backup

While using YFi, there might be a few things you'd want to automate to make your life easier. I've automated the MySQL database backup and have it email to me every day. Here's how you can do the same: (This little howto is specific to Ubuntu, but should work on any Linux)

Lets create a folder where we keep everything:

$ mkdir ~/sqlbackups
$ cd ~/sqlbackups

Create a file called backupscript.sh and put the following in there:

1 2 3 4

#!/bin/sh /usr/bin/mysqldump -uYOURUSER -pYOURSQLPASSWORD yfi | gzip > /home/YOURUSER/sqlbackups/yfi.`date +"%Y%m%d"`.sql.gz /bin/echo | /usr/bin/mutt -s "MySQL backup `date +"%F"`" YOUR@EMAIL.COM -a /home/YOURUSER/sqlbackups/yfi.`date +"%Y%m%d"`.sql.gz /usr/bin/find /home/YOURUSER/sqlbackups/yfi* -mtime +14 -exec rm {} \;

Replace "YOURUSER", "YOURSQLPASSWORD" and "YOUR@…" with your details. The first line dumps the database and compresses it. We then email the file using mutt and then last we do a bit of cleanup and delete all sql dumps older than 14 days.

For that script to work, you will need to install mutt and on Ubuntu, it will also install postfix:

$ sudo apt-get install mutt

Your server may use a system email that doesn't exist in the real world and most mailservers will reject it because of that. Lets tune up postfix to take care of that. We're also going to change the message size limit as the database you will be emailing could grow larger than the default 10mb allowance in postfix. Edit /etc/postfix/main.cf and add these 2 lines:

message_size_limit = 20480000
canonical_maps = hash:/etc/postfix/canonical

Now edit/create /etc/postfix/canonical and add a line like below:

bob@uncle.yourdomain.com notbob@yourdomain.com

Eg, if your user on the server is called "bob" and your server name "uncle", the system email may look like "bob@…". This should change it to "notbob@…"

Lets finalise the changes:

$ postmap /etc/postfix/canonical
$ /etc/init.d/postfix reload

Make the script executable (chmod +x) and execute your script. It will email the database to you as an attachment. To make this happen automatically as a daily event, we create a cron job for it:

$ crontab -e

Add the following:

05 01 * * * /home/YOURUSER/sqlbackups/backupscript.sh >/dev/null 2>&1

There... no more worries about database backups.

edit hmmm, this wiki seems to shorten email addresses when not used in code block, but you should still make out whats going on.