// you’re reading...

MySQL

How I setup MySQL Multi Master Replication

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



Discussion

7 comments for “How I setup MySQL Multi Master Replication”

  1. nice write up

    Posted by Phil Chen | April 26, 2012, 3:11 pm
  2. Thank you for writing this up! The replication setup steps changed between 5.1 and 5.5 and I was having trouble migrating until I saw your post.

    Thanks again!

    Posted by Matt Rogers | February 27, 2013, 8:51 am
  3. Great post, thanks! :)
    Is there really any reason for having different auto_increment_increment on the two?

    Posted by Daniel | March 19, 2013, 8:48 am
  4. I’m happy this has helped some people. Rising tides lift all boats!

    The strategy with auto_increment_increment & auto_increment_offset is to create a difference in your federations of downstream slave hosts, should you have an outage and have to cross-import data (aka rebuild by hand).

    Of course the scenario should never happen, but this is one additional counter-measure.

    Also, if you have high-write traffic, it’s another measure to guard against duplicate auto incrementing ids. Again, your monitors should be detecting replication problems early, however, as DBAs we have to take every advantage possible to ensure data integrity.

    Posted by Randy | March 19, 2013, 10:11 am
  5. Happy this helped you. There’s a ton of MySQL replication techniques out there, and mine is definitely one of them! ;-)

    Posted by Randy | March 19, 2013, 10:13 am
  6. Hey.. It helped me a lot.. Really a nice post.
    Can you please explore more options like
    1)auto_increment_increment
    2)auto_increment_offset

    in my.cnf file and let us know how they works and their advantages

    Posted by Kiran | June 13, 2013, 11:12 pm
  7. Posted by MySQL Master-Master Replication over a Wide Area Network (Research) | Jordan Kirby | August 19, 2013, 7:35 am

Post a comment

Help support my site and buy a domain name at http://domainsemailhosting.com/