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:
- Two Virtual Machines with CentOS or Oracle Linux 7 (How to install Oracle Linux 7)
- 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 firewalldNode Priority
On both Nodes, edit the MySQL configuration file
vi /etc/my.cnfBy adding these two lines
First Node
bind-address = 192.168.1.35 server-id = 1 log_bin = mysql-binSecond Node
bind-address = 192.168.1.36 server-id = 2 log_bin = mysql-binAnd then restart the mysql on both nodes
sudo systemctl restart mysqldCreate ‘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\GAnd 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
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\GHere 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: YesNow 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
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\GSlave_IO_State: Waiting for master to send event Master_Host: 192.168.1.36 Slave_IO_Running: Yes Slave_SQL_Running: Yes