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

[[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)

Sources

Share this page:

Suggested articles

10 Comment(s)

Add comment

Comments

By: A

Why not open the firewall ports instead of disabling it?

By: wido

Wait, does this mean that I could put a keepalived with a VIP and not 'care' where the db is active, as in 'there is no active/all are active'?

By: snix

no. you would need to monitor server ups/downs and replication lag. even with this info you still can't be sure if you can switch "masters" without creating inconsistentncy. its easy to break replication.

By: Morgan

You should add a big notice about disabling the firewall and reducing selinux as it is greatly reducing the security of your server,  thus should never be done on a live net accessible server.

By: SemperOSS

I agree with @A, this approach to ignore security is typical for many in IT and thus the problems we see these days with exploits. The right way to do it would be to explain how to set up SELinux to handle the appropriate programs and what ports to open in the firewall.

I appreciate that teaching secure setup was not the purpose of the article but the approach taken is worse than not mentioning it at all.

By: Jörg Brühe

The security aspects (firewall, SElinux) have already been mentioned, I won't repeat that.

In addition, this tutorial calls for a number of comments on MySQL aspects:

1) On both machines, the configuration needs a setting added:auto-increment-increment = 2

2) To make sure the binlog is complete (needed for point-in-time recovery), also add:log-slave-updates = 1

3) Filtering the binlog is (in general) a no-no, because it prevents point-in-time recovery:Drop the lines with "binlog-do-db", so that every change is binlogged.

4) The article builds on the assumption that only one database (schema) is to be replicated, but does not mention it. Such a design decision must never be taken "by accident", it must be a conscious decision and explicitly mentioned. It must be listed in the assumptions.

5) Filtering replication (by "replicate-do-db") has some issues, for example after creating the database (which gets replicated) a "create table" in this DB will not be replicated via a binlog in row format - see my bug report: https://bugs.mysql.com/bug.php?id=77673Because of this, my general advice is not to filter but to replicate everything. (As always, there are exceptions.)

6) Similar, I doubt that a "create user" or "grant" will be replicated, I expect it to be filtered. So all commands to manage your application users need to be issued on both servers.

7) If you really want to filter replication, the binlog format ("row" vs "statement") will control the semantics. So this setting becomes important in the configuration.

8) If the "create database" is done after the reboot and gets replicated from serverA to serverB, this is a proof that replication is working in this direction. Still, this should be checked on both servers by issuing "show slave status \G". (Note the "\G" rather than the ";", for better readability.)

By: Faisal Ali

this is my second tutorial that I did for the master master replication, and it still does not work.

do i have to access the replicator user? or is this tutorial old anyone who can point me to the right direction.

By: B

Don't follow such tutorial for production environment. Disabling firewall / grant access to replication for all remote host is definitely not a good practice. Can't understand how such process can be on top search in Google. 

By: Ruslan

Instead "GRANT REPLICATION SLAVE ON foo.* TO 'replicator'@'%'" should be "GRANT REPLICATION SLAVE ON *.* TO 'replicator'@'%'" otherwise you faced issue: "ERROR 1221 (HY000): Incorrect usage of DB GRANT and GLOBAL PRIVILEGES". 

By: Kamil

Hi, why article don't show publish date? Comments too;)