Quick Tip: Backup MySql DB and upload to remote server(Bash Script)

Screenshot from 2014-02-13 00:00:39

This script will automatically create backup of MySql DB and will transfer it to remote server.

#!/bin/bash
# MySQL settings - You can get this info from your web host
# The name of the database you want to backup
db_name=''
# MySQL database username
db_user=''
# MySQL database password
db_password=''
# MySQL hostname
db_host='localhost'
#End MySql Settings
 
#Second server credentials
server_user='root'
server_host=''
server_password=''
 
#backup folder path
backup_path=$( dirname $( readlink -f ${BASH_SOURCE[0]} ) )
#it will be used in the naming of backup file
date=$(date +"%d-%b-%Y")
 
#remote directory should be present in the remote server
remote_path=/root/backup
 
# Dump database into SQL file
mysql_backup_file=$backup_path/$db_name-$date.sql
mysqldump --user=$db_user --password=$db_password --host=$db_host $db_name > $mysql_backup_file
EXITCODE=$?
#check exit code
#if 0 which means success upload to remote server
if [ $EXITCODE -eq 0 ] ; then 
	#upload to a remote server
		sshpass -p $server_password scp $mysql_backup_file $server_user@$server_host:$remote_path
fi

Add cron to perform this job automatically by adding the following line in crontab, this will make the backup every midnight..
00 00 * * * /path/to/your/file

Tags: , ,