Sign up ×
Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

I have a (possibly not so quick question) about the best way to set up a 'global' MySQL Database.

I basically need to set up multiple MySQL servers in separate geographical locations that will all server the same data set (i.e. If I add data to one server, it will appear on another server relatively quickly). For example, if a user in America connects to our US website and orders a product (the US web server will be connected to a US MySQL Server), we need the data that was just inserted/updated/deleted/whatever to be syncronised with our Australian, New Zealand, UK etc MySQL Servers.

I've taken a look into MySQL Clusters, Master-Master replication, Galera Cluster, Circular Replication etc and was wondering what your thoughts were? I've read many pros and cons and simply can't decide what the best option is. Are there any reference setups that you are aware of?

I thank you for your time and valuable input :)

share|improve this question

closed as not constructive by mdpc, Tom O'Connor, Magellan, Michael Hampton, John Gardeniers Jan 21 '13 at 9:48

As it currently stands, this question is not a good fit for our Q&A format. We expect answers to be supported by facts, references, or expertise, but this question will likely solicit debate, arguments, polling, or extended discussion. If you feel that this question can be improved and possibly reopened, visit the help center for guidance.If this question can be reworded to fit the rules in the help center, please edit the question.

1 Answer 1

Very likely, you will need to re-consider your decision to use MySQL. If you need multi-master replication, it is a poor choice.

Also, you need to really do some work on your specs - "relatively quickly" is by no means any measure you could put to any existing or hypothetical infrastucture. You also should not be spec'ing times (as those might vary depending on the quality of the connection used) but state whether or not you need transaction safety across all your replicas:

  • if something has been ordered from the US database server, do you need to make absolutely sure it can't be ordered a second time from any other database servers or is your logic able to handle over-commitments?
  • if you are writing data from re-stocks to server X, do you need to make absolutely sure none of the other servers is showing stale data?
  • if a deposit has been added to an account and an order for this account is being placed in UK and New Zealand simultaneously, do you need to make sure the money cannot be spent twice because your replication was not up to speed with the ordering process?

All in all, as your project is obviously in the design stage, this is not an infrastructure question. Do a decent design first, come back with questions about implementation when you're done. Consider using other resources - especially read what other people have written on the topic of database design and ask over at StackOverflow if you have any specific developer-related questions.

share|improve this answer

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