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?
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?
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?
Insert this tuple ('Uncle Sam', 'Male', 26), on a table with auto-increment field say,
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!