MySQL Daily Backup and Transfer to other Server

When you have 2 MySQL server which is not running in replication or cluster, is recommended to have MySQL backup running daily. This will help you on fast restoration, reliable data backup and disaster recovery.

I have created a bash script to run daily and make sure the data is save into local disk and another copy being transferred to another server via rsync. You can use following script and change the value to suit your environment. I am using following variables:

OS: CentOS 5.6 64bit
Backup user: mysql_backup
Backup user password: l3tsb4ckup
Backup path: /backup

Before we use the script, is good to have a specific user to run the backup scripts. Now lets start configuring in Server1:

1. Create specific user to run the backup:

[root@centos ~] useradd mysql_backup
[root@centos ~] passwd mysql_backup

(notes: enter the password 2 times as above)

2. Assign that user to a specific location inside your server:

[root@centos ~] usermod -d /backup mysql_backup

3. Test the user environment by using su and create ssh-key so this user will have password-less connection to another server:

[root@centos ~] su - mysql_backup
-bash-3.2$ mkdir ~/.ssh/
-bash-3.2$ ssh-keygen -t dsa

(notes: just press enter for all prompts)

4. Now login to Server2 via root and repeat step 1 to step 3. Once done, continue below step in both servers to transfer the SSH key for mysql_backup user. This will allow password-less connection between each of them:

-bash-3.2$ cat ~/.ssh/ | ssh mysql_backup@ "cat -- >> ~/.ssh/authorized_keys"

(notes: enter mysql_backup’s password which is l3tsb4ckup)

5. Now back on Server1, we will need to copy and paste following scripts into a file called /backup/mysqldailybackup using text editor:

#! /bin/bash
# Backup MySQL databases and copy it over to another server
eof=`ls -l $DATADIR | egrep '^d' | awk {'print $9'} | egrep -v '^mysql' | egrep -v '^test' | wc -l`
for (( i=1; i<=$eof; i++ ))
dbname=`ls -l $DATADIR | egrep '^d' | awk {'print $9'} | egrep -v '^mysql' | egrep -v '^test' | head -$i | tail -1`
/usr/bin/mysqldump --opt --single-transaction $dbname | gzip -c > $BACKUPDIR/$dbname.sql.gz

6. Change the permission to be executable:

-bash-3.2$ chmod 755 /backup/mysqldailybackup

7. Now back to Server2 and repeat step 5 and step 6 but do some changes on the script so it will send the mysql backup copy to Server1 on line 7 which is:

DESTINATION='' # destination change to server1

8. Now, we need to setup cron job as mysql_backup user. Do next consequence steps in both servers:

-bash-3.2$ crontab -e

9. Copy following line and paste into crontab:

0 0 * * * /backup/mysqldailybackup &> /dev/null

Now your MySQL will be backed up to local server in directory /backup and another copy will be sent to Server2 under directory /backup/hostname_of_source_server. The cron job has been set to run every day at 12AM. You should see some thing like this in backup directory:

-bash-3.2$ tree -da
|-- .ssh
|-- local
`-- hostname.source.server
3 directories

Leave a comment

Leave a Reply

Your email address will not be published. Required fields are marked *