2

I have created a POS system for our corporation, in the HQ we have a wamp server with the main database, we also have more than 25 branches across the country.

I will setup a wamp server on each branch, so I can access its database directly when putting wamp online.

I want to make MySQL replication with all branches, so every query on any branch will affect the main database on HQ.

I tried to test but found no one to explain how to do it using wamp on different PCs.

Improve this question

1 Answer 1

Reset to default
2

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
Improve this answer
2
  • @Rolando I've been on hiatus, so I just saw this question...tungsten replicator has a cookbook for setting up a star topology
    – Derek Downey
    Jul 3, 2013 at 3:04
  • MariaDB 10.0 is in Alpha still, but I've been using its multi-source replication since 10.0.2. I even got the original data set in place by stopping a running MySQL 5.5.28 slave and doing a binary copy of all the files to a new machine, dropping in the MariaDB executable and support files, starting it up, and running MariaDB's mysqlupgrade... far more painless than I anticipated. Added the 2nd data set from Master #2 with mysqldump and configured to the 2nd master. It's been quietly humming along ever since with no issues. I would think it would be a a strong candidate for this scenario.
    – Michael - sqlbot
    Jul 5, 2013 at 4:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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