current community

your communities

more stack exchange communities

Stack Exchange Inbox Reputation and Badges
tour help
Sign up ×
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.
up vote 0 down vote favorite

I'm working on a company where I have a central DB and several agency databases. See the diagram at here. All the databases are identical, just the data can change and in some cases. I've no internet connection between agencies and central but I need to maintain a replication between central and agencies and in both path meaning for example if I make a change at central this change should be replicated across all the agencies and viceversa, if I any of the agencies make a change then should be replicated. This has a additional problem and is the following: from agencies to central all the data should be replicate all the time but from central to agencies only the updated data should be replicated and depending on which agency. For example: I made changes in Agency1 DB and Agency2 DB and those changes will be replicated to Central, I made changes in Central but for data taken from Agency1 DB, so I should replicate the changed data from Central to Agency1 DB only. Any help? Advice? Method?

share|improve this question
    
The first question is this: How do you propose to get the data between sites with "no internet connection"? What kind of connectivity is available, if not an Internet connection? Private line? Postal mail? Telegraph? –  Michael - sqlbot May 31 '13 at 14:24
    
@Michael-sqlbot Ok, there is a internet connection but it's slowly and IP's are dinamically so is a real pain. I not in a secure VPN so a agency, for example, could be in Spain and another on Argentina and Central is on Venezuela, just as a example. The idea is to make mysqldump backups and send me trough email and I do the job all the time due to characteristics of the internet connection –  Reynier May 31 '13 at 15:15
    
How about sending the binlogs, which you can dump into "normal" sql statements and replay? That would cover the one-way problem, but not when the agencies update. For them, you might consider triggers to write to a staging table that can then be exported and migrated. –  Alain Collins Jun 16 '13 at 21:17
    
@AlainCollins thanks for your answer, as I'm not a MySQL expert could you please answer the question and explain better your solution? –  Reynier Jun 17 '13 at 17:24

Your Answer

 
discard

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

Browse other questions tagged or ask your own question.