Over a million developers have joined DZone.
{{announcement.body}}
{{announcement.title}}

Setting Up a DBA Port Using MariaDB MaxScale

DZone 's Guide to

Setting Up a DBA Port Using MariaDB MaxScale

MariaDB MaxScale can do a few more things than you might think.

· Database Zone ·
Free Resource

Introduction

MariaDB MaxScale can do a few more things than you might think about at first. For example, you can have multiple Routers for the same set of servers. Why would you want that, you ask? Well, there are a few uses. Consider that you have MariaDB MaxScale running as a router for a database server and that this database server is accessed from an application server. Then let us also assume that you have multiple hosts on the Server running MariaDB MaxScale. Your network might look something like this then:

The server running MariaDB MaxScalehas two ports, one where the application server connects and one for monitoring. We want to make sure that we can access the MariaDB Server from either host, but that we have access and can use any command that is granted to the user in question in the database server only when accessing from the application server.

Basic MariaDB MaxScale Setup

In the following, we assume that MariaDB Server is already set up and that it is configured to use the proxy protocol (which in turn assumes that we are running MariaDB Server 10.3), see this page in the MariaDB Knowledge Base for more information on how to use MariaDB Server with the proxy protocol https://mariadb.com/kb/en/library/proxy-protocol-support. Also note that we are using the default MaxScale users to get information from the MariaDB Server here, myuser/mypwd. If you use a different username/password for this (hint: You really should!) then adjust the MariaDB MaxScale configuration below accordingly.

With that out of the way, let us move on to setting up MariaDB MaxScale to guide traffic from the Application Server to MariaDB Server. The MariaDB Server runs on 192.168.0.11, the application host on the MariaDB MaxScale server Is at 192.168.0.110, and the monitoring host is on 192.168.0.111. Note that the example below focuses on the specifics of what we are looking at here, there a few more things to set up with MariaDB MaxScale that are not mentioned here. Now we are ready to set up MariaDB MaxScale global settings, the server and the monitor we will be working with by editing the /etc/maxscale.cnffile:

# Global settings

#

[maxscale]

threads=auto



# Server definitions

#

[server1]

type=server

address=192.168.0.11

port=3306

protocol=MariaDBBackend

proxy_protocol=yes



# Monitor definitions

#

[MariaDB-Monitor]

type=monitor

module=mariadbmon

servers=server1

user=myuser

password=mypwd

monitor_interval=2000 

Note that we are using the proxy protocol here, this is to make life with MariaDB MaxScale a bit easier, but you also have to enable the proxy protocol in MariaDB Server for this to work. Check this page for instructions on how to set up the proxy protocol support on MariaDB Server: https://mariadb.com/kb/en/library/proxy-protocol-support/.

The next step is to set up a service, and for a service to work, we also need a listener.

# Services definitions

#

[Default-Service]

type=service

router=readwritesplit

servers=server1

user=myuser

password=mypwd




# Listener definitions

#

[Default-Listener]

type=listener

service=Default-Service

protocol=MariaDBClient

address=192.168.0.110

port=3306

There is just one thing in the above configuration that is a bit out of the ordinary, which is that we set an explicit address for the listener. If we don’t do that, then the listener will listen on all addresses/hosts, which specifically wasn’t what we wanted. We will listen on 192.168.0.111 also, but that will be a different listener thread.

Setting Up Monitoring Host

For monitoring we will use a different host, 192.168.0.111, so we need a new listener and a service. To protect this port from any but the commands we want to run we also need a filter, so let’s start with that.

Creating a MariaDB MaxScale Regex Filter

We are to use a regex filter here, but in a rather unusual way. Mostly, regex is used to replace parts of the syntax like some outdated statement or some minor compatibility feature. In this case, we will filter out most statements and only retain a few commands. In short, we are to allow a few show commands, and all other commands will be replaced with a SELECT with an error message. The filter looks like this; note that this is an example, so feel free to update it to fit your needs.

# Filter definitions

#

[Monitor-Filter]

type=filter

module=regexfilter

options=ignorecase

match=^(?!show (variables|global status|processlist)).*$

replace=SELECT 'Not allowed'

So any command that doesn’t start with “show variables,” “show global status,” or “show processlist” will be replaced with the command “SELECT ‘Not allowed.’” With that in place, we are now ready to set up the corresponding service and listener.

Creating a Monitoring Service and Listener

This service and listener is different from the one we just created in two ways. First, it use a different host (192.168.0.111) and secondly, it also included the filter we created above. Except for this, nothing special.

# Services definitions

#

[Monitor-Service]

type=service

router=readwritesplit

servers=server1

user=myuser

password=mypwd

filters=Monitor-Filter




# Listener definitions

#

[Monitor-Listener]

type=listener

service=Monitor-Service

protocol=MariaDBClient

address=192.168.0.111

port=3306

Starting the MariaDB MaxScale Service and Testing

At this point, our configurations should be correct, so let’s restart MariaDB MaxScale.

$ sudo systemctl restart maxscale

Then we can access MaxScale in the same way as the Application Server:

$ mysql -h 192.168.0.110 -u myuser -pmypwd

Welcome to the MariaDB monitor.  Commands end with ; or \g.

Your MariaDB connection id is 5

Server version: 10.3.11-MariaDB MariaDB Server




Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.




Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.




MariaDB [(none)]> use test;

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A




Database changed

MariaDB [test]> select count(*) from t1;

+----------+

| count(*) |

+----------+

|        1 |

+----------+

1 row in set (0.002 sec)




MariaDB [test]> exit

Bye

$

So, now we can try to access MariaDB Server through MariaDB MaxScale, but on the other port, designated for monitoring only and only allowing certain commands.

$ mysql -h 192.168.0.111 -u myuser -pmypwd

Welcome to the MariaDB monitor.  Commands end with ; or \g.

Your MariaDB connection id is 6

Server version: 10.3.11-MariaDB Not allowed




Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.




Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.




MariaDB [(none)]> select 1;

+-------------+

| Not allowed |

+-------------+

| Not allowed |

+-------------+

1 row in set (0.001 sec)




MariaDB [(none)]> use test

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A




Database changed

MariaDB [test]> show variables like 'innodb_buffer_pool_size';

+-------------------------+-----------+

| Variable_name           | Value     |

+-------------------------+-----------+

| innodb_buffer_pool_size | 134217728 |

+-------------------------+-----------+

1 row in set (0.003 sec)




MariaDB [test]> insert into t1 values(2);

+-------------+

| Not allowed |

+-------------+

| Not allowed |

+-------------+

1 row in set (0.001 sec)




MariaDB [test]> exit

Bye

$


Conclusion

This is a simple example of something that MariaDB MaxScale can be used for. If you are using MariaDB MaxScale already, then you are probably using it for load balancing or high availability or a combination of the two. But if you are, you can always combine that use with some other use, like the one outlined above. Happy SQL’ing!

Topics:
database ,mariadb maxscale ,mariadb ,sql ,tutorial

Published at DZone with permission of

Opinions expressed by DZone contributors are their own.

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}