Connect to a MySQL master-master setup from code

Question

For a new website we must connect to a MySQL master-master setup. This is a .NET website using NHibernate, but the same would also apply to Java or any other language. We chose this setup because we want the site to continue working if a database would go down. We don't like downtime.

Maybe I have a complete misunderstanding of how a master-master setup works (in MySQL), but the way I see it, you connect to your database as you'd normally do, but behind the scenes, MySQL replicates the data between the two databases. If you do a write, it can go to either master 1 or master 2, you normally wouldn't know (except that the auto-increment id would return a different value). If master A would somehow fail, master B will still work, thus no downtime, master A will be ignored until it goes up again, the data is replicated, and if all is well, master A will be back in the field again.

IF this is correct, and please correct me if my above rambling is wrong, do you need to do anything special in case one master goes down? If I connect to 192.168.1.50 (which is master A), what happens if master A goes down? Will MySQL somehow automagically connect me to 192.168.1.51 (master B) so my site will continue to work?

If I was NOT correct, how does MySQL master-master replication work then? Do I have to tell each query on which master it should be executed? That would make no sense, right, since if master A goes down, then all my queries on master A would still fail and the master-master setup doesn't help me at all.

So basically, I think my question is actually:

do I still connect to a single MySQL host (I'm using NHibernate but that doesn't really matter), do I specify a single connectionstring, and will MySQL know that there are two masters, or does my code change in such a way that I need to specify connectionstrings for both masters (how?), do some special magic to balance the queries between the two servers, etcetera.

Am I missing anything else? Thanks!

Answer 1

Maybe I have a complete misunderstanding of how a master-master setup works (in MySQL), but the way I see it, you connect to your database as you'd normally do, but behind the scenes, MySQL replicates the data between the two databases. If you do a write, it can go to either master 1 or master 2, you normally wouldn't know (except that the auto-increment id would return a different value)

This is incorrect.

MySQL replication works by writing committed data (meaning either the changed rows or the actual SQL statements, depending on the replication mode) to a replication log, then shipping that log to the slaves, where they replay it and make the same changes.

In multi-master replication, each node is both a master and a slave, receiving updates from the previous machine in the loop, and transmitting them forward to the next machine. Each machine has a unique identifier that it uses when sending and receiving replication logs, allowing it to identify when data has come full circle.

This method is primitive but effective. It's also traditionally been a real pain in the rear end to manage and maintain. If at all possible, don't use multi-master in favor of other solutions. I use multi-master in production, and can say this from experience.

If I connect to 192.168.1.50 (which is master A), what happens if master A goes down? Will MySQL somehow automagically connect me to 192.168.1.51 (master B) so my site will continue to work?

When you connect to one machine in a multi-master loop, you are only connected to that one machine. If you need to be able to connect to multiple machines, should one be down, then you will need to handle that circumstance manually, either through modifications in your code or an intermediary load balancer.

Worse, when one machine in the loop does go down, the loop is broken. Let's say you have three, A, B, and C. The loop would be A => B => C => A. If B goes down, A can no longer transmit updates to C, meaning that C would be the only safe machine to connect to until B comes back up and the loop is restored.

In regards to auto-increment, take a look at auto_increment_increment and auto_increment_offset, two server variables that make auto-increment in multi-master replication setups possible. You should not, under any circumstances, use auto-increment in multi-master without having set up these two variables.

Answer 2

Server=serverAddress1, serverAddress2, serverAddress3;Database=myDataBase;Uid=myUsername;Pwd=myPassword;

you can use this connection string. but i didn't try.