mysql master master master

Question

I have n servers and I am planning to setup n master servers using master-master configuration. What architecture will be the best one ? Also I need to take care of auto-increment table things...

Say, if my servers are A, B ,C and D . Is it ok and safe to point master of B as A, master of C and B, master of D as C and master of A as D ? Will that cause any data consistent issue. Is there any other better approach ??

Answer 1

The topology you're describing is called "circular replication". You're basically setting up a ring in which each node acts as a master to the following one. The authors of High Performance MySQL advise against this topology, with the following reasons:

• it depends on each node being available, therefore increasing your failure probability.
• if you remove a node from the ring, replication events originating from that node go into an infinite loop, as the only node that would filter them out is not available anymore.

Answer 2

This type of topology can be you enemy or your friend depending on where you perform the master read and writes. The key to successfully using circular replication is the following circular replication rules of engagement:

• Restrict DML on a database to the same DB Server
• Restrict SELECT queries on a database to the same DB Server
• Use auto_increment values to create demarcation of data

CASE IN POINT

My employer's web hosting company host a Car Dealership CRM firm whose has 859 dealerships throughout the US (+ Hawaii).

They have 3 DB Servers each with following

• 192 GB RAM (16GB RAM Disk, 162GB InnoDB Buffer Pool)
• Dual HexaCore (That's right, 12 CPUs)
• 1.7TB Data Volume (776GB Dealership Data)

All DB servers are using circular replication

The client has a database per dealership, thus a multitenant DB layout within the MySQL Instance.

The client's developers separate writes by assigning a certain number of dealerships to read and write data among the the 15 Web Servers. Each web server is dedicated to read and write from one of the three DB Servers. That's approxiamately 283 dealerships writing to one database. The client opted to use neither replicate-do-db nor replication-ignore-db as that would make a humongous inclusion or exclusion list of DBs.

Each DB Server the folling settibgs for /etc/my.cnf for auto_increment_increment and auto_increment_offset

Server1

[mysqld]
auto_increment_increment=10
auto_increment_offset=1

Server2

[mysqld]
auto_increment_increment=10
auto_increment_offset=4

Server3

[mysqld]
auto_increment_increment=10
auto_increment_offset=7

auto_increment_increment and auto_increment_offset are set to protect the integrity of auto_increment values within each DB server's MySQL Instance amongst all dealership DBs.

As long as the circular replication rules of engagement were followed by my client, the client had the following paradigm to work with:

For any dealership DLR

• One DBServer W used for writes to DLRDB
• Two DBServers (R1,R2) provided warm backups of DLRDB
• One of the Warm Backups can be used for mysqldump backups without disturbing other DLRDBs

Client has used this topology since March 2011 without so much as a single complaint as to data integrity.

END CASE IN POINT

My employer's web hosting company also provides this same DB topology/infrastructure amongst dozens of smaller clients for years without complaint. You can fully trust circular replication, provided you strictly obey the circular replication rules of engagement.

Give it a Try !!!

CAVEAT

MySQL 5.5 has new command extension for CHANGE MASTER TO called IGNORE_SERVER_IDs. According to the MySQL Documentation on this:

IGNORE_SERVER_IDS was added in MySQL Cluster NDB 6.1.29, MySQL Cluster NDB 6.3.31, MySQL Cluster NDB 7.0.11, and MySQL Cluster NDB 7.1.0 (see Bug #47037). This option takes a comma-separated list of 0 or more server IDs. Events originating from the corresponding servers are ignored, with the exception of log rotation and deletion events, which are still recorded in the relay log.

In circular replication, the originating server normally acts as the terminator of its own events, so that they are not applied more than once. Thus, this option is useful in circular replication when one of the servers in the circle is removed. Suppose that you have a circular replication setup with 4 servers, having server IDs 1, 2, 3, and 4, and server 3 fails. When bridging the gap by starting replication from server 2 to server 4, you can include IGNORE_SERVER_IDS = (3) in the CHANGE MASTER TO statement that you issue on server 4 to tell it to use server 2 as its master instead of server 3. Doing so causes it to ignore and not to propagate any statements that originated with the server that is no longer in use.

If a CHANGE MASTER TO statement is issued without any IGNORE_SERVER_IDS option, any existing list is preserved; RESET SLAVE also has no effect on the server ID list. To clear the list of ignored servers, it is necessary to use the option with an empty list: CHANGE MASTER TO IGNORE_SERVER_IDS = (); If IGNORE_SERVER_IDS contains the server's own ID and the server was started with the --replicate-same-server-id option enabled, an error results.

Beginning with MySQL 5.1.47, invoking CHANGE MASTER TO causes the previous values for MASTER_HOST, MASTER_PORT, MASTER_LOG_FILE, and MASTER_LOG_POS to be written to the error log, along with other information about the slave's state prior to execution.

This command extension is great for removing DB servers out of a circular replication topology without having SQL commands go around the ring in an infinite loop.