Multi-master replication / clustering

[Simon Garner]

Hello,

I am working on a web application (php) that will have a largish mysql 
database (millions of rows, eventually), and for which high availability 
will be important. I am wondering if anyone here can suggest options for 
multi-master replication or clustering.

The application will be mostly read queries but also a significant 
number of writes, and it will be important to have synchronicity between 
nodes.

Load balancing apache servers appears to be reasonably straightforward 
using the software from www.linuxvirtualserver.org. This allows high 
availability and high performance using a cluster of servers acting as one.

Load balancing mysql servers appears to be, of course, much more difficult.

I have considered using replication but this has severe limitations. 
Having one master server and replicating to several slaves would allow 
read queries to be processed on the slaves leaving the master to do 
writes only. However mysql replication is asynchronous so it is possible 
that the data on the slaves would differ from the master and from each 
other. It also offers no performance gain as it means every slave has to 
be able to handle the entire transaction volume. And most importantly 
this leaves a single point of failure - the master. So replication is 
not a solution.

Next I looked at MySQL Cluster. But this too has important limitations - 
firstly, the requirement for the entire database to be held in RAM. This 
means the hardware cost becomes very high for a large database. 
Secondly, NDB does not support FULLTEXT indexes. We need to be able to 
search across a TEXT and a VARCHAR column (i.e. "title" and 
"description") in one of the tables - without FULLTEXT indexes this is 
very slow (with LIKE). I am still considering ways we can make this work 
though.

I have also looked at the third-party m/cluster software offered by EMIC 
(www.emicnetworks.com). This is a multi-master mysql clustering 
solution, and looks like it would do exactly what I want. Unfortunately, 
it is a lot more expensive than I can afford! Would be nice if MySQL 
could implement something like this themselves.

Does anybody know of any other options for MySQL clustering?

TIA,

-Simon