How to configure MySQL Master-Master replication in CentOS 7
How to configure MySQL Master-Master replication in CentOS 7
The Master-Master replication is used to replicate one MySQL database on one or more servers. This type of replication uses master1 as master and master 2 as slave and both responding to data queries. This tutorial will explain on how to configure MySQL master-master replication in CentOS7.
Pre requisite:- Install MariaDB package into two CentOS machines. In this configuration procedure, we have used these two machines to explain the procedure.
Master1 IP : 192.168.7.244
Master2 IP : 192.168.7.210
Configuration procedure
Master1
To start the configuration procedure, first check the status of MariaDB. If is already installed, it should be active.
[root@localhost ~]# systemctl status mariadb.service
Now add the MySQL service into firewall if it is necessary and reload it.
[root@localhost ~]# firewall-cmd --permanent --add-service=mysql Success [root@localhost ~]# firewall-cmd --reload Success
The firewall is successfully added. Open the my.cnf file and add the following statement to the file. Save and exit from the file.
[root@localhost ~]# vim /etc/my.cnf
server-id=10
log-bin=mysql-bin
Restart the MariaDB service by running the following command.
[root@localhost ~]# systemctl restart mariadb
The MariaDB is running without any glitches. Open the MySQL terminal and execute the following steps.
[root@localhost ~]# mysql MariaDB [(none)]> create user ' reply' @' %' identified by ' 12345' Query OK, 0 rows affected (0.00 sec) MariaDB [(none)]> grant replication slave on *.* to ' reply' @' %' identified by ' 12345' Query OK, 0 rows affected (0.00 sec) MariaDB [(none)]> flush privileges Query OK, 0 rows affected (0.00 sec) MariaDB [(none)]> flush tables with read lock Query OK, 0 rows affected (0.00 sec) MariaDB [(none)]> show master status *************************** 1. row *************************** File: mysql-bin.000001 Position: 566 Binlog_Do_DB: Binlog_Ignore_DB: 1 row in set (0.03 sec)
Take the MySQL dump from master1 terminal and execute the following command.
[root@localhost ~]# mysqldump mysql > mysql-db.sql
Now transfer the dump file into master2 terminal and run the command.
[root@localhost ~]# rsync -Pavzxl mysql-db.sql root@192.168.7.210:/root/
Master2
In master 2 terminal, check the status of MariaDB by executing the following command as it should be actively running.
[root@localhost ~]# systemctl status mariadb.service
Add MySQL service into firewall if it is necessary and reload it.
[root@localhost ~]# firewall-cmd --permanent --add-service=mysql Success [root@localhost ~]# firewall-cmd --reload Success
The firewall is reloaded successfully. Open the my.cnf file and add the below statement. Save and exit from the file.
[root@localhost ~]# vim /etc/my.cnf
Server-id=20
Restart the MariaDB service by running the following command.
[root@localhost ~]# systemctl restart mariadb.service
Now inject the MySQL dump file into master2 database.
[root@localhost ~]# mysql mysql < mysql-db.sql
After dumping, open the MySQL shell and do the replication configuration as shown below.
[root@localhost ~]# mysql MariaDB [(none)]> stop slave Query OK, 0 rows affected, 1 warning (0.00 sec) MariaDB [(none)]> change master to -> master_host = ' 192.168.7.244' , -> master_user = ' reply' , -> master_password = ' 12345' , -> master_log_file = ' mysql-bin.000001' , -> master_log_pos = 566 Query OK, 0 rows affected (0.04 sec) MariaDB [(none)]> start slave Query OK, 0 rows affected (0.00 sec) MariaDB [(none)]> show processlist +----+-------------+-----------+------+---------+------+-----------------------------------------------------------------------------+------------------+----------+ | Id | User | Host | db | Command | Time | State | Info | Progress | +----+-------------+-----------+------+---------+------+-----------------------------------------------------------------------------+------------------+----------+ | 4 | root | localhost | NULL | Query | 0 | NULL | show processlist | 0.000 | | 6 | system user | | NULL | Connect | 19 | Slave has read all relay log waiting for the slave I/O thread to update it | NULL | 0.000 | +----+-------------+-----------+------+---------+------+-----------------------------------------------------------------------------+------------------+----------+ 2 rows in set (0.00 sec)
Check the slave status by using the command as shown below.
MariaDB [(none)]> show slave statusG *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.7.244 Master_User: reply Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000001 Read_Master_Log_Pos: 566 Relay_Log_File: mariadb-relay-bin.000004 Relay_Log_Pos: 529 Relay_Master_Log_File: mysql-bin.000001 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 566 Relay_Log_Space: 825 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 0 Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 10 1 row in set (0.00 sec) MariaDB [(none)]> exit
The slave status is shown. Exit from MariaDB and open the my.cnf file add the below statements. Save and exit from the file.
[root@localhost ~]# vim /etc/my.cnf
log-bin=mysql-bin
Restart the MariaDB service by running the following command.
[root@localhost ~]# systemctl restart mariadb.service
Go to the master and check the master status using the command as shown below.
[root@localhost ~]#mysql
MariaDB [(none)]> show master statusG
*************************** 1. row ***************************
File: mysql-bin.000001
Position: 447
Binlog_Do_DB:
Binlog_Ignore_DB:
1 row in set (0.00 sec)
Master 1
In the master terminal, check the established connection using netstat command.
[root@localhost ~]# netstat -natp | egrep -i established.*mysql
tcp 0 0 192.168.7.244:3306 192.168.7.210:35299 ESTABLISHED 46857/mysqld
Open the MySQL shell and check the processlist and also configure the replication settings.
[root@localhost ~]# mysql MariaDB [(none)]> unlock tables MariaDB [(none)]> show processlist +----+-------+---------------------+------+-------------+------+-----------------------------------------------------------------------+------------------+----------+ | Id | User | Host | db | Command | Time | State | Info | Progress | +----+-------+---------------------+------+-------------+------+-----------------------------------------------------------------------+------------------+----------+ | 6 | reply | 192.168.7.210:35299 | NULL | Binlog Dump | 570 | Master has sent all binlog to slave waiting for binlog to be updated | NULL | 0.000 | | 7 | root | localhost | NULL | Query | 0 | NULL | show processlist | 0.000 | +----+-------+---------------------+------+-------------+------+-----------------------------------------------------------------------+------------------+----------+ 2 rows in set (0.04 sec) MariaDB [(none)]> change master to -> master_host = ' 192.168.7.210' , -> master_user = ' reply' , -> master_password = ' 12345' , -> master_log_file = ' mysql-bin.000001' , -> master_log_pos = 245 Query OK, 0 rows affected (0.05 sec) MariaDB [(none)]> start slave Query OK, 0 rows affected (0.01 sec) MariaDB [(none)]> show slave status G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send even Master_Host: 192.168.7.210 Master_User: reply Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000001 Read_Master_Log_Pos: 245 Relay_Log_File: mariadb-relay-bin.000002 Relay_Log_Pos: 529 Relay_Master_Log_File: mysql-bin.000001 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 245 Relay_Log_Space: 825 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 0 Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 20 1 row in set (0.00 sec)
Checking the Replication process
Master 1
In the master terminal, create a database in master1 using MariaDB.
MariaDB [(none)]> create database Linux
Query OK, 1 row affected (0.02 sec)
Create a table for the newly created database.
MariaDB [Linux]> create table Distribution(Distro varchar(25) NOT NULL)
Query OK, 0 rows affected (0.03 sec)
Insert some values into newly created table.
MariaDB [Linux]> insert into Distribution values(' REDHAT' )
Query OK, 1 row affected (0.00 sec)
The table and the database has been created successfully. List the table from the database.
MariaDB [Linux]> select * from Distribution
+--------+
| Distro |
+--------+
| REDHAT |
+--------+
Master 2
In the master 2, login to MySQL shell and list the databases.
MariaDB [(none)]> show databases
+--------------------+
| Database |
+--------------------+
| information_schema |
| Linux |
| mysql |
| performance_schema |
| test |
+--------------------+
5 rows in set (0.00 sec)
Now the database which is created in master1 is replicated into master2. Use the database and list the table.
MariaDB [(none)]> use Linux
MariaDB [Linux]> show tables
+-----------------+
| Tables_in_Linux |
+-----------------+
| Distribution |
+-----------------+
Select and list the table from the selected database.
MariaDB [Linux]> select * from Distribution
+--------+
| Distro |
+--------+
| REDHAT |
+--------+
Insert some values into that table and list the table' s values the updated values is shown. Now the replication between master-master is working successfully.
MariaDB [Linux]> insert into Distribution values(' Ubuntu' ) Query OK, 1 row affected (0.00 sec) MariaDB [Linux]> select * from Distribution +--------+ | Distro | +--------+ | REDHAT | | Ubuntu | +--------+ 2 rows in set (0.00 sec)
Master1
Again goto master1 terminal, now select and list the tables to check the replication process. If the updated value from the master 2 is shown, then the replication process is done without any glitches.
MariaDB [Linux]> select * from Distribution
+--------+
| Distro |
+--------+
| REDHAT |
| Ubuntu |
+--------+
2 rows in set (0.00 sec)
Wasn' t that an easy configuration procedure? The Master-Master replication offers Data security, high availability, database backup from slave and failover solution.
sudo iptables -L -n -v
on each droplet, please do check them.