Load-balanced MySQL cluster without load balancer
Please read my other answer to this question before actually using a MySQL proxy of any kind. If You have 2 master-master servers that a CMS is writing to, and 10 httpd that only read from it, You'll be fine, but (as pointed out in the other answer) that's not always the case. You've been warned.
MySQL Proxy is a simple program that sits between your client and MySQL server(s) that can monitor, analyze or transform their communication. Its flexibility allows for unlimited uses; common ones include: load balancing; failover; query analysis; query filtering and modification; and many more.
.
HAProxy is a free, very fast and reliable solution offering high availability, load balancing, and proxying for TCP and HTTP-based applications
If You would run it in TCP mode, it could be even better than Wackamole. If I had to choose between them, I would use HAProxy. Also HAProxy can have a lot of backends, Waclamole can have only 2. Note that HAProxy is "dumb", it connects sockets without any looking on what's inside the stream - dedicated MySQL Proxy might f.e. have an option to point various requests to specified servers.
Probably worth mentioning, Galera Replication for MySQL for a true multi-master MySQL setup. Galera is a synchronous replication protocol, so applications can read from and write to any of the MySQL Servers. Here is a quick tutorial: http://www.severalnines.com/clustercontrol-mysql-galera-tutorial
As for load balancers in front of the MySQL Servers, either use a MySQL connector that supports this functionality (e.g. Connector/J for Java, or Mysqlnd for php)
If you do not have a connector that can do this, then use something like an HA Proxy. This script automatically sets up HA Proxy and maintains the list of good MySQL Servers: https://github.com/severalnines/haproxy
Best regards,
Vinay
www.severalnines.com
Master-master replication is not as good as You might think, same goes to the round-robin proxy and similar 'easy' solutions. If You commit colliding data to separate servers fast enough (faster than the delay between the servers, which on production servers might be up to a full second*), both will accept the data. If You have an auction server, You just sold the same car twice. Who bought it? It depends on which DB will You ask!
The application must be aware that there are actually 2 databases out there and it has to know both of their ip addresses. If You want to "sell", You should f.e.
DB_number = `auction_number` % `number_of_databases`
( % is for modulo )
... and commit it to DB_number database. If You get a connection error, then perhaps do it with the other one (but in case of an auction server, I'd just display an error).
Also, the IP addresses should be wackamole-d between both servers. On a disaster scenario, where one database server goes down for a couple of hours in peak usage time, You will find that the application will try to connect to the absent server and hang until TIMEOUT, say, 3s. Suddenly half of Your queries run 3s longer (and they all go to the same database eventually - which doesn't make it run faster than before the disaster). This doesn't make Your httpd happy, as it probably has a limited connection pool of concurrent request handler threads...
* replication delay on production servers might be up to a full second - I have tested this in a remote colocation and in our datacenter and for like 99% of the time it's 0, but sometimes mysql shows 1s. On massive traffic I had many collisions due to client application making two requests resulting in two queries, insert and select. For some cases, the row just wasn't there yet, so We used hash of the userID and it fixed the problem
I hope You will learn from my mistakes ;-)