MySQL live backup using replication

Assume that you have a website using MySQL as its database. You want to make a live backup of your database on another machine, so that if your host machine encounters a hardware failure, you can recover your web service ASAP. MySQL replication provides a simple way to do that.

First, you should prepare your backup machine:

• it is 'closed' to your host machine, in the meaning that data transfer speed is fast between them.
• it is installed MySQL, the major version should be the same as MySQL on the host machine, ie, both are MySQL 5.x.x, or both are MySQL 4.x.x.

Assume that your host machine's IP is 192.168.1.111, your backup machine's IP is 192.168.2.222.

On the host machine:

add the following lines to '[mysqld]' section of your my.cnf:

server-id=1
log-bin=mysql-bin
binlog-do-db=myblog

If you want the host machine as the replication of the backup machine too, add the following lines in my.cnf:

master-host=192.168.2.222
master-user=backup
master-password=backup
master-port=3306
master-connect-retry=10
replicate-do-db=myblog
relay-log=relay-bin

# important: in multi-master replication,
# add the following 2 lines to avoid conflicting `AUTO_INCREMENT' values
auto_increment_increment=2
auto_increment_offset=1

Note: 'binlog-do-db' and 'replicate-do-db' specify the database you want to replicate. If you want to replicate multiple databases, just write multiple lines, one database per line.

Add an account on your MySQL server:

GRANT FILE,REPLICATION SLAVE,REPLICATION CLIENT,SUPER ON *.* TO backup@'192.168.2.222' IDENTIFIED by 'backup';

On the backup machine:

add the following lines to '[mysqld]' section of your my.cnf:

server-id=2
master-host=192.168.1.111
master-user=backup
master-password=backup
master-port=3306
master-connect-retry=10
replicate-do-db=myblog
relay-log=relay-bin

If you want dual-direction backup, add the following lines to my.cnf:

log-bin=mysql-bin
binlog-do-db=myblog

# important: in multi-master replication,
# add the following 2 lines to avoid conflicting `AUTO_INCREMENT' values
auto_increment_increment=2
auto_increment_offset=2

Add an account on your MySQL server:

GRANT FILE,REPLICATION SLAVE,REPLICATION CLIENT,SUPER ON *.* TO backup@'192.168.1.111' IDENTIFIED by 'backup';

Now, shutdown MySQL servers on both machines. Copy all databases you need to replicate from the host machine to the backup machine. Then startup MySQL servers on both machines. Examine your mysqld.log for possible errors. Test the replication using some simple SQLs, for example, create a simple table.