We've been working on a networked online game project which will run in near future and looking for a satisfying solution for our distributed game database. We didn't take networking costs into account so far.
We have following constraints:
1) We planned to use MySql but we can review alternatives if offered solution makes sense.
2) We planned to host multiple game servers on different geographical regions such as USA, Europe and Asia.
3) Every gamer usually connect to the nearest game server and distributed database node in order to reduce networking costs and lags.
We've been thinking to develop a logic in one of our server side layers that will check the user location he wants to connect from. Then, replicate user specific data from one database node to another if the check operation results that the gamer attempt to connect from a different region than he did before.
We wonder that is the correct way to do that?
Of course we have to think disaster cases. For that purpose, what can we do if one of the distributed database node fails?
We don't think to replicate all data between database nodes because it causes high networking costs.