How To Configure MariaDB Replication on Ubuntu 18.04 / Debian 9

In this guide, I’ll show you how to configure MariaDB Master-Slave replication on Ubuntu 18.04 and Debian 9 server. MariaDB is a community-developed fork of the MySQL relational database management system, that has a huge community behind its development, security, and improvements.

The MariaDB replication process allows you to maintain multiple copies of MySQL data. All data in the master is synced to Slave servers in an automated process and if you have a disaster, you can easily promote Slave to a Master for commit operations. The main role of replication is to spread read and write workloads across multiple servers for easy scalability.

We have other tutorials which cover the installation and configuration of single node MariaDB server on both Ubuntu 18.04 and Debian 9:

How to Install MariaDB 10.3 on Debian 9 / Debian 8

Install MariaDB 10.3 on Ubuntu 18.04 and CentOS 7

Step 1: Install MariaDB on Ubuntu 18.04 / Debian 9

I have two nodes which will be used for setting up MariaDB Master-Slave replication. The first node will act as Master node, while the second being a Slave.

Node 1: 192.168.18.40
Node 2: 192.168.18.41

The version of MariaDB used in this demonstration is v10.3. You can replace 10.3 with the version of MariaDB you intend to install.

Install MariaDB 10.3 on Ubuntu 18.04

Use the commands below to have MariaDB 10.3 installed on Ubuntu 18.04 server.

sudo apt update
sudo apt-key adv --recv-keys --keyserver hkp://keyserver.ubuntu.com:80 0xF1656F24C74CD1D8
sudo add-apt-repository 'deb [arch=amd64] http://mirror.zol.co.zw/mariadb/repo/10.3/ubuntu bionic main'
sudo apt update
sudo apt install mariadb-server mariadb-client

Install MariaDB 10.3 on Debian 9

Add MariaDB 10.3 repository and install mariadb-server package.

sudo apt-key adv --recv-keys --keyserver keyserver.ubuntu.com 0xF1656F24C74CD1D8
sudo add-apt-repository 'deb [arch=amd64,i386,ppc64el] http://mirror.zol.co.zw/mariadb/repo/10.3/debian stretch main'
sudo apt-get update && sudo apt-get install mariadb-server

Set root password when prompted

While not mandatory, it is highly recommended that you set a password for 
the MariaDB administrative "root" user.

If this field is left blank, the password will not be changed.

New password for the MariaDB "root" user:
Repeat password for the MariaDB "root" user:

Step 2: Configure MariaDB  Master Server

Once MariaDB is installed on both servers, login to Node 1 (Master node) via ssh and change Listening address to the actual IP address of the server. Edit the file /etc/mysql/my.cnf and add the following line under mysqld section.

#bind-address            = 127.0.0.1
bind-address             = 192.168.18.40

Set the server ID which will be a unique identifier of the master server.

server-id = 100

Create  a database replication user

$ mysql -u root -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 50
Server version: 10.3.9-MariaDB-1:10.3.9+maria~bionic-log mariadb.org binary distribution

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

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

MariaDB [(none)]> grant replication slave on *.* to mysql_replica@'%' identified by 'StrongPassword';
Query OK, 0 rows affected (0.001 sec)

MariaDB [(none)]> flush privileges; 
Query OK, 0 rows affected (0.001 sec)

MariaDB [(none)]> exit
Bye

Restart the MariaDB server for changes to take effect.

sudo systemctl restart mysql

Check status using ss or netstat command.

# ss -tunelp | grep 3306
tcp   LISTEN  0       70               192.168.18.40:3306         0.0.0.0:*      users:(("mysqld",pid=16877,fd=22)) uid:111 ino:48116 sk:4 <->

If you have a firewall running, open port 3306

sudo ufw allow 3306

Step 3: Configure MariaDB  Slave Server

Login to the slave server/servers and configure MariaDB:

$ sudo vim /etc/mysql/my.cnf

Set below values under [mysqld] section.

[mysqld]
bind-address = 192.168.18.41
server-id = 101
log_bin = /var/log/mysql/mariadb-bin
read_only = 1
report-host = mariadb-slave1
expire-logs-days = 7

read_only = 1: This sets the slave to read-only mode. Only users with the SUPER privilege and the replication slave thread will be able to modify data on it. This ensures there are no applications that can accidentally modify data on the slave instead of master.

server-id = 101: This is a Unique server identification number. It will default to 1 if “master-host” is not set.

log_bin = /var/log/mysql/mariadb-bin:  This enables binary logging. This is required for acting as a MASTER in a replication configuration. You also need the binary log if you need the ability to do point in time recovery from your latest backup.

Restart mariadb after the change.

sudo systemctl restart mysql

Step 4: Initialize Replication process

We should be ready to start Replication process on the slave server. Start by checking Status on the master:

MariaDB [(none)]> show master status\G
*************************** 1. row ***************************
            File: mariadb-bin.000003
        Position: 344
    Binlog_Do_DB: 
Binlog_Ignore_DB: 
1 row in set (0.000 sec)

Take a note of current Master log file and position. Then configure Slave server with details obtained from the master status command.

Login to MariaDB Slave server as root user and configure connection to the Master server

$ mysql -u root -p

CHANGE MASTER TO MASTER_HOST='192.168.18.40',
MASTER_USER='mysql_replica',
MASTER_PASSWORD='StrongPassword',
MASTER_LOG_FILE='mariadb-bin.000003',
MASTER_LOG_POS=344;

Then start replication on the slave:

mysql> start slave;
Query OK, 0 rows affected (0.002 sec)

To check slave status, use:

MariaDB [(none)]> show slave status\G
*************************** 1. row ***************************
                Slave_IO_State: Waiting for master to send event
                   Master_Host: 192.168.18.40
                   Master_User: mysql_replica
                   Master_Port: 3306
                 Connect_Retry: 60
               Master_Log_File: mariadb-bin.000003
           Read_Master_Log_Pos: 344
                Relay_Log_File: mysqld-relay-bin.000002
                 Relay_Log_Pos: 557
         Relay_Master_Log_File: mariadb-bin.000003
              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: 344
               Relay_Log_Space: 867
               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: 100
                Master_SSL_Crl: 
            Master_SSL_Crlpath: 
                    Using_Gtid: No
                   Gtid_IO_Pos: 
       Replicate_Do_Domain_Ids: 
   Replicate_Ignore_Domain_Ids: 
                 Parallel_Mode: conservative
                     SQL_Delay: 0
           SQL_Remaining_Delay: NULL
       Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
              Slave_DDL_Groups: 0
Slave_Non_Transactional_Groups: 0
    Slave_Transactional_Groups: 0
1 row in set (0.001 sec)

Slave IO and SQL should indicate running state:

Slave_IO_Running: Yes
Slave_SQL_Running: Yes

Check of process list on the master should also display connections from slave servers.

MariaDB [(none)]> select ID,user,host,db,command,time,state from information_schema.processlist order by time desc limit 5;
+----+---------------+---------------------+------+-------------+------+------------------------------------------------------------------+
| ID | user          | host                | db   | command     | time | state                                                            |
+----+---------------+---------------------+------+-------------+------+------------------------------------------------------------------+
| 38 | mysql_replica | 192.168.18.41:51522 | NULL | Binlog Dump |  988 | Master has sent all binlog to slave; waiting for binlog to be up |
|  2 | system user   |                     | NULL | Daemon      |    0 | InnoDB purge worker                                              |
|  5 | system user   |                     | NULL | Daemon      |    0 | InnoDB shutdown handler                                          |
|  1 | system user   |                     | NULL | Daemon      |    0 | InnoDB purge coordinator                                         |
|  4 | system user   |                     | NULL | Daemon      |    0 | InnoDB purge worker                                              |
+----+---------------+---------------------+------+-------------+------+------------------------------------------------------------------+
5 rows in set (0.000 sec)

 If you’re a MySQL user, check How to Configure MySQL 8.0 Master Slave Replication on Ubuntu 18.04