speedtech-website/content/en/blog/mariadb-replication-master-master-internet.md

+++
images = ['images/blog/mariadb-replication-master-master.jpg']
categories = ['Solutions', 'Technology']
tags = ['Server', 'Database', 'Firewall', 'MariaDB', 'OpenSSL', 'UFW']
date = '2022-07-19'
lastmod = '2022-12-11'
title = 'MariaDB replication master-master via Internet'
published = true
translationKey ='mariadb-replication-master-master-internet'
slug = 'mariadb-replicazione-master-master-tramite-internet'
+++

[MariaDB](https://mariadb.org) (and MySQL) allow master-slave replication to be configured between two servers.
A master-master type of replication can be implemented via [Galera](https://galeracluster.com/), 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.
<!--more-->

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](https://help.ubuntu.com/community/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```
```bash
ufw allow from server1-ip to server2-ip 3306 comment "Allow replication from server1"
```

On ```server1```
```bash
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](https://hohnstaedt.de/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```

```sql
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```

```sql
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 :

```bash
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 :

```bash
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 :
```sql
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``` :

```sql
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 :

```sql
SHOW SLAVE STATUS\G;
```

##### 8. Configure ```server2``` as ```server1``` slave

On ```server1``` execute the command :
```sql
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``` :

```sql
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 :

```sql
SHOW SLAVE STATUS\G;
```