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?

RolandoMySQLDBA
  • 185,223
  • 33
  • 326
  • 536
mandatory
  • 13
  • 3

1 Answers1

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

Community
  • 1
RolandoMySQLDBA
  • 185,223
  • 33
  • 326
  • 536