I 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.
#! /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=”[email protected] [email protected] [email protected]”
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 <
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!
Thanks for the tips!
I’ll give it a try.
Would be nice to have option to keep monthly backup for each database.
You can run this monthly if you’d like for that effect. In that case, just ensure that the DAYS variable is set to 30 days or less.
Thank you, this script saved me.
Works really great!
excellent script, just saw it.
the only addition i’d make is to create a file in /var/log that would log all the echo’d dates and messages in there, so i have a history of the sql backup status. that way if you drop this script in your cron.daily or cron.x , your /var/log lets you know what went down whilst it was doing backups!
not a seasoned scripter, but i think someone could code it better than myself, with a flag to log or not to log. v2.2 maybe 🙂
Hi Faruq,
If you set the MAILTO variable in your crontab, you will get all of the output of the script emailed to you.
If you want to write all the output to a file, you can simply use a redirect in Linux. For example, you can do something like:
[code]
./backupmysql.sh > output.txt 2>&1
[/code]
Note that if you write the log to /var/log, you will need root access. One ‘beauty’ of this script is that it does not need root access for what it does, but you can have it do privileged things if you code it to and give it the rights to.
Hope that helps!
I have problem with transfer to ftp, i get the message:
“200 PORT command successful
425 Could not open data connection to port 43978: Connection timed out
221-Goodbye. You uploaded 0 and downloaded 0 kbytes.
221 Logout.”
with random port numbers. What is the problem?
Firstly, please try the updated script here:
http://www.ameir.net/blog/archives/48-MySQL-Backup-to-FTP-and-Email-Shell-Script-for-Cron-v2.2.html	
Your issue probably has something to do with passive vs active FTP. The updated version uses passive FTP. Please try it and let me know if you’re still running into issues.
i personally prefer using mysql gui tool like dbforge studio for mysql to make a backup. but unfortunately this tool is for windows http://www.devart.com/dbforge/mysql/studio/mysql-backup.html
Thanks for the script! I only had to remove the
-e-flag inATTACH=for file in *$DATE.sql.gz; do echo -n -e "put ${file}\n"; donebecause it gave me the following error:?Invalid command.I have a feature request please. I have been using this script for a while for its simplicity in using FTP to send out database backups. I recently had to change the default port on the database server from port 21 (ftp default) to a non-standard port (ex:2121). I also had to change this within the /etc/services file on the database server by changing the default ftp port number in this file from 21 to 2121. I think that this broke the script in that it now wants to send the backup to the remote ftp server utilizing its own non-standard port number (ex:2121). Naturally it gets a “connection error” when it tries to connect to the ftp server. Again, I made no changes to the .conf file or script other than to change the /etc/services file on the database server. I broke the script, but I hope it can be made to work again.
My request is this: can you add the ability to set the ftp port number to be used on the remote ftp server? I tried using as the “ftp host”, example.com:2121 in the .conf file but it did not work. I don’t know what else to try.
Thank you for considering this.
Thanks for your comment, Craig. I’ve updated the script to support alternate FTP ports, so you should be good to go. You shouldn’t need to updated /etc/services at all; that’s used to resolve non-numerical ports (e.g. ‘ftp’ < -> ’21’). Let me know if you run into any issues with the update!