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?
Add a comment
|
2 Answers
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.
answered Aug 26, 2012 at 4:16
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.
answered Jul 25, 2012 at 14:11
-
2Semisynchronous replication is a very bad idea for geographically distant slaves. if you have an additional 50ms of latency due to the distance, you could add an order of magnitude to the response time for simple write operations. With 50ms of latency, you have limited yourself to 20 write operations per second at best, not including the actual execution time. Commented Jul 26, 2012 at 0:38
-
@AaronBrown I use semisync replication over geographic distance but I alter timeout values to give some ground for network latency. MySQL already knows how to alternate from semisync to asynch. If high-volume transactions and data durability were essential, I would combine replication with DRBD ( dba.stackexchange.com/a/1983/877 ) in both data centers as an added level of insurance. From your comment, I do realize that replication over a geographic distance is bad no matter you slice it (+1 for your comment). I just try to compensate as best as possible. Commented Jul 26, 2012 at 11:41
-
You can never get more than 20 writes queries per sec with 50ms latency and semisync. I don't see how timeouts help there. From east to west coast in the US is 85ms, which leaves you at 11 qps, best case. That will cripple all but the smallest of applications. Commented Jul 26, 2012 at 12:18
-
@AaronBrown I read your blog : blog.9minutesnooze.com/… It's brilliant and I humbly admit your right. In fact, you should submit this here on this question. I would upvote it !!! Commented Jul 27, 2012 at 3:04
-
Thanks, Rolando - I will consider it. Please know that while this post inspired the idea to write that blog, no disrespect is intended at all towards you! Commented Jul 27, 2012 at 14:49