MYSQL Database Backup and Rsync Bash Script

MYSQL Database Backup and Rsync Bash Script.

A bash script is a great and powerful utility to automate the “MySQL” backup in Linux like environment. With the help of Bash script, you can take dump automatically at a defined time and transfer that dump to any remote location with the Rsync, SCP and FTP services.

Create MYSQL Database Backup Bash Script:
Prerequisite:

sshpass (utility to append password in Rsync and ssh commands, available through yum,apt-get, rpm and deb packages.)

Copy below code into some location into your MySQL DB server like /backup and save as “db_dump_rsync.sh” and give execute permission like below,

chmod +x /backup/db_dump_rsync.sh

#!/bin/bash

############################################
##	  MySql DB Dump Script Bash.      ##
##	Source: http://www.syshunt.com	  ##
############################################

# Database1 Credentials:
 username="user_name1"
 password="xxxxxxxxxx"
 host="localhost"
 db_name="db_name1"

# Rsync Server Credentials:
 rsync_user="rsync_user1"
 rsync_pass="xxxxxxxxx"
 rsync_host="rsync.example.com"


# Other options:
 mysql_backup_path="/backup/db"
 date=$(date +"%d-%b-%Y")

# Empty Backup Directory:
rm -rf $backup_path/*.gz > /dev/null 2>&1
rm -rf $backup_path/*.sql > /dev/null 2>&1

# Dump database into SQL file:
mysqldump -u $username -p$password -h $host $db_name > $mysql_backup_path/$db_name-$date.sql

# Create a tarball:

if [ "$?" -eq 0 ];
then
	cd  $mysql_backup_path && tar -cvzf $db_name-$date.sql.tar.gz  $db_name-$date.sql
	if [ "$?" -eq 0 ];
	then
		# Delete SQL File:
		rm -rf $mysql_backup_path/$db_name-$date.sql
		sshpass -p $rsync_pass rsync -aP -e "ssh -o StrictHostKeyChecking=no" $db_name-$date.sql.tar.gz $rsync_user@$rsync_host:.
		if [ "$?" -eq 0 ];
		then
			echo " $db_name DATABASE BACKUP Rsync and was Successful."
		else
			echo " $db_name Rsync was failed hence DB Backup was Failed."
		fi
	else
		echo " $db_name TAR creation Unsuccessful hence Backup was Failed."
	fi
else
	echo " $db_name DATABASE DUMP was Unsuccessful hence Backup was Failed."
fi

Automate Backup:

Linux comes with great Cron utility and we can you this for automation like below,
edit crontab with “crontab -e”