Automated MySQL Backups


Problem

There are various reasons we probably don't have to explain, but backups are important. While your hosting company usually takes care of the backups, sometimes they require you to restore a whole server in order to get access to a single missing table row. Having your own backup of the database can be useful and doesn't cost much if you have a small database.



Solution

We are going to create a simple shell script we can run using cron to create hourly backups.

First we need to make sure that mysqldump can access our database without asking for a password. In order to do that, we create a new file called .my.cnf in the home of the user which will run the cronjob. Put the following content in it, but change the user and password to match yours:

[mysqldump]
user=YOUR_USER
password=YOUR_PASSWORD

Next we are going to create a directory called db-backups in our home directory, then we create a shell script called db-backup.sh in our home directory with the following content:

#!/bin/sh
find ~/db-backups/* -mtime +30 -type f -delete
mysqldump YOUR_DATABASE | bzip2 -c > ~/db-backups/YOUR_DATABASE-$(date +%Y-%m-%d-%H.%M.%S).sql.bz2

The find command removes all files older than a month to keep the necessary storage limited. The mysqldump command creates a dump file, compresses it using bzip2and keeps it in our db-backupsdirectory.

Our shell script needs to be executable, let's make that happen:

chmod +x db-backup.sh

You can now test your script by running ~/db-backup.sh.

If everything is running fine you can type crontab -e and paste the following snippet in it to run the cronjob every hour:

@daily ~/db-backup.sh

Save the cronjob and wait for the magic to happen, one new file every hour!

Comments




Please sign-in to post a comment