Terminology
What you're describing is a common set up that may be variously described as master-master replication, dual-master, active-active, bidirectional, or a few other terms.
Good reasons not to do it
The team who wrote High Performance MySQL offer this pithy summary of master-master replication - specifically, "active-active" replication:
In general, allowing writes on both servers causes way more trouble than it’s worth.
Baron Schwartz, one of the authors, offers a slightly more blunt opinion on his blog.
Save yourself grief, work, and money. Never write to both masters.
There are many things that can go wrong if both servers are updating data at the same time, including (but by no means limited to):
If something goes wrong with replication - even a temporary network glitch, you'll likely end up with a split brain and it may be impossible to determine which data is "correct"
If AUTO_INCREMENT
MySQL settings are wrong, it's easy for data collision to take place
Row locking no longer offers any protection - data can be manipulated on one master even while a transaction has it locked on another
Good reasons to do it - carefully
The most common reason for master-master replication is to so that you can very quickly switch the "current" master if something goes wrong with one of your servers.
The common way to implement this is to set up a barrier that ensures that only one of your servers is every written to by your application servers. This is usually a hardware load balancer, or software load balancer like HAProxy.
The authors of High Performance MySQL (mentioned above) describe this as "active-passive" master-master replication.
As long as the barrier is robust, you are fairly well protected from all of the issues described above. This may be supported by setting read_only on the "passive" server for an extra layer of protection.
A well-built master-master setup can offer you some amazing flexibility - you can do some really complex table or server maintenance on your passive server, wait for replication to catch up, then migrate your load balancer over to point at your shiny, reconfigured environment with almost no service downtime. This sort of thing does need to be planned carefully, though, and a lot can go wrong.
Best Answer
Let's start with a sample display of
SHOW SLAVE STATUS\G
As long as
Slave_IO_Running
is Yes, MySQL Replication has the needed connectivity. According to your question, when MySQL loses connectivity and reconnects, will it lose data? In a perfect world, I would say no as long as the relay log (Relay_Log_File
) is written in a clean format. Once in a long while, when the network intermittency is significantly long, the relay log may get corrupted.There is one sure fire way to correct this (please follow along):
Take note of these two values from the
SHOW SLAVE STATUS\G
To properly connect to a safe place on Master, do the following
Select the
Relay_Master_Log_File
and use the value : mysql-bin.000262Select the
Exec_Master_Log_Pos
and use the value : 803779735Once you have those values, then run these commands using those values:
The
CHANGE MASTER TO
command will erase all relay logs on the Slave and start with a fresh relay log.This is most likely the best way because you can only be sure about the last binary log entry from the Master that successfully reached the Slave and was executed on the Slave. Any entries from the Master's binary log that was written to a current relay log but was not closed properly or correctly written due to an intermittency problem with the IO Thread could result in a malformed relay log.
The only way this method would not work is if the binary log on the Master is itself corrupt. I have seen this happen when a DB Server crashed leaving the last binary log not closed properly. Running mysqlbinlog against the Master's last binary log had shown a difference between the filesize and last legal position. Trying to reestablish replication from a Master's corrupt binary log is a headache. You must then fanagle replication to work by running
RESET MASTER
on the Master, connecting replication to the first binary log on the Master, and perform due diligence of syncing the Slave using pt-table-checksum and pt-table-sync.UPDATE 2012-02-17 08:13 EDT
@vinny : If you are thinking of creating an additional Slave, here are other posts I have made in the past on this and similar subjects:
I hope they help.