Geographically Distributed MySQL

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.

Comments:

VPN is a technical reason for downtime. This is not a solution. Ask a question to free hosting. There can be no clustering here. Old question, read about it for the first time. - s

Answers

hestia23

In the past, there was a single master vs slave circuit in different regions. The task of balancing at the base level was not solved, access was mostly local. You can live, but, as you have already noticed, replication can lag behind, and it does this unevenly. Another rake - channels, bitch, still not as reliable as we would like. Missing link between servers - replica got up. For the same reason, quite occasionally, the ability to write something into the master “from afar” disappeared. So, in any case, I advise first of all to introduce at least the simplest monitoring and try to understand how much it will blunt specifically in your case, and assess whether this is appropriate. If you have graphs of the replication latency, control of the availability of the wizard from each point from which you write to it - it will be easier to live but simpler, but more predictable :)

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.

eric dawson

MySQL proxy - with the help of this product, it is possible to redistribute the load for MySQL servers, incl. configure complex schemes with distributed databases.

dev.mysql.com/downloads/mysql-proxy/

Replies:

This partially resolves the issues I have deleted. - mike auteri

missy marriott

Who will say something about cluster types of databases in MySQL?

Means engine = NDB? This is definitely not for you. NDB just assumes that the machines are side by side and well connected ...

juli simon thomas

We had ~ 10 hours as the record lag time for master-master replication, it was ~ 900 MB. Channels and iron were definitely not a weak point. Something like this.

kirsten

Now I stare at the commercial DRBD-proxy about this.

tiffany mcelmurry

If geographic separation is done to speed up the access of local users (those to remote database servers are also backends), then the best thing is to split the database into two parts.
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.

Best ORM for C # :: DoubleVPN do it yourself. Is it possible to? :: Books on Zend Framework :: Advise material on OpenSSL :: How much can an experienced web developer get today?

Leave Repply for Geographically Distributed MySQL