A practical guide to MySQL Master-Master replication

Sponsored

This article is a piece of my personal experience. I’ll explain how to setup MySQL Master-Master replication on Ubuntu 18.04. Even though my guide walks you through Ubuntu specifics, with minor differences, the same steps should work on any other Linux distribution.

Why would you need a Master-Master setup? There are lots of different reasons. In my case, I wanted to run a personal website on two machines and use both of them as active endpoints via DNS load balancing.

I highly recommend setting up the replication over a secure channel. There are several options. Among them two are the most obvious:

  • Use SSL / TLS between replicas
  • Utilize an encrypted VPN channel between hosts

I have chosen to proceed with option two as it looked more interesting to me. In addition to secure replication, I wanted to have a dedicated VPN path between my machines for running rsync for web content synchronization.

Beginning: primary specs

In my setup, I had two VPS (virtual private servers) powered by Ubuntu 18.04 LTS. Both machines had slightly different specs, but the software was identical.

$ lsb_release -a
No LSB modules are available.
Distributor ID: Ubuntu
Description:    Ubuntu 18.04.2 LTS
Release:        18.04
Codename:       bionic

I won’t explain here how to set up the OpenVPN channel between two hosts. That is a different topic, and it deserves a dedicated post to reveal all the nuances and steps of the VPN setup.

First server

Once we have working VPN channel between two machines, let’s edit /etc/my.cnf on the first machine. If you aren’t sure where is it located in your system, you can find it by running the following command:

egrep -v '^$|^#' /etc/mysql/my.cnf
!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/mysql.conf.d/

As you can see from the output, the file is here:

root@ubuntu-8gb-hel1-1:/etc/mysql/mysql.conf.d# ls
mysqld.cnf  mysqld_safe_syslog.cnf

The name of the file that we need is mysqld.cnf. Let’s make it look as follows:

[mysqld_safe]
socket          = /var/run/mysqld/mysqld.sock
nice            = 0
[mysqld]
user            = mysql
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
port            = 3306
basedir         = /usr
datadir         = /var/lib/mysql
tmpdir          = /tmp
lc-messages-dir = /usr/share/mysql
skip-external-locking
bind-address            = 0.0.0.0
key_buffer_size         = 16M
max_allowed_packet      = 16M
thread_stack            = 192K
thread_cache_size       = 8
myisam-recover-options  = BACKUP
query_cache_limit       = 1M
query_cache_size        = 16M
log_error = /var/log/mysql/error.log
server-id               = 2
log_bin                 = /var/log/mysql/mysql-bin.log
log_bin_index           = /var/log/mysql/mysql-bin.log.index
relay_log               = /var/log/mysql/mysql-relay-bin
relay_log_index         = /var/log/mysql/mysql-relay-bin.index
expire_logs_days        = 10
max_binlog_size         = 100M
log_slave_updates       = 1
auto-increment-increment = 2
auto-increment-offset = 2
slave_exec_mode         = IDEMPOTENT

There are a few key points here.

  • MySQL daemon was configured to listen on all available IP addresses (bind-address= 0.0.0.0)
  • The log_bin has been added, as well as other replication-related parameters

Since we are expecting to run the replication over the OpenVPN, we have to allow ONLY internal traffic through the tunnel interface and prohibit everything else. Obviously, we should not keep MySQL running on tcp:3306 on an external IP addresses.

tun0: flags=4305<UP,POINTOPOINT,RUNNING,NOARP,MULTICAST>  mtu 1500
        inet 10.8.0.1  netmask 255.255.255.255  destination 10.8.0.2
        inet6 fe80::782e:bdb2:60ef:cb76  prefixlen 64  scopeid 0x20<link>
        unspec 00-00-00-00-00-00-00-00-00-00-00-00-00-00-00-00  txqueuelen 100  (UNSPEC)
        RX packets 32386  bytes 8441773 (8.4 MB)
        RX errors 0  dropped 0  overruns 0  frame 0
        TX packets 32429  bytes 8292722 (8.2 MB)
        TX errors 0  dropped 0 overruns 0  carrier 0  collisions 0

As we see, our internal IP address is 10.8.0.1.

Now, we have to use iptables to allow MySQL traffic only via OpenVPN.

iptables -A INPUT -s 127.0.0.1/32 -d 127.0.0.1/32 -p tcp -m tcp --dport 3306 -j ACCEPT
iptables -A INPUT -s 10.8.0.6/32 -d 10.8.0.6/32 -p tcp -m tcp --dport 3306 -j ACCEPT
iptables -A INPUT -s 10.8.0.6/32 -d 10.8.0.1/32 -p tcp -m tcp --dport 3306 -j ACCEPT
iptables -A INPUT -s 10.8.0.1/32 -d 10.8.0.6/32 -p tcp -m tcp --dport 3306 -j ACCEPT
iptables -A INPUT -p tcp -m tcp --dport 3306 -j DROP

Don’t forget to save iptables rules persistently, since the input listed above will disappear after server restarts.

Now we have to create a MySQL user for replication:

mysql -u root -p [root_password]
CREATE USER 'replicator'@'10.8.0.6' IDENTIFIED BY '[myC001p@ssw0rd]';
GRANT REPLICATION SLAVE ON *.* TO 'replicator'@'10.8.0.6' IDENTIFIED BY '[myC001p@ssw0rd]';

Please note, 10.8.0.6 is the IP address of the Second server that will be connected to this machine via OpenVPN.

☄Warning! The password for the replication user must not exceed 32 symbols. Make sure you do it right from the first time. Otherwise, you will learn it in a hard way, as I did.

Second server

The second machine has exactly the same operating system, so all we need to do is to adjust MySQL config file as we previously did. The full path to the file that we will edit is  /etc/mysql/mysql.conf.d/mysqld.cnf

[mysqld_safe]
socket          = /var/run/mysqld/mysqld.sock
nice            = 0
[mysqld]
user            = mysql
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
port            = 3306
basedir         = /usr
datadir         = /var/lib/mysql
tmpdir          = /tmp
lc-messages-dir = /usr/share/mysql
skip-external-locking
bind-address            = 0.0.0.0
key_buffer_size         = 16M
max_allowed_packet      = 16M
thread_stack            = 192K
thread_cache_size       = 8
myisam-recover-options  = BACKUP
query_cache_limit       = 1M
query_cache_size        = 16M
log_error = /var/log/mysql/error.log
server-id               = 1
log_bin                 = /var/log/mysql/mysql-bin.log
log_bin_index           = /var/log/mysql/mysql-bin.log.index
relay_log               = /var/log/mysql/mysql-relay-bin
relay_log_index         = /var/log/mysql/mysql-relay-bin.index
expire_logs_days        = 10
max_binlog_size         = 100M
log_slave_updates       = 1
auto-increment-increment = 2
auto-increment-offset   = 1
slave_exec_mode         = IDEMPOTENT

Similarly to the steps taken on the first server, we have to create our replication user on the second machine. Now this user will have an address after ‘@’ from the remote machine, from where we’re going to connect to the second server.

mysql -u root -p [root_password]
CREATE USER 'replicator'@'10.8.0.1' IDENTIFIED BY '[myC001p@ssw0rd]';
GRANT REPLICATION SLAVE ON *.* TO 'replicator'@'10.8.0.1' IDENTIFIED BY '[myC001p@ssw0rd]';

The next step is to add the same firewall rules as we have on the first machine.

iptables -A INPUT -s 127.0.0.1/32 -d 127.0.0.1/32 -p tcp -m tcp --dport 3306 -j ACCEPT
iptables -A INPUT -s 10.8.0.6/32 -d 10.8.0.6/32 -p tcp -m tcp --dport 3306 -j ACCEPT
iptables -A INPUT -s 10.8.0.6/32 -d 10.8.0.1/32 -p tcp -m tcp --dport 3306 -j ACCEPT
iptables -A INPUT -s 10.8.0.1/32 -d 10.8.0.6/32 -p tcp -m tcp --dport 3306 -j ACCEPT
iptables -A INPUT -p tcp -m tcp --dport 3306 -j DROP

I also want to mention that the Database which I tried to replicate was already created as I was setting this up on my existing production. Before starting the replication, you have to manually copy the DB file via mysqldump and restore it on your second machine. Otherwise, the replication might complain about missing tables and could throw other errors, which is hard to predict. I learned this hard way and spent an extra ten minutes trying to troubleshoot the issue. Dumping and restoring the DB on the second machine solved the problem.

Enabling replication

SSH on your first machine, enter the mysql as root and run:

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000005 |   488168 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

Now let’s go to the second server and enable the replication from the position mentioned above (4889168):

STOP SLAVE;
CHANGE MASTER TO MASTER_HOST = '10.8.0.6', MASTER_USER = 'replicator', MASTER_PASSWORD = 'myC001p@ssw0rd', MASTER_LOG_FILE = 'mysql-bin.000001', MASTER_LOG_POS = 48168; 
START SLAVE;

Doing the same steps on the scond machine:

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000004 |  1024406 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

Now it’s time to SSH to the first server and start the replication there:

STOP SLAVE;
CHANGE MASTER TO MASTER_HOST = '10.8.0.1', MASTER_USER = 'replicator', MASTER_PASSWORD = 'myC001p@ssw0rd', MASTER_LOG_FILE = 'mysql-bin.000001', MASTER_LOG_POS = 1024406; 
START SLAVE;

That’s it! We can consider that everything is complete at this point. To confirm that the replication works as expected, check the following output from both machines:

mysql> pager less;
PAGER set to 'less'
mysql> show slave status \G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.8.0.6
                  Master_User: replication
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000005
          Read_Master_Log_Pos: 490967
               Relay_Log_File: mysql-relay-bin.000015
                Relay_Log_Pos: 491180
        Relay_Master_Log_File: mysql-bin.000005
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 490967
  Relay_Log_Space: 491434
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 1
                  Master_UUID: 26a385be-d0d2-11e8-81d5-42010aa60002
             Master_Info_File: /var/lib/mysql/master.info
                    SQL_Delay: 0
SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 0
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 

Two essential things indicate success or non-success of our effort. If everything is going rightly, in the output you will see:

Slave_IO_Running: Yes
Slave_SQL_Running: Yes

Be the first to comment

Leave a Reply

Your email address will not be published.


*