Maxscale Data Archiving with filters Mq & Tee ( Mirror)

Jan 29, 2018

Mins to Read

All

Introduction –

Maxscale is an excellent Proxy from Mariadb Corporation, which providing the High Availability, Realtime RW split with replication Glaera cluster Amazon RDS Amazon Aurora, binlog streaming and many more advanced features, here in this blog we will discuss one such feature,

In this blog post, i am going to share my recent activity with Maxscale. We had to help one of our client to archive only the DML ( CREATE & INSERT ) data into archive server from specific table.

Problem Statement –

Our client is having only one standalone ( Master ) setup and a Archive server , they need to archive one table,Archive server should receive all the Queries as in Production Server at same time, the table should be applied only with INSERT & UPDATE statements on Archive Server. All other SQL statements has to be filtered out.

How to achieve this ? Is that possible ?

Yes, we can achieve this via the Maxscale 2.1 MQ & Tee filters(mirror).

Architecture –

Client will be connected with Maxscale, the Tee filter will be pipe all the connection from Master (standalone) to Archive via MQ filter. The MQ filter match the mentioned string & allow the needed SQL statements to archive Server.

The queries routed to the archive servers are also transformed into a canonical format and sent to a RabbitMQ broker for analysis.

Below, i am sharing my testing environment results & steps as well.

Requirements for Testing –

Mariadb Servers ( 2 )

Production Server – 192.168.33.16

Archive Server – 192.168.33.17

Maxscale & Rabbit MQ Server

Proxy Server – 192.168.33.13

Steps & Results –

MariaDB Server Installation –

I had installed the MariaDB Server on both production & archive environment using MariDB repository.

 
[root@mydbopslabs16 vagrant]# cat /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

Installing the MariaDB server,

 
yum install MariaDB-server MariaDB-client

Starting the MariaDB server,

 
systemctl status mariadb

Version – 10.1.30

Configuring RabbitMQ & Maxscale –

Now, let’s start with Maxscale & RabbitMQ Server configuration on node 192.168.33.13,

I have directly installed the latest Maxscale using the RPM file,

 
yum install https://downloads.mariadb.com/MaxScale/2.1.13/rhel/7/x86_64/maxscale-2.1.13-1.rhel.7.x86_64.rpm

service maxscale start

For install the Rabbit MQ server, we need to configure the epel repository,

Installing epel repo,

 
yum install https://dl.fedoraproject.org/pub/epel/epel-release-latest-7.noarch.rpm

Installing dependencies,

 
yum install http://packages.erlang-solutions.com/erlang-solutions-1.0-1.noarch.rpm

Installing RabbitMQ server,

 
yum install https://www.rabbitmq.com/releases/rabbitmq-server/v3.5.6/rabbitmq-server-3.5.6-1.noarch.rpm

It will take some time.

Starting the RabbitMQ Server,

 
systemctl start rabbitmq-server

Adding user & permissions,

 
rabbitmqctl add_user "sakthi_labs" "sakthi@labs"
rabbitmqctl set_permissions "sakthi_labs" ".*" ".*" ".*"
[root@mydbopslabs13 vagrant]# rabbitmqctl list_queues
Listing queues ...

Now, we have installed all the needed things. Finally, we need to configure the Maxscale configuration file, Below is the set of configurations need for smooth process.

 
[root@mydbopslabs13 vagrant]# cat /etc/maxscale.cnf
# The production and archive servers
[production]
type=server
address=192.168.33.16
port=3306
protocol=MySQLBackend

[archive]
type=server
address=192.168.33.17
port=3306
protocol=MySQLBackend

[MySQL Monitor]
type=monitor
module=mysqlmon
servers=production, archive
user=maxscale
passwd=maxscale@123
monitor_interval=10000

[Production]
type=service
router=readconnroute
servers=production
user=maxscale
passwd=maxscale@123
filters=Tee

[Archive]
type=service
router=readconnroute
servers=archive
user=maxscale
passwd=maxscale@123
filters=MQ Filter

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

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

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

[MQ Filter]
type=filter
module=mqfilter
hostname=192.168.33.13
port=5672
username=sakthi_labs
password=sakthi@labs
exchange=msg-ex-1
queue=msg-queue-1
key=MaxScale

[MaxAdmin Service]
type=service
router=cli

[MaxAdmin Listener]
type=listener
service=MaxAdmin Service
protocol=maxscaled
socket=default

Note – Make sure to create the users with needed privileges for Maxscale access on both production & archive Env. Refer the MariaDB official page here for user administration.

Now, feel free we have done with the configurations.

 
[root@mydbopslabs13 vagrant]# maxadmin
MaxScale> list servers
Servers.
-------------------+-----------------+-------+-------------+--------------------
Server | Address | Port | Connections | Status
-------------------+-----------------+-------+-------------+--------------------
production | 192.168.33.16 | 3306 | 0 | Running
archive | 192.168.33.17 | 3306 | 0 | Running
-------------------+-----------------+-------+-------------+-------------------

Journey to testing phase –

From configuration file, i have just configured the match filter with CREATE & INSERT Statements.

 
match=match=\(insert\)\|\(create\)

So, it should allow the CREATE & INSERT statements to the archive & production as well. Remaining all will be affect only production Server.

In Maxscale host, i am creating the database and table with production listener port ( 4000 ).

 
[root@mydbopslabs13 vagrant]# hostname -I
192.168.33.13
[root@mydbopslabs13 vagrant]# mysql -h192.168.33.13 -uarchive_labs -p'mydbops@123' -P4000 -e "create database data_archivelab"
[root@mydbopslabs13 vagrant]# mysql -h192.168.33.13 -uarchive_labs -p'mydbops@123' -P4000 -e "use data_archivelab; create table archive_test(id int, name varchar(16));"
[root@mydbopslabs13 vagrant]# rabbitmqctl list_queues
Listing queues ...
msg-queue-1 2     ( queue status )

Here archive_labs is the user, which i have created for Maxscale connection.

In production host,

 
[root@mydbopslabs16 vagrant]# hostname -I
192.168.33.16
[root@mydbopslabs16 vagrant]# mysql -u'archive' -p'archive@123' -e "show schemas like 'data_archivelab'" -s -N
data_archivelab
[root@mydbopslabs16 vagrant]# mysql -u'archive' -p'archive@123' -e "use data_archivelab; show tables" -s -N
archive_test

In archive host,

 
[root@mydbopslabs17 init.d]# hostname -I
192.168.33.17
[root@mydbopslabs17 init.d]# mysql -u'archive' -p'archive@123' -e "show schemas like 'data_archivelab'" -s -N
data_archivelab
[root@mydbopslabs17 init.d]# mysql -u'archive' -p'archive@123' -e "use data_archivelab; show tables" -s -N
archive_test

Well, seems the DB & Table has been created on both production & archive Servers.Now,

testing with INSERT Statement ,

In Maxscale host,

 
[root@mydbopslabs13 vagrant]# hostname -I
192.168.33.13
[root@mydbopslabs13 vagrant]# mysql -h192.168.33.13 -uarchive_labs -p'mydbops@123' -P4000 -e "use data_archivelab; insert into archive_test values(1,'MySQL'),(2,'MariaDB'),(3,'Percona'),(4,'Mydbops');"

In Production host,

 
[root@mydbopslabs16 vagrant]# hostname -I
192.168.33.16
[root@mydbopslabs16 vagrant]# mysql -u'archive' -p'archive@123' -e "use data_archivelab; select * from archive_test" -s -N
1       MySQL
2       MariaDB
3       Percona
4       Mydbops

In archive host,

 
[root@mydbopslabs17 init.d]# hostname -I
192.168.33.17
[root@mydbopslabs17 init.d]# mysql -u'archive' -p'archive@123' -e "use data_archivelab; select * from archive_test" -s -N
1       MySQL
2       MariaDB
3       Percona
4       Mydbops

Perfect, seems the results illustrate the CREATE & INSERT statements was perfectly updating the records in both production & archive Servers.

Finally, now i am going to delete some records, let see what happen,

In Maxscale host,

 
[root@mydbopslabs13 vagrant]# hostname -I
192.168.33.13
[root@mydbopslabs13 vagrant]# mysql -h192.168.33.13 -uarchive_labs -p'mydbops@123' -P4000 -e "use data_archivelab; delete from archive_test where id in (1,3);"

In production host,

 
[root@mydbopslabs16 vagrant]# hostname -I
192.168.33.16
[root@mydbopslabs16 vagrant]# mysql -u'archive' -p'archive@123' -e "use data_archivelab; select * from archive_test" -s -N
2       MariaDB
4       Mydbops

In archive host,

 
[root@mydbopslabs17 init.d]# hostname -I
192.168.33.17
[root@mydbopslabs17 init.d]# mysql -u'archive' -p'archive@123' -e "use data_archivelab; select * from archive_test" -s -N
1       MySQL
2       MariaDB
3       Percona
4       Mydbops

Well, the things are clear now. The DELETE statement is not affecting the archive Env, still it affects the production.

Hope this post will be helpful for understand the Maxscale data archiving process. This architecture can be implement in such a scenario like to save all the data without any delete or any modifications.

This completes the data archiving process with Maxscale, remaining features will be followed up in future blog posts.

Thank you !!!

MySQL

tools

MaxScale

No items found.