How to setup a master-master replication system between two MySQL servers

In this post, I will go to explain how to set up a master-master replication system between two MySQL to replicate an existing database.

The MySQL master-master configuration is a master-slave configuration in both directions and allows us to get a high availability configuration because you have the same data in both servers and if one goes down you can still work with the live server.

Machines

For the test, I have two virtual machines running Debian 9 in server version (you can download the virtual image from this link (Spanish version)).

The IP of this two virtual machine will be:

  • Server 1
    • 192.168.0.201
  • Server 2
    • 192.168.0.202

It is important to configure the virtual machine (Virtualbox in my case) network in “bridge” mode so that the machines see each other, as this way they have access to the local network. This does not happen in physical machines or in provisioned virtual machines.

Creating the server 1

To create the server 1 you have to import the appliance. Go to “File”->”Import Appliance…”:

Now you have to change the network. Over the virtual machine, select “Settings”:

And change the network configuration, using the “Bridged Adapter” option.

Creating the server 2

You have to make the same steps to create the server 2:

And you have to change the network adapter to “Bridged adapter”:

Now you have two virtual machines ready to work.

Starting the machines

Now you have to start the two virtual machines. To do this, you have to select each virtual machine and click on the “Start” button.

Changing the network configuration

Now you have to update the network address of each VM. Login in each machine with these credentials:

  • Username: root
  • Password: toor

Server 1

To check the address configuration you have to execute this command:

$ ip addr show

To add a new IP you have to edit the /etc/network/interfaces file:

$ sudo vi  /etc/network/interfaces 

auto lo
iface lo inet lookpack

auto enp0s3 
iface enp0s3 inet static 
     address 192.168.0.201 
     netmask 255.255.255.0 
     gateway 192.168.0.1 
     dns-nameservers 1.1.1.1 
     dns-nameservers 9.9.9.9

Once you save the file, you have to bring the interface down.

$ sudo ifdown enp0s3

Then bring the interface up:

$ sudo ifup enp0s3

And check the change:

$ sudo ip addr

Server 2

You have to do the same in the server 2 to add the IP 192.168.0.202. The unique change is to replace 201 with 202 in the /etc/network/interfaces file.

Check the change:

$ sudo ip addr

Try to ping to the other machine to check the connectivity.

From server 1 execute:

$ ping 192.168.0.202

And from server 2 execute:

$ ping 192.168.0.201

Updating the packages

To update the packages you have to execute in each machine:

$ sudo apt update && sudo apt upgrade -y

Installing MySQL 5.7

Debian 9 installs MariaDB by default (more information on this link and on this), so you have to add a new repo, then reload the sources and finally install the software.

You have to install MySQL 5.7 server on both servers. Execute the same process in both servers.

Go to the download page https://dev.mysql.com/downloads/repo/apt/ and get the filename of the executable (now is mysql-apt-config_0.8.13-1_all.deb). Maybe when you download it, the version will be a bigger one.

Download the version using wget or curl:

$ wget https://dev.mysql.com/get/mysql-apt-config_0.8.13-1_all.deb

Install the package:

$ sudo dpkg -i mysql-apt-config_0.8.7-1_all.deb

Add the MySQL 5.7 repo because the default repo is the MySQL 8.0.

Update the package information with the new repo:

$ sudo apt update

And install the MySQL server:

$ sudo apt install mysql-server

Enter the root password for the MySQL server:

Testing the MySQL server

Try in both servers that the server works well locally. To do this, try to connect using the root user:

$ mysql -u root -p
Enter password: 
Welcome to the MySQL monitor. Commands end with ; or \g. 
Your MySQL connection id is 2 
Server version: 5.7.28 MySQL Community Server (GPL) 

Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved. 

Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. 

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

To check that you can execute a command, show the databases that MySQL creates by default, with the command “show databases;”

mysql> show databases; 
+--------------------+ 
| Database | 
+--------------------+
|information_schema | 
| mysql | 
| performance_schema | 
| sys | 
+--------------------+ 
4 rows in set (0.00 sec)

To exit the MySQL client console run the “exit” command:

mysql> exit 
Bye

Execute this test on both servers.

Securing MySQL

If you’re going to use these servers in production, it’s interesting that you secure them. Also, (I’m not going to explain how to do it) it is highly recommended that you only allow remote access to MySQL from the other MySQL servers. To filter this traffic you can use a firewall as iptables.

To secure the server, execute the command “mysql_secure_installation”, which guided you to adjust the security of the MySQL server. After the command I leave the result of my execution to show the values I have used when the script asks me something.

$ sudo mysql_secure_installation 

Securing the MySQL server deployment. 

Enter password for user root: 

VALIDATE PASSWORD PLUGIN can be used to test passwords and improve security. It checks the strength of password and allows the users to set only those passwords which are secure enough. Would you like to setup VALIDATE PASSWORD plugin? 

Press y|Y for Yes, any other key for No: y 

There are three levels of password validation policy: 

LOW Length >= 8 
MEDIUM Length >= 8, numeric, mixed case, and special characters 
STRONG Length >= 8, numeric, mixed case, special characters and dictionary file 

Please enter 0 = LOW, 1 = MEDIUM and 2 = STRONG: 2 
Using existing password for root. 

Estimated strength of the password: 50 
Change the password for root ? ((Press y|Y for Yes, any other key for No) : n 

... skipping. 
By default, a MySQL installation has an anonymous user, allowing anyone to log into MySQL without having to have a user account created for them. This is intended only for testing, and to make the installation go a bit smoother. You should remove them before moving into a production environment. 

Remove anonymous users? (Press y|Y for Yes, any other key for No) : y 
Success. 

Normally, root should only be allowed to connect from 'localhost'. This ensures that someone cannot guess at the root password from the network. 

Disallow root login remotely? (Press y|Y for Yes, any other key for No) : y 
Success. 

By default, MySQL comes with a database named 'test' that anyone can access. This is also intended only for testing, and should be removed before moving into a production environment. 

Remove test database and access to it? (Press y|Y for Yes, any other key for No) : y 
- Dropping test database... 
Success. 

- Removing privileges on test database... 
Success. 

Reloading the privilege tables will ensure that all changes made so far will take effect immediately. 

Reload privilege tables now? (Press y|Y for Yes, any other key for No) : y 
Success. All done!

Then I restart the server:

$ sudo systemctl restart mysql.service

Execute this command on both servers.

Interface configuration

Next, I put MySQL to listen in all the IP, changing in the file /etc/mysql/mysql.conf.d/mysqld.cnf the line

bind-address = 127.0.0.1

with

bind-address = 0.0.0.0

Then I have to restart the service:

$ sudo systemctl restart mysql.service

Change this configuration on both servers. Remember to protect the server with a firewall in production.

To test that the MySQL port is open to other machines, I use nmap from one virtual machine to test the other and viceversa.

$ # Server 1
$ sudo apt install nmap -y
$ sudo nmap -p 3306 192.168.0.202
 Starting Nmap 7.40 ( https://nmap.org ) at 2019-10-23 13:24 CEST
 Nmap scan report for 192.168.0.202
 Host is up (-0.18s latency).
 PORT     STATE SERVICE
 3306/tcp open  mysql
 MAC Address: 08:00:27:44:DB:B4 (Oracle VirtualBox virtual NIC)
 Nmap done: 1 IP address (1 host up) scanned in 0.46 seconds

$ # Server 2
$ nmap -p 3306 192.168.0.201
 Starting Nmap 7.40 ( https://nmap.org ) at 2019-10-23 13:24 CEST
 Nmap scan report for 192.168.0.201
 Host is up (0.00027s latency).
 PORT     STATE SERVICE
 3306/tcp open  mysql
 MAC Address: 08:00:27:BD:0C:DB (Oracle VirtualBox virtual NIC)
 Nmap done: 1 IP address (1 host up) scanned in 0.47 seconds

Installing WordPress in the server 1

I will go to install a WordPress database to get a clean database to replicate. To do this I create a new database, a new user and grant access to this user to the new database:

$ # Server 1
$ mysql -u root -p
mysql> CREATE DATABASE wordpress CHARACTER SET utf8 COLLATE utf8_general_ci;
mysql> CREATE USER 'wordpress'@'localhost' identified by 'my_password';
mysql>  GRANT ALL PRIVILEGES ON  wordpress.* TO  wordpress@localhost; 
mysql> FLUSH PRIVILEGES;
mysql> exit

Now you have to install the WP-CLI to make a fast WordPress install:

$ sudo apt install php php7.0-mysql -y
$ wget https://raw.githubusercontent.com/wp-cli/builds/gh-pages/phar/wp-cli.phar 
$ php wp-cli.phar --info 
$ chmod +x wp-cli.phar 
$ sudo mv wp-cli.phar /usr/local/bin/wp 
$ wp --info 
OS:     Linux 4.9.0-11-amd64 #1 SMP Debian 4.9.189-3+deb9u1 (2019-09-20) x86_64
Shell: /bin/bash
PHP binary:    /usr/bin/php7.0
PHP version:    7.0.33-0+deb9u5
php.ini used:   /etc/php/7.0/cli/php.ini 
WP-CLI root dir:       phar://wp-cli.phar/vendor/wp-cli/wp-cli
WP-CLI vendor dir:     phar://wp-cli.phar/vendor
WP-CLI phar path:      /root 
WP-CLI packages dir:   
WP-CLI global config:  
WP-CLI project config: 
WP-CLI version: 2.3.0

To install WordPress you have to execute these commands:

$ cd /var/www/html
$ wp core download
$ wp config create \
 --dbname=wordpress \
 --dbuser=wordpress \
 --dbpass=my_password
$ wp core install --url=wordpress.test \
 --title="Testing WordPress" \
 --admin_user=manager_wordpress \
 --admin_password="my_long_and_secure_password" \
 --admin_email=hello@wordpress.test

Configuring the replication

Now you have to configure the replication in the MySQL configuration.

Server 1

You have to edit the file /etc/mysql/myssql.conf.d/mysqld.conf to add the next lines:

server-id = 1
log_bin = /var/log/mysql/mysql-bin.log
expire_logs_days = 10
max_binlog_size = 100M
binlog_do_db = wordpress

Then you have to restart the service to update the configuration:

$ sudo systemctl restart mysql.service

To check the service you can execute 

$ netstat -ntpl | grep mysql
 tcp        0      0 0.0.0.0:3306            0.0.0.0:*               LISTEN      17592/mysqld

Server 2

You have to make the same change in the same file in the server 2, changing the server-id from 1 to 2:

server-id = 2
log_bin = /var/log/mysql/mysql-bin.log
expire_logs_days = 10
max_binlog_size = 100M
binlog_do_db = wordpress

Then you have to restart the service to update the configuration:

$ sudo systemctl restart mysql.service

To check the service you can execute:

$ netstat -ntpl | grep mysql
 tcp        0      0 0.0.0.0:3306            0.0.0.0:*               LISTEN      5996/mysqld

Creating the users for the replication

You need to create one MySQL user in each server for the replication, so you have to create the user and grant replication permissions.

Server 1

Open the MySQL command line, create the user and grant the correct permissions:

> CREATE USER 'replicationuser'@'%' identified by 'my_password'; 
> GRANT REPLICATION SLAVE ON *.* TO 'replicationuser'@'%';

Server 2

Open the MySQL command line, create the user and grant the correct permissions:

> CREATE USER 'replicationuser'@'%' identified by 'my_password'; 
> GRANT REPLICATION SLAVE ON *.* TO 'replicationuser'@'%';

Testing the users

You need to test that the users can access to the other server:

Server 1

Check that the replication user can access to the server 2:

$ mysql -u replicationuser -p -h 192.168.0.202
 Enter password:
 Welcome to the MySQL monitor.  Commands end with ; or \g.
 Your MySQL connection id is 84
 Server version: 5.7.28-log MySQL Community Server (GPL)
 Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.
 Oracle is a registered trademark of Oracle Corporation and/or its
 affiliates. Other names may be trademarks of their respective
 owners.
 Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
 mysql>

Server 2

Check that the replication user can access to the server 1:

$ mysql -u replicationuser -p -h 192.168.0.201 
Enter password:
 Welcome to the MySQL monitor.  Commands end with ; or \g.
 Your MySQL connection id is 16
 Server version: 5.7.28-log MySQL Community Server (GPL)
 Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.
 Oracle is a registered trademark of Oracle Corporation and/or its
 affiliates. Other names may be trademarks of their respective
 owners.
 Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
 mysql>

Export the database from server 1 to server 2

To start the replication both servers must have the same copy of the database, so you have to stop all applications or other elements that can change the content of the databases until we start the replication.

Server 1

In the server 1, I have the database, so I will go to dump it and then I will copy it to the server 2 using scp:

$ mysqldump wordpress > wordpress.sql
$ scp wordpress.sql user@192.168.0.202:/home/user

Server 2

In the server 2, I have to create the database, a user to access to it and then I restore the database content with the dump from the server 1:

$ mysql -u root -p
mysql> CREATE DATABASE wordpress CHARACTER SET utf8 COLLATE utf8_general_ci;
mysql> CREATE USER 'wordpress'@'localhost' identified by 'my_password';
mysql>  GRANT ALL PRIVILEGES ON  wordpress.* TO  wordpress@localhost; 
mysql> FLUSH PRIVILEGES;
mysql> exit 
$ cd /home/user
$ mysql -u root -p wordpress < wordpress.sql

Start the replication

Now you have to tell each server that the other server is its master and the point from which the replication will start:

Server 1

Open the MySQL command line and check the master status:

mysql> show master status\G;
 * 1. row *
              File: mysql-bin.000004
          Position: 617
      Binlog_Do_DB: wordpress
  Binlog_Ignore_DB:
 Executed_Gtid_Set:
 1 row in set (0,00 sec)

You need two values:

  1. The file name: mysql-bin.000004
  2. The position: 617

Go to the server 2, open the MySQL command line, stop the slave:

> stop slave;
Query OK, 0 rows affected, 1 warning (0,00 sec)

Change the master configuration:

>  CHANGE MASTER TO MASTER_HOST = '192.168.0.201', MASTER_USER = 'replicationuser', MASTER_PASSWORD = 'my_password', MASTER_LOG_FILE = 'mysql-bin.000004', MASTER_LOG_POS = 617; 
Query OK, 0 rows affected, 2 warnings (0,20 sec)

And start the slave:

> start slave;

Server 2

Open the MySQL command line and check the master status: 

> show master status\G;
 * 1. row *
              File: mysql-bin.000004
          Position: 38321
      Binlog_Do_DB: wordpress
  Binlog_Ignore_DB:
 Executed_Gtid_Set:
 1 row in set (0,00 sec)

You need two values:

  1. The file name: mysql-bin.000004
  2. The position: 38321

Go to server 1, open the MySQL command line, stop the slave:

> stop slave;
Query OK, 0 rows affected, 1 warning (0,00 sec)

Change the master configuration:

>  CHANGE MASTER TO MASTER_HOST = '192.168.0.202', MASTER_USER = 'replicationuser', MASTER_PASSWORD = 'toor', MASTER_LOG_FILE = 'mysql-bin.000004', MASTER_LOG_POS = 38321; 
Query OK, 0 rows affected, 2 warnings (0,20 sec)

And start the slave:

> start slave;

Test the replication

Replication from server 1 to server 2

Server 1

To test the replication from server 1 to server 2 I will change the “thread_comments_depth” option in the database from “5”, the current value, to “6”, using the WP-CLI:

$ wp option update thread_comments_depth 6

Server 2

To see if the value has changed on the server 2, open the MySQL command line and check the value using an SQL query:

> use wordpress;
> select * from wp_options where option_name='thread_comments_depth'\G;
   * 1. row *
      option_id: 72
    option_name: thread_comments_depth
   option_value: 6
       autoload: yes
   1 row in set (0,00 sec)

You can see that the value has changed.

Replication from server 1 to server 2

Server 2

Now I will check the synchronization from server 2 to server 1. To test the replication I will change the “wp_page_for_privacy_policy” option in the database from “3”, the current value, to “4”, using an SQL query: 

> update wp_options set option_value=4 where option_name='wp_page_for_privacy_policy';
   Query OK, 1 row affected (0,03 sec)
   Rows matched: 1  Changed: 1  Warnings: 0

Server 1

To see if the value has changed on the server 1, I check the value using the WP-CLI: 

$ wp option get wp_page_for_privacy_policy
4

You can see that the value has changed.

More info

Leave a comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.