MySQL Database Backup to FTP Server

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 to FTP server. Being a system administrator, I recommend keeping a remote copy of your every backup.

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42

#!/bin/bash ###################################################### #  #  ###################################################### 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 fi

Setup Details – Edit the above script for the following variable as per your system environment. Place all the values correctly to make script working 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 FTP server.