database sharding on mysql

Question

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

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

My questions are:

1. 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?
2. 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?
3. 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.

Answer 1

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

Answer 2

1. Keep Global Tables A,B on Global DB,saying that DB1. Which all data need to be cached via memcached.
2. HA via mysql master-master replica. Only write to the Active server.
3. Since Global data on DB1, so no necessary to do load balance. Just getGlobalDBConnection() is enough.

Answer 3

You have asked multiple questions in your single question - Sharding, High Availability and Load Balancing. These are vast topics in themselves.
There are lot of resources available on Internet on these technologies. I suggest you should first read about them and you can always come back on StackOverflow if you are unclear on some concept or you get stuck somewhere in implementing these. Thanks.
EDIT:
I would like to share a link which I just found:
How to implement MySQL Sharding

Hope it helps.

Answer 4

I work for ScaleBase, which is a maker of a complete scale-out solution an "automatic sharding machine" if you like, analyzes the data and SQL stream, splits the data across DB nodes, load-balances reads, and aggregates results in runtime – so you won’t have to!

ScaleBase supports and maintains global tables transparently and safely, also enable high availability over replicated MySQLs for each "shard", read-repl and failover are configurable and automatic.

True, it's a commercial product, but developing it all in-house is such a pain... And even after all that pain it'll never be a complete as ScaleBase with the support for the application and the administration around it.

I invite you to see a demo of everything you wrote in your 1,2,3 in 5 mins.

Also, take a look on my post in http://database-scalability.blogspot.com/ about Pinterest, then and now...

I hoped I helped!