A poor mans MySQL backup solution.
Summary
In this tutorial you’ll find a poor-mans backup solution for a MySQL database server. Implementing this solution, you’ll end up with a script that:- creates daily local backups going back up to a month
- saves one of those backups each month. The others are discarded.
- mails a copy of the backups somewhere (root, for example), to create an offsite backup.
For Windows users
This tutorial is not for you. A MySQL Administator that provides backup functionality can be downloaded from http://www.mysql.com.
Background
I wanted to create a back-up solution for a database-server I had no regular physical access to. The databases I use aren’t that big and the server is connected to the internet through a DSL connection, so using E-mail to send out the backups wasn’t a problem. I chose to mail on Sundays, because my network traffic is low in the weekends. I came up with this SH-based script, which runs every night using cron.
I wrote this script on a FreeBSD machine, but you should be able to run it on most machines running a ‘sh’ shell. I have the script ran by a dedicated backup-user (‘backup’). The script uses these commands. Most are very basic, but if your OS doesn’t support them, you’ll have to replace them with an alternative suitable to your situation:- sh
- mail
- uuencode (to attach files to mail)
- date (returns current date)
- mysqldump (comes with MySQL)
- gzip (compress data)
Implementation
Below you’ll find the source of the script. Copy it into a file (preferably with a .sh extension), and use ‘chmod’ to make that file executable (chmod 500 filename.sh). Make sure to replace USER and PWD – you should enter a suitable MySQL account here.
Create the directories specified in the first few lines of the script. Defaults are these:- /home/backup/mysql
- /home/backup/mysql/old
- /home/backup/mysql/monthly
Now, run the script, and check for errors. Running the script should result in a file called something like backup_datebases-2004-10-29-02:00:00.sql.gz in /home/backup/mysql. If it happens to be the first of the month, you’ll find the file in the monthly/ directory. If it happens to be a Sunday, a copy will be mailed to root. Check the size of your backup, and make sure that you have at least 31 times that size as free space on your partition, plus one time that size for each month you plan to use this script.
Finally, setup cron to run this script daily, preferably at a time the databases aren’t used. As you can see in the filename, I’m running it at 2 AM.
Enter this to edit your cron table:
crontab -e
Add a line like this:
0 2 * * * /path/to/filename.sh
That’s it! Next Sunday, you should be mailed a copy of the backup.
Restore information
As said, this script outputs several gzipped tarfiles. After decompressing, you'll find ordinary SQL in these files (you should be able to look at it using any text editor, unless your database consists of binary data, of course).
You can use this SQL to restore your databases, or parts of it, if needed. Normally, you'd issue this command to do perform a restore: mysql -u root -p < /path/to/ungzippedfile.sql. You’ll be prompted for a password before any restoring takes place.
Depending on your situation, you could have to add some commands (to delete existing/left-over old data for example). You’ll have to make an assessment of your current situation before you do any restoring. Don’t just execute commands, or you might end up creating a bigger mess than the one you were already in. Databases with false information usually are a bigger problem than databases with no information at all.
You could alter the parameters for mysqldump used in the backup-script, so that the resulting data matches your criteria better. I used the --opt parameter, which is shorthand for a bunch of default parameters, but you might want to include or exclude some others. For more information, have a look at the MySQL documentation, paragraph 8.8 ("The mysqldump Database Backup Program") at http://dev.mysql.com/doc/mysql/en/mysqldump.html.
I found this script to be useful, but if you have any suggestions to add functionality, or improve this script in any other way, please reply to this thread.
Script code
Code:
#!/bin/sh
date=`date "+%Y-%m-%d-%H:%M:%S"`
file="backup_datebases-$date.sql.gz"
workdir="/home/backup/mysql"
old="$workdir/old"
monthly="$workdir/monthly"
if [ -d $workdir ] && [ -d $old ] && [ -d $monthly ]; then
# Toss out old backups each month
if [ `date "+%d"` = 1 ]; then
mv $workdir/backup* $old/
fi
# Dump all databases.
/usr/local/bin/mysqldump --all-databases -u USER --password="PWD" --opt | gzip > $workdir/$file
# Finish tossing out those old backups.
if [ `date "+%d"` = 1 ]; then
rm $old/*
fi
# Mail a copy to root each sunday (offsite backup)
if [ `date "+%w"` = 0 ]; then
uuencode $workdir/$file $file | mail -s "Uuencoded database backup $file" root
fi
# Save one copy of the databases each month in monthly/
if [ `date "+%d"` = 1 ]; then
cp $workdir/$file $monthly/
fi
else
echo "Database-backup not executed at $date - Working directories are missing.";
fi