Geographically Distributed MySQL
Asked by teshanee
Greetings to all.
There is a desire to geographically distribute the project, and start with one of its components: MySQL. Interesting answers are those who worked closely with this database and not in theory knows how various geographically distributed balancing schemes work.
The current scheme is approximately as follows: one web server and two database servers in master-slave mode. Only read requests go to one, mainly to write to the other, both database servers are nearby and are connected by a cross. There is an idea to make the scheme a little more complicated and put into operation several more servers in another country, while setting up database replication. The channels are good here and there, but the delays are already greater than when the pop-in-ass servers are connected. Who implemented such schemes: what can you say?
Is there really any known problems?
Maybe replication traffic can somehow be pressed to save the channel?
Is it worth using the ssl built into MySQL or is it better to pack everything in OpenVPN?
What kind of underwater (or even completely surface) stones will be encountered, if we add to this the master-master?
Who will tell about cluster types of databases in MySQL?
I will add that in the first place, practical knowledge is naturally more interesting than theoretical knowledge.
Answers
There may also be floating issues with code that expects no delays. Let's say a new user is registered (you start it in the master database), but you really can't do anything, because his data did not reach the slave. This problem looks pretty dumb, but there may be more cunning its manifestations.
dev.mysql.com/downloads/mysql-proxy/
Who will say something about cluster types of databases in MySQL? Blockquote>
Means engine = NDB? This is definitely not for you. NDB just assumes that the machines are side by side and well connected ...
One part is the “core” which is often synchronized, the second is the local geographic budding, which is somewhat of itself.
And which can sync without paranoia. Which greatly facilitates the work.