MySQL – Master-Master Replication Topologies on More Than Two Machines

MySQLreplication

I have 3 Mysql servers that I would like to replicate: two (including the main server) are local and one is remote. Let's call my main server A, the secondary server B, and a remote, development server C.

As a possible configuration, I started reading about a ring configuration. Some comments seem to look down on this type of topology.

My question is, given the current state of Mysql 5.1, what types of master-master topologies are advisable and known to be fault-tolerant?

Is a ring really a bad idea, in general?

In my case, A and B are on an intranet that isn't expected to have any network failures so B can theoretically be used as a failover for A. Most of the time, I am working on C, a remote machine, which sometimes gets network hiccups in reaching A and B. None of the data is currently critical (ie as long as they eventually get mirrored, it's fine), but I expect A and B to be mirrored tightly – within say a 5 second timeframe on light loads (I think this should be an easy requirement given my preliminary tests).

I currently have B as a Slave to A, but I am looking forward to going Master-Master on A, B, and C.

Best Answer

A ring can be a very good idea under certain conditions

For servers A, B, C

Server A

Master of B
Slave of C

Server B

Master of C
Slave of A

Server C

Master of A
Slave of B

All three servers have 3 databases (db1, db2, and db3)

Here is the only setup that protects the three databases

restrict all reads and writes to db1 on Server A
restrict all reads and writes to db2 on Server B
restrict all reads and writes to db3 on Server C

Replication would do the following in respone

backup database db1 by replicating to db2 and db3
backup database db2 by replicating to db3 and db1
backup database db3 by replicating to db1 and db2

IMHO performing writes (INSERTs,UPDATEs,DELETEs) for db1 to server A,B,C makes all auto_increment values separate and distinct. This would make it brittle to restore data to other boxes.

More to come...

UPDATE

I have another crazy idea

Have you ever heard of a star topology? I posted stuff earlier on this subject

Create a master with two slaves

For servers A, B, C

Server A

Master of B and C
All tables use storage engine BLACKHOLE

Server B

Slave of A
All user tables use storage engine InnoDB

Server C

Slave of A
All user tables use storage engine InnoDB

Benefits

Post all writes (INSERTs, UPDATEs, DELETEs) to serve A
Writes should be fast becasue BLACKHOLE tables map to /dev/null
Replication actual posts real data on Slaves (Server B and Server C)
no actual data will reside in server A, only binary logs
All SELECTs can go between Server B and Server C via a Load Balancer
Slaves would virtually be identical into terms of content

Drawbacks

Once you set this up you cannot freely use ALTER TABLE statements
If replication breaks at Server A, it stops updating the Slaves

Question 1

Does the DML operations committed by db2 during the replication process gets included in its own bin-log?

Answer to Question 1

Yes it will, provided you have this in /etc/my.cnf on both db1 and db2

[mysqld]
log-slave-updates

If you do not have this, add it and restart mysql

Question 2

Would the resulting bin-log in db2 be exactly the same with the bin-log of db1, to the letter?

Answer to Question 2

Yes. Make sure the clocks on both DB servers are synchronized

Question 3

What happens to the entries in db2 relay-log once they are committed to the database during the replication process, are they discarded? What role does the relay-log info log has in this?

Answer to Question 3

In MySQL Replication, the IO Thread of a Slave will read its Master's bin-log entries and store them in a FIFO queue. For each relay log in a slave, when every entry in the currently processed relay is executed it is rotated out and discarded. If relay logs are piling up, this quickly indicates that the SQL thread died because of any SQL error. Just do SHOW SLAVE STATUS\G to find out what stopped the SQL thread. The IO Thread would conitnue collecting completed SQL statements from its Master.

Question 4

How does db1 know where in the bin-log of db2 (somehow dependent on the answer of Question 2), it will start the replication process?

Answer to Question 4

When you do SHOW SLAVE STATUS\G, look for the following lines:

Master_Log_File : The latest binary log whose most recently command was copied to the Slave
Read_Master_Log_Pos : The latest position of the latest binary log whose most recently command was copied to the Slave
Relay_Master_Log_File : The latest binary log whose most recently command was executed on the Slave
Exec_Master_Log_Pos : The latest binary log whose most recently command was executed on the Slave
Relay_Log_Space : The sum total (in bytes) of all relay logs. By default, each relay log is the default size of a binary log (1G). If Relay_Log_Space starts to significant exceed 1G, this indicates one of two things:
- SQL thread died due to SQL Error
- SQL thread is busy with a long-running query

Question 4.1

If you enable log-slave-updates on both databases i.e. dB1 & dB2, then that would mean all items from the binary log of dB1, which was successfully replicated by dB2 will be written into dB2's binary log and vice-versa. Would not this result to some sort of infinite circular replication or duplications of entries on both databases, if it's possible at all, considering the possible key-collision issues that would arise? What I'm trying to say is, How would dB1 know once it checks on the binary log of dB2 that, "I should not replicate those entries in there because they all just came from me"?

Answer to Question 4.1

You must have log-slave-updates available on both DB servers in order to have an audit trail that the SQL executed on on DB server made it to the other. If you don't, you would have to do your due diligence to compare the data explicitly. Such ways would include:

Running CHECKSUM TABLE on every table you have in both DB servers to compare their contents.
Using pt-table-checksum, which is an automated version of running CHECKSUM TABLE between Master and one or more Slaves

You need not worry about infinite circular replication unless you are dealing with more that two masters. There have been rare times when someone with, let's say four Masters, removes one of the four servers from circular rep cluster. Let's suppose the the server_id is 13. It is remotely, but still, possible for binary log entries whose server_id belongs to the server that removed to be inside the relay logs on other servers. Only in such a scenario would you worry about infinite circular replication.

To circumvent such situations, MySQL 5.5 has a new option for the CHANGE MASTER TO command called IGNORE_SERVER_IDS. You would do the following to repair things on all the remaining servers:

STOP SLAVE;
CHANGE MASTER TO IGNORE_SERVER_IDS = (13);
START SLAVE;

In fact, here is what the MySQL Documentation says on this:

IGNORE_SERVER_IDS was added in MySQL 5.5. 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.

Question 5

On INSERT queries on the master, what form of the query is written into the binary log? Is it the 'raw' form of the query, or the one which already has the auto-generated value of the auto-increment key?

Answer to Question 5

Whichever form is presented. Here is what I mean: The raw form would usually not include the auto_increment column expressed explicitly. On the other hand, it you import a mysqldump into a DB server with binary logging, the rows being inserted would explicitly be given. Either version of INSERT would be allowed execution in mysqld. In like fashion, either version of INSERT would be recorded AS IS...

Mysql – Which database system can break me free

There's a lot to consider. I'm going to narrow this down to a single problem and offer one solution, although there's many approaches.

Problem: You need AD-HOC queries on lots of rows.

First, setup a server to handle writes and a replicated slave (running some flavor of MySQL 5.5.x) to handle reads. My personal preference is Percona Server. Your mileage may vary.

Once you have replication caught up, ALTER your slave table to: engine=InnoDB, ROW_FORMAT=COMPRESSED, partition by date, and index the data to optimize your query WHERE clauses. Each of these have an optimization for your case and there is further tuning required.

Finally, setup a schedule to archive/shard the data when it falls out of scope. Say 2-3 years. Depends on your SLA for providing back reporting. You can always union it if you have to.

MySQL 5.6 will have Memcached built in which will allow you to store pre-processed results. Map/Reduce is great, as long as it's not the first run of the reduce function. NoSQL is great as long as the data is already processed. Neither is really your problem.

Additions 2012-06-13: Since it appears my recommendations have stirred another response, I'll submit my reasons:

Why InnoDB in this case? With the constant insertions of many rows, you can use InnoDB to avoid implicit read locks at the table level.
Why COMPRESSED + Barracuda? "The compression means less data is transferred between disk and memory, and takes up less space in memory. The benefits are amplified for tables with secondary indexes, because index data is compressed also."
Why partition by date? Reduce scan breadth.
Why index properly? Decrease search times by reducing cpu bottleneck through elimination of full table scans.