MySQL Multi Master Replication

mysql-5.7replication

I have 4 servers running MySQL 5.7 in total , servers 1,2,3 each has single database on them For example: 

Server 1 has the DB x_1
Server 2 has the DB x_2
Server 3 has the DB x_3

Server 4 has 3 databases in it:

x_1
x_2
x_3

What i want to achieve is master to master replication between the 3 servers and server 4 each to his own database:

Changes in x_1 in server 4 or server 1 will replicate both ways
Changes in x_2 in server 4 or server 2 will replicate both ways
.
.
.

Appreciate any guidance to the correct way to achieve this (Note: I already know about multi source replication single slave and multiple masters but i am not sure if it can be used here)

Best Answer

You have to build two replication setups:

  • from masters 1, 2 and 3 to the slave 4 in the multimaster config
  • from master 4 to slaves 1, 2 and 3 in the plain replication config

Caveats:

  • each instance of mysql should have an unique server_id to tell events originators

  • multimaster masters 1, 2 and 3 should use binlog_do_db directive to restrict the set of databases being replicated (until you want to populate ALL the DB's like mysql.* across the whole setup):
    binlog_do_db = x_? # on each server 1, 2 and 3

  • mysql 4 as a master needs a restriction for replicated databases too:
    binlog_do_db = x_1,x_2,x_3

  • plain replication from the single master to three slaves means the shared master binlog will contain the events from all the databases mentioned in the binlog_do_db directive. So be ready that relay files will be about three times bigger than required for a single DB.

  • also you have to restrict the replication on each slave to the proper DB:
    replicate_do_db = x_? # on 1, 2 and 3
  • you have to turn on the binloging on all the servers
  • you have to CHANGE MASTER 's1'..., CHANGE MASTER 's2'... and CHANGE MASTER 's3'... on the server 4
  • you have to CHANGE MASTER 's4'... on each server 1, 2 and 3

Replication events are represented in the common object naming manner: originator.database.table

When event is replicated from, say, S1 to S4 it will be applied to the replica of x_1 on S4 as well as written to the binlog. Then that event will be replicated back to the S1. Each replication event is marked by its originator. S4 will see that certain event's originator is S1 and applies it to the corresponding database. When that event has been replicated back to the S1 it will see that he was an originator of the event and discard it. If certain event will be originated from S4 it will be applied by S1.

The whole setup isn't easy to maintain as far as each crash recovery will require the total reinitialization from the scratch. If you really want something like that you'd better look at the mariadb/galera.

Related Question