Originally posted by torifile:
I've searched high and low for an answer to this question and I'm sure it's been answered before but....
I want to backup my mysql database on a regular basis. I know I need to set up a cron job to do it and I've found cronnix to let me do it. But how can I pass arguments in a cron script like the date/time of the backup so the backup doesn't get constantly overwritten by the new backup?
I'd like to backup daily (or more) and have a set number of backups before they start getting overwritten with newer files. Can anyone help? TIA.
I don't know anything about "cronnix".
I use a couple of generic and easily adjustable parts in most all of my cronjobs. I will give an example of a script that may be useful to you.
Code:
#!/bin/sh
####
## This script will dump a MySQL database to file
## with a stamp that corresponds to the
## day of the month (DOM). As the DOM cycles each month
## it will replace the file from the previous month.
##
####
# For verbosity.
say ()
{
echo "${PREFIX}> ${1}"
logger -t "$PREFIX" "$1"
}
# Timestamp and date of month
TS=`date +%Y%m%d-%H%M`
DOM=`date +%d`
# Start message
say "Starting daily dump of MySQL database..."
# Sanity checks
say "Checking sanity of backup volume..."
if [ -f /backup/volume/.MydumpOK ]
then {
say "Sanity checks passed..."
# Remove month old dump
rm -f /backup/volume/mydump.sql.$DOM
# Do actual dump
/usr/local/mysql/bin/mysqldump -A > /backup/volume/mydump.sql.$DOM
say "Dump COMPLETED."
}
else {
# Sanity checks fail
say "Sanity checks FAILED for /backup/volume PANIC: .MydumpOK not found"
}
fi
To use the script you will need to create a .MydumpOK marker file in whatever folder you were going to dump to. Just do:
touch /backup/volume/.MydumpOK
The reason for the sanity check is so you know you are dumping to a mounted volume. The "say" stanza will echo messages to the terminal when the script is run manually and to the system log. In general, sanity checks and verbosity is a good thing for critical scripts. You can add more of them if you like. Perhaps a test for whether the database is actually running.
You will also need a /var/private/root/.my.cnf file with the password to the database otherwise the mysqldump command may fail. The .my.cnf file should be mode 0600. I suggest that you do not put the database password in to the script directly.
The mydump.sql.$DOM files will be created as mydump.sql.12 (for today February 12 2004) and tomorrow the file name will be mydump.sql.13... and so on. Next month (March) todays dump will be deleted and replaced with a new dump. You will always have one months worth of dumps (after running it for at least a month).
As always... there are many many ways to do these things. I would be interested in what you finally come up with.
Top