Setting Up a DBA Port Using MariaDB MaxScale
MariaDB MaxScale can do a few more things than you might think.
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 $
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!