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.

My software is deployed in various cities, each have a database, but they have different authorizations such as:

              central DB
                 /\
                /  \
              DB1  DB2
              /\    /\
             a  b  c  d

Databases a & b need replication to DB1. Databases DB1 & DB2 need replication to central DB.

How can I accomplish this?

share|improve this question

migrated from stackoverflow.com Mar 20 '13 at 15:07

This question came from our site for professional and enthusiast programmers.

    
Are the schema names and table names unique on each of a, b, c, and d? Or are they the same? What is the nature of the access needed to the data on DB1, DB2, and central DB -- read-only or do updates need to go back upstream to a/b/c/d (which I'm assuming are the sources of the data)? –  Michael - sqlbot Mar 21 '13 at 4:39

1 Answer 1

up vote 1 down vote accepted

I have two suggestions

Commercial Product

There is a product called ScaleBase that can handle Geographic replication topologies.

Handcrafted Solution

If you want to set up Geographic Replication using MySQL Replication, you will need to make the MySQL Instance in each Data Center resilient. Here is what thinking:

IDEA #1 : Local Data Redundancy

In each city, setup DRBD with MySQL running on the DRBD Primary

IDEA #2 : Use Distribution Masters

Having a MySQL Replication Master handle the traffic of SQL statement between Data Center could possibly add some intermittent behavior in terms of Replication Lag. If you run another MySQL Instance within the same Data Center whose sole purpose is to ship binary logs to another Data Center, that can be setup in what some call a BLACKHOLE slave. That MySQL Instance contains no actual MySQL. It simply has binary logs and relay logs sued for shipping to multiple slaves. I have discussed using this type of Slave in StackOverflow.

CAVEAT

The two ideas can be combined. The only drawback for using IDEA #2 is doing ALTER TABLE. If any major changes are to be done to table layouts, make sure the Distribution Masters do not change from the BLACKHOLE Storage Engine because this could cascade to the other Data Center and possibly destroy data.

EPILOGUE

I wrote about setting up such topologies before

Give it a Try !!!

share|improve this answer
    
A multi-tiered synchronization like that is something that SymmetricDS can do. It's open source and works with MySQL, along with many other databases. It is backed by JumpMind if you need a GUI setup and professional help. –  Eric Long Mar 21 '13 at 17:41
    
thank you @RolandoMySQLDBA ,the two ideas are very useful to me . –  mandatory Apr 1 '13 at 14:56
    
thanks. Have you ever use SymmetricDS ,I ever found somebody recommand it. –  mandatory Apr 1 '13 at 15:01

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.