How to set up MariaDB Master-Slave replication with SSL on Ubuntu Linux

Posted on in Categories , , last updated July 26, 2017

How do I setup Master-slave data replication using MariaDB server on Ubuntu Linux 16.04 LTS server? How do I encrypt replication traffic so that the data get encrypted in transit, protecting my data and users from snooping?

Master-slave data replication allows you to copy databases to multiple MariaDB servers. This is useful for backup, data recovery, load balancing and much more. In this tutorial, you will learn how to configure SSL-protected MariaDB replication between a master and slave server.

Our sample setup

Set Up Master Slave Replication in MariaDB
Fig.01: Set Up Master Slave Replication in MariaDB

This tutorial will use the following IP addresses for db1 and db2 servers:

  1. 192.168.2.5 (db1) – Master MariaDB server
  2. 192.168.2.6 (db2) – Slave MariaDB server

I am going to assume that both db1 and db2 are brand new servers. There is no existing database on server.

What you need to setup MariaDB master-slave cluster?

  1. Minimum two servers (can be a cloud or bare metal boxes)
  2. A private network (LAN/VLAN) between servers
  3. A VPN between two data center if setting between two IDCs
  4. Ubuntu Linux 16.04 LTS on both servers

Update your /etc/hosts

First updated your /etc/hosts file as follows on db1 and db2 server:
$ sudo vi /etc/hosts
Edit/append as follows:
192.168.2.5 db1
192.168.2.6 db2

Save and close the file. Test it as follows:
$ ping -c4 db1
$ ping -c4 db2

Step 1 – Install MariaDB latest stable version on Ubuntu 16.04 LTS

Type the following apt-get command on both db1 and db2 server to install MariaDB:
$ sudo apt-get install software-properties-common
$ sudo apt-key adv --recv-keys --keyserver hkp://keyserver.ubuntu.com:80 0xF1656F24C74CD1D8
$ sudo add-apt-repository 'deb [arch=amd64,i386,ppc64el] http://sgp1.mirrors.digitalocean.com/mariadb/repo/10.2/ubuntu xenial main'
$ sudo apt-get update
$ sudo apt-get install mariadb-server mariadb-client

Sample outputs:

Fig.02: Installing MariaDB server
Fig.02: Installing MariaDB server

Step 2 – Secure MariaDB

Type the following command:
$ mysql_secure_installation
Sample outputs:

Fig.02:  Secure the MariaDB installation
Fig.02: Secure the MariaDB installation

Step 3 – Create SSL keys and certificates for MariaDB

Type the following commands on the db1 server only (730 days == 2 years):
$ sudo mkdir -p /etc/mysql/ssl/
$ cd /etc/mysql/ssl/
$ sudo openssl genrsa 2048 > ca-key.pem
## set CA common name to "MariaDB admin" ##
$ sudo openssl req -new -x509 -nodes -days 730 -key ca-key.pem -out ca-cert.pem
## set server certificate common name to "MariaDB server" ##
$ sudo openssl req -newkey rsa:2048 -days 730 -nodes -keyout server-key.pem -out server-req.pem
$ sudo openssl rsa -in server-key.pem -out server-key.pem
$ sudo openssl x509 -req -in server-req.pem -days 730 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 -out server-cert.pem
## set client common name to "MariaDB client" ##
$ sudo openssl req -newkey rsa:2048 -days 730 -nodes -keyout client-key.pem -out client-req.pem
$ sudo openssl rsa -in client-key.pem -out client-key.pem
$ sudo openssl x509 -req -in client-req.pem -days 730 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 -out client-cert.pem
$ sudo openssl verify -CAfile ca-cert.pem server-cert.pem client-cert.pem

To avoid any issues you must common name them as follows:

  • CA common Name : MariaDB admin
  • Server common Name: MariaDB server
  • Client common Name: MariaDB client

Also copy all certificates to the db2 slave server:
$ ssh [email protected] mkdir /tmp/ssl/
$ cd /etc/mysql/ssl/
$ scp * [email protected]:/tmp/ssl/

Step 4 – Configure the master MariaDB server

Type the following commands on the db1 master server. You need to edit the /etc/mysql/my.cnf file, run:
$ sudo vi /etc/mysql/my.cnf
Configure the MariaDB client (add in [client] section) to use SSL:
ssl-ca=/etc/mysql/ssl/ca-cert.pem
ssl-cert=/etc/mysql/ssl/client-cert.pem
ssl-key=/etc/mysql/ssl/client-key.pem

Set bind address i.e. listen only on db1’s private IP address 192.168.2.5 (make sure you replace the IP 192.168.2.5 with your actual IP address)
bind-address = 192.168.2.5
Mark this server as a Master server. This number must be unique:
server-id = 1
Make sure server has binlogs enabled i.e. make sure log_bin and lob_bin_index are set as follows:
log_bin = /var/log/mysql/mariadb-bin
log_bin_index = /var/log/mysql/mariadb-bin.index

Configure the MariaDB server (add in [mysqld] section) to use SSL:
ssl
ssl-ca=/etc/mysql/ssl/ca-cert.pem
ssl-cert=/etc/mysql/ssl/server-cert.pem
ssl-key=/etc/mysql/ssl/server-key.pem

Save and close the file. Restart MariaDB server, run:
$ sudo systemctl restart mysql.service

Configure replication

Type the following command on the master db1 server:
$ mysql
OR
$ mysql -u root -p
You must create a MySQL user the master server (db1). The syntax is:

grant replication slave on *.* TO {username}@'{ip_of_db2_server}' identified by '{password}' REQUIRE SSL;

For example:
mysql> GRANT REPLICATION SLAVE ON *.* TO 'db_slave_usr'@'192.168.2.6' IDENTIFIED BY 'secretePassword' REQUIRE SSL;
mysql> FLUSH PRIVILEGES;
mysql> quit

Find out master status

Type the following command at shell:
$ mysql -u root -p
Once the MySQL shell opened, type:
mysql> SHOW MASTER STATUS;
Sample outputs:

Fig.03: Find out master status and note down the position
Fig.03: Find out master status and note down the position

Record the file and position details for the slave server named db2.

Step 5 – Configure the slave MariaDB server

Type the following commands on the db2 server. First, move all certificates from /tmp/ssl/ to /etc/mysql/ directory:
$ sudo mv /tmp/ssl/ /etc/mysql/
You need to edit the /etc/mysql/my.cnf file, run:
$ sudo vi /etc/mysql/my.cnf
Configure the MariaDB client (add in [client] section) to use SSL:
ssl-ca=/etc/mysql/ssl/ca-cert.pem
ssl-cert=/etc/mysql/ssl/client-cert.pem
ssl-key=/etc/mysql/ssl/client-key.pem

Set bind address i.e. listen only on db2’s private IP address 192.168.2.6 (make sure you replace the IP 192.168.2.6 with your actual IP address)
bind-address = 192.168.2.6
Mark this server as a slave server. This number must be unique:
server-id = 2
Configure the MariaDB server (add in [mysqld] section) to use SSL:
ssl
ssl-ca=/etc/mysql/ssl/ca-cert.pem
ssl-cert=/etc/mysql/ssl/server-cert.pem
ssl-key=/etc/mysql/ssl/server-key.pem

Save and close the file. Restart MariaDB server, run:
$ sudo systemctl restart mysql.service
Type the following command on the slave db2 server:
$ mysql -u root -p
Type the following sql command:
mysql> CHANGE MASTER TO MASTER_HOST='192.168.2.5', MASTER_USER='db_slave_usr', MASTER_PASSWORD='secretePassword', MASTER_LOG_FILE='mysql-bin.000006', MASTER_LOG_POS=661, MASTER_SSL=1;
mysql> SLAVE START;
mysql> SHOW SLAVE STATUS\G

Now add or delete data on your master server named db1 and it will get replicated on the db2 slave server.

A note about failover

You need to modify your web app written in PHP/Perl/Python to write data only to the master db1 server. If the master db1 server is down read data from the slave db2 server.

Posted by: Vivek Gite

The author is the creator of nixCraft and a seasoned sysadmin and a trainer for the Linux operating system/Unix shell scripting. He has worked with global clients and in various industries, including IT, education, defense and space research, and the nonprofit sector. Follow him on Twitter, Facebook, Google+.

5 comment

  1. CHANGE MASTER TO MASTER_HOST=’192.168.2.5′, MASTER_USER=’db_slave_usr’, MASTER_PASSWORD=’secretePassword’, MASTER_LOG_FILE=’mysql-bin.000006′, MASTER_LOG_POS=661; MASTER_SSL=1;

    fix: …. 661, MASTER_SSL…
    ; to ,

Leave a Comment