MySQL replicate multiple places

Question

Very trick task to find a good title for this question, but here goes the q:

I have a few development machines, where I develop my PHP applications on, and testing via a local webserver.

This works out pretty well for each machine. However, I would like to replicate the DB from my machines to a central location.

So, to sum up:

DEV1 -> CENTRAL
DEV2 -> CENTRAL
DEV3 -> CENTRAL
CENTRAL -> DEV1
CENTRAL -> DEV2
CENTRAL -> DEV3

I hope this makes sense, as I cannot find an easy way to tell it.

Basically, it is a 2-way replication, where all 4 databases contain the same info, and each of them can be updated locally, to then be pushed out to the others.

Is this actually doable?

All my dev machines are running Windows 7, and my central DB server is running CentOS 6.

score 1 · Answer 1 · answered Sep 24 '12 at 15:18

1

@Frederik Nielsen, yes, it is possible to have multiple slave MySQL instances for each master MySQL instance.

However, note that each slave can only replicate from one master. So two-way (or quadruple way) replication is not really possible in your original setup, but I think the idea of one master with 4 slaves might be a good solution.

answered Sep 24 '12 at 15:18

Lena Weber

313
1
4

Thanks for your answer, and will also be what I choose to do. Rolando's answer, however, was the correct one for my original question. – Frederik Sep 24 '12 at 15:19

score 0 · Answer 2 · answered Sep 05 '12 at 11:46

0

Looks like you want multi-master replication which has been supported for a little while in MySQL.

answered Sep 05 '12 at 11:46

James Yale

5,182
1
17
20

Sounds quite right. Is it just something I setup via fx phpmyadmin? – Frederik Sep 05 '12 at 12:16
No, running circular master replication gets very *VERY* complicated when you have more than 2 nodes. For more nodes you need to use a non circular erplicator such as Percona or Tungsten. – symcbean Sep 05 '12 at 12:16
Hmm allright.. Maybe I should just go for a single MySQL DB, and then let the others connect to it. Is it possible to have 1 master, that replicates out to several others? Then I would just do the changes on the master, and then I can use the local DB when I got no internet connectivity. – Frederik Sep 05 '12 at 12:25

score 0 · Accepted Answer · edited Apr 13 '17 at 12:43

You could go with Percona XtraDB Cluster

I am currently evaluating it and I think it is brilliantly designed for MultiMaster writes. It can use mysqldump (default), rsync, and xtrabackup (preferred) for initializing new Cluster node. You have total freedom and power. This may be the greatest cliche of all time but WITH GREAT POWER, THEIR MUST ALSO ALWAYS BE GREAT RESPONSIBILITY (19:16 - 19:25 of the Video).

I discussed this as one of many options for cross-datacenter replication in the DBA StackExchange : https://dba.stackexchange.com/questions/22788/best-solution-for-cross-datacenter-mysql-master-slave-replication/22790#22790

This sounds like the best solution, though, I decided to move away from the multi-master-setup. — Frederik, Sep 19 '12 at 17:39

MySQL replicate multiple places

3 Answers3