Many people look for options or ways to auto backup there database. I actually needed one for my self, so I began searching online for the best possible solution. luckily I found few geniuses on net describing various ways to write a script. The whole credit for this script goes to all those unknown contributors. Thank you so much.
Autobackup all or selective databases.
Encrypt them for security.
FTP my backups to remote servers (any plateform)
Email notification to the admin regarding the backup.
I have clubbed various options I found online into a single script. It first take auto-backup of your databases, encrypt them, compress them into a gzip file, send an email notification to the admin and then finally FTP it to remote server (in my case a window server). I run my script using traditional cron jobs.
—————————————————————————————————————————————————-
# your MySQL server’s name
SERVER=`localhost`
# directory to backup to
BACKDIR=~/backups/mysql
# date format that is appended to filename
DATE=`date +’%m-%d-%Y-%H%M’`
#———————-MySQL Settings——————–#
# your MySQL server’s location (IP address is best)
HOST=localhost
# MySQL username
USER=”root”
# MySQL password
PASS=”you db password comes here”
# List all of the MySQL databases that you want to backup in here,
# each separated by a space
DBS=”sample_database1 sample _database2″
# set to ‘y’ if you want to backup all your databases. this will override
# the database selection above.
DUMPALL=n
#———————-Mail Settings——————–#
# set to ‘y’ if you’d like to be emailed the backup (requires mutt)
MAIL=y
# email addresses to send backups to, separated by a space
EMAILS=”your email id comes here”
SUBJECT=”MySQL backup on $SERVER ($DATE)”
#———————-FTP Settings——————–#
# set “FTP=y” if you want to enable FTP backups
FTP=y
# FTP server settings; group each remote server using arrays
# you can have unlimited remote FTP servers
FTPHOST[0]=”000.000.000.000″
FTPUSER[0]=”your ftp username comes here”
FTPPASS[0]=”your ftp password comes here”
# directory to backup to; if it doesn’t exist, file will be uploaded to
# first logged-in directory; the array indices correspond to the FTP info above
FTPDIR[0]=”the name of your backup folder”
#——————-Deletion Settings——————-#
# delete old files?
DELETE=y
# how many days of backups do you want to keep?
DAYS=15
#———————-End of Settings——————#
# check of the backup directory exists
# if not, create it
if [ ! -d $BACKDIR ]; then
echo -n “Creating $BACKDIR…”
mkdir -p $BACKDIR
echo “done!”
fi
if [ $DUMPALL = “y” ]; then
echo -n “Creating list of all your databases…”
DBS=`mysql -h $HOST –user=$USER –password=$PASS -Bse “show databases;”`
echo “done!”
fi
echo “Backing up MySQL databases…”
for database in $DBS
do
echo -n “Backing up database $database…”
mysqldump -h $HOST –user=$USER –password=$PASS $database > \
$BACKDIR/$SERVER-$database-$DATE-mysqlbackup.sql
gpg -e -r blackswan $BACKDIR/$SERVER-$database-$DATE-mysqlbackup.sql
gzip -f -9 $BACKDIR/$SERVER-$database-$DATE-mysqlbackup.sql.gpg
echo “done!”
done
# if you have the mail program ‘mutt’ installed on
# your server, this script will have mutt attach the backup
# and send it to the email addresses in $EMAILS
if [ $MAIL = “y” ]; then
BODY=”Your backup is ready!”
ATTACH=`for file in $BACKDIR/*$DATE-mysqlbackup.sql.gz; do echo -n “-a ${file} “; done`
echo uuencode $ATTACH | mail -s “$SUBJECT” $EMAILS
if [[ $? -ne 0 ]]; then
echo -e “ERROR: Your backup could not be emailed to you! \n”;
else
echo -e “Your backup has been emailed to you! \n”
fi
fi
if [ $FTP = “y” ]; then
echo “Initiating FTP connection…”
if [ $DELETE = “y” ]; then
OLDDBS=`cd $BACKDIR; find . -name “*-mysqlbackup.sql.gpg.gz” -mtime +$DAYS`
REMOVE=`for file in $OLDDBS; do echo -n -e “delete ${file}\n”; done`
fi
cd $BACKDIR
ATTACH=`for file in *$DATE-mysqlbackup.sql.gpg.gz; do echo -n -e “put ${file}\n”; done`
for KEY in “${!FTPHOST[@]}”
do
echo -e “\nConnecting to ${FTPHOST[$KEY]} with user ${FTPUSER[$KEY]}…”
ftp -nv <<EOF
open ${FTPHOST[$KEY]}
user ${FTPUSER[$KEY]} ${FTPPASS[$KEY]}
tick
cd ${FTPDIR[$KEY]}
$REMOVE
$ATTACH
quit
EOF
done
echo -e “FTP transfer complete! \n”
fi
if [ $DELETE = “y” ]; then
cd $BACKDIR; for file in $OLDDBS; do rm ${file}; done
if [ $DAYS = “1” ]; then
echo “Yesterday’s backup has been deleted.”
else
echo “The backups from $DAYS days ago and earlier have been deleted.”
fi
fi
echo “Your backup is complete!”
————————————————————————————————————————————-
Just replace the text with the details required. Script is self explainatary. Hope you find it usefull.
