Well, you are absolutely right to be concerned about log-slave-updates
causing an issue with your Master-Master setup, though it won't necessarily be an infinite loop changes. I suspect if you are writing to both masters, that you will constantly be having to set skip_slave_counter
and restart the slave thread.... In a word, not ideal.
I would look over this post, specifically the short section on data integrity:
The safest solution is to simply never write data to both masters.
So, if both master's are up, but your clients only ever write to one master at a time (Master 1 by default), then you shouldn't have an issue.
*shouldn't doesn't mean you won't, though.
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
On a basic level, what you have done is enough, but there is a risk it could be re-enabled by mistake as long as the Binary logs exist on master2.
So, if this is just a temporary thing then running
RESET SLAVE
will remove themaster_log_file
andread_master_log_pos
values, thus preventing replication from starting again by mistake, but maintaining the other values for future use.If this is going to be permanent, then
RESET SLAVE ALL
will remove all the replication information (include host, port, user, password etc).