I appreciate this is a question that probably only a few can answer, but I'm going to ask it and hope for the best anyway.
Basically, I need to be able to replicate data from one data centre in the EU to another in the US. They are with different hosts but the platforms are designed to be otherwise similar.
Less basically, we direct people from any particular country to a localised version of the site. The US platform is to offer a faster experience for US based customers. I suggested that rather than replicate the whole database to the new US platform that we only put the US data on it, but the boss wants the two database servers to act as failover for each other, so we need both to have the full data set.
So, has anyone any experience of this, and how did you find it? I've only experience of replicating to a server sat alongside the master in the same data centre, or using NDB Cluster as an engine. How would you recommend we approach this with InnoDB?
It appears that replication is not the most popular topic on these forums as I have an unanswered question on the topic as well. The only suggestion I have for you from what I have been reading is make sure that your hardware on the second machine is AT LEAST equivalent to the first machine. I'm sure you may already know this, but since there will be a slight increase in latency due to the distance of the two servers, you don't want an additional bottleneck due to hardware.
Indeed. I did see your topic and noticed lots of views but no comments and I didn't really have anything to contribute.
The more I look at the concept, the less viable I believe it to be. I've spoken to Amazon about their cloud service and making the most of their "multi availability zones" and "read duplicates" but they don't tick enough boxes for what we need, so it's a no go.
As it stands, I'm considering a local replication setup, with cache servers at each datacentre, possibly leveraging a CDN for media itself. Making two identical copies of the site, one in the EU and one in the US is not practical.
For database replication with MySQL InnoDB across a wide area network, take a look at SymmetricDS It's open-source data synchronization software that works asynchronously and tolerates slow or intermittent network connections. If you have multiple sites involved in a multi-master configuration, it can detect and resolve conflicts. It can also be configured to transform data and handle differences in schema if needed.
Hope that helps,
Last edited by Mittineague; Aug 20, 2012 at 07:47.
Reason: OP forgot to turn off auto-link
Hi Eric, thanks for that. Do you have experience of SymmetricDS yourself? The latest thing I've been looking at is DRBD from Linbit. Sounds like it can handle what we need but I'm still investigating. Will look in to SymmetricDS as well. Thanks
I am a contributor to SymmetricDS project, so I am biased. I work for retailers in Fortune 500, where retail stores need to sync sales transactions to the central office. SymmetricDS grew out of this environment where the network is not always good and lots of remote locations need to sync data to a central location. I've done prototypes with DRBD to replicate two in-store servers as a fail-over solution. It does block-level duplication of a disk drive between two machines. It works very well when the two computers are next to each other. SymmetricDS works at the database level and syncs across wide area networks. It uses triggers in the database to guarantee capture of data, then synchronizes in the background to the remote location. There is also a commercial version called SymmetricDS Pro that includes production support.
Last edited by Mittineague; Aug 20, 2012 at 07:48.
Reason: OP forgot to turn off auto-link
I've been told that DRBD Proxy will provide us with a solution, but that the servers need to be an active/passive cluster. Since both platforms are hosted with competing providers I can imagine difficulties here, but I'm prepared to investigate at least.
So, I'll look further in to SymmetricDS later. I have a million and one things to work on at the moment, but it's great that I have another promising technology to consider. I have a couple of projects in mind where this could be beneficial also, so it's great information, thank you.
If I need more information should I tap the community, or approach the commercial guys, do you think?
I've had a conference call with a couple of guys at JumpMind and I'm still waiting to get more information back from the developers. In the mean time I've approached the hosts whose tech-support have recommended standard MySQL replication. Facepalm!