Mysql Master-Master architecture

UPDATE: The FreeNAC documentation was re-written to use the master-master architecture on 11.Dec.2007. This is now the default way to install FreeNAC.

Introduction

If you install FreeNAC according to the standard instructions in the current Install Guide, then one master and optionally slave servers are installed. The database is stored on the master, which is replicated to (one or more) slaves.

We'll discuss some issues with this setup, and describe an alternative setup used (in production) by a Scott LeFevre.

Please comment/improvements on this to help find an optimal solution going forward.

The current design

The Master replicates all SQL changes to the slaves, the slaves answer requests, reading from their local database, but do not do any SQL changes or inserts.

Disadvantages: All scans, housekeeping functions, and postconnect must run on the master, since they need to be able to make DB changes. Postconnect can only run on slaves, if the policy does not require DB changes.

Advantages: Slaves are very simple: a trivial mysql replication, one daemon (vmpsd_external), and very few cron entries. Slaves are easy to setup, and there can be many of them. If replication breaks its easy to setup again.

Slaves communicate with the master via syslog. Syslog is simple, standard, non connectionless and works well. However it does not (easily) allow transfer of structure data and it not a really queuing mechanism.

The key disadvantage going forward is that postconnect cannot run on slaves.

Multiple master

The idea is that each server can insert data locally, changes are replicated to other servers and the changes do not conflict.

The mysql servers are configured to do a circular replication. Datasets must be configured with autoincrement keys, and the autoincrement value set differently on each server - thus avoiding replication conflicts.

The following is a example with two master servers nac03 and nac04, used with FreeNAC v2.2 in production. Note especially the auto_increment_increment and auto_increment_offset values.

nac03 - my.cnf:

[mysqld] 
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1
log-bin=mysql-bin
server-id = 1
master-host = nac04.MYDOMAIN.com
master-user = opennac-repl
master-password = yourpasswordhere
replicate-do-db = opennac
replicate-ignore-table = opennac.vmpsauth
log-warnings
expire_logs_days = 1
max_binlog_size = 52428800
report-host = nac03
relay-log = nac03-relay-bin
#
auto_increment_increment= 5
auto_increment_offset = 1
#
# Uncomment for cascading replication
#log-slave-updates
#replicate-same-server-id = 0

nac04 - my.cnf:

[mysqld] 
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1
log-bin=mysql-bin
server-id=4
master-host = nac03.MYDOMAIN.com
master-user = opennac-repl
master-password = yourpasswordhere
replicate-do-db = opennac
replicate-ignore-table=opennac.vmpsauth
log-warnings
expire_logs_days= 1
max_binlog_size = 268435456
relay-log = nac04-relay-bin
#
auto_increment_increment= 5
auto_increment_offset = 2
#
# Uncomment for cascading replication
#log-slave-updates
#replicate-same-server-id = 0

Analysis

This is used in production with v2.2 since the autoincrement key structure in v2.2. would seem fine. V3.0 has few keys changes, it should work fine two.

There is question of what happens when there are 3 or 4 servers in circular replication, i.e. for lareg sites. We don't yet have a reference site with cascaded replication. How difficult would it be to fix replications if it breaks?

Its probably important that the Web/Windows GUI only point to one master, to concentrate those updates/deletes in one place. Other if the same field in changes in two masters from two GUIs, which one wins.

Perhaps we also need to look at MySQL cluster? What are the pros/cons? I've no experience, but on mysql.com I read "There are some cases where the MySQL Cluster is the perfect solution, but for the vast majority, replication is still the best choice."

It would be useful to have a production installation with FreeNAC V3 and 3 masters in circular replication ...

Further reading

http://dev.mysql.com/tech-resources/articles/advanced-mysql-replication....

http://www.onlamp.com/pub/a/onlamp/2006/04/20/advanced-mysql-replication...

http://www.mysql.com/news-and-events/newsletter/2003-05/a0000000127.html

http://forums.mysql.com/read.php?26,162270,162270

http://dev.mysql.com/doc/refman/5.1/en/mysql-cluster-replication-issues....

http://www.mysql.com/news-and-events/web-seminars/display-77.html

http://mysqlha.blogspot.com/2007/11/how-to-keep-mysql-replication-in-syn...