MySQL Backup to FTP and Email Shell Script for Cron v2.1

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
# 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

# directory to backup to

# date format that is appended to filename
DATE=date +'%m-%d-%Y'

#———————-MySQL Settings——————–#

# your MySQL server’s location (IP address is best)

# MySQL username

# MySQL 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.

#———————-Mail Settings——————–#

# set to ‘y’ if you’d like to be emailed the backup (requires mutt)

# email addresses to send backups to, separated by a space
EMAILS=”[email protected] [email protected] [email protected]


#———————-FTP Settings——————–#

# set “FTP=y” if you want to enable FTP backups

# FTP server settings; should be self-explanatory

# directory to backup to. if it doesn’t exist, file will be uploaded to
# first logged-in directory

#——————-Deletion Settings——————-#

# delete old files?

# how many days of backups do you want to keep?

#———————-End of Settings——————#

# check of the backup directory exists
# if not, create it
if [ -e $BACKDIR ]
echo Backups directory already exists
mkdir $BACKDIR

if [ $DUMPALL = “y” ]
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

echo “Backing up MySQL databases…”
for database in $DBS
mysqldump -h $HOST –user=$USER –password=$PASS $database > \\
gzip -f -9 $BACKDIR/$SERVER-mysqlbackup-$database-$DATE.sql

# 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” ]
BODY=”Your backup is ready! Find more useful scripts and info at”
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”

if [ $FTP = “y” ]
echo “Initiating FTP connection…”
ATTACH=for file in *$DATE.sql.gz; do echo -n -e "put ${file}\n"; done

ftp -nv < open $FTPHOST
echo -e “FTP transfer complete! \n”

if [ $DELETE = “y” ]
find $BACKDIR -name “*.sql.gz” -mtime $DAYS -exec rm {} \\;

if [ $DAYS = “1” ]
echo “Yesterday’s backup has been deleted.”
echo “The backup from $DAYS days ago has been deleted.”

echo Your backup is complete!


108 comments on “MySQL Backup to FTP and Email Shell Script for Cron v2.1
  1. Kimberly says:

    Thanks for the tips!
    I’ll give it a try.

  2. Someguy says:

    Would be nice to have option to keep monthly backup for each database.

  3. Ameir Abdeldayem says:

    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.

  4. Idan says:

    Thank you, this script saved me.
    Works really great!

  5. Faruq says:

    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 🙂

  6. Ameir Abdeldayem says:

    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:

    ./ > output.txt 2>&1

    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!

  7. AG says:

    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?

  8. Ameir Abdeldayem says:

    Firstly, please try the updated script here:;

    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.

  9. AFisher says:

    i personally prefer using mysql gui tool like dbforge studio for mysql to make a backup. but unfortunately this tool is for windows

  10. Giel Berkers says:

    Thanks for the script! I only had to remove the -e-flag in ATTACH=for file in *$DATE.sql.gz; do echo -n -e "put ${file}\n"; done because it gave me the following error: ?Invalid command.

  11. Craig says:

    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”, in the .conf file but it did not work. I don’t know what else to try.
    Thank you for considering this.

  12. 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!

1 Pings/Trackbacks for "MySQL Backup to FTP and Email Shell Script for Cron v2.1"
  1. […] below: You can view the previous version here. If there’s a feature that’s missing that you’d like to see, leave a comment […]

Leave a Reply

Your email address will not be published. Required fields are marked *