Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

We are working on a new system architecture for our company. We have a HPC, which runs in our own data-center and we are planning our front-end and a fallback system on Amazon Web Service.

System Architecture:

System Architecture

Preconditions:

  • The HPC cluster has a lot of write operations
  • Website and API both read the data most of the time and write rarely
  • Ping from AWS to our cluster is ~35ms
  • In case of our local data-center failing, the HPC should be replicated on AWS, turning the MySQL Slave into the new Master

Question:

What is the best solution to replicate the MySQL database in such a setup?

share|improve this question

1 Answer 1

up vote 4 down vote accepted

There are three(3) suggestions I have that you may want to look into...

SUGGESTION #1

You could use MySQL 5.5 both locally and in AWS. You would also setup Semisync Replication. That way, every SQL statement executed on the Master has its latest recorded SQL statement sent over to the Slave.

In my past post (Jul 26, 2012 : Mysql database replication on different vlan/subnet/another site) I had recommended SemiSynchronous Replication over a geographic distance. Interestingly, @AaronBrown points out by experimentation that High Latency would degrade Semisynch Replication (See his blog on this one).

SUGGESTION #2

If all your data is InnoDB, you could use Percona XtraDB Cluster (preferable to have two Slaves in AWS but one would do). Why Percona XtraDB Cluster? You have synchronized writes. You only major bottleneck would strictly be network communication (again High Latency would have an adverse affect)

Here are my past posts on using Percona XtraDB Cluster

SUGGESTION #3

If you want to strengthen each data center's local copy of the data, I would suggestion using DRBD, not over a geographic distance, but having a DRBD cluster in each data center. In your case, at least have DRBD in the local data center.

Here is my past post on this suggestion :

share|improve this answer
    
Thank you very much for your suggestions. I will try to setup a Master-Master asynchronous replication, with each database having only one node as the master (the HPC and the front-end don't need to write to the same databases) –  Thomas Aug 21 '12 at 9:37

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.