Database replication with mariadb on CentOS 7 linux

August 27, 2019October 17, 2019

In this tutorial, we are going to show you how to setup an active-active three-nodes database replication with mariadb on centos 7 linux.
active-active database replication means that two servers exchange update information bidirectional. also arbitrator is a server that will refree between two masters when there is a problem between them about who has lastest data.
in addition we setup an encrypted connection between three servers.

for implementing this scenario we need 3 cenots 7 linux servers with the following specification:

OS: Centos7 Linux

Mariadb ver: 10.3

Name: node 1
Role: MariaDB Galera Cluster as master
IP address: 192.168.1.1

Name: node2
Role: MariaDB Galera Cluster as master
IP address: 192.168.1.2

Name: node3
Role: Galera garb as arbitrator
IP address: 192.168.1.3

1-Install MariaDB on node 1 and 2:

to install mariadb on Centos 7 Linux, we need to add its repository. so run the following command on node 1 and 2:

# vi /etc/yum.repos.d/mariadb.repo

then put these content in it:

[mariadb]
name = MariaDB
baseurl = http://yum.mariadb.org/10.3.12/centos7-amd64/
gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
gpgcheck=1

save file and issue the following command:

# yum install mariadb-server mariadb-client galera rsync

after installing above packages, start mariadb server

# systemctl start mariadb

# systemctl enable mariadb

then do initial setup of mariadb:

# mysql_secure_installation

2- Initial database servers setup

now it’s time to create a user for our cluster. therefore login to mariadb with root user and password:

# mysql -u root -p

then create desired user:

# CREATE USER 'Your username'@'localhost' IDENTIFIED BY 'You Password';

change ‘Your username’ and ‘Your Password’ with the one you choose by yourself.
your should grant all privileges to the user that has been created:

GRANT ALL PRIVILEGES ON *.* TO ' Your username'@'localhost' REQUIRE ssl;

then flush privileges:

# FLUSH PRIVILEGES;

now stop mariadb:

# systemctl stop mariadb

then edit /etc/my.cnf.d/server.cnf file in node 1 and add these lines:

# vi /etc/my.cnf.d/server.cnf 
[galera]
wsrep_on=ON
wsrep_provider=/usr/lib64/galera/libgalera_smm.so
wsrep_cluster_address="gcomm://192.168.1.1,192.168.1.2,192.168.1.3"
binlog_format=row
default_storage_engine=InnoDB
innodb_autoinc_lock_mode=2
wsrep_cluster_name="mycluster"
bind-address=0.0.0.0
wsrep_node_address="192.168.1.1"
wsrep_node_name="node1"
wsrep_sst_method=rsync
wsrep_sst_auth="Your Username:Your Password"
query_cache_size=10M
innodb_doublewrite=1
innodb_autoinc_lock_mode=2
innodb_flush_log_at_trx_commit=0
innodb_buffer_pool_size=2560M
innodb_rollback_on_timeout=1
innodb_lock_wait_timeout=600
max_connections=1000

Do the same thing on node 2 , but edit these lines and set it properly:

wsrep_node_address="192.168.1.2"
wsrep_node_name="node2"

3- Setup firewall rules

here i use firewall-cmd to define required rules in firewall. but if you use CSF or iptables, you should open specific port as shown below:
Ports: 3306(tcp), 4567(tcp + udp), 4568(tcp) ,4444(tcp).

also you can refer to this article how to install csf on centos 7 linux.

# firewall-cmd --zone=public --add-port=3306/tcp --permanent
# firewall-cmd --zone=public --add-port=4567/tcp --permanent
# firewall-cmd --zone=public --add-port=4567/udp --permanent
# firewall-cmd --zone=public --add-port=4568/tcp --permanent
# firewall-cmd --zone=public --add-port=4444/tcp --permanent

4- Start cluster

now we want to start cluster to check if our initial configuration works properly or not. for this, we should start one node as bootstrap node and another as normal node. here we start node 1 as bootstrap with the following command:

# galera_new_cluster

on node 2 run the following command:

# systemctl start mariadb

then check if service started successfully on both nodes:

# systemctl status mariadb

now we want to check cluster size. so login to mariadb on node 1 and run the following command:

# SHOW STATUS LIKE 'wsrep_cluster_size';

you should see 2 and it confirms that cluster works properly.

5- Test replication

to check replication, we create a test database on node 1 and then check if it has been created on node 2. therefore run the following command:

# mysql -u root -p -e 'CREATE DATABASE clustertest;'
# mysql -u root -p -e 'CREATE TABLE clustertest.mycluster ( id INT NOT NULL AUTO_INCREMENT, name VARCHAR(50), ipaddress VARCHAR(20), PRIMARY KEY(id));' 
# mysql -u root -p -e 'INSERT INTO clustertest.mycluster (name, ipaddress) VALUES ("db1", "1.1.1.1");'

now we check database on node 2:

# mysql -u root -p -e 'SELECT * FROM clustertest.mycluster;'

and the following output should be appeared:

+----+------+-----------+
| id | name | ipaddress |
+----+------+-----------+
| 2  | db1  | 1.1.1.1   |
+----+------+-----------+

it confirms that replication works properly.

6- Setup ssl connection

now we want to encrypt connections in cluster. first we check ssl connection status. on node 1 run the following command in mariadb:

# SHOW VARIABLES LIKE 'have_ssl';

the output will be:

+---------------+----------+
| Variable_name | Value    |
+---------------+----------+
| have_ssl      | DISABLED |  ###==> SSL Disabled
+---------------+----------+
1 row in set (0.01 sec)

To fully secure all cluster communication we must encrypt replication traffic within Galera Cluster, State Snapshot Transfer and traffic between database server and client. We will create SSL Certificates and Keys using openssl.
on node 1, create new folder for certificates:

# mkdir -p /etc/mysql/ssl
# cd /etc/mysql/ssl

Generate CA key:

# openssl genrsa 2048 > ca-key.pem

Using the CA key, generate the CA certificate:

# openssl req -new -x509 -nodes -days 3600 -key ca-key.pem -out ca-cert.pem

you can use the following input or use your own:

Country Name (2 letter code) [AU]:HR
State or Province Name (full name) [Some-State]:Zagreb
Locality Name (eg, city) []:Zagreb
Organization Name (eg, company) [Internet Widgits Pty Ltd]:Dummycorp
Organizational Unit Name (eg, section) []:IT
Common Name (e.g. server FQDN or YOUR name) []:myu1.localdomain
Email Address []:[email protected]

Create the server key:

# openssl req -newkey rsa:2048 -days 3600 -nodes -keyout server-key.pem -out server-req.pem

again you can use the following or your own:

Country Name (2 letter code) [AU]:HR
State or Province Name (full name) [Some-State]:Zagreb
Locality Name (eg, city) []:Zagreb
Organization Name (eg, company) [Internet Widgits Pty Ltd]:Dummycorp
Organizational Unit Name (eg, section) []:IT

Common Name (e.g. server FQDN or YOUR name) []:node1.localdomain
Email Address []: [email protected] 
Please enter the following 'extra' attributes to be sent with your certificate request
A challenge password []:
An optional company name []:

Process the server RSA key:

# openssl rsa -in server-key.pem -out server-key.pem

Sign the server certificate:

# openssl x509 -req -in server-req.pem -days 3600 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 -out server-cert.pem

Create the client key:

# openssl req -newkey rsa:2048 -days 3600 -nodes -keyout client-key.pem -out client-req.pem

again you can use the following or your own:

Country Name (2 letter code) [AU]:HR
State or Province Name (full name) [Some-State]:Zagreb
Locality Name (eg, city) []:Zagreb
Organization Name (eg, company) [Internet Widgits Pty Ltd]:Dummycorp
Organizational Unit Name (eg, section) []:IT

important note: dont use the “.localdomain” here:

Common Name (e.g. server FQDN or YOUR name) []:node2
Email Address []:[email protected]
Please enter the following 'extra' attributes to be sent with your certificate request
A challenge password []:
An optional company name []:

Process client RSA key:

# openssl rsa -in client-key.pem -out client-key.pem

Sign the client certificate:

# openssl x509 -req -in client-req.pem -days 3600 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 -out client-cert.pem

Verify certificates:

# openssl verify -CAfile ca-cert.pem server-cert.pem client-cert.pem

the output should be:

server-cert.pem: OK
client-cert.pem: OK

copy these certificates to node 2 and 3:

# scp -r /etc/mysql/ssl [email protected]:/etc/mysql
# scp -r /etc/mysql/ssl [email protected]:/etc/mysql

Change owner of certificates to mysql on all servers:
node1:

# chown -R mysql:mysql /etc/mysql/ssl

node2:

# chown -R mysql:mysql /etc/mysql/ssl

node3:

# chown -R mysql:mysql /etc/mysql/ssl

Add following lines in /etc/my.cnf configuration file on node 1 and 2:

[mysqld]
ssl-ca=/etc/mysql/ssl/ca-cert.pem
ssl-cert=/etc/mysql/ssl/server-cert.pem
ssl-key=/etc/mysql/ssl/server-key.pem
[client]
ssl-ca=/etc/mysql/ssl/ca-cert.pem
ssl-cert=/etc/mysql/ssl/client-cert.pem
ssl-key=/etc/mysql/ssl/client-key.pem

Define paths to the key, certificate and certificate authority files. Galera Cluster will use this files for encrypting and decrypting replication traffic.
put this in /etc/my.cnf.d/server.cnf on node 1 and 2:

wsrep_provider_options="socket.ssl_key=/etc/mysql/ssl/server-key.pem;socket.ssl_cert=/etc/mysql/ssl/server-cert.pem;socket.ssl_ca=/etc/mysql/ssl/ca-cert.pem;socket.checksum=2;socket.ssl_cipher=AES128-SHA256"

Now we must recreate whole cluster. so on node 2 stop mariadb service:

# systemctl stop mariadb

then stop mariadb on node 1:

# systemctl stop mariadb

pay attention to order of stopping mariadb.
then start cluster again. on node 1 run:

# galera_new_cluster

and on node 2 run:

# systemctl start mariadb

then check if ssl is in use. on mariadb run:

# SHOW VARIABLES LIKE 'have_ssl';

the output should be:

+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| have_ssl      | YES   |
+---------------+-------+
1 row in set (0.01 sec)

7- Configure arbitrator

install galera from mariadb repo. first copy repo file from node 1 to node 3:

# scp /etc/yum.repos.d/mariadb.repo [email protected]:/etc/yum.repos.d/

then issue the following command on node 3:

# yum install galera
# touch /var/log/garbd.log
# chown -R nobody:nobody /var/log/garbd.log

then put the following config on /etc/sysconfig/garb:

GALERA_NODES="192.168.1.1,192.168.1.2"
GALERA_GROUP="mycluster"
GALERA_OPTIONS="socket.ssl_key=/etc/mysql/ssl/server-key.pem;socket.ssl_cert=/etc/mysql/ssl/server-cert.pem;socket.ssl_ca=/etc/mysql/ssl/ca-cert.pem;socket.checksum=2;socket.ssl_cipher=AES128-SHA256"
LOG_FILE="/var/log/garbd.log"

then start service:

# systemctl start garb
# systemctl enable garb

then check cluster size on node 1:

# SHOW GLOBAL STATUS LIKE 'wsrep_cluster_size';

the output should be 3.

Great. now cluster works effectively and securely.

Note:

for shutting down cluster, if you improperly do it, the cluster will not run again until you specify node 1 or node 2 as bootstrap node. therefore to specify bootstrap node you should edit the following file on your desired node:

/var/lib/mysql/grastate.dat

and set safe_to_bootstrap to 1.

safe_to_bootstrap: 1

then start galera cluster as mentioned above.