Search code examples
mysqlreplicationmaster-slave

MySQL 2 masters N-slaves replication

I'm trying to set up a two masters, N-slaves replication in MySQL

I have setup two masters that works perfectly fine with each other.

It breaks when I add a slave for master#1.

Creating

master#1 : Create database test.

master#2 : test database gets created from master#1's query.

  • creates a test database in slave#1

Deleting

master#2 : Delete database test.

master#1 : test database gets deleted from master#2's query.

  • test doesn't get removed in slave#1

What I did was to create a user in **master#

create user 'root'@'slave.one.ip' identified by 'slaveonepass';

Give it replication privileges:

grant replication slave on dbname.* to 'root'@'slave.one.ip';

Get the info of master#1:

show master status;

Add the info from above to slave#1

mysql> change master to\
master_host='first.master.ip',\
master_user='root',\
master_password='slaveonepass',\
master_log_file='mysql-bin.123456',\
master_log_pos=123456;`

Reference:

Solution

  • This would be the expected behavior if you have not set LOG_SLAVE_UPDATES=ON on both masters. Set this in the configuration files, and restart the masters.

    Normally, a slave does not write to its own binary log any updates that are received from a master server. This option causes the slave to write the updates performed by its SQL thread to its own binary log

    https://dev.mysql.com/doc/refman/5.6/en/replication-options-binary-log.html#sysvar_log_slave_updates

    This option is mandatory for all cascading replication configurations, where an instance needs to propagate incoming replication events out to additional replicas.

    You should also enable this in any slave that will have downstream slaves connected.