Spiked Software Coding Articles

I have a MySQL 5 Database, running inside Ubuntu 9.04. I have around 10 databases that are modified reasonably frequently, so obviously they need to be backed up incase something terrible happens.

There is a very nice command called ‘mysqldump’, which can do pretty much everything we are after for us! Brilliant, let’s see how it works! Fire up your favourite command line, i’ll assume we are using gnome-terminal for this post.

As you can see, it’s got a pretty simple syntax, just like the actual mysql command itself, where it accepts a host, user and password.

$ mysqldump -uUSER -pPASSWORD -hHOST

For this tutorial, i will also assume you have a local database, as i will be running this off of localhost.

Let’s run a backup of everything inside of our MySQL database locally! This will not alter any data in your mysql database, it is only a read, there is no writing.

$ mysqldump -uroot -piamroot -hlocalhost --all-databases

What?! What was that mess it just printed all over my screen? Is probably what your thinking. That ‘mess’ is an entire sql script that will restore your database back to it’s current state. Not very helpful if we can only see it on the command line right? We need it stored in a file some place. So let’s pipe out that data to a SQL file, that we could load up and read over if we really wanted to.

$ mysqldump -uroot -piamroot -hlocalhost --all-databases > all-databases.sql

This will now dump all of that output into a file called ‘all-databases’ which will be placed in the current folder you are in inside your shell.We can confirm this by typing..

$ ls -allh | grep all-databases.sql
#Should print out something simlar...
-rw-r--r--  1 dean dean 420K 2009-08-21 16:55 all-databases.sql

So we know we can back up our database now, but we don’t really want to back it up to the same file name every time, one backup is never enough. Someone will delete something and want a backup from 3 days ago to bring back one row of data, i know, it’s happened too many times to me. So let’s timestamp it, that way we can have months worth (i suggest a weeks worth, and then 1 a week for 3 months, but thats for another post).

$ mysqldump -uroot -piamroot -hlocalhost --all-databases > mysql-`date -I`-backup.sql

This will now dump our database to a file wth a naming convention of ‘mysql-YYYY-MM-DD-backup.sql’, so it should be easily found and very archivable.

Great, so we can backup a database, we can timestamp it… we’re done? No, not yet. We should probably compress the output, just to save some more space. Yet another pretty simple process.

$ mysqldump -uroot -piamroot -hlocalhost --all-databases | gzip -9 > mysql-`date -I`-backup.sql.gz

As you can see, we now pipe the output of mysqldump into this new fancy gzip command. Which will compress the output from mysqldump into a compressed file. gzip takes a parameter for how much compression we want, it ranges from 1 to 9, 9 being the best (slowest) and 1 being the worst (fastest). I use 9, because i want to save as much space as possible. I’m tight like that.

If we run another ls to see how big our backup is… it should be much smaller now.

$ ls -allh | grep mysql-*-backup.sql.gz

We should see…

-rw-r--r--  1 dean dean 112K 2009-08-21 17:18 mysql-2009-08-21-backup.sql.gz

As you can see, that compared to my previous file, is almost 4 times as small! We’ve gone from 420kb to 112kb! That’s quite a save.

Upto this point, we have a backup of every database in our MySQL server, it is compressed and time stamped… now we should schedule it every night. I run my backups from Midnight to 1am, so we’ll set it to run at 10 past midnight using cron. I only use cron, so if you use something else, please consult the manual in setting this up. I also want my cron jobs to run as root, in this example at least.

$ sudo crontab -e

There is our cron tab file, it could be empty, it could have some cron jobs already in it, the least you should see is…

# m h  dom mon dow   command

Let us add in our cron job, that will backup the entire mysql database at 10 past midnight, every night.

# m h  dom mon dow   command
10 00 * * * mysqldump -uroot -piamroot -hlocalhost --all-databases | gzip -9 > /mnt/backup/mysql-`date -I`-backup.sql.gz

Save this file and exit. That’s it, all done. At 10 past midnight, that command will execute.

There are just a few thigs to note…

1) I have changed the path of the exported file, i mount a drive under the name backup where i store all my backups. You should change this to suite your needs.
2) You should create a mysql user with read-only rights to your database to prevent anything malicious happening to your database by accident.
3) I Also suggest creating a new user on the machine which has only read access, but write access to your backup folder which has the job scheduled on their crontab.

That’s all from me… i hope this helps someone how it helped me!