Master/Master Replication with MySQL 5

Given a situation where I’m moving offices for work, I find that I have a database server that holds quite a lot of data.  This data taken away for a period of time would be… bad.

So, I’m pushed for a replication that isn’t master/slave since a slave doesn’t exactly replicate back to the master without a bunch of trouble and headaches.  (sounds like real life eh?)

I’ve tested and put into operation a dual master configuration where both servers can act as a database server and replicate against each other instantly.  I guess one could call it a cluster but I don’t have them behind a single IP being load balanced so.. they aren’t a cluster.

With no further adieu, on with the show.

Dual master (both servers master/slave at the same time) MySQL 5.0 configuration

Two servers will be in this configuration, however multiples can be in place as needed with a slight amount of configuration differences that will be noted

Since both servers in this situation are the same, we’ll divide them into machine 1 and machine 2.

Machine 1 = 10.1.1.2

Machine  2 = 10.1.1.3

On machine 1, do the following:

Edit my.cnf (every distribution has it in a different location it seems) and put the following within the [mysqld] section

server-id=1

replicate-same-server-id = 0

auto-increment-increment = 2

auto-increment-offset = 1

master-host = 10.1.1.2
master-user = replication
master-password = slave
master-connect-retry = 60
replicate-do-db = <database being replicated>

log-bin = log-bin.log
binlog-do-db = <database being replicated>

connect to mysql, and type:

use mysql;

INSERT INTO user (Host, User, Password, Select_priv, Reload_priv, Super_priv, Repl_slave_priv) VALUES ('10.1.1.3','replication',password('slave'),'Y', 'Y', 'Y', 'Y');

flush privileges;

quit

on Machine 2, do the following:

edit my.cnf and put the following into the [mysqld] section

server-id               = 2
replicate-same-server-id = 0
auto-increment-increment = 2
auto-increment-offset = 2
master-host = 10.1.1.3
master-user = replication
master-password = slave

master-connect-retry = 60
replicate-do-db=<database being replicated>
binlog_do_db            = <database being replicated>
log_bin                 = mysql-bin.log

Connect to mysql and type:

use mysql;

INSERT INTO user (Host, User, Password, Select_priv, Reload_priv, Super_priv, Repl_slave_priv) VALUES ('10.1.1.2','replication',password('slave'),'Y', 'Y', 'Y', 'Y');

flush privileges;

quit

Then, time to get the two talking proper.  go into mysql on each machine and type “slave stop”.  Then, on each, type “show master status”, and record it for use here.

On machine 1's  mysql prompt, type "CHANGE MASTER TO MASTER_HOST = '10.1.1.3',MASTER_LOG_FILE = '<machine 2's logfile name>', MASTER_LOG_POS = '<machine 2's log file position>';

Then type: start slave;

On machine 2's mysql prompt, type "CHANGE MASTER TO MASTER_HOST = '10.1.1.2',MASTER_LOG_FILE = '<machine 1's logfile name>', MASTER_LOG_POS = '<machine 1's log file position>';

Then type: start slave;

quit

That should do it… you can monitor the mysqld.log (or wherever your mysql configuration file shows logs going to) and see any errors.

Of course it’s possible to make a circle of database servers in order to maintain a “cluster” behind a load balancer with a shared IP.  This would work but there’s always the slight possibility of issues arising when large amounts of data connections write to several databases fast enough where data’s changed on one server before it’s replicated correctly from another change.  In those cases, the better approach to take is using a DRBD or OCFS2 distributed filesystem structure between all machines (mounted to /var/lib/mysql) with the proper configuration so locking occurs.

Thanks, and have a great day.