Script to Backup MySQL Databases
Posted: October 27th, 2007, 8:47 am
Just copy the code and save it into a text file on your server (Let's call it /usr/local/sbin/dumpdatabases, but feel free to place it in any directory in your path, and call it whatever you want). Just make sure you change the list of databases at the top to reflect the ones you want to backup, and change the username and password for MySQL. Most other things should be ok, but you might need to change the pathnames if different on your system. Also make sure you do a chmod +x /usr/local/sbin/dumpdatabases to make it executable.
If you want to run this automatically through cron (recommended so you don't forget): just do a crontab -e and add the following line:
This will run it everyday at 3:00am. Feel free to change the time to something else if your databases aren't as busy at some other time.
Code: Select all
#!/bin/sh
# List all of the MySQL databases that you want to backup in here,
# each seperated by a space
databases="mysql forum"
# Directory where you want the backup files to be placed
backupdir=/backup
# MySQL dump command, use the full path name here
mysqldumpcmd=/usr/local/mysql/bin/mysqldump
# MySQL Username and password
userpassword=" --user=root --password=password"
# MySQL dump options
dumpoptions=" --quick --add-drop-table --add-locks --extended-insert --lock-tables"
# Unix Commands
gzip=/bin/gzip
uuencode=/usr/bin/uuencode
mail=/bin/mail
# Send Backup? Would you like the backup emailed to you?
# Set to "y" if you do
sendbackup="n"
subject="Your MySQL Backup"
mailto="you@yourdomain.com"
# Create our backup directory if not already there
mkdir -p ${backupdir}
if [ ! -d ${backupdir} ]
then
echo "Not a directory: ${backupdir}"
exit 1
fi
# Dump all of our databases
echo "Dumping MySQL Databases"
for database in $databases
do
$mysqldumpcmd $userpassword $dumpoptions $database > ${backupdir}/${database}.sql
done
# Compress all of our backup files
echo "Compressing Dump Files"
for database in $databases
do
rm -f ${backupdir}/${database}.sql.gz
$gzip ${backupdir}/${database}.sql
done
# Send the backups via email
if [ $sendbackup = "y" ]
then
for database in $databases
do
$uuencode ${backupdir}/${database}.sql.gz > ${backupdir}/${database}.sql.gz.uu
$mail -s "$subject : $database" $mailto < ${backupdir}/${database}.sql.gz.uu
done
fi
# And we're done
ls -l ${backupdir}
echo "Dump Complete!"
exit
Code: Select all
0 3 * * * /usr/local/sbin/dumpdatabases