[web] SQL Replication or Clustering?

I've done some very extensive research into this topic for MySQL. The conclusion I came to is that your requirements can be met, if you're willing to pay a performance price and license some commercial software to help.

MySQL Cluster is a strange product that only works for pure in-memory databases, and was designed specifically for the telecom industry. Skip it.

MySQL replication is a master-slave situation. You always write to the master, and the slave gets those writes (hopefully soon) afterward. If you don't care that your data is that up-to-date, you can do read queries on the slave(s), spreading out the load. It works great for scaling out for high-read situations. For example, Yahoo uses (or at least did at one point) this approach with MySQL to provide data for their Yahoo Finance stuff.

MySQL replication has the following problems:
* Data on the slave may be a little or a lot behind the master.
* If something goes wrong on the slave, it will be permanently out of sync with the master without any way for you to know.
* If your slave crashes, it's painful and very time-consuming to rebuild it and reattach to the master, and may require shutting down the master or another slave temporarily.
* If your master crashes, there's no built-in way to automatically fail over and use the other server as master. You'll have to manually set up the slave to be master, reconfigure your application (or DNS) to point to that other server as the master, then reconstruct a slave on the failed machine when you get it back up.

There are all kinds of elaborate ways to deal with these problems. Slave crashes aren't a big deal if you have a lot of slaves; you can just set another up when it's convenient. But master crashes are always painful unless you have some specialized software to help with it.

Continuent has done some fantastic work making replication actually work the way you'd hope. They have automatic master failover (with perhaps several seconds of downtime, maybe a bit more), some consistency checks, and much easier slave setup. If you're willing to come out of pocket for a solution, they would be a great place to start.

Continuent also used to offer a product called uni/cluster. I'm not sure if they still do. It was a master/master replication system, where you were guaranteed that both servers had the same data every time a transaction was committed. It had some serious write-performance issues, though, apparently bad enough to scrap the line.

Sorry, the MySQL high-availability scene isn't quite what you might call "robust."

Thanks BeanDog

Well I have also tried to read the huge documentation for connecting databases in cluster/replication system. One of major priorities are that master-slave are never out of sync, because every piece of data is important. 2-3 seconds without connection to the database in case of failover is not a big deal if all the data from master are same as on slave.

I have came up that I have only two possible scenario:
1. Clustering, which will take 5 servers with two DB servers and two storage server and one that will take role of data forwarding to DB in case of failover. Good thing as I read is that clustering is actually increasing the performance of DB as two servers actually act as one bigger DB?

2. Master-master replica in which case I will have two DB that will never be out of sync ( you wrote about this ) but I didn't heard about write-performance issue [sad], also to mention that two DB needs to be on the same domain, which is also not good...

Where can I get the pricing for the Continuent products?
And why MySQL Cluster have only memory-oriented databases? Is it so bad?

thanks in advance