179 lines
5.5 KiB
Markdown
179 lines
5.5 KiB
Markdown
+++
|
|
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;
|
|
```
|