How to Configure Multi Master Replication on MYSQL 8

May 5, 2020 Mudhalvan Leave a comment

How to Configure MySQL 8 Multi Master Replication on Ubuntu

Note : Please follow the Master-Slave Configuration and make sure replication is working between Master to Slave. I tested and created the document on the same here

Now let us continue how to enable the replication from Slave to Master. We can call it as Multi Master replication or Bi-directional replication

1. On MySQL-Slave Node
We have to create replication user in MySQL-Slave and allow access from MySQL-Master

ubuntu@MySQL-Slave:~$ mysql -u root -p
mysql> create user rpl_user@192.168.0.8 identified by ‘password’;
Query OK, 0 rows affected (0.08 sec)
Grant the user REPLICATION SLAVE privileges:

mysql> grant replication slave on . to rpl_user@192.168.0.8;
Query OK, 0 rows affected (0.09 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
Confirm grants for created user:

mysql> show grants for rpl_user@192.168.0.8;

ALTER USER rpl_user@192.168.0.8 IDENTIFIED WITH mysql_native_password BY ‘password’;

mysql> show master status \G;

Take a note of current Master log file and position

2. On MySQL-Master – Node
We have to update the MASTER_HOST in MySQL-Master node as MySQL-Slave

mysql>CHANGE MASTER TO MASTER_HOST=’192.168.0.9′,
MASTER_USER=’rpl_user’,
MASTER_PASSWORD=’password’,
MASTER_LOG_FILE=’mysql-bin.000006′,
MASTER_LOG_POS=1178;

mysql> start slave;