1

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?

1
  • 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)? Mar 21, 2013 at 4:39

1 Answer 1

1

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 !!!

3
  • 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, 2013 at 17:41
  • thank you @RolandoMySQLDBA ,the two ideas are very useful to me .
    – mandatory
    Apr 1, 2013 at 14:56
  • thanks. Have you ever use SymmetricDS ,I ever found somebody recommand it.
    – mandatory
    Apr 1, 2013 at 15:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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