1

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?

1
  • 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, 2012 at 18:47

3 Answers 3

0

Have you considered creating a MySQL Cluster to achieve this?

0

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.

0

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.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .