Shell script to backup a Mysql database and save it on a remote server using Ftp

The following shell script will dump the mysql database and will save the .sql file on a remote location using Ftp. This script will create a backup file including the current date so you can have multiple copies of the backups of the same database under one directory.

Create a file called mysqlbkup.sh

# vi /root/mysqlbkup.sh

and paste the following code in the file as it is.

##############START OF THE SCRIPT##############

#!/bin/bash

# Specify the temporary backup directory.
BKUPDIR="/tmp"

# Specify Database Name
dbname="dbname_here"

# Save the current date
date=`date '+%Y-%m-%d'`

# Specify Ftp details of the remote server.
ftpserver="FtpServerIP"
ftpuser="your ftp user"
ftppass="your ftp password"

# Dump the mysql database with the current date and compress it.
# Save the mysql password in a file and specify the password file
# path in the below command.

/usr/bin/mysqldump -uroot -p`cat /path/to/pass/file` $dbname\
 | gzip > $BKUPDIR/$date.$dbname.sql.gz

# Change directory to the backup directory.
cd $BKUPDIR

# Upload the backup
ftp -n $ftpserver <<!EOF!
user $ftpuser $ftppass
binary
prompt
mput *.sql.gz
quit
!EOF!

# Remove the local backup file.
/bin/rm -f /$BKUPDIR/$date.$dbname.sql.gz

##############END OF THE SCRIPT##############

Make sure to specify your db_name, ftpserver, ftpuser and ftppass values in the script.

Save the file and schedule a cronjob to execute the file on daily basis, say during night hours at 1.00AM. Edit the cron file

# crontab -e

and set the following cronjob

0 1 * * * /bin/sh /root/mysqlbkup.sh

save the file and restart the crond service

# service crond service

The script will work on a Linux/Plesk server as well. You just have to replace the mysqldump line in the script with the following

/usr/bin/mysqldump -uadmin -p`cat /etc/psa/.psa.shadow` $dbname\
 | gzip > $BKUPDIR/$date.$dbname.sql.gz

Note: Leave a comment if you have any suggestions, questions OR have received any error message using this script.

This entry was posted on Friday, July 23rd, 2010 and is filed under Scripts. You can follow any responses to this entry through the RSS 2.0 feed. Both comments and pings are currently closed.

2 Responses to “Shell script to backup a Mysql database and save it on a remote server using Ftp”

  1. PdC

    Great script!
    Thank you for your share 😉

  2. Cao Anh

    Yeah, very great shell!!!
    Thanks you so much 🙂