Learn how to – Configure MariaDB Master-Master replication on Ubuntu 20.04|18.04

Learn how to – Configure MariaDB Master-Master replication on Ubuntu 20.04|18.04.

MySQL Replication is the process whereby a single data set stored in a given MyQSL database server, known as the source will be live-copied to another server known as the replica. Replication is asynchronous by default, although, there are other methods of synchronization supported such as; one-way asynchronous replication, semi-synchronous replication, delayed replication e.t.c. It is possible to replicate all databases and tables within a database depending on your configuration.

Replication in MySQL is advantageous in the following ways:

• It offers data security – it is possible to run a backup on the replica without corrupting the corresponding source data
• Long-distance data distribution – It makes it possible to create a local copy of data on a remote server/site
• Analytics – live data can be stored on the source and analytic information obtained from the replica’s database.
• Offers scale-out solutions – the load is spread among multiple replicas, this, in turn, improves performance.

There are two types of replication setups:

• Master-slave replication – In this setup, the data stored in the master will be live-copied to the slave
• Master-master replication – Here, data can be copied from either server to the other one. This setup adds redundancy and increases efficiency, especially when dealing with accessing the data
• Multi-source replication.
• Star replication.

In this guide, We will walk through how to configure MariaDB Master-Master replication on Ubuntu 20.04|18.04. MariaDB is preferred here, due to the following amazing features:

• PHP, one of the most popular web development languages, is supported by MariaDB.
• MariaDB makes advantage of a well-known and widely used querying language.
• MariaDB supports a wide range of programming languages and operates on a variety of operating systems.
• It provides a variety of storage engines, including high-performance storage engines such as XtraDB and InnoDB
• Supports Galera cluster technology – a multi-master cluster based on synchronous replication
• It has a sequence storage engine that allows one to create ascending or descending sequences of numbers.

Getting Started

In this guide, we will have two Ubuntu 20.04|18.04 servers, both serving as masters in the replication.

Set the hostnames of the servers.

##On Master Node 1
sudo hostnamectl set-hostname master1.ownyourlife.com.ng --static

##On Master Node 2
sudo hostnamectl set-hostname master2.ownyourlife.com.ng --static

Then proceed and add the servers to the list of known hosts(on both servers)

$ sudo vim /etc/hosts
192.168.100.50  master1.ownyourlife.com.ng master1
192.168.100.51  master2.ownyourlife.com.ng master2

From Master Node 1, try ping Master Node 2 as below.

$ ping master2
PING master2.ownyourlife.com.ng (192.168.100.51) 56(84) bytes of data.
64 bytes from master2.ownyourlife.com.ng (192.168.100.51): icmp_seq=1 ttl=64 time=0.568 ms
64 bytes from master2.ownyourlife.com.ng (192.168.100.51): icmp_seq=2 ttl=64 time=0.470 ms

Now update all the servers to the latest stable packages.

sudo apt update && sudo apt upgrade

Step 1 – Configure Chrony NTP synchronization

The time between the two MariaDB master nodes must be synchronized. Install Chrony NTP packages on the servers.

sudo apt -y install chrony

Once installed, start and enable Chrony

sudo systemctl enable --now chronyd

Set your local TimeZone and configure Chrony NTP

sudo timedatectl set-timezone Africa/Nairobi --adjust-system-clock
sudo timedatectl set-ntp yes

Verify if the time has been synchronized.

$ timedatectl 
               Local time: Wed 2022-05-04 12:53:37 EAT
           Universal time: Wed 2022-05-04 09:53:37 UTC
                 RTC time: Wed 2022-05-04 09:53:38    
                Time zone: Africa/Nairobi (EAT, +0300)
System clock synchronized: yes                        
              NTP service: active                     
          RTC in local TZ: no                         

Step 2 – Install MariaDB on both servers

Now we will ensure that both servers have MariaDB installed. First, install the required packages.

sudo apt install curl software-properties-common -y

In this guide, we will install MariaDB on both Ubuntu 20.04|18.04 servers from the default repositories

sudo apt update
sudo apt install mariadb-server mariadb-client

With MariaDB installed successfully Start and enable it.

sudo systemctl enable --now mariadb.service

Verify the installation by logging in to the MariaDB shell as below. Provide the default password: root

$ sudo mysql -u root -p
Enter password: root
Welcome to the MariaDB monitor.  Commands end with ; or g.
Your MariaDB connection id is 43
Server version: 10.3.34-MariaDB-0ubuntu0.20.04.1-log Ubuntu 20.04

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)]> exit

sudo ufw allow 3306/tcp
sudo ufw allow 4567/tcp
sudo ufw allow 4568/tcp
sudo ufw allow 4444/tcp

Step 3 – Configure MariaDB for Master-Master Replication

Now we need to edit our master nodes to enable replication.

On Master Node 1.

On master node 1, we will edit the config file below.

sudo vi /etc/mysql/mariadb.conf.d/50-server.cnf

At the end of the file, add the below information.

[mysqld]
............
bind-address           = 192.168.100.50
......
#       other settings you may need to change.
server-id              = 1
log_bin                = /var/log/mysql/mysql-bin.log
expire_logs_days        = 10
max_binlog_size        = 100M
binlog_do_db           = replica
#binlog_ignore_db       = exclude_database_name

Replace the bind address with the IP_address of your Master node 1 and replica, with the database to replicate. Save the file and restart MariaDB.

sudo systemctl restart mariadb

Now login to the MariaDB instance.

sudo mysql -u root -p

Create a master user and the required grants.

CREATE USER 'master_user1'@'%' IDENTIFIED BY 'Passw0rd';
GRANT REPLICATION SLAVE ON *.* TO 'master_user1'@'%';
FLUSH PRIVILEGES;

Remember to replace master_user with your desired username and Passw0rd with your preferred password for the user.

Check the status to show the current binary log and the exact location where the master should start replication from.

SHOW MASTER STATUS;

Sample Output:

MariaDB [(none)]> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 |      782 | replica      |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.001 sec)

Remember, the position of the file is important as it will be required later in this configuration.

On Master Node 2.

Now on the other node, Master Node 2, we will do a similar configuration as above. Edit the config file as below.

sudo vim /etc/mysql/mariadb.conf.d/50-server.cnf

In the file, add the below lines replacing them appropriately.

[mysqld]
............
bind-address           =192.168.100.51
.......
#       other settings you may need to change.
server-id              = 2
log_bin                = /var/log/mysql/mysql-bin.log
expire_logs_days        = 10
max_binlog_size        = 100M
binlog_do_db           = replica
#binlog_ignore_db       = exclude_database_name

Replace the bind address with the IP_address of Master Node 2. Save the file and restart MariaDB.

sudo systemctl restart mariadb

Log into the MariaDB shell.

sudo mysql -u root -p

Also here, create the master user with the necessary privileges.

CREATE USER 'master_user1'@'%' IDENTIFIED BY 'Passw0rd';
GRANT REPLICATION SLAVE ON *.* TO 'master_user1'@'%';
FLUSH PRIVILEGES;

Now in the next step, we need to add the information from Master Node 1 to our Master node 2. First, stop the slave.

STOP SLAVE;

Now proceed and add the Master Node 1 information.

CHANGE MASTER TO MASTER_HOST = "master1", MASTER_USER = "master_user1", MASTER_PASSWORD = "Passw0rd", MASTER_PORT=3306, MASTER_LOG_FILE = "mysql-bin.000001", MASTER_LOG_POS = 782, MASTER_CONNECT_RETRY=10;

The details provided here are details for the Master Node 1, Replace mysql-bin.000001 with the path of the file and 782 with the position of the file on Master Node 1.

Now start the slave.

START SLAVE;

Show the Master status and also take note of the path and position of the file.

SHOW MASTER STATUS;

Sample Output:

MariaDB [(none)]> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 |      782 | replica      |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.001 sec)

Complete the Master-Master Replication setup on Master Node 1.

Now we need to complete the setup by adding the Master Node 2 information to our Master node 1.

While logged in to the MariaDB shell on Master node 1.

Stop the replication.

STOP SLAVE;

Now on Master node 1 add the information for Master Node 2.

CHANGE MASTER TO MASTER_HOST = "master2", MASTER_USER = "master_user1", MASTER_PASSWORD = "Passw0rd", MASTER_PORT=3306, MASTER_LOG_FILE = "mysql-bin.000001", MASTER_LOG_POS = 782, MASTER_CONNECT_RETRY=10;

Replace the provided details appropriately and proceed as below.

START SLAVE;

Verify the slave status on any node.

MariaDB [(none)]> show slave statusG

Sample Output:

On both nodes, the service should be waiting for the other to send an event as shown above.

Step 4 – Test the MariaDB Master-Master replication

Now that we have successfully completed all the required configurations, we need to test if the setup is working correctly.

On Master node 2:

On the MariaDB shell on Master Node 2, create the replication database(replica) and a new table in it.

create database replica;
CREATE TABLE replica.test (`name` varchar(18));

On Master node 1:

Show the database and table in the replica database

SHOW TABLES IN replica;

Sample Output:

With the above output, it is safe to assume that our MariaDB master-master replication is working perfectly.

Conclusion

This is the end! We have configured MariaDB Master-Master replication on Ubuntu 20.04|18.04. You can use MariaDB replication to solve innumerable problems from performance, supporting backup of databases to alleviating system failures.

Cheers!

See more on this page:

• Setup OpenLDAP Multi-Master Replication on CentOS 8
• Configure OpenLDAP Master-Slave Replication on CentOS 8
• Configure MongoDB Replication On Ubuntu