Do you think any of your clients will _need_ load balancing? (this is a serious question)
And is the reason:
1. need it due to high load
2. want the redundancy to reduce downtime
If the reason is the first how much would it cost you to buy faster hardware compared with the amount of work you will have to put down to create and set up a scale out environment and keep it running?
Because you can come a huge amount of way without scaling out. Buying one of the fastest x86 servers doesn't cost much compared to the work hours needed for such a solution.
My reason for writing this is that after 15 years in the problem solving part of this business I have grown very cautious against basically all cluster/HA solutions and as a technician I have a really hard time to justify the cost.
Since I have had so many single server setups running without problems for so long compared to with how many times I have had to spend a lot of time to sort out odd race condition/split brain/lost sync problems with cluster solutions.
The reason is that the more elaborate setup you have with more and more hosts to try to avoid a SPOF, you get a more and more complex environment with more things that can go wrong. And since in my experience most problems does not arise from hardware failure but from human errors a more complex environment causes a greater risk for errors.
Since it's seldom the same person that built the system that's also operates it on a daily basis the risk for human errors increases with a more complex environment where it is harder to understand the consequences of changes performed.
So anytime when someone talks about cluster or scaling out I say start by thinking if you really need it and are willing to pay the price.
That said (just to ensure you that I'm not a whining old fart
) there are cases when you need scaling out and if you for example take load balancing Application servers for the web then this is seldom a problem since every request is basically atomic so it makes it very easy to scale out and create a stable solution.
But since databases needs to store a state (the entire purpose of their existence
) it makes them very poor candidates for scaling out. Since all writes to a database requires a lock in some form or another and at some time or another, scaling out only means that there are more entities that need to be in sync (just consider how much time it takes to agree with a girlfriend about the weekend schedule) while if she unanimously decides it and just tells you the schedule afterwards (master->slave
My point is don't underestimate the simplicity of master->slave replication since it can save you a lot of work.
So my suggestion is to keep it simple:
1. Write to just one server
2. If you need the redundancy choose a master->slave replication.
3. If you have a slave run backups on the slave (which ensures no locks or high load on the master during the backup execution).
4. If you want your application to support load sharing of selects between servers in a master->slave setup you add a database query function in your application something like mysqlQueryLazySelect().
That way you can control which queries you want to distribute between the servers and which queries that are still run against the master. Since you can sometimes need to be sure that you query the master to avoid race conditions. And all writes are of course always sent to the master.
Now if you still want a clustering solution after all that.
One I think is very interesting for the MySQL future is the Percona XtraDB Cluster
, although the software is very fresh and the documentation is still lacking if you run into trouble I think it shows a lot of promise in it's simplicity.
Sorry for a far longer post than I intended and the best of luck!