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.