Take the 2-minute tour ×
Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

The company I work for, has just moved to AWS - and currently they have one very large central database with the instance currently located in America.

However, one of their clients has requested that all of their data is held in the EU. So creating an AWS instance in Ireland isn't a problem, the problem is the database and how to manage it.

We were considering having another database that runs in the EU for European customers, and use a different primary key step, so that the primary keys will never conflict in case the two locations need to be merged in the future.

The problem is, if we have a customer that uses our system in both America, and the EU we would have to create 2 accounts for that user, and reporting across both regions would not be possible as the connection time would be too high.

Is there an alternative to set this up?

share|improve this question
    
You can centralize your accounts in a directory server (LDAP). Maybe some fancy logic in MySQL Proxy can help you? Re: reporting, not sure. Reporting means different things to different people. –  HTTP500 Aug 3 '12 at 18:47

3 Answers 3

Have you considered creating a MySQL Cluster to achieve this?

share|improve this answer

First of all, you can define the primary auto-incremental key like 2n (even number) for EU, and 2n+1 (odd number) for US, for those accounts reside in both locations, you should define a primary location for them and define the key by the primary location. In this way there would be no conflict of key, but you easily merge the data from two locations Secondly, for the reporting, you can generate billing reports for the two location separately, and to merge billing info for accounts that are in both locations.

share|improve this answer

You might want to look into distributed transactions. Distributed transaction are supported by MySQL's InnoDB storage engine. Here's a link to get you started.

share|improve this answer

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.