MySQL database backup to FTP server – Shell Script

Hello Guys,

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 ftp server. Being a system administrator, I recommend to keep a remote copy of your each backup.

This script is tested with CentOS 5,6 and REDHAT 5,6. Copy script below or [wpdm_file id=1]

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

Setup Details:

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 your for taking database backup over ftp server.