Mariadb master slave replication

September 24, 2017 System

In this article I will explain how to set up mariadb database in master slave configuration in order to have a system ready in every time to go live or to use for backup purposes.

It’s suggested to run the slave database in read mode in order to avoid misalignment with the master. This means that the solution described cannot be used for high availability, but for implementing a disaster recovery site.

For having a high availability service we should use a database cluster. Please read this my article for that: https://www.securityandit.com/system/understanding-cluster-application/.

I will start with the master configuration in my laboratory implemented by amazon aws.

Mariadb installation

I will install mariadb database on Centos 7 with selinux enabled. For installing the last version, it’s necessary to import the official repository on both nodes called mariadb-master and mariadb-slave.

[root@mariadb-master~]# vi /etc/yum.repos.d/MariaDB.repo
[mariadb]
name = MariaDB
baseurl = http://yum.mariadb.org/10.1/centos7-amd64
gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
gpgcheck=1
[root@mariadb-master~]# yum install MariaDB

Before configuring and starting mariadb, the selinux policy must be configured in order to permit to mysqld_t domain to read/write the database directory (/data/mysql) belonging to mysqld_db_t type. On both nodes (this is to avoid if you leave as database directory the default /var/lib/mysql):

[root@mariadb-master~]# yum -y install policycoreutils-python
[root@mariadb-master~]# semanage fcontext -a -t mysqld_db_t “/data/mysql(/.*)?”
[root@mariadb-master~]# grep -i mysql /etc/selinux/targeted/contexts/files/file_contexts.local
/data/mysql(/.*)? system_u:object_r:mysqld_db_t:s0
[root@mariadb-master~]# restorecon -R -v /data/mysql/
[root@ant-ftp-batch01 DATA]# ls -ltrZ
drwxr-xr-x. mysql mysql unconfined_u:object_r:mysqld_db_t:s0 mysql

On both nodes I suggest to execute the following script for better configuration security:

[root@mariadb-master~]# mysql_secure_installation

The next step, before configuring master and slave, is to create the certifications on both nodes for mutual authentication.

On one only node (master or slave) one certification autority must be created. A self signed is enough:

[root@mariadb-master~]# openssl genrsa 2048 > ca-key.pem
[root@mariadb-master~]# openssl req -new -x509 -nodes -days 1000 -key ca-key.pem > ca-cert.pem

The ca-cert.pem is the certification autority self signed that must be copied in both servers. I will copy it in /etc/mysql/ca-certs/. Be attention to protect ca-key.pem with right permissions.

Following, the commands for generating the certificates of master and slave signed with the certification authority key already created:

Master:

[root@mariadb-master~]# openssl genrsa -out mariadb-master-key.pem 4096
[root@mariadb-master~]# openssl req -subj “/CN=mariadb-master” -sha256 -new -key mariadb-master-key.pem -out mariadb-master.csr
[root@mariadb-master~]# openssl x509 -req -days 365 -sha256 -in mariadb-master.csr -CA ca-cert.pem -CAkey ca-key.pem -CAcreateserial -out mariadb-master.pem

Slave:

[root@mariadb-master~]# openssl genrsa -out mariadb-slaver-key.pem 4096
[root@mariadb-slave~]# openssl req -subj “/CN=mariadb-slave” -sha256 -new -key mariadb-slave-key.pem -out mariadb-slave.csr
[root@mariadb-slave~]# openssl x509 -req -days 365 -sha256 -in mariadb-slave.csr -CA ca-cert.pem -CAkey ca-key.pem -CAcreateserial -out mariadb-slave.pem

The master and slave certifications and keys can be copied in the directory /etc/my.cnf.d/master-cert/ (master) and /etc/my.cnf.d/slave-cert/ (slave).

Let’s go now to configure master database.

Mariadb master slave configuration

The first step to do is configure the master and slave database and next enable and start slave process.

Following the master configuration:

[root@mariadb-master~]# vi server.cnf
ssl
ssl-ca=/etc/ mysql/ca-certs/ca-cert.pem
ssl-cert=/etc/ mysql/master-cert/master-cert.pem
ssl-key=/etc/ mysql/master-cert/master-key.pem
server-id = 1
log_bin = /var/ log/mysql/mysql-bin.log
binlog-format = row
expire_logs_days = 10
max_binlog_size = 100M
binlog_do_db = test

In the configuration above the ssl is enabled with the master and ca certifications configuration. Only the certificates signed by the ca-cert.pem will be authenticated.

The server id is a unique id that must be different in the slave server. In the following lines the archive logs are enabled: every write activity is logged in the log_bin files and the pointer to next record to written is called GTID (I will explain better next). The maximum size of archive log is 100 MB and they are deleted after 10 days.

These archive logs are useful for two reasons:

Restoring the database from a disaster. In this case the last backup must be recovered and next all the data starting from this backup until the time of disaster. Mysqlbinlog utility is used in this case (https://mariadb.com/kb/en/library/using-mysqlbinlog/)
Permitting to slave database to be aligned in any moment with the master. This is necessary in our case.

The bin_log_db enables the log mode only for the test database.

For the slave:

[root@mariadb-slave~]# vi server.cnf
ssl
ssl-ca=/etc/ mysql/ca-certs/ca-cert.pem
ssl-cert=/etc/ mysql/slave-cert/slave-cert.pem
ssl-key=/etc/ mysql/slave-cert/slave-key.pem
server-id = 2
read-only=1
replicate-do-db=test

The configuration is very intuitive. The server-id is different the the replication process is enabled only for test database. The database is enabled in read only for avoiding not wanted misalignment with the master.

Before enabling the master-slave replication, I will create a test database on master:

[root@mariadb-master~]# mysql –user=root -pxxxxxxxx
MariaDB [(none)]> create database test;
Query OK, 1 row affected (0.00 sec)
MariaDB [(none)]> use test;
Database changed
MariaDB [test]> CREATE TABLE Persons (
-> PersonID int,
-> LastName varchar(255),
-> FirstName varchar(255),
-> Address varchar(255),
-> City varchar(255)
-> );
Query OK, 0 rows affected (0.03 sec)
MariaDB [test]> INSERT INTO Persons VALUES (“11”, “rossi”, “stefano”,”via pp”,”Milan”);
Query OK, 1 row affected (0.00 sec)

It’s necessary to put in read mode the master for tracing the exact point where the slave must start to connect.

mysql> FLUSH TABLES WITH READ LOCK;
Query OK, 0 rows affected (0.00 sec)

As I said, starting from mariadb 10.x, the master replication process has beed improved adding a new parameter to value called GTID. Please read this for more information: https://mariadb.com/kb/en/mariadb/setting-up-replication/.

The master introduces a new event attached to each event group in the binlog called GTID used by slave server for keeping track of the position in the master’s binlog of the last event applied on the slave and allowing the slave server to re-connect and resume from where it left off after replication has been temporarily stopped.

For checking the GTID in the master database:

This is how to align the slave database before enabling the slave process:

backup the test database in master database,
copy it in the slave server
Enabling the slave process starting from 0-1-67 index:
Start the slave process.

Le’ts start with the backup on master database:

[root@mariadb-master~]# mysqldump -pxxxxxx –user=root test > /tmp/test_dump

The write data can now be enabled on master database. It’s ready to accept connections from slave process after that a slave user is created on it.

mysql> UNLOCK TABLES;
MariaDB [(none)]>GRANT REPLICATION SLAVE ON *.* TO ‘slave_user’@’%’ IDENTIFIED BY ‘xxxxx’ REQUIRE SSL;

After moved the backup to slave database, it’s possible to restore on it:

MariaDB [(none)]> create database test;
Query OK, 1 row affected (0.00 sec)
MariaDB [(none)]> exit
Bye
[root@mariadb-master~]# mysql -pxxxxxx –user=root test < /tmp/test_dump

The slave process can be enabled and started:

MariaDB [(none)]> SET GLOBAL gtid_slave_pos = ‘0-1-67’;
Query OK, 0 rows affected (0.01 sec)
MariaDB [(none)]> CHANGE MASTER TO master_host=”mariadb-master”, master_port=3306, master_user=”repl”, MASTER_SSL=1, master_password=”xxxx”, master_use_gtid=slave_pos;
Query OK, 0 rows affected (0.01 sec)
MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.01 sec)
MariaDB [(none)]> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: mariadb-master
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000011
Read_Master_Log_Pos: 511
Relay_Log_File: mariadb-master–relay-bin.000002
Relay_Log_Pos: 654
Relay_Master_Log_File: mysql-bin.000011
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: test;
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 511
Relay_Log_Space: 953
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: Yes
Master_SSL_CA_File: /etc/ mysql/ca-certs/ca-cert.pem
Master_SSL_CA_Path: /etc/ mysql/ca-certs/
Master_SSL_Cert:/etc/ mysql/slave-cert/slave-cert.pem
Master_SSL_Cipher:
Master_SSL_Key:/etc/ mysql/slave-cert/slave-key.pem
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
Master_SSL_Crl:
Master_SSL_Crlpath:
Using_Gtid: Slave_Pos
Gtid_IO_Pos: 0-1-67
Replicate_Do_Domain_Ids:
Replicate_Ignore_Domain_Ids:
Parallel_Mode: conservative
1 row in set (0.00 sec)

If one more row is added in the master database, it is replicated and the GTID is increased to 0-1-68. Le’t test it:

Adding a row in master database:

You should now verify that the GTID has already been incremented on the slave:

[root@mariadb-master~]# mysql -pxxxx –user=root test -e “show slave status\G” |grep Gtid
Using_Gtid: Slave_Pos
Gtid_IO_Pos: 0-1-68

Conclusions

In this article I have showed how to configure a mariadb in master slave configuration that is very useful for disaster recovery environment because there is a time to evaluate for promote the slave to master.

For High Availability you should consider a cluster solution implemented by mariadb in MariaDB Galera Cluster that is a synchronous multi-master cluster.

Don’t hesitate to contact me for any question or suggestion.

Mariadb master slave replication

Mariadb installation

Mariadb master slave configuration

Conclusions

LEAVE A COMMENT Cancel reply

My Posts