Backup a MySQL Database Nightly

I run several websites, which use MySQL, and I've discovered the importance of backing up my databases regularly. I've had tables become corrupt, and hard drives go without warning. It's important to have a backup plan so you can restore your website quickly if something goes wrong.

The easiest way to backup a MySQL database is to use the mysqldump program, which is included with MySQL server. The mysqldump program allows you to dump a database or table to an SQL file which can be quickly restored using the MySQL command line client. You can ensure your backup is executed on a regular basis using the crontab.

Here is a sample shell script for backing up a MySQL database:

#!/bin/sh

DATE=`date +%Y-%m-%d`
cd /PATH_TO_BACKUP/db_backup
mkdir $DATE
/usr/local/mysql/bin/mysqldump -u root DATABASENAME > /PATH_TO_BACKUP/db_backup/$DATE/DATABASENAME.sql
gzip $DATE/DATABASENAME.sql

The first line stores the current date as a string of the form YYYY-MM-DD, the next two lines change to the directory where you want the backup to be stored and creates a folder named the current date. The next line creates the actual backup using mysqldump, using the root@localhost account. The final line compresses the backup using the gzip utility, this is optional, but it is a good idea if you have a large database.

You can add this script to your crontab and have it execute daily by first typing:

sudo crontab -e

Then adding the following line into your crontab:

1 0 * * * sh /PATH_TO_BACKUP_SCRIPT/backup_mysql.sh

This will execute the script at one minute past midnight every night, but you can easily change this to suit your needs.

We can easily restore from our backup in a few easy steps. First open Terminal and navigate to the folder where our backup is stored. Then type:

gunzip -c DATABASENAME.sql.gz | mysql -u root DATABASENAME

This will decompress our backup, recreate the table structure and import all the data that was stored in the tables. Make sure you have created the database in MySQL prior attempting to restore.