MariaDB (and MySQL) allow master-slave replication to be configured between two servers. A master-master type of replication can be implemented via Galera, but it requires at least 3 servers. If you have 2 servers, you can configure master-master replication by by cross-duplicating the master-slave replication.

This functionality can also be implemented through servers connected to each other via the Internet, by encrypting MariaDB’s native connection via SSL certificates and controlling access to the service via software firewalls (ufw for example).

Assuming then that we have 2 servers server1 and server2 the procedures to follow are as follows.

1. Setting up firewall rules

In order for the two servers to communicate with each other, it is necessary to allow communication on the TCP port of MariaDB (default 3306)

On server2

ufw allow from server1-ip to server2-ip 3306 comment "Allow replication from server1"

On server1

ufw allow from server2-ip to server1-ip 3306 comment "Allow replication from server2"

2. SSL certificates generation

To secure the connection between the two servers, one can take advantage of the SSL connectivity already present in MariaDB by by generating your own CA (Certification Authority) and respective certificates for the two servers.

XCA is an excellent visual software for generating and maintaining SSL certificates.

An alternative to a direct, encrypted connection via SSL is to create a VPN linking the two servers.

3. Data servers alignment

In case the two servers already contain data, it will be necessary to proceed with an alignment beforehand by exporting and importing the affected databases, so that both servers contain the same databases, tables, and records.

4. Create the dedicated users on each server to enable replication

On server1 create the user for the connection from server2 and give it permissions for replication to server1

CREATE USER 'server2'@'server2-ip' IDENTIFIED BY 'server2pwd';
GRANT REPLICATION SLAVE ON *.* TO 'server2'@'server2-ip';
FLUSH PRIVILEGES;

On server2 create the user for the connection from server1 and give it permissions for replication to server2

CREATE USER 'server1'@'server1-ip' IDENTIFIED BY 'server1pwd';
GRANT REPLICATION SLAVE ON *.* TO 'server1'@'server1-ip';
FLUSH PRIVILEGES;

5. Configure server1

In file server.conf :

key_buffer_size        = 64M
max_allowed_packet     = 64M

server-id   = 1
report_host = server1
log_bin                = /var/log/mysql/mysql-bin.log
expire_logs_days        = 10

relay_log       = /var/lib/mysql/relay-bin
relay_log_index = /var/lib/mysql/relay-bin.index

log-slave-updates
auto_increment_increment=1
auto_increment_offset=1
slave-skip-errors=1062,1032,1007,1008,1050,1396
skip-slave-start=FALSE

6. Configure server2

In file server.conf :

key_buffer_size        = 64M
max_allowed_packet     = 64M

server-id   = 2
report_host = server2
log_bin                = /var/log/mysql/mysql-bin.log
expire_logs_days        = 10

relay_log       = /var/lib/mysql/relay-bin
relay_log_index = /var/lib/mysql/relay-bin.index

log-slave-updates
auto_increment_increment=2
auto_increment_offset=2
slave-skip-errors=1062,1032,1007,1008,1050,1396
skip-slave-start=FALSE

7. Configure server1 as server2 slave

On server2 execute the command :

SHOW MASTER STATUS \G;

and take note of the values : File and Position which are to be replaced in the variables MASTER_LOG_FILE and MASTER_LOG_POS respectively in the following code to be executed on server1 :

CHANGE MASTER TO
  MASTER_HOST='server2-ip',
  MASTER_USER='server1',
  MASTER_PASSWORD='server1pwd',
  MASTER_PORT=3306,
  MASTER_LOG_FILE='mysql-bin.000004',
  MASTER_LOG_POS=17680369,
  MASTER_CONNECT_RETRY=10,
  MASTER_SSL=1,
  MASTER_SSL_CA='/etc/mysql/ssl/ca.crt',
  MASTER_SSL_CERT='/etc/mysql/ssl/server1.crt',
  MASTER_SSL_KEY='/etc/mysql/ssl/server1.key';

START SLAVE;

Check the status of slave replication with the command :

SHOW SLAVE STATUS\G;

8. Configure server2 as server1 slave

On server1 execute the command :

SHOW MASTER STATUS \G;

and take note of the values : File and Position which are to be replaced in the variables MASTER_LOG_FILE and MASTER_LOG_POS respectively in the following code to be executed on server2 :

CHANGE MASTER TO
  MASTER_HOST='server1-ip',
  MASTER_USER='server2',
  MASTER_PASSWORD='server2pwd',
  MASTER_PORT=3306,
  MASTER_LOG_FILE='mysql-bin.000051',
  MASTER_LOG_POS=825490817,
  MASTER_CONNECT_RETRY=10,
  MASTER_SSL=1,
  MASTER_SSL_CA='/etc/mysql/ssl/ca.crt',
  MASTER_SSL_CERT='/etc/mysql/ssl/server2.crt',
  MASTER_SSL_KEY='/etc/mysql/ssl/server2.key';

START SLAVE;

Check the status of slave replication with the command :

SHOW SLAVE STATUS\G;