How to Setup MySQL Master-Master Replication
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 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)
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/