MySQL replication master-master for two hosts

This howto describes, step by step, how to setup a MySQL replication cluster with only two hosts. It tries to give a solution to the one-way synchronization master-slave documented in MySQL website without the need to setup a cluster (which requires at least three hosts). So, with this howto, a two-ways synchronization can be done and, because of that, I named it master-master replication mode.

It has been successfully tested in Mysql Server 5.1.41 but it probably will also work for more advanced versions.

1.- Edit my.cnf files:

[In server A]
server-id = 1
log_bin = /var/log/mysql/mysql-bin.log
auto_increment_increment = 2
auto_increment_offset = 1
#bind-address = 0.0.0.0

[In server B]
server-id = 2
log_bin = /var/log/mysql/mysql-bin.log
auto_increment_increment = 2
auto_increment_offset = 2
#bind-address = 0.0.0.0

Notes:

• Server-id should be different for every node
• There is a well known issue with master-master replication and auto_increments described here: http://onlamp.com/pub/a/onlamp/2006/04/20/advanced-mysql-replication.html
• Bind address should be commented or set to the ip address where mysql daemon will be listening.

Then restart both servers.

2.- Server A to server B replication:

In both servers stop every connection to the databases.
Check it with a "show full processlist" and use "kill" if necessary.

[In server A]
mysql> CREATE USER 'repl'@'ip_server_B' IDENTIFIED BY 'password';
mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'ip_server_B';

Lock tables:
mysql>FLUSH TABLES WITH READ LOCK;
mysql>SHOW MASTER STATUS

Notes:

• Take note of these values as they will be used later.
• Very important - Keep that mysql client running so tables keep locked.

Backup the entire database:
mysqldump --all-databases --lock-all-tables --master-data -u root -ppassword > dbdump.db
Note: --master-data option includes a line with master position
CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS= 270346;

move the backup file to server B:
scp -v dbdump.db root@ip_server_B:/root/

[In server B]
recover backup from server A:
mysql -u root -ppassword < dbdump.db

mysql>CREATE USER 'repl'@'ip_server_A' IDENTIFIED BY 'password';
mysql>GRANT REPLICATION SLAVE ON *.* TO 'repl'@'ip_server_A';
mysql>STOP SLAVE;
mysql>CHANGE MASTER TO MASTER_HOST='ip_server_A', MASTER_USER='repl', MASTER_PASSWORD='password', MASTER_LOG_FILE='binary_log_file', MASTER_LOG_POS='position';
Note: For MASTER_LOG_FILE and MASTER_LOG_POS use values you already got.

mysql>START SLAVE;
mysql> SHOW MASTER STATUS;
Note: Take note of these values too, as they will be used when replicating server B to server A.

3.-Server B to server A replication:

[In server A]
mysql>STOP SLAVE;
mysql>CHANGE MASTER TO MASTER_HOST='ip_server_B', MASTER_USER='repl', MASTER_PASSWORD='password', MASTER_LOG_FILE='binary_log_file', MASTER_LOG_POS='position';
Note: For MASTER_LOG_FILE and MASTER_LOG_POS use values you already got.

mysql>START SLAVE;

4.- Summary:

Once finished these steps you should have a high-availability system.

Every change, doesn't matter on which server, should be replicated in the other. And, in case of outage of one of them, the other will keep working and changes will be synchronized when it comes back up again.

It is still in my TODO to test it using linux-ha so that way replication cluster will be transparent for clients using the database.