0

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

  • So you are saying that if you make a change through master-1, it is replicated to slave-1 and to master-2 but not to slave-2? And when you make a change though master-2, it is replicated to slave-2? – ypercubeᵀᴹ Jan 19 '16 at 7:19
  • yeap. as you said when i change the master-1 the master-2 and slave-1 will be changed but not slave-2. when i change the master-2 the master-1 and slave-2 will be changed but not slave-1. – amir jj Jan 19 '16 at 7:29
  • Are you sure that it isn't just delayed? – ypercubeᵀᴹ Jan 19 '16 at 7:31
  • also take care of this comment: slave-1 is the master-1 's slave and slave-2 is the master-2 's slave. – amir jj Jan 19 '16 at 7:31
  • yes it's not delay. according to my comment i think it is logical that every master replicate the changes he himself has made to his slaves not the changes that the other master has done. but I need a solution that any master replicate all changes – amir jj Jan 19 '16 at 7:34
2

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.

  • But have you lost the updates that happened before turning on that setting? – Rick James Jan 20 '16 at 2:00
  • yes unfortunately. – amir jj Jan 20 '16 at 5:44

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.