database sharding on mysql I need to do database sharding(horizontal) based on MySQL. My database schema is as following:


I have tables A,B,C
A,B is Global(which need not to be sharding)
C have huge data so it need to be sharding to achieve write scalability.
I will have several sharding,each contains one copy of A,B, and a subset of C.


My questions are:


How could i keep A,B on all sharding consistent,i.e, replicate A,B on all sharding so that they should have exactly same data. Or just store the Global Tables on single DB and cache via memcached?
How to do high availability for each sharding,i.e, if one sharding  crashed due to unknown reasons how could i do fail over.Through MySQL backup or master-master AP mode replica? 
How to do load balance for Global tables A,B on the multiple shardings?


Note that i will not use MySQL read replicas in this case.
It is possible for you to do this yourself rather than using commercial products like dbShards or ScaleBase but you will end up building the same thing and it isn't trivial.

To answer your question though, it makes sense to replicate A and B across all shards so you either need your application to duplicate the writes across all shards or you will need some replication technology to do this. I don't think you can easily configure MySQL replication to do this.

To load balance you will need your client to randomly choose a shard when running queries against these tables, just like you'll need your client to determine which shard to use when accessing table C.

For HA you will want to have master/slave setup for each shard so that you can failover in the case of losing a master shard.

Regards,

Andy Grove
Chief Architect
http://www.dbshards.com