MySql Database Backup on Linux

Following script can be used to backup all of your mysql databases on linux server. It include local and FTP backup retention as well.
Just set the Env variable and done.



#!/bin/bash
#Author - Tanveer Haider Baig
#Script For local MySql backup ONLY
#This script will backup all databases
. ~/.bash_profile
echo "=============== Setting Env Variables ========================="
#local backup folder
DB_BACKUP=/backup/mysql_bkp
DB_USER="MySqlUser"
DB_PASSWD="MySqlUserPwd"
#FtP folder, set accordingly
FTP_LOC=/Ftp/ftp_Folder
#backup file name, set accordingly
BkpName=MySqlDb.tar.gz
#setting date
LoadDate=$(date +"%b_%d_%y")
#setting delete date for FTp.
DelDate=$(date -d "05 days ago" +"%b_%d_%y")
#Ftp Server Details, set accordingly
SERVER=Ftp Server Ip
USER=FtpUser
PASSW=FtpUserPwd
echo "===============  Backup Start ========================="
#Removing 5 days old files
find $DB_BACKUP/ -name "*.gz" -mtime +5 -exec rm {} \;

#################################################
# PERFORM All schemas Backup
#################################################
for db in $(mysql --user=$DB_USER --password=$DB_PASSWD -e 'show databases' -s --skip-column-names|grep -vi information_schema);
do mysqldump --user=$DB_USER --password=$DB_PASSWD --opt $db > "$DB_BACKUP/$db-$LoadDate.sql";
done

echo "===============  Backup completed ========================="

echo "===============  creating archive  ========================="
tar cfvz $DB_BACKUP/$LoadDate"_"$BkpName $DB_BACKUP/*.sql
if [ $? = 0 ]
then
echo "===============  archive successful now remove .sql  ========================="
rm -rf $DB_BACKUP/*.sql
fi
echo "===============  archive and remove .sql done ========================="
#################################################
# sending file to ftp server
#################################################
    ftp -v -n $SERVER <<END_OF_SESSION
    user $USER $PASSW
    $FILETYPE
    binary
    lcd $DB_BACKUP/
    cd $FTP_LOC/
    delete ${DelDate}"_"$BkpName
    mput $LoadDate"_"$BkpName
    bye
    END_OF_SESSION
   

Scheduling Script:
Following cron entry can be used for schedule, it is set to execute at 3 every day and send output to your email.
0 3 * * * /Your_location/MySql_bkp.sh 2>&1| mail -s "MySql Backup" dba@yourdomain.com


and that's all