Geographically distributed MySQL

0 like 0 dislike
5 views
Welcome all.

There is a desire to geographically distribute the project, and start with one of its components: MySQL. Interesting answers are those who have worked closely with this database and not in theory knows how to operate various schemes geographically distributed balancing.

The current scheme like this: one web server and two database servers in a mode "master-slave". To one are read-only, to another primarily on the record that both database servers are nearby and connected by cross. There is an idea to make the circuit a little harder and build some more servers in another country, in this case to configure replication for the database. Channels there and there good, but delays longer than when connecting to server "pop-in-the ass". Who implemented such schemes: what can I say?

  • Really or are there any known issues?
  • Can replication traffic can press to save the channel?
  • You should use MySQL built-in ssl or is it better to pack everything in OpenVPN?
  • What are underwater (or even quite surface) rocks will meet, if we add to this master-master?
  • Anyone say anything about clustered databases in MySQL?


Will addthat in the first place, naturally interested in practical knowledge than theoretical.
by | 5 views

6 Answers

0 like 0 dislike
The last job was the scheme of one master vs slave in different regions. The task of balancing at the database level is not solved, the access was predominantly local. You can live, but as You can see, the replication can lag, and it makes it uneven. Another mistake — channels, bitch, still not as reliable as might be desired. Missing link between the servers is a replica of rose. For this reason, it is sufficient periodically lost the ability to record something in the master "afar". So in any case suggest to enter at least a simple monitoring and try to understand how much it will rebate specifically in your case, and assess whether it is suitable. If You have graphics lag time replication, control access to the master from each point, where will it write to live will be, maybe not easier, but more predictable :)
\r
Can also appear floating problems with code that expects no delays. For example, it registers a new user (you start in master database), but to do really can not do anything, because its data is not reached to the slave. This problem looks pretty stupid, but there may be more cunning manifestation.
by
0 like 0 dislike
MySQL proxy — with the help of this product can be transparent to users to redistribute load to MySQL servers, including configure complex circuits with distributed databases.
\r
\rdev.mysql.com/downloads/mysql-proxy/
by
0 like 0 dislike
Anyone say anything about clustered databases in MySQL?

\r
This refers to engine=NDB? This is definitely not for You. NDB just assumes that machines side by side and well connected...
by
0 like 0 dislike
Record time the lag master-master replication we had ~10 hours, it is ~900mb. Channels and iron were not the weak point. Something like that.
by
0 like 0 dislike
Now looking at the commercial DRBD-proxy for this reason.
by
0 like 0 dislike
If geographic separation is done for the accelerating access local users( those to internal servers and database backends are) then it is best to split the database into two parts.
One part of the "core" which is often syncretise, the second topically-geographical spin-off, which in some ways in itself.
And which can be sinkrit without paranoia. Which greatly facilitates the work.
by

Related questions

0 like 0 dislike
3 answers
0 like 0 dislike
1 answer
asked Mar 24, 2019 by darkslesh
0 like 0 dislike
5 answers
asked Mar 25, 2019 by Cyrax
110,608 questions
257,186 answers
0 comments
1,245 users