How to Setup MySQL Master-Master Replication

This article consolidates information from several sources into the format I use to setup MySQL Master/Master Replication. The beauty of Linux and open source is that there are many different ways to do this. Please take a look at my references and use them to accommodate any needs you may have. If you have any questions or run into any issues feel free to drop me a line in the comments.

Assumptions

This article assumes you have already installed MySQL on to each of your servers. If not you can easily do so through the MySQL website at https://www.mysql.org/downloads. This article has not been tested on MariaDB but should work if you prefer to use MariaDB.

Change SELINUX to permissive (if installed)

Server A

[root@mysqla ~]# vi /etc/selinux/config
  
# This file controls the state of SELinux on the system.
# SELINUX= can take one of these three values:
#     enforcing - SELinux security policy is enforced.
#     permissive - SELinux prints warnings instead of enforcing.
#     disabled - No SELinux policy is loaded.
SELINUX=permissive
# SELINUXTYPE= can take one of these two values:
#     targeted - Targeted processes are protected,
#     mls - Multi Level Security protection.
SELINUXTYPE=targeted

Server B

[root@mysqlb ~]# vi /etc/selinux/config
  
# This file controls the state of SELinux on the system.
# SELINUX= can take one of these three values:
#     enforcing - SELinux security policy is enforced.
#     permissive - SELinux prints warnings instead of enforcing.
#     disabled - No SELinux policy is loaded.
SELINUX=permissive
# SELINUXTYPE= can take one of these two values:
#     targeted - Targeted processes are protected,
#     mls - Multi Level Security protection.
SELINUXTYPE=targeted

Stop and disable firewalld on each server

Server A

[root@mysqla ~]# systemctl stop firewalld
[root@mysqla ~]# systemctl disable firewalld

Run the following command to insure there are no firewall rules

[root@mysqla ~]# iptables -L

The result should look like:

Chain INPUT (policy ACCEPT)
target     prot opt source               destination
 
Chain FORWARD (policy ACCEPT)
target     prot opt source               destination
 
Chain OUTPUT (policy ACCEPT)
target     prot opt source               destination

Server B

[root@mysqlb ~]# systemctl stop firewalld
[root@mysqlb ~]# systemctl disable firewalld

Run the following command to ensure there are no firewall rules.

[root@mysqlb ~]# iptables -L

The result should look like:

Chain INPUT (policy ACCEPT)
target     prot opt source               destination
 
Chain FORWARD (policy ACCEPT)
target     prot opt source               destination
 
Chain OUTPUT (policy ACCEPT)
target     prot opt source               destination

Edit /etc/my.cnf on both servers

Add the following information to the bottom of the [mysqld] section

Server A

[root@mysqla ~]# vi /etc/my.cnf
	
    server-id=1
    log-bin="mysql-bin"
    binlog-do-db=name_of_database
    replicate-do-db=name_of_database
    relay-log="mysql-relay-log"
    auto-increment-offset = 1

Server B

[root@mysqlb ~]# vi /etc/my.cnf
    
    server-id=2
    log-bin="mysql-bin"
    binlog-do-db=name_of_database
    replicate-do-db=name_of_database
    relay-log="mysql-relay-log"
    auto-increment-offset = 2

Make sure you replace name_of_database with the name of the database that you want to replicate

Restart and enable the MySQL daemon on each server

Server A

[root@mysqla ~]# systemctl restart mysqld
[root@mysqla ~]# systemctl enable mysqld

Server B

[root@mysqlb ~]# systemctl restart mysqld
[root@mysqlb ~]# systemctl enable mysqld

Create the replicator user on each server

[root@mysqla ~]# mysql -u root -p
 
mysql> CREATE USER 'replicator'@'%' IDENTIFIED BY 'change_me';
mysql> GRANT REPLICATION SLAVE ON foo.* TO 'replicator'@'%'
[root@mysqlb ~]# mysql -u root -p
 
mysql> CREATE USER 'replicator'@'%' IDENTIFIED BY 'change_me';
mysql> GRANT REPLICATION SLAVE ON foo.* TO 'replicator'@'%'

Get log file information for use on the other server

Server A

[root@mysqla ~]# mysql -u root -p
 
mysql> SHOW MASTER STATUS;
 
+------------------+----------+------------------+------------------+
| File             | Position | Binlog_Do_DB     | Binlog_Ignore_DB |
+------------------+----------+------------------+------------------+
| mysql-bin.000001 | 154      | name_of_database |                  |
+------------------+----------+------------------+------------------+
1 row in set (0.00 sec)

Note the "File" and "Position" from this command

Server B

[root@mysqlb ~]# mysql -u root -p
 
mysql> STOP SLAVE;
 
mysql> CHANGE MASTER TO MASTER_HOST = 'Server A IP Address or HOSTNAME',MASTER_USER = 'replicator', MASTER_PASSWORD = 'change_me', MASTER_LOG_FILE = 'mysql-bin.000001', MASTER_LOG_POS = 154;
mysql> START SLAVE;

Repeat the same steps on Server B

Server B

[root@mysqlb ~]# mysql -u root -p mysql> SHOW MASTER STATUS;
 
+------------------+----------+------------------+------------------+
| File             | Position | Binlog_Do_DB     | Binlog_Ignore_DB |
+------------------+----------+------------------+------------------+
| mysql-bin.000001 | 154      | name_of_database |                  |
+------------------+----------+------------------+------------------+
1 row in set (0.00 sec)

Note the "File" and "Position" from this command

Server A

[root@mysqla ~]# mysql -u root -p 
 
mysql> STOP SLAVE; CHANGE MASTER TO MASTER_HOST = 'Server B IP Address or HOSTNAME', MASTER_USER = 'replicator', MASTER_PASSWORD = 'passw0rd', MASTER_LOG_FILE = 'mysql-bin.000001', MASTER_LOG_POS = 154; 
mysql> START SLAVE;

Reboot both servers

Server A

[root@mysqla ~]# systemctl reboot

Server B

[root@mysqlb ~]# systemctl reboot

On either server create your database

[root@mysqla ~]# mysql -u root -p 
 
mysql> CREATE DATABASE foo;

On the other server check to see that the database is there

[root@mysqlb ~]# mysql -u root -p
 
mysql> SHOW DATABASES; 
 
+--------------------+
| Database           |
+--------------------+
| information_schema |
| foo                |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

Sources

Share this page:

10 Comment(s)