Master-Master Replication in MySQL

Question

Good day everyone!

I want to clarify something about the behavior of master-master replication in MySQL, with respect to the logs i.e. bin-log and relay-log, it use in performing replication.

I assume that in this kind of setup, only one of the 2 db servers is processing DML operations i.e. 'active' server. Read requests are distributed to both of the servers.

So when db1 is the active server, DML operations to it are recorded in its bin-log, and db2 would have to fetch it in, placed it in its relay-log and also process it as a part of the replication process.

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

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

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?

The reason I am asking this because I could not get the whole scenario yet when db1 goes down and db2 becomes the active db server.

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?

UPDATE 1:

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"?

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?

UPDATE 2:

Question 5.1: So you're saying that on raw INSERT queries, the actual query itself is the one written on the bin-log. So in essence, when such queries are replicated and committed on the slaves, it is the case that the same tuple would be associated with or will have, different values on its auto_incremented field, across the different databases i.e. slaves?

Example: Insert this tuple ('Uncle Sam', 'Male', 26), on a table with auto-increment field say, pki

Then, it follows therefore from your answer that pki may have different values for this tuple across the different databases i.e. master and slaves.

Question 5.2:Assuming above is right, would not this be an issue when load-balancing read queries across databases?

Thank you very much!

Answer 1

1: Yes. These are the transactions that are sent to db1.

2: No. replication happens by replicating sql statements. This asynchronicity infers that the replicated sql statement generate resulting legitimate/valid data but not consistent data.

• the trivial example is when the sql statement includes the date function NOW()

(timestamps are not guaranteed to be exactly the same when executed at possibly different times)

It could be that the sql themselves are the same to the letter but I dont' think this is true either. auto_increment values should differ (see below) as well as bin-log positioning indicators, timestamps (probably used for seconds_behind_master ticks) and possibly some other logistical data.

3: Don't know specifically. Once they are committed in both DBs why would you need them?

If db1 goes down are your writes going to db2 now? Is db2 in r/o mode (shouldn't be since it is part of a multi-master setup)? How are your read operations being distributed? HA-Linux? What is meant by when db2 becomes active? By definition it is active already as a master (unless you mean simply by how write operations are being distributed).

4: bin-log and bin-log position are sent to db1 in fashion just like master-slave replication.

I feel like there should be another question here. In master-master replication you need to make sure to specify the following configuration values:

auto_increment_increment auto_increment_offset

The short explanation is that if you have multi-master replication you run the risk of creating "colliding" auto_increment values during replication.

http://dev.mysql.com/doc/refman/5.0/en/replication-options-master.html

4.1: log-slave-updates is for chaining. db1 -> db2 (slave of db1) -> db3 -> (slave of db2). Your question is totally spot on but my understanding is that it doesn't produce an infinite loop of updates or the db1 committing its own statements multiple times. I'm guessing because in the bin-log the server id is specified for each statement.

5: Now you're talking low-level. Which is fine. Auto-increment values don't get generated until the time at which a transaction or statement is committed. There may be some nuance to my statement here but that's my understanding (pre-committed vs committed?).

Also, the raw statement is saved in bin-log.

Hope that helps.

Answer 2

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