MySQL 5 Master-Master question

[tfunky]

Hi All,

I'm confused on Master-Master replication vs. a MySql HA cluster...

Specifically the HA Mysql Cluster Howto and the Mysql5 Master-Master replication on Etch howto.

I've googled around looking for a definitive anwser on which way to go and I'm not finding anything really concreate other than mysql.com saying that "In some instances, a HA cluster is the perfect solution, but in most other Master-Master replication is the way to go"

What??? That's just about as clear as mud!

I currently have 1 mysql server running and would like to be able to add capacity and redundancy.

In the past I did the MySQL HA Cluster howto with the 2 loadbalancers, etc, but I somehow managed to smoke the database, so I went back to one system.

I have multiple webservers behind loadbalancers, and the database server supports those servers.

With all of that said...my basic questions are:
1. I'm hoping for a definitive direction on Master-Master replication vs HA Cluster'
2. With a Master-Master replication, do you still need the loadbalancers?
3. With Master-Master replication do you have a VIP or does each webserver just point to one database server?


What is the best/easiest to maintain way of getting a scalable and fault tolerant MYsql setup?

Thanks!

Tfunky

[falko]

I recommend Master-Master replication because it's easier to set up and maintain. The big disadvantage with a MySQL cluster (at least for MySQL 5.0) is that all data is held in the memory. So if there's a power failure, all your data is gone.

3. With Master-Master replication do you have a VIP or does each webserver just point to one database server?You can let each web server point to one database server. :)

[tfunky]

Excellent! Whooohooo!

Would you recommend using a load balancer/VIP incase there is a failure?

In my head, if each webserver points to 1 database server, then if one of the database servers fails, the LoadBalancers for the website won't know it, and will keep sending people to a "good" webserver, that has a broken backend.

What do you think is best?

[falko]

You could put the webserver and MySQL server on the same node. So if a node goes down, both the webserver and the MySQL server are down, so that all requests will be sent to the healthy node. :)