How to create off-site backup for your files and database on CentOS 6.x

This article provides a quick solution to backup your website files and database.

Let’s start!

First we create the  directories that will be used to store the backups on the local server:

mkdir -p /backup/files /backup/db

install rsync if it’s not already installed:

yum install rsync -y

Create SSH key that will be used to login to the remote server.  Execute:

ssh-keygen -t rsa

Hit ENTER when you are asked where to save the key and to enter passphrase:

Enter file in which to save the key (/home/demo/.ssh/id_rsa):
....
Enter passphrase (empty for no passphrase):

Copy the key to the remote server:

cat ~/.ssh/id_rsa.pub | ssh user@123.45.56.78 "mkdir -p ~/.ssh && cat >>  ~/.ssh/authorized_keys"

Now let’s create the directories on the remote server that will be used to store the backup files and databases:

ssh user@123.45.56.78 "mkdir -p ~/backup/files ~/backup/db"

Install rsync on the remote server if it’s not already installed:

ssh user@123.45.56.78 "yum install rsync -y"

We’ll be using bash script that will be executed from a cronjob everyday at 02:00.

Here it is:

#!/bin/bash

# Define local backup dir
BACKUP=/backup

# Define remote location
REMOTE="user@123.45.56.78:/backup"

# Time stamp
TIME_STAMP=`date +%Y-%m-%d`

# MySQL details
export_db="mysqldump --routines -u DB_USER -pDB_PASSWORD"

# Remove files that are older than 7 days in the local backup dir
find $BACKUP/ -type f -mtime +7 -exec rm {} \;

# Backup the DB and upload it to the remote server
$export_db DB_NAME | gzip -c | cat > $BACKUP/db/$TIME_STAMP-DB_NAME.sql.gz
rsync -azP $BACKUP/db/ $REMOTE/db/

# Backup website files
tar -zcvf $BACKUP/files/$TIME_STAMP-website.tar.gz /path/to/your/website/
rsync -azP $BACKUP/files/ $REMOTE/files/
Backup script

Save it as backup.sh in your user home dir so the path will be /home/user/backup.sh or /root/backup.sh if you are using the root account.

Make it executable:

chmod +x /path/to/backup.sh

Create cronjob that will be executing the backup script everyday at 02:00:

EDITOR=nano crontab -e
....
MAILTO=your@email.com
0 2 * * * /bin/bash /path/to/backup.sh

Hit CTRL+X and then ENTER to save the new cronjob.

Now you have a solution that will keep backups of your files and database from the last 7 days on your local and remote server.

This may not be the best backup script but it’s OK for my needs.