If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

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.

I actually use something similar, that involves a backup-server (called repository) and the
mysql-server (called mysql). I mention it, because it runs under windows, and might be
of interest for some people. The basics to create a more complex script, like yours, is
given. Note, there is no tar-zcf'ing, no backup_rotating, no mail-notification etc.
(otherwise I would write a tutorial of my own ).
It is running on a daily basis, using the windows scheduler.

'Again'? Dude, lighten up, this is my first how-to-install-dash-backup-something tutorial. Nevertheless, you're right, I should've included some restore-information. Read this as an addition to the tutorial.

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 &lt; /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 wish to express my gratitude to the people of Italy. Thank you for inventing pizza.