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
Server1: 192.168.1.2
Server2: 192.168.1.3
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/id_dsa.pub | ssh mysql_backup@192.168.1.2 "cat -- >> ~/.ssh/authorized_keys"

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

Continue reading “MySQL Daily Backup and Transfer to other Server” »