MySQL Backup to FTP and Email Shell Script for Cron v2.1
23 09 2006I updated the script just a tad bit. Using the magic of sed, this version of the script makes a backup of each database individually when you choose to backup all of your databases. The previous version dumped all of the databases into a single file. When you run this script and have it emailed to you, you'll end up with multiple attachments, each attachment being a single database name. Why did I do this? Well, I myself needed to restore a database and I had all the databases in a single file. I ended up searching throughout the file to find where surrounding databases started or stopped and copied the info in between. It wasn't very fun, especially since the databases weren't very small. This script gets around that headache completely. If you still prefer to have all of your DB data dumped into a single file, search for "MySQL Backup to FTP and Email Shell Script for Cron v2". Both scripts are exactly the same besides this feature.
CODE:
#! /bin/bash
# Ameir Abdeldayem
# http://www.ameir.net
# You are free to modify and distribute this code,
# so long as you keep my name and URL in it.
# your MySQL server's name
SERVER=ameir.net
# directory to backup to
BACKDIR=~/backups
# date format that is appended to filename
DATE=`date +'%m-%d-%Y'`
#----------------------MySQL Settings--------------------#
# your MySQL server's location (IP address is best)
HOST=localhost
# MySQL username
USER=username
# MySQL password
PASS=password
# List all of the MySQL databases that you want to backup in here,
# each separated by a space
DBS="db1 db2"
# set to 'y' if you want to backup all your databases. this will override
# the database selection above.
DUMPALL=y
#----------------------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="1@gmail.com 2@inbox.com 3@goowy.com"
SUBJECT="MySQL backup on $SERVER ($DATE)"
#----------------------FTP Settings--------------------#
# set "FTP=y" if you want to enable FTP backups
FTP=n
# FTP server settings; should be self-explanatory
FTPHOST="ftp.server.com"
FTPUSER="username"
FTPPASS="pass"
# directory to backup to. if it doesn't exist, file will be uploaded to
# first logged-in directory
FTPDIR="backups"
#-------------------Deletion Settings-------------------#
# delete old files?
DELETE=y
# how many days of backups do you want to keep?
DAYS=3
#----------------------End of Settings------------------#
# check of the backup directory exists
# if not, create it
if [ -e $BACKDIR ]
then
echo Backups directory already exists
else
mkdir $BACKDIR
fi
if [ $DUMPALL = "y" ]
then
echo "Creating list of all your databases..."
mysql -h $HOST --user=$USER --password=$PASS -e "show databases;" > dbs_on_$SERVER.txt
# redefine list of databases to be backed up
DBS=`sed -e ':a;N;$!ba;s/\n/ /g' -e 's/Database //g' dbs_on_$SERVER.txt`
fi
echo "Backing up MySQL databases..."
for database in $DBS
do
mysqldump -h $HOST --user=$USER --password=$PASS $database > \
$BACKDIR/$SERVER-mysqlbackup-$database-$DATE.sql
gzip -f -9 $BACKDIR/$SERVER-mysqlbackup-$database-$DATE.sql
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! Find more useful scripts and info at http://www.ameir.net"
ATTACH=`for file in $BACKDIR/*$DATE.sql.gz; do echo -n "-a ${file} "; done`
echo "$BODY" | mutt -s "$SUBJECT" $ATTACH $EMAILS
echo -e "Your backup has been emailed to you! \n"
fi
if [ $FTP = "y" ]
then
echo "Initiating FTP connection..."
cd $BACKDIR
ATTACH=`for file in *$DATE.sql.gz; do echo -n -e "put ${file}\n"; done`
ftp -nv <<EOF
open $FTPHOST
user $FTPUSER $FTPPASS
cd $FTPDIR
$ATTACH
quit
EOF
echo -e "FTP transfer complete! \n"
fi
if [ $DELETE = "y" ]
then
find $BACKDIR -name "*.sql.gz" -mtime $DAYS -exec rm {} \;
if [ $DAYS = "1" ]
then
echo "Yesterday's backup has been deleted."
else
echo "The backup from $DAYS days ago has been deleted."
fi
fi
echo Your backup is complete!
# Ameir Abdeldayem
# http://www.ameir.net
# You are free to modify and distribute this code,
# so long as you keep my name and URL in it.
# your MySQL server's name
SERVER=ameir.net
# directory to backup to
BACKDIR=~/backups
# date format that is appended to filename
DATE=`date +'%m-%d-%Y'`
#----------------------MySQL Settings--------------------#
# your MySQL server's location (IP address is best)
HOST=localhost
# MySQL username
USER=username
# MySQL password
PASS=password
# List all of the MySQL databases that you want to backup in here,
# each separated by a space
DBS="db1 db2"
# set to 'y' if you want to backup all your databases. this will override
# the database selection above.
DUMPALL=y
#----------------------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="1@gmail.com 2@inbox.com 3@goowy.com"
SUBJECT="MySQL backup on $SERVER ($DATE)"
#----------------------FTP Settings--------------------#
# set "FTP=y" if you want to enable FTP backups
FTP=n
# FTP server settings; should be self-explanatory
FTPHOST="ftp.server.com"
FTPUSER="username"
FTPPASS="pass"
# directory to backup to. if it doesn't exist, file will be uploaded to
# first logged-in directory
FTPDIR="backups"
#-------------------Deletion Settings-------------------#
# delete old files?
DELETE=y
# how many days of backups do you want to keep?
DAYS=3
#----------------------End of Settings------------------#
# check of the backup directory exists
# if not, create it
if [ -e $BACKDIR ]
then
echo Backups directory already exists
else
mkdir $BACKDIR
fi
if [ $DUMPALL = "y" ]
then
echo "Creating list of all your databases..."
mysql -h $HOST --user=$USER --password=$PASS -e "show databases;" > dbs_on_$SERVER.txt
# redefine list of databases to be backed up
DBS=`sed -e ':a;N;$!ba;s/\n/ /g' -e 's/Database //g' dbs_on_$SERVER.txt`
fi
echo "Backing up MySQL databases..."
for database in $DBS
do
mysqldump -h $HOST --user=$USER --password=$PASS $database > \
$BACKDIR/$SERVER-mysqlbackup-$database-$DATE.sql
gzip -f -9 $BACKDIR/$SERVER-mysqlbackup-$database-$DATE.sql
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! Find more useful scripts and info at http://www.ameir.net"
ATTACH=`for file in $BACKDIR/*$DATE.sql.gz; do echo -n "-a ${file} "; done`
echo "$BODY" | mutt -s "$SUBJECT" $ATTACH $EMAILS
echo -e "Your backup has been emailed to you! \n"
fi
if [ $FTP = "y" ]
then
echo "Initiating FTP connection..."
cd $BACKDIR
ATTACH=`for file in *$DATE.sql.gz; do echo -n -e "put ${file}\n"; done`
ftp -nv <<EOF
open $FTPHOST
user $FTPUSER $FTPPASS
cd $FTPDIR
$ATTACH
quit
EOF
echo -e "FTP transfer complete! \n"
fi
if [ $DELETE = "y" ]
then
find $BACKDIR -name "*.sql.gz" -mtime $DAYS -exec rm {} \;
if [ $DAYS = "1" ]
then
echo "Yesterday's backup has been deleted."
else
echo "The backup from $DAYS days ago has been deleted."
fi
fi
echo Your backup is complete!
Download backupmysql-2.sh
Categories : Linux Luvin'
Trackbacks : No Trackbacks »
I may add a donation link up sometime, but in the meantime, you can click on the Google ads at the right of the page. Thanks for your support.
. I'm glad to hear that the script worked out well for you. I may make a couple of changes to it and offer it here; I'm sure it'll be useful to others.
Trackbacks
No Trackbacks