Archiving MariaDB Database with Mq and Tee filters

This whitepaper is about how to use MariaDB MaxScale filters for Archiving MariaDB database. We have considered two MariaDB / MySQL servers in this exercise, one is the MariaDB Production Database Server and other one for Archiving MariaDB Production Database Server . We have also accommodated RabbitMQ and MariaDB MaxScale servers in their respective Instances .

Our use case is a MariaDB Production Database Server and MariaDB archive Database Server where only INSERT, UPDATE AND DELETE statements are routed. The queries routed to the archive servers are also transformed into a canonical format and sent to a RabbitMQ broker for analysis. This setup allows us to control what we send to the server and could possibly allow us to filter out DELETE statements completely, making the archive server a true archive of all data.

Reference source – MariaDB.com 

Installation and configuration of MariaDB MaxScale

We have blogged about MariaDB MaxScale installation and configuration – https://minervadb.com/index.php/2019/06/06/installation-and-configuration-of-mariadb-galera-cluster-and-mariadb-maxscale-on-centos/

Installation and configuration of RabbitMQ Server on CentOS 7

First we need the EPEL and Erlang repositories for CentOS 7 after which we will install the RabbitMQ server:

sudo yum -y install https://dl.fedoraproject.org/pub/epel/epel-release-latest-7.noarch.rpm
sudo yum -y install http://packages.erlang-solutions.com/erlang-solutions-1.0-1.noarch.rpm
sudo yum -y install https://www.rabbitmq.com/releases/rabbitmq-server/v3.5.6/rabbitmq-server-3.5.6-1.noarch.rpm

Start RabbitMQ Server

sudo systemctl start rabbitmq-server

Create RabbitMQ msguser account ( with read, write and configurations permissions on the default virtual host ) to relay the messages to the broker using rabbitmqctl command:

sudo rabbitmqctl add_user "msguser" "msgpwd"
sudo rabbitmqctl set_permissions "msguser" ".*" ".*" ".*"

Configure MariaDB MaxScale Server adding both MariaDB Production Database Server and MariaDB Archive Database Server

[Production-DB]
type=server
address=192.168.56.101
port=3306
protocol=MySQLBackend

[Archive-DB]
type=server
address=192.168.0.102
port=3000
protocol=MySQLBackend

After successfully configuring the Production-DB and Archive-DB servers,  We need a monitor module for those servers, We have blogged about installation and configuration of MariaDB MaxScale monitoring here

[MySQL Monitor]
type=monitor
module=mysqlmon
servers=Production-DB, Archive-DB
user=maxscaleuser
passwd=maxscaleuserpassword
monitor_interval=5000

The monitor will use the user maxscaleuser with the password maxscalepassword to connect to the servers and query them for their state. In the servers parameter we have listed both of the Production-DB and Archive-DB servers. The monitor_interval parameter controls how often the monitor will poll the servers for status. For this tutorial, we’ve set it to 5000 milliseconds.

Next step is the configuration of the Production and Archive services. The Production service will be the main access point to the system.

[Production-DB]
type=service
router=readconnroute
servers=Production-DB
user=maxscaleuser
passwd=maxscaleuserpassword
filters=Tee

[Archive-DB]
type=service
router=readconnroute
servers=Archive-DB
user=maxscaleuser
passwd=maxscaleuserpassword
filters=MQ Filter

The filters parameters for the services refer to the filters we will be creating next. The Production service will use the Tee filter to duplicate INSERT, UPDATE and DELETE statements to the Archive service. The statements passed to the Archive service will use the MQ Filter to send the canonical versions of the statements to the RabbitMQ broker. The Production service will use the Production-DB server and the Archive service will use the Archive-DB server. Both services user the maxscaleuser user with the maxscalepassword password.

Next we will configure the listeners for these two services.

[Production Listener]
type=listener
service=Production
protocol=MySQLClient
port=4000

[Archive Listener]
type=listener
service=Archive
protocol=MySQLClient
port=4001

The port parameter controls which port the listener will listen on and where the client connections should be made. The service parameter tells which listener belongs to which service.

After the services and their listeners are configured we will configure the two filters we’ll use. We begin with the Tee filter.

[Tee]
type=filter
module=tee
service=Archive
match=\(insert\)\|\(update\)\|\(delete\)

The service parameter controls which service we want to duplicate the statements to. This needs to be set to the section name of the archive service: Archive. The match parameter is a regular expression which, if matched, cause the statement to be duplicated. Here we will want a regular expression which will match the INSERT, UPDATE and DELETE statements.

After the Tee filter is configured, we will configure the Mqfilter to communicate with the RabbitMQ server we’ve set up.

[MQ Filter]
type=filter
module=mqfilter
hostname=192.168.56.101
port=5672
username=msguser
password=msgpwd
exchange=msg-ex-1
queue=msg-queue-1
key=MaxScale

The hostname is the address and the port is the port of the RabbitMQ server. For username and password we will use the msguser and msgpwd credentials we created earlier. The exchange is the name of the RabbitMQ exchange we’re using and the key is the key used for all the sent messages. The messages will be stored in the mxs-queue-1 queue.

The next thing to configure is the administration interface.

[MaxAdmin Service]
type=service
router=cli

[MaxAdmin Listener]
type=listener
service=MaxAdmin Service
protocol=maxscaled
port=6603

We have created MaxScale configuration file in  /etc/maxscale.cnf , Here is the complete configuration file:

# The production and archive servers
[Production-DB]
type=server
address=192.168.56.101
port=3306
protocol=MySQLBackend

[Archive-DB]
type=server
address=192.168.56.102
port=3000
protocol=MySQLBackend

# MySQL server monitor
[MySQL Monitor]
type=monitor
module=mysqlmon
servers=Production-DB, Archive-DB
user=maxscaleuser
passwd=maxscalepassword
monitor_interval=5000

# Production service
[Production]
type=service
router=readconnroute
servers=Production-DB
user=maxscaleuser
passwd=maxscalepassword
filters=Tee

# Archive service
[Archive]
type=service
router=readconnroute
servers=Archive-DB
user=maxscaleuser
passwd=maxscalepassword
filters=MQ Filter

# Listeners for the services
[Production Listener]
type=listener
service=Production
protocol=MySQLClient
port=4000

[Archive Listener]
type=listener
service=Archive
protocol=MySQLClient
port=4001

# Tee filter to duplicate insert, update and delete
# statements to the archive server
[Tee]
type=filter
module=tee
service=Archive
match=\(insert\)\|\(update\)\|\(delete\)

# Mqfilter to log the canonical versions of the archive
# server queries to a RabbitMQ broker
[MQ Filter]
type=filter
module=mqfilter
hostname=192.168.56.101
port=5672
username=msguser
password=msgpwd
exchange=msg-ex-1
queue=msg-queue-1
key=MaxScale

# Administration interface setup
[MaxAdmin Service]
type=service
router=cli

[MaxAdmin Listener]
type=listener
service=MaxAdmin Service
protocol=maxscaled
port=6603

Testing the new infra. with MariaDB MaxScale and RabbitMQ Server

Step 1. Restart MariaDB MaxScale:

sudo systemctl start maxscale

Step 2. Confirm the state of the two servers with MaxAdmin:

maxadmin list servers

Servers.
-------------------+-----------------+-------+-------------+--------------------
Server             | Address         | Port  | Connections | Status              
-------------------+-----------------+-------+-------------+--------------------
Production-DB      | 192.168.56.101  |  3306 |           0 | Running
Archive-DB         | 192.168.56.102  |  3000 |           0 | Running
-------------------+-----------------+-------+-------------+--------------------

Step 3. check the queue status on the RabbitMQ server:

sudo rabbitmqctl list_queues

Listing queues …….

So now if we connect to MariaDB Production Service on port 4000 and execute the data modifying statement then we should be seeing the equal number of statements being sent to the RabbitMQ Server:

MariaDB [(none)]> insert into DB1.tab1 values(500);
Query OK, 1 row affected (0.09 sec)

MariaDB [(none)]> insert into DB1.tab1 values(1000);
Query OK, 1 row affected (0.07 sec)

MariaDB [(none)]> update DB1.tab1 set id = 250;
Query OK, 5 rows affected (0.08 sec)
Rows matched: 5  Changed: 5 Warnings: 0

On the RabbitMQ server we can see the new queue msg-queue-1 with three new messages in it.

sudo rabbitmqctl list_queues

Listing queues ...
msg-queue-1 3

We can also see the data on the Archive Server:

MariaDB [(none)]> select * from DB1.tab1;
+------+
| id   |
+------+
|    0 |
|    0 |
+------+
2 rows in set (0.00 sec)