0

I am creating a shared hosting environment, load balanced and HA. HTTP/HTTPS is okay as well as the distribution of files (code and file uploads) between the servers. The last thing remaining is to load balance and make HA MySQL. I have two servers and I think master-to-master replication with haproxy as balancer is a solution.

What is your advice and what do you think? I need a transparent solution for the end user, something like having only one database to connect to.

womble
  • 96,255
  • 29
  • 175
  • 230
rtacconi
  • 745
  • 4
  • 14
  • 28

1 Answers1

2

As a proposal only:

Master-slave replication, mysql-proxy (eventually with the rw-splitting solution, write to master, read from slave) and script yourself a method in case the master goes bananas to promote the slave as a new master and reconfigure the proxy.

Master-master replication works, but might actually cause more trouble than you want (not even Oracle recommends it).

O G
  • 874
  • 4
  • 6
  • I agree, Master-Master is very painful. I suggest using some disk sync solution like drbd and HA to take over the IP. – Konrads Aug 16 '11 at 15:06
  • With one note on DRBD: 2nd server will have to be offline, detect if primary has crashed and attempt to start. In most of the times, you will have to do some recovery or even face some filesystem consistency issues. – O G Aug 16 '11 at 15:08
  • what about rubyrep? – rtacconi Aug 16 '11 at 16:02
  • @O G: You need to do pretty much all the same things when you're promoting a MySQL slave replica to be a master, too. – womble Aug 16 '11 at 21:06
  • @womble: less the fs integrity checks, if you are not using a distributed fs. Perhaps the semi-sync plugin could be also handy if you don't have an enormous amount of data to transact per second, but that's a different story. – O G Aug 17 '11 at 05:35