Scott Klarr Jr

Linux: Automatically backup your mysql databases daily

Automation Rocks

Updated and improved on March 7, 2008

We've all been there. In the back of your mind you know the importance of backing up critical data, but yet you still manage to let it slip into the To-Do....Later list. Thats where automation comes in to save the day[ta].

This script will not only backup all the databases on your server once a day, but it will also optimize/repair them -- and to keep things clean, it gzips the backups and automatically deletes any backups that are more than 3 days old. You must have the root mysql password in order to use this particular script, though.

The Script

First thing you want to do is hop into the root driver seat by changing to super user. You do this by typing su and then enter the root password when asked.

Create a directory where you will keep the backup files. For example: /home/mysqlbk/

Create a new file called backup.sh and paste the following.

#!/bin/sh cd "/home/mysqlbk" i=`date +%F` echo "Running - $i" >> log `mysqlcheck -Aorm -u root -p[XXXXX]` `mysqldump -u root -p[XXXXX] -AcQ --comments=false --allow-keywords | gzip > backup-$i.sql.gz` `find ./backup-* -mtime +3 -exec rm -f {} ;` echo -e "Finished\n" >> log

Replace both instances of [XXXXX] with the root mysql password. You will also need to modify the instance of /home/mysqlbk with the path you are using.

Give this file execution permissions:

chmod +x backup.sh

Setup Crob Job

We will now schedule this script to be ran once per day (or at whatever interval you want if you understand how to manipulate the cron job timing format)

crontab -e

An editor will now show up and there will probably be a few lines of cron jobs. Do not edit these! Goto the bottom of the file and create a new line. Enter the following onto the new line:

0 12 * * * /home/mysqlbk/backup.sh

Save the file and exit. Your done!

Restoring Backup

Backups will now be made every day, and any backups older than 3 days will be deleted. If you ever need to restore one of these backup files, you can do so with the following commands:

gunzip backup-XXX-XX.sql.gz mysql databaseName < backup-XXX-XX.sql