There are tons of articles online about setting up MySQL multi-master replication. Recently, a friend asked me to send him a link so he could try it on his own. I must have read 20 articles and could not find one that did it my way. So here it is. My BASIC step-by-step guide to setting up Multi Master MySQL.

Setup

• I’m going to pretend that we’re starting clean with two brand new sparkly boxes.
• The running database is on a third box and can be exported with some downtime. I know, it’s naive, but for this illustration, it’s okay.
• I’m also going to say that the server OS is CentOS Linux version 4/5/6, just so we don’t get some crazy guy saying “these commands blah blah on my Commodore Amiga box”…
• We’re also going to assume that MySQL community server is installed on both boxes and there’s an /etc/my.cnf file in place as this tutorial has NOTHING TO DO WITH performance tuning.

Configuration

On both boxes, we’re going to have to edit the /etc/my.cnf file adding the appropriate replication settings for a circular environment.

Server A – /etc/my.cnf

[mysqld]
# ... other configuration, tuning, etc ...
server-id  = 10
# Make sure this partition has space to log bin, relay and whatever else!
log-bin = /var/lib/mysql/bin.log 
relay-log = /var/lib/mysql/slave-relay.log
relay-log-index = /var/lib/mysql/slave-relay-log.index
# Creating some room between pk ids, we can always manually insert if need be.
auto_increment_increment = 10
auto_increment_offset = 1
# This is default, but let's be safe
replicate-same-server-id = FALSE
# Want more slaves in the future with writes going to both masters?
log-slave-updates = TRUE
# If there's a reboot, let's not auto start replication.
skip-slave-start = TRUE

Server B – /etc/my.cnf

[mysqld]
# ... other configuration, tuning, etc ...
server-id = 11
log-bin = /var/lib/mysql/bin.log 
relay-log = /var/lib/mysql/slave-relay.log
relay-log-index = /var/lib/mysql/slave-relay-log.index
auto_increment_increment = 20
auto_increment_offset = 2
replicate-same-server-id = FALSE
log-slave-updates = TRUE
skip-slave-start = TRUE

Starting the Replication

The process is to create a user for replication and create the replication process from the mysql command line client. On each host, the commands are the same, so I’ll only post once.

First, create the replication user on BOTH hosts:

# mysql -u root -p
mysql>GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO repuser1@'10.0.1.11' IDENTIFIED BY 'repPassword1';

You may not want all data replicated, but that’s a more advanced configuration. Your mileage may vary.

Next, find the master info on Server B:

mysql> show master status;
+------------+----------+--------------+------------------+
| File       | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------+----------+--------------+------------------+
| bin.000001 |      107 |              |                  |
+------------+----------+--------------+------------------+
1 row in set (0.00 sec)

Let’s pretend this is the same, for Server A.

Next, start replication on Server A using info from Server B:

mysql>CHANGE MASTER TO
  MASTER_HOST='10.0.1.11',
  MASTER_USER='repuser1',
  MASTER_PASSWORD='repPassword1',
  MASTER_LOG_FILE='bin.000001',
  MASTER_LOG_POS=107;
mysql>start slave;
mysql>show slave status\G

Then, just configure Server B the same way, but using the IP address for Server A.

mysql>CHANGE MASTER TO
  MASTER_HOST='10.0.1.10',
  MASTER_USER='repuser1',
  MASTER_PASSWORD='repPassword1',
  MASTER_LOG_FILE='bin.000001',
  MASTER_LOG_POS=107;
mysql>start slave;
mysql>show slave status\G

You should now see replication running.

Exporting/Importing the Data

For each schema on your existing host, perform a mysqldump export. Then simply import that on one of your hosts.

http://dev.mysql.com/doc/refman/5.5/en/replication-options-slave.html
http://dev.mysql.com/doc/refman/5.1/en/replication-howto-mysqldump.html