MySQL Database Backup to FTP Server - Shell Script

As a system administrator, you need to take backup on daily basis. Backups are very useful to recover data from any crashes or corruption. I have written a simple script to take database backup from MySQL server and upload it to FTP server. Being a system administrator, I recommend keeping a remote copy of your every backup. You can also try our new advance script for MySQL databases backup and upload to remote locations.

Create a shell script file and copy the below script. Then update all the required values and execute.

#!/bin/bash ###################################################### # Script Written by: Rahul Kumar # Date: Feb 21, 2013 ###################################################### DATE=`date +%d%b%y` LOCAL_BACKUP_DIR="/backup/" DB_NAME="test" DB_USER="root" DB_PASSWORD="your password" FTP_SERVER="ftp.tecadmin.net" FTP_USERNAME="ftp user name" FTP_PASSWORD="ftp user password" FTP_UPLOAD_DIR="/backup/" LOG_FILE=/backup/backup-DATE.log ############### Local Backup ######################## mysqldump -u $DB_USER -p$DB_PASSWORD $DB_NAME | gzip > $LOCAL_BACKUP_DIR/$DB_NAME-$DATE.sql.gz ############### UPLOAD to FTP Server ################ ftp -n $FTP_SERVER << EndFTP user "$FTP_USERNAME" "$FTP_PASSWORD" binary hash cd $FTP_UPLOAD_DIR #pwd lcd $LOCAL_BACKUP_DIR put "$DB_NAME-$DATE.sql.gz" bye EndFTP if test $? = 0 then echo "Database Successfully Uploaded to Ftp Server File Name $DB_NAME-$DATE.sql.gz " > $LOG_FILE else echo "Error in database Upload to Ftp Server" > $LOG_FILE fi123456789101112131415161718192021222324252627282930313233343536373839404142#!/bin/bash #######################################################  Script Written by: Rahul Kumar#  Date: Feb 21, 2013###################################################### DATE=`date +%d%b%y`LOCAL_BACKUP_DIR="/backup/"DB_NAME="test"DB_USER="root"DB_PASSWORD="your password"FTP_SERVER="ftp.tecadmin.net"FTP_USERNAME="ftp user name"FTP_PASSWORD="ftp user password"FTP_UPLOAD_DIR="/backup/"LOG_FILE=/backup/backup-DATE.log ############### Local Backup  ######################## mysqldump -u $DB_USER  -p$DB_PASSWORD $DB_NAME | gzip  > $LOCAL_BACKUP_DIR/$DB_NAME-$DATE.sql.gz ############### UPLOAD to FTP Server  ################ ftp -n $FTP_SERVER << EndFTPuser "$FTP_USERNAME" "$FTP_PASSWORD"binaryhashcd $FTP_UPLOAD_DIR#pwdlcd $LOCAL_BACKUP_DIRput "$DB_NAME-$DATE.sql.gz"byeEndFTP if test $? = 0then    echo "Database Successfully Uploaded to Ftp Server        File Name $DB_NAME-$DATE.sql.gz " > $LOG_FILEelse    echo "Error in database Upload to Ftp Server" > $LOG_FILEfi

Setup Details – Edit the above script for the following variable as per your system environment. Place all the values correctly to make the script work properly.

• LOCAL_BACKUP_DIR => Local direction path to store backup
• DB_NAME => database name
• DB_USER => database adminitrator user name
• DB_PASSWORD => database administrator password
• FTP_SERVER => ftp server ip for hostname
• FTP_USERNAME => ftp username
• FTP_PASSWORD => ftp password
• FTP_UPLOAD_DIR => ftp server backup path
• LOG_FILE => log file name and location

I hope this script will help you for taking database backup over the FTP server.