We're looking to implement load balancing by horizontally sharding our tables across a cluster of servers. What are some options to implement live redundancy should a server fail?
Would it be effective to do (2) INSERTS instead of one ... one to the target shard, and another to a secondary shard which could be accessed should the primary shard not respond? Or is there a better way?
Thanks.
the most common approach to achieve load-balancing and fail-safe is to have a master server and at least one or more slave servers.
in your application you have a connection only for writing which always use the master, and for reading uses one of the slaves. (this can be autloadbalenced with for example heartbeat and ldirectord.
Your single point of failure now would be the master server, for which you should have a hot-standby. (DRBD and heartbeat are a good team for such needs)