2004-04-02, 07:13 PM
This script will backup your mysql databases, gzip them up, ftp them off site, email you confirmation. For emailing you you have to create a file with the text for the body of the email. The email file used in this script is
Quote:/usr/local/logs/sqlbackupsqlbackup could have something like this in it.
Quote:Mysql database backup was successfully completed. Your a demigod!
To do several databases just do like this to backup 4 databases named db1 - db4
Quote:databases="db1 db2 db3 db4"The Actual Script!!!
Quote:#!/bin/sh
# This script will backup one or more mySQL databases
# and then optionally email them and/or FTP them
# This script will create a different backup file for each database by day of the week
# i.e. 1-dbname1.sql.gz for database=dbname1 on Monday (day=1)
# This is a trick so that you never have more than 7 days worth of backups on your FTP server.
# as the weeks rotate, the files from the same day of the prev week are overwritten.
############################################################
#===> site-specific variables - customize for your site
# List all of the MySQL databases that you want to backup in here,
# each seperated by a space
databases="<db name here>"
# Directory where you want the backup files to be placed
backupdir=/usr/local/backup
# MySQL dump command, use the full path name here
mysqldumpcmd=/usr/local/mysql/bin/mysqldump
# MySQL Username and password
userpassword=" --user=<username here> --password=<password here>"
# 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="y"
subject="mySQL Backup"
mailto="<email here>"
#===> site-specific variables for FTP
ftpbackup="n"
ftpserver="<ftp server here>"
ftpuser="<login here>"
ftppasswd="<pass here>"
# If you are keeping the backups in a subdir to your FTP root
ftpdir="forums"
#===> END site-specific variables - customize for your site
############################################################
# Get the Day of the Week (0-6)
# This allows to save one backup for each day of the week
# Just alter the date command if you want to use a timestamp
DOW=`date +%C%y%m%d`
# 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}/${DOW}-${database}.sql
done
# Compress all of our backup files
echo "Compressing Dump Files"
for database in $databases
do
rm -f ${backupdir}/${DOW}-${database}.sql.gz
$gzip ${backupdir}/${DOW}-${database}.sql
done
# Send the backups via email
if [ $sendbackup = "y" ]
then
for database in $databases
do
# $uuencode ${backupdir}/${DOW}-${database}.sql.gz > ${backupdir}/${database}.sql.gz.uu
# $mail -s "$subject : $database" $mailto < ${backupdir}/${DOW}-${database}.sql.gz.uu
$mail -s "$subject : $database" $mailto </usr/local/logs/sqlbackup
done
fi
# FTP it to the off-site server
echo "FTP file to $ftpserver FTP server"
if [ $ftpbackup = "y" ]
then
for database in $databases
do
echo "==> ${backupdir}/${DOW}-${database}.sql.gz"
ftp -n $ftpserver <<EOF
user $ftpuser $ftppasswd
bin
prompt
cd $ftpdir
lcd ${backupdir}
put ${DOW}-${database}.sql.gz
quit
EOF
done
fi
# And we're done
ls -l ${backupdir}
echo "Dump Complete!"
exit
Note to attach a backup to your email you have to use "uuencode" which I personally do not do as I would rather not attach several megs or hundreds of megs to my email.
Strabo