What is master master replication mysql?

In another article, we learned how to set up a Master-Slave type of replication, today i’m going to show you how to set up a Master-Master one, meaning both nodes are open in Read/Write mode.

Both nodes will be in sync and will accept any modifications (and will replicate it to the other one)

🛠 Prerequisites

First of all, let’s make a list of what we need:

1. Two Virtual Machines with CentOS or Oracle Linux 7 (How to install Oracle Linux 7)
2. MySQL 8 installed on both VMs (How to install MySQL 8).

🖥 Scenario:

First Node

• OL7-MYSQL1: 192.168.1.35
  • ol7-mysql1: 50GB

Second Node

• OL7-MYSQL2: 192.168.1.36
  • ol7-mysql2: 50GB

⚙️ Setup

Once you have your VMs all set up, with MySQL installed and configured, it’s time to set up the replicas.

We will start by replicating the First Node to the Second one, then we will configure the First one to replicate any modifications from the Second.

Setting up a Master-Master replica it’s pretty much the same as setting up a Master-Slave one, with one additional step at the end.

Firewalld Rule

Unless you want to disable the firewall, you need to add a rule to let clients connect to your Database.

sudo firewall-cmd --zone=public --permanent --add-port=3306/tcp 

sudo systemctl restart firewalld

Node Priority

On both Nodes, edit the MySQL configuration file

vi /etc/my.cnf

By adding these two lines

First Node

bind-address = 192.168.1.35
server-id = 1 
log_bin = mysql-bin

Second Node

bind-address = 192.168.1.36
server-id = 2 
log_bin = mysql-bin

And then restart the mysql on both nodes

sudo systemctl restart mysqld

Create ‘Replica’ User

On the first node, create the user that will replicate the data

CREATE USER 'replica'@'192.168.1.36' IDENTIFIED BY 'R3plic4$';

ALTER USER 'replica'@'192.168.1.36' IDENTIFIED WITH mysql_native_password BY 'R3plic4$';

GRANT REPLICATION SLAVE ON .TO 'replica'@'192.168.1.36';

Now check the Master Status

SHOW MASTER STATUS\G

And note down the binlog file name and the position

*************************** 1. row ***************************
             File: mysql-bin.000001
         Position: 324
     Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set:
1 row in set (0.00 sec) 

Replicate to the Second Node

Now on the Second Node, connect to your MySQL database and stop any running slave process

STOP SLAVE;

Ignore the warning

Change now the master to the primary node, be careful to use the correct MASTER_LOG_FILE and MASTER_LOG_POS, accordingly to the SHOW MASTER STATUS command above.

CHANGE MASTER TO
MASTER_HOST='192.168.1.35',
MASTER_USER='replica' ,
MASTER_PASSWORD='R3plic4$',
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=324;

Ignore the warning

Once done, start the slave process to begin the replication

START SLAVE;

Give it some time to connect to the First Node and execute the command

SHOW SLAVE STATUS\G

Here you only have to care about 4 main things:

Slave_IO_State: Waiting for master to send event 
Master_Host: 192.168.1.35 
Slave_IO_Running: Yes 
Slave_SQL_Running: Yes

Now that we have a fully functional Master-Slave replication, we need to convert it into a Master-Master one, by configuring the First node to accept data from the Second one.

First of all, we need to create the replica user

CREATE USER 'replica'@'192.168.1.35' IDENTIFIED BY 'R3plic4$';

ALTER USER 'replica'@'192.168.1.35' IDENTIFIED WITH mysql_native_password BY 'R3plic4$';

GRANT REPLICATION SLAVE ON .TO 'replica'@'192.168.1.35';

Then, we need to know the binlog name and position of the “slave”, so let’s execute this command on the Second node:

SHOW MASTER STATUS\G

 *************************** 1. row ***************************
             File: mysql-bin.000002
         Position: 1442
     Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set:
1 row in set (0.00 sec) 

Replicate to the First Node

Now that we have all the info, let’s proceed with the configuration.

On the First Node, connect to the MySQL and first of all, stop any running slave process

STOP SLAVE;

Ignore the warning

Now execute the CHANGE MASTER TO with the MASTER_HOST pointing to the Second Node.

CHANGE MASTER TO
MASTER_HOST='192.168.1.36',
MASTER_USER='replica' ,
MASTER_PASSWORD='R3plic4$',
MASTER_LOG_FILE='mysql-bin.000002',
MASTER_LOG_POS=1442;

Ignore the warning

Once done, time to start the slave process again

START SLAVE;

Last thing we need to do, is check the slave status, to ensure everything’s working fine

SHOW SLAVE STATUS\G

Slave_IO_State: Waiting for master to send event 
Master_Host: 192.168.1.36 
Slave_IO_Running: Yes 
Slave_SQL_Running: Yes

How does master master replication work in MySQL?

How do I set master to master replication?

What is replication in MySQL?

How does multi master replication work?