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 [email protected]$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”
0 2 * * * root /backup/db_dump_rsync.sh