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

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.

ADVERTISEMENTS

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 user@db2 mkdir /tmp/ssl/
$ cd /etc/mysql/ssl/
$ scp * user@db2:/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.

🐧 Get the latest tutorials on SysAdmin, Linux/Unix, Open Source/DevOps topics:
CategoryList of Unix and Linux commands
File Managementcat
FirewallAlpine Awall CentOS 8 OpenSUSE RHEL 8 Ubuntu 16.04 Ubuntu 18.04 Ubuntu 20.04
Network Utilitiesdig host ip nmap
OpenVPNCentOS 7 CentOS 8 Debian 10 Debian 8/9 Ubuntu 18.04 Ubuntu 20.04
Package Managerapk apt
Processes Managementbg chroot cron disown fg jobs killall kill pidof pstree pwdx time
Searchinggrep whereis which
User Informationgroups id lastcomm last lid/libuser-lid logname members users whoami who w
WireGuard VPNCentOS 8 Debian 10 Firewall Ubuntu 20.04

ADVERTISEMENTS
8 comments… add one
  • Guntbert Reiter Apr 15, 2017 @ 20:16

    Thank you for the detailed howto!
    Just one small correction: In step 3 the comments in the code all should read common instead of command

  • ptrj May 20, 2017 @ 17:03

    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 ,

  • ptrj May 20, 2017 @ 17:06

    And mysql> SLAVE START;
    for me works mysql> start slave;

    • 🐧 Vivek Gite Jul 26, 2017 @ 11:41

      thanks the post has been updated

      • wr Sep 29, 2017 @ 13:07

        It’s still “SLAVE START” for me …

  • wr Sep 28, 2017 @ 15:14

    Hi,

    thanks for the great summary. Two questions:

    1. Is it possible to use host names instead of IP addresses in the mariadb configuration, like this?

    bind-address = db2

    2. Is there any reason why the names in the certificate have to be exactly as you specified (“MariaDB admin”, “MariaDB server”, and “MariaDB client”)? Could I use different names, provided I use them consistently?

    • 🐧 Vivek Gite Sep 28, 2017 @ 17:09

      1) Yes. Provided that dns or /etc/hosts configured.

      2) You can use anything you want. This is just example to avoid confusion.

Leave a Reply

Your email address will not be published.

Use HTML <pre>...</pre>, <code>...</code> and <kbd>...</kbd> for code samples.