As you can see in the screenshots, there is the connection between two servers, and no errors occur.

3. How to Check Replication Between MariaDB Servers?

Then to make sure that the replication between two MariaDB servers works in master+master, we will create a new database on Master-1 and create a table in it.

MariaDB [(none)]> create database master1;
MariaDB [(none)]> use master1;
MariaDB [master1]> CREATE TABLE hello (
-> AuthorID INT NOT NULL AUTO_INCREMENT,
-> AuthorName VARCHAR(100),
-> PRIMARY KEY(AuthorID)
-> );

Make sure that this database has automatically replicated on the second master and contains the same table:

MariaDB [(none)]> show databases;

+--------------------+
| Database |
+--------------------+
| information_schema |
| master1 |
| mysql |
| performance_schema |
+--------------------+

MariaDB [(none)]> use master1;
MariaDB [master1]> show tables;

+-------------------+
| Tables_in_master1 |
+-------------------+
| hello |
+-------------------+

The database has been created on the second master as well. To check the full cycle, create a table in the Master1 database on the second Master server and check if it appears on the first server.

MariaDB [master1]> CREATE TABLE hello_master1 (
-> AuthorID INT NOT NULL AUTO_INCREMENT,
-> AuthorName VARCHAR(100),
-> PRIMARY KEY(AuthorID)
-> );

The hello_master1 table has been replicated to the first server:

MariaDB [master1]> show tables;

+-------------------+
| Tables_in_master1 |
+-------------------+
| hello |
| hello_master1 |
+-------------------+

As you can see, the new table has appeared on Master-1. The replication works.

How to Set Up Master-Slave Replication in MariaDB?

In the master-slave MariaDB replication mode, one server acts as a slave, and data from the Master server are constantly written to it. All changes you make on the Slave server won’t be transferred to the Master server. This database replication type is more resilient and used more often. In this configuration, you always have a backup server with up-to-date database, and if a slave server fails, the Master will not lost any data. You can also distribute the database load for your project so that the apps will read data from your Slave servers and write the data only to the Master server. Thus, you make the database response as fast as possible.

When configuring the master+slave replication of MariaDB database, the master server (master1) is configured as described above.

Move on to the slave server. Add the following configuration to the slave my.cnf file:

#replication
server_id = 2
report_host = slave2
log_bin = /var/lib/mysql/mariadb-bin
log_bin_index = /var/lib/mysql/mariadb-bin.index
relay_log = /var/lib/mysql/relay-bin
relay_log_index = /var/lib/mysql/relay-bin.index

Restart the MariaDB service. Get the bin_log info from the first server:

MariaDB [(none)]> show master status;

+--------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+--------------------+----------+--------------+------------------+
| mariadb-bin.000001 | 664 | | |
+--------------------+----------+--------------+------------------+

Run these commands in the mariadb console on slave server:

MariaDB [(none)]> STOP SLAVE;

MariaDB [(none)]> CHANGE MASTER TO MASTER_HOST='IP_master', MASTER_USER='test_master', MASTER_PASSWORD='test_master', MASTER_LOG_FILE='mariadb-bin.000001', MASTER_LOG_POS=664;
MariaDB [(none)]> START SLAVE;

To check the Slave status: SHOW SLAVE STATUS\G;

To create a database on the Master:

MariaDB [(none)]> create database master_hello;

Make sure that the database has been created on the Slave server as well:

MariaDB [(none)]> show databases;

+--------------------+
| Database |
+--------------------+
| information_schema |
| master_hello |
| master_test |
| mysql |
| performance_schema |
| test |
+--------------------+

Create a database on the Slave server and check if the database has been replicated to the Master.

As you can see, we have created the database and it exists on the Slave. Let’s check if it has appeared on the Master. It has not. The data are not replicated from slave to master.

It means that MariaDB replication works in one direction only. Let’s do another check and delete the master_hello database from the Slave server:

Check if it has deleted from the Master server:

As you can see, it is OK and the database exists.

P. S.: When you configuring mariadb replication, you may come across some pitfalls, with the firewall as the most frequent of them. By default, the firewalld is installed in CentOS, which blocks the MariaDB replication port (TCP 3396). You can either open the port using iptables, or disable your firewall (not the best option).

During the initial setup, I came across this issue, but it is detected easily. If you run the Slave status check SHOW SLAVE STATUS\G, you will see this error:

You can add some parameters to the #replication block in my.cnf file. Below I will give some examples and a brief description of the useful parameters. I’ll give some examples of other functions that may be helpful when you configure the mariadb database replication.

server_id = 1 — specify the server ID, usually we start with 1, but you may use any number that will not match with those of other servers used for replication.

report_host = master — usually you specify the host name of the server, but you can enter its IP address instead.

log_bin = /var/lib/mysql/mariadb-bin — is the path to the update log (log_bin).

log_bin_index = /var/lib/mysql/mariadb-bin.index — allows you to find out, which log is currently active and which logs were used earlier.

relay_log = /var/lib/mysql/relay-bin

relay_log_index = /var/lib/mysql/relay-bin.index — are the replication logs.

If you want to configure the replica of a specific database or selected databases, use the following parameters:

replicate-do-db = dbname — if you need multiple databases, specify their names separated by commas.

To exclude some databases from replication (as usual, service databases are excluded from replication: information_schema, mysql and performance_schema):

binlog-ignore-db = dbname

To set the log history time for your bin_log:

expire_logs_days = 10 — where 10 is the number of days the logs will be stored.

Also, if the data from your Master server are written to a database with a different name, you can set it in the config file:

replicate-rewrite-db=dbmastername->dbslavename

The replication configuration is completed. I think that using this article you will be able to configure MariaDB replication both in Master + Master and Master + Slave mode without any problems.