For something like this, you may want to think about a unique architecture known as a Star Topology.
Picture the following:
- Server HQDB with 25 databases (1 for each branch)
- Server BranchDB001 with database `branch001``
- Server BranchDB002 with database
branch002
- ...
- Server BranchDB025 with database
branch025
Setup Data for HQDB
- On HQDB, create the 25 databases
branch001
through branch025
- Load data for each branch into their respective databases
Setup each Branch
- Load HQDB's
branch001
into branch001
on Server BranchDB001
- mysqldump branch001 database on HQDB into a script
- Run that script in
branch001
on Server BranchDB001
- Repeat this for all Branch Databases
Setup Replication
Here is the tricky part. You want to make the HQDB the Slave of Every Branch. Unfortunately, MySQL Replication is inherently designed to be a Slave reading from one Master. (NOTE: MultiMaster (MultiSource) Replication is being implemented in the soon-to-be-released MariaDB 10.0. Since I am a MySQL DBA, I leave that to more adventurous DBAs. I need to more free time to learn MariaDB.)
Getting on with MySQL Replication, the idea is
RESET MASTER
on BranchDB001
(Clears Binary Logs)
- make HQDB a Slave of a Branch (say
branch01
)
- Start Replication loading changes from BranchDB001's
branch001
into HQDB's branch001
RESET MASTER
on BranchDB002
- make HQDB a Slave of a Branch (say
branch02
)
- Start Replication loading changes from BranchDB002's
branch002
into HQDB's branch002
- ...
RESET MASTER
on BranchDB025
- make HQDB a Slave of a Branch (say
branch25
)
- Start Replication loading changes from BranchDB025's
branch025
into HQDB's branch025
If you have common tables to ship to every branch, simply mysqldump each branch's copy of that table and load into each branch.
All these things could be done in a maintenance cycle to update HQDB's copy of every branch
I have discussed this wild idea before
CAVEAT
- Make sure your firewalls allow for DB Access in both directions
- Branch to HQ
- HQ to Branch
- SSH Tunneling, please