Make MySQL database replication always use the most free node?

Question

We started using Multi-Master Replication Manager for MySQL, and I am wondering whether it is possible to to treat this setup like multi-symmetric processing: a process pops off the process queue, and the node (in this case a server) that is most free is selected for the job.

It seems that what happens is, the service switches to a slave ONLY when it mysqld crashes or goes away.

Is there a way to make database replication for MySQL act in more of a distributed manner? Maybe there is other software besides MMM that can do this?

Is there a way to switch the reader role to another server when mysqld slows down (rather than just when it fails)?

Answer 1

With Mysql multi-master replication it is not safe to write to arbitrary nodes in the general case. This is because it is asynchronous.

Always write to the same node. I mean ALWAYS. Only ever write to the other node if you're sure it's down, and you're sure that before it went down, the whole binlog was processed.

In practice this means always write to one node.

In any case, spreading the writing between several nodes will do nothing to increase write performance as both nodes must do all the writes anyway.