How to Setup MySQL Master-Master Replication
On this page
- Assumptions
- Change SELINUX to permissive (if installed)
- Stop and disable firewalld on each server
- Edit /etc/my.cnf on both servers
- Restart and enable the MySQL daemon on each server
- Create the replicator user on each server
- Get log file information for use on the other server
- Reboot both servers
- On either server create your database
- On the other server check to see that the database is there
- Sources
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
[[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:
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 A
[[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)
