This article explains how to run MariaDB SQL server in as master/slave replication cluster on two Ubuntu virtual machines.

master: 192.168.122.25
slave: 192.168.122.26

1. Before anything else you need to update all packages on the two machines:

sudo apt update
sudo apt upgrade

2. First thing is to add the official MariaDB repo for the stable release from here – https://downloads.mariadb.org/mariadb/repositories/
In my case, for Ubuntu 18.04 I had to use this:

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,arm64,ppc64el] http://ams2.mirrors.digitalocean.com/mariadb/repo/10.3/ubuntu bionic main'

3. Install it on both servers:

sudo apt install mariadb-server

You will have to provide password for the root user during install. Please note this is not the exisitng Ubuntu root user, but is new password the root user for mysql.

4. On both servers: sudo mysql_secure_installation
This will ask you for the root password you have set up in the previous step. You should remove anonymous users, disable remote root loginand remove test database. Basically answer yes[Y] to all if you are installing this on a machine available from the internet.

5. On both servers:

sudo systemctl enable mariadb.service
sudo systemctl start mariadb

The first command will make the mariadb server start every time the machine is re/started and the second will just the start service right now as it still not running.

6. On the master server create empty database

MariaDB [(none)]> mysql -uroot -p
MariaDB [(none)]> create database database_name;

7. On the master server we need to enable binary logging.
– Backup the original file in /etc/mysql/

cp my.cnf my.cnf.bkp

Add this new lines under the [mysqld] section, and replace the IP address with the one your master machine have.

#Replication settings
log-bin
server_id=1
bind-address=192.168.122.25
binlog-ignore-db = information_schema
binlog-ignore-db = mysql
binlog-ignore-db = performance_schema
binlog-ignore-db = test

This will replicate all new databases to the slave server, if you like to replicate just one specific database you should use

replicate-do-db = 

8. Now we need to login to the master sql server and create replication user and give the necessary grants.

MariaDB [(none)]> CREATE USER 'slave'@'localhost' IDENTIFIED BY 'SomePassword';
MariaDB [(none)]> GRANT REPLICATION SLAVE ON *.* TO slave IDENTIFIED BY 'SomePassword' WITH GRANT OPTION;
MariaDB [(none)]> FLUSH PRIVILEGES;
MariaDB [(none)]> FLUSH TABLES WITH READ LOCK;
MariaDB [(none)]> SHOW MASTER STATUS;

The last command output is important in order the slave to know from which point it should start replicating from.

Unlock the databases and exit:

MariaDB [(none)]> UNLOCK TABLES;
MariaDB [(none)]> exit;

9. Login to the slave and create another empty database with the same name and the slave user.

CREATE DATABASE DATABASE_NAME;
CREATE USER 'slave'@'localhost' IDENTIFIED BY 'SomePassword';
FLUSH PRIVILEGES;

10. Add this the to the [mysqld] section in /etc/mysql/my.cnf in the slave:

server_id=2

Note that the master have server_id=1 so you should have different IDs on the different servers.

11. Log in to the slave database and run the following commands in the MariaDB prompt. Double check the MASTER_LOG_FILE and MASTER_LOG_POS variables, which should be the same as the values returned by SHOW MASTER STATUS above.

MariaDB [(none)]> CHANGE MASTER TO
MASTER_HOST='192.168.122.25',
MASTER_USER='slave',
MASTER_PASSWORD='SomePassword',
MASTER_PORT=3306,
MASTER_LOG_FILE='mariadb-bin.000001',
MASTER_LOG_POS=314,
MASTER_CONNECT_RETRY=10,
MASTER_USE_GTID=current_pos;

Now start the slave and check the status without exiting the MariaDB prompt:

MariaDB [(none)]> START SLAVE;
MariaDB [(none)]> SHOW SLAVE STATUS\G;

12. Test the replication:
login in the master server and create table in our empty database:

CREATE TABLE IF NOT EXISTS names (
task_id INT AUTO_INCREMENT,
title VARCHAR(255) NOT NULL,
start_date DATE,
due_date DATE,
status TINYINT NOT NULL,
priority TINYINT NOT NULL,
description TEXT,
PRIMARY KEY (task_id)
) ENGINE=INNODB;

You should see the new table created on the slave server too.

13. Debug: If there is something wrong with the slave replication it should show with when you run

SHOW SLAVE STATUS\G;

Most of the time problems are easily resolved with updating the slave configuration with the

CHANGE MASTER

query, stopping and then starting the slave. Watch for log position and the log file name.