Mysql database replication on different vlan/subnet/another site

Posted on

Question :

Saw the posts for “MySQL high availability, failover and replication with Latency”

Is it possible to work it in a DR site when its not in the same vlan?

Answer :

Being asynchronous, replication works very well across WANs or otherwise remote locations. I have used such topologies many times in the past (e.g. replication from West Coast to East Coast, from US to Middle East etc.).

Just as with normal replication, but somewhat more obvious, you must watch for slave lag if you intend to read from slave and you must have the very latest data updated. Due to latency, bandwidth issues etc. you may find that your remote slave tends to lag more than a local slave. This doesn’t have to be the case and I have witnessed remote slaves keeping up just fine with the master.

If you’re using a VPN, there’s nothing special about setting up this kind of replication. Otherwise, please consider using replication with SSL, so that your traffic is encrypted.

Yes, it is possible.

You would have to use a Public IP for the MASTER_HOST definition of the CHANGE MASTER TO command.

Just make sure your firewall settings allow traffic via port 3306 or whichever port you decide to use for MySQL.

I had recommended using MySQL 5.5’s semisync replication so that a Slave will acknowledge receipt of the SQL commands, rather than acknowledge completion of the SQL. This I recommend because of the geographic distance.

UPDATE 2012-07-26 23:15 EDT

@AaronBrown has enlightened me on the use of semisynchronous replication over a geographic distance. He presented empirical evidence in his blog that it is so bad that you can literally measure bottlenecks with a degree of accuracy.

DBA.SE Community, I beg you, DO NOT UPVOTE MY ANSWER !!! I leave this here as a learning experience for me and others to not just answer questions in the DBA.SE but to learn from better answers when presented. Thank you @AaronBrown.

Leave a Reply

Your email address will not be published. Required fields are marked *