How to Setup MySQL Master-Master Replication

• 23 3月 2021
• by: hostslb.com
• in: linux
• Tags: linux, mysql, server
• note: no comments

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.if(typeof __ez_fad_position != ‘undefined’){__ez_fad_position(‘div-gpt-ad-howtoforge_com-box-3-0’)};

if(typeof __ez_fad_position != ‘undefined’){__ez_fad_position(‘div-gpt-ad-howtoforge_com-medrectangle-3-0’)};

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

[[email protected] ~]# 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

[[email protected] ~]# 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

[[email protected] ~]# systemctl stop firewalld
[[email protected] ~]# systemctl disable firewalld

Run the following command to insure there are no firewall rules

[[email protected] ~]# iptables -L

The result should look like:if(typeof __ez_fad_position != ‘undefined’){__ez_fad_position(‘div-gpt-ad-howtoforge_com-medrectangle-4-0’)};

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

[[email protected] ~]# systemctl stop firewalld
[[email protected] ~]# systemctl disable firewalld

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

[[email protected] ~]# 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

[[email protected] ~]# 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

[[email protected] ~]# 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 AAdvertisement.banner-1{text-align:center; padding-top:10px !important;padding-bottom:10px !important;padding-left:0px !important;padding-right:0px !important;width:100% !important;box-sizing:border-box !important;background-color:#eeeeee !important;border: 1px solid #dfdfdf}if(typeof __ez_fad_position != ‘undefined’){__ez_fad_position(‘div-gpt-ad-howtoforge_com-banner-1-0’)};

[[email protected] ~]# systemctl restart mysqld

[[email protected] ~]# systemctl enable mysqld

Server B

[[email protected] ~]# systemctl restart mysqld

[[email protected] ~]# systemctl enable mysqld

Create the replicator user on each server

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

[[email protected] ~]# 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

[[email protected] ~]# 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

[[email protected] ~]# 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

[[email protected] ~]# 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

[[email protected] ~]# 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

[[email protected] ~]# systemctl reboot

Server B

[[email protected] ~]# systemctl reboot

On either server create your database

[[email protected] ~]# mysql -u root -p 
 
mysql> CREATE DATABASE foo;

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

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

Sources

• https://www.howtoforge.com/mysql_database_replication
• https://www.digitalocean.com/community/tutorials/how-to-set-up-mysql-master-master-replication
• https://www.howtoforge.com/mysql_master_master_replication
• http://www.ryadel.com/en/mysql-master-master-replication-setup-in-5-easy-steps/