Mysql multi-master active-active replication : replication doesn’t happen for all slaves

multi-masterMySQLreplication

so i created a multi-master replication with bellow servers two for master and two for slave.

hostname : master-1
ip : 192.168.120.107

hostname : master-2
ip : 192.168.120.111

hostname : slave-1
ip : 192.168.120.117

hostname : slave-2
ip : 192.168.120.118

the two masters (master-1,master-2) are deployed as active-active replication and works well and bellow is the configuration files /etc/mysql/my.cnf :

master-1 : 

[mysqld]
#
# * Basic Settings
#
server-id       = 1
user            = root
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
port            = 3306
basedir         = /usr
datadir         = /var/lib/mysql
tmpdir          = /tmp
lc-messages-dir = /usr/share/mysql
log-bin         = master-bin
log-bin-index   = master-bin.index
relay-log       = slave-relay-bin
relay-log-index = slave-relay-bin.index
skip-external-locking

master-2 :

[mysqld]
#
# * Basic Settings
#
server-id       = 2
user            = root
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
port            = 3306
basedir         = /usr
datadir         = /var/lib/mysql
tmpdir          = /tmp
lc-messages-dir = /usr/share/mysql
log-bin         = master-bin
log-bin-index   = master-bin.index
relay-log       = relay-log-bin
relay-log-index = relay-log-bin.index
skip-external-locking

masters work well. the two slaves are connected to the masters like this :
slave-1 is the master-1 's slave and slave-2 is the master-2 's slave. and these are the configuration files :

slave-1 : 

[mysqld]
#
# * Basic Settings
#
server-id       = 3
user            = mysql
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
port            = 3306
basedir         = /usr
datadir         = /var/lib/mysql
tmpdir          = /tmp
lc-messages-dir = /usr/share/mysql
relay-log       = slave-relay-bin
relay-log-index = slave-relay-bin.index
skip-external-locking

slave-2 :

[mysqld]
#
# * Basic Settings
#
server-id       = 4
user            = mysql
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
port            = 3306
basedir         = /usr
datadir         = /var/lib/mysql
tmpdir          = /tmp
lc-messages-dir = /usr/share/mysql
relay-log       = slave-relay-bin
relay-log-index = slave-relay-bin.index
skip-external-locking

when a master make changes to the database his slaves changes two, it mean works well for example when master-1 changes the database the changes will be replicated to master-2 and slave-1 and changes will be done in those as well. my problem is i need the changes happen in all slaves for example when master-1 changes the database ,slave-2 change his database like master-2 and slave-1. how can i make this happen?

enter image description here

Best Answer

Looking at you config, it seems you are missing log-slave-updates in your my.cnf files.

You will need to add this in to both masters my.cnf files and restart them.

This tells the slave database to take anything it receives from it's master, and copy from the relay log to the binary log so it can be replicated onwards.

e.g.

Master 1 Updates a table. It gets written to the binary log on Master1

This is replicated to Master 1's slaves (which are slave 1 and Master2)

In order for it to proceed to slave 2, master 2 has to be told to add it to it's binary log. This is what log-slave-updates does.

Related Question