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
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 !!!
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.