MaxScale Basic Management Using MaxCtrl for MariaDB Cluster - Part Two

Ashraf Sharif

In the previous blog post, we have covered 4 basic management components using the MaxCtrl command-line client. In this blog post, we are going to cover the remaining part of the MaxScale components which are commonly used in a MariaDB Cluster:

  • Filter management
  • MaxScale management
  • Logging management

All of the commands in this blog post are based on MaxScale 2.5.3. 

Filter Management

The filter is a module in MaxScale which acts as the processing and routing engine for a MaxScale service. The filtering happens between the client connection to MaxScale and the MaxScale connection to the backend database servers. This path (the client-side of MaxScale out to the actual database servers) can be considered a pipeline, filters can then be placed in that pipeline to monitor, modify, copy or block the content that flows through it.

There are many filters that can be applied to extend the processing capabilities of a MaxScale service, as shown in the following table:

Filter Name

Description

Binlog

Selectively replicates the binary log events to slave servers combined together with a binlogrouter service.

Cache

A simple cache that is capable of caching the result of SELECTs, so that subsequent identical SELECTs are served directly by MaxScale, without the queries being routed to any server.

Consistent Critical Read

Allows consistent critical reads to be done through MaxScale while still allowing scaleout of non-critical reads.

Database Firewall

Blocks queries that match a set of rules. This filter should be viewed as a best-effort solution intended for protecting against accidental misuse rather than malicious attacks.

Hint

Adds routing hints to a service, instructing the router to route a query to a certain type of server.

Insert Stream

Converts bulk inserts into CSV data streams that are consumed by the backend server via the LOAD DATA LOCAL INFILE mechanism

Lua

Calls a set of functions in a Lua script.

Masking

Obfuscates the returned value of a particular column

Maxrows

Restricting the number of rows that a SELECT, a prepared statement, or stored procedure could return to the client application.

Named Server

Routes queries to servers based on regular expression (regex) matches.

Query Log All

Logs query content to a file in CSV format.

Regex

Rewrites query content using regular expression matches and text substitution.

Tee

Make copies of requests from the client and send the copies to another service within MariaDB MaxScale.

Throttle

Replaces and extends on the limit_queries functionality of the Database Firewall filter

Top

Monitors the query performance of the selected SQL statement that passes through the filter.

Transaction Performance Monitoring

Monitors every SQL statement that passes through the filter, grouped as per transaction, for transaction performance analysis.

Every filter has its own ways to configure. Filters are commonly attached to a MaxScale service. For example, a binlog filter can be applied to the binlogrouter service, to only replicate a subset of data onto a slave server which can hugely reduce the disk space for huge tables. Check out the MaxScale filters documentation for the right way to configure the parameters for the corresponding filter.

Create a Filter

Every MaxScale filter has its own way to be configured. In this example, we are going to create a masking filter, to mask our sensitive data for column "card_no" in our table "credit_cards". Masking requires a rule file, written in JSON format. Firstly, create a directory to host our rule files:

$ mkdir /var/lib/maxscale/rules

Then, create a text file:

$ vi /var/lib/maxscale/rules/masking.json

Specify the lines as below:

{
    "rules": [
        {
            "obfuscate": {
                "column": "card_no"
            }
        }
    ]
}

The above simple rules will simply obfuscate the output of column card_no for any tables, to protect the sensitive data to be seen by the MariaDB client.

After the rule file has been created, we can create the filter, using the following command:

maxctrl: create filter Obfuscates-card masking rules=/var/lib/maxscale/rules/masking.json
OK

Note that some filters require different parameters. As for this masking filter, the basic parameter is "rules", where we need to specify the created masking rule file in JSON format.

Attach a Filter to a Service

A filter can only be activated by attaching it to a service. Modifying an existing service using MaxCtrl is only supported by some parameters, and adding a filter is not one of them. We have to add the filter component under MaxScale's service configuration file to basically attach the filter. In this example, we are going to apply the "Obfuscates-card" filter to our existing round-robin service called rr-service.

Go to /var/lib/maxscale/maxscale.cnf.d directory and find rr-service.cnf, open it with a text editor and then add the following line:

filters=Obfuscates-card

A MaxScale restart is required to load the new change:

$ systemctl restart maxscale

To test the filter, we will use a MariaDB client and compare the output by connecting to two different services. Our rw-service is attached to a listener listening on port 3306, without any filters configured. Hence, we should see the unfiltered response from the MaxScale:

$ mysql -ucard_user -p -hmaxscale_host -p3306 -e "SELECT * FROM secure.credit_cards LIMIT 1"
+----+-----------+-----------------+-------------+-----------+---------+
| id | card_type | card_no         | card_expiry | card_name | user_id |
+----+-----------+-----------------+-------------+-----------+---------+
|  1 | VISA      | 425388910909238 | NULL        | BOB SAGAT |       1 |
+----+-----------+-----------------+-------------+-----------+---------+

When connecting to the rr-service listener on port 3307, which configured with our filter, our "card_no" value is obfuscated with a gibberish output:

$ mysql -ucard_user -p -hmaxscale_host -p3307 -e "SELECT * FROM secure.credit_cards LIMIT 1"
+----+-----------+-----------------+-------------+-----------+---------+
| id | card_type | card_no         | card_expiry | card_name | user_id |
+----+-----------+-----------------+-------------+-----------+---------+
|  1 | VISA      | ~W~p[=&^M~5f~~M | NULL        | BOB SAGAT |       1 |
+----+-----------+-----------------+-------------+-----------+---------+

This filtering is performed by MaxScale, following the matching rules inside masking.json that we have created earlier.

List Filters

To list out all created filters, use the "list filters" command:

maxctrl: list filters
┌─────────────────┬────────────┬─────────────┐
│ Filter          │ Service    │ Module      │
├─────────────────┼────────────┼─────────────┤
│ qla             │            │ qlafilter   │
├─────────────────┼────────────┼─────────────┤
│ Obfuscates-card │ rr-service │ masking     │
├─────────────────┼────────────┼─────────────┤
│ fetch           │            │ regexfilter │
└─────────────────┴────────────┴─────────────┘

In the above examples, we have created 3 filters. However, only the Obfuscates-card filter is linked to a service.

To show all services in details:

maxctrl: show filters

Or if you want to show a particular service:

maxctrl: show filter Obfuscates-card
┌────────────┬──────────────────────────────────────────────────────┐
│ Filter     │ Obfuscates-card                                      │
├────────────┼──────────────────────────────────────────────────────┤
│ Module     │ masking                                              │
├────────────┼──────────────────────────────────────────────────────┤
│ Services   │ rr-service                                           │
├────────────┼──────────────────────────────────────────────────────┤
│ Parameters │ {                                                    │
│            │     "check_subqueries": true,                        │
│            │     "check_unions": true,                            │
│            │     "check_user_variables": true,                    │
│            │     "large_payload": "abort",                        │
│            │     "prevent_function_usage": true,                  │
│            │     "require_fully_parsed": true,                    │
│            │     "rules": "/var/lib/maxscale/rules/masking.json", │
│            │     "treat_string_arg_as_field": true,               │
│            │     "warn_type_mismatch": "never"                    │
│            │ }                                                    │
└────────────┴──────────────────────────────────────────────────────┘

Delete a Filter

In order to delete a filter, one has to unlink from the associated services first. For example, consider the following filters in MaxScale:

 maxctrl: list filters
┌─────────────────┬────────────┬───────────┐
│ Filter          │ Service    │ Module    │
├─────────────────┼────────────┼───────────┤
│ qla             │            │ qlafilter │
├─────────────────┼────────────┼───────────┤
│ Obfuscates-card │ rr-service │ masking   │
└─────────────────┴────────────┴───────────┘

For the qla filter, we can simply use the following command to delete it:

 maxctrl: destroy filter qla
OK

However, for the Obfuscates-card filter, it has to be unlinked with rr-service and unfortunately, this requires a configuration file modification and MaxScale restart. Go to /var/lib/maxscale/maxscale.cnf.d directory and find rr-service.cnf, open it with a text editor and then remove the following line:

filters=Obfuscates-card

You could also remove the "Obfuscates-card" string from the above line and let "filters" line equal to an empty value. Then, save the file and restart MaxScale service to load the changes:

$ systemctl restart maxscale

Only then we can remove the Obfuscates-card filter from MaxScale using the "destroy filter" command:

maxctrl: destroy filter Obfuscates-card
OK

MaxScale Management

List Users

To list all MaxScale users, use the "list users" command:

maxctrl: list users
┌───────┬──────┬────────────┐
│ Name  │ Type │ Privileges │
├───────┼──────┼────────────┤
│ admin │ inet │ admin      │
└───────┴──────┴────────────┘

Create a MaxScale User

By default, a created user is a read-only user:

 maxctrl: create user dev mySecret
OK

To create an administrator user, specify the --type=admin command:

 maxctrl: create user dba mySecret --type=admin
OK

Delete a MaxScale User

To delete a user, simply use the "destroy user" command:

 maxctrl: destroy user dba
OK

The last remaining administrative user cannot be removed. Create a replacement administrative user before attempting to remove the last administrative user.

Show MaxScale Parameters

To show all loaded parameters for the MaxScale instance, use the "show maxscale" command:

maxctrl: show maxscale
┌──────────────┬──────────────────────────────────────────────────────────────────────┐
│ Version      │ 2.5.3                                                                │
├──────────────┼──────────────────────────────────────────────────────────────────────┤
│ Commit       │ de3770579523e8115da79b1696e600cce1087664                             │
├──────────────┼──────────────────────────────────────────────────────────────────────┤
│ Started At   │ Mon, 21 Sep 2020 04:44:49 GMT                                        │
├──────────────┼──────────────────────────────────────────────────────────────────────┤
│ Activated At │ Mon, 21 Sep 2020 04:44:49 GMT                                        │
├──────────────┼──────────────────────────────────────────────────────────────────────┤
│ Uptime       │ 1627                                                                 │
├──────────────┼──────────────────────────────────────────────────────────────────────┤
│ Parameters   │ {                                                                    │
│              │     "admin_auth": true,                                              │
│              │     "admin_enabled": true,                                           │
│              │     "admin_gui": true,                                               │
│              │     "admin_host": "127.0.0.1",                                       │
│              │     "admin_log_auth_failures": true,                                 │
│              │     "admin_pam_readonly_service": null,                              │
│              │     "admin_pam_readwrite_service": null,                             │
│              │     "admin_port": 8989,                                              │
│              │     "admin_secure_gui": true,                                        │
│              │     "admin_ssl_ca_cert": null,                                       │
│              │     "admin_ssl_cert": null,                                          │
│              │     "admin_ssl_key": null,                                           │
│              │     "auth_connect_timeout": 10000,                                   │
│              │     "auth_read_timeout": 10000,                                      │
│              │     "auth_write_timeout": 10000,                                     │
│              │     "cachedir": "/var/cache/maxscale",                               │
│              │     "connector_plugindir": "/usr/lib/x86_64-linux-gnu/mysql/plugin", │
│              │     "datadir": "/var/lib/maxscale",                                  │
│              │     "debug": null,                                                   │
│              │     "dump_last_statements": "never",                                 │
│              │     "execdir": "/usr/bin",                                           │
│              │     "language": "/var/lib/maxscale",                                 │
│              │     "libdir": "/usr/lib/x86_64-linux-gnu/maxscale",                  │
│              │     "load_persisted_configs": true,                                  │
│              │     "local_address": null,                                           │
│              │     "log_debug": false,                                              │
│              │     "log_info": false,                                               │
│              │     "log_notice": false,                                             │
│              │     "log_throttling": {                                              │
│              │         "count": 0,                                                  │
│              │         "suppress": 0,                                               │
│              │         "window": 0                                                  │
│              │     },                                                               │
│              │     "log_warn_super_user": false,                                    │
│              │     "log_warning": false,                                            │
│              │     "logdir": "/var/log/maxscale",                                   │
│              │     "max_auth_errors_until_block": 10,                               │
│              │     "maxlog": true,                                                  │
│              │     "module_configdir": "/etc/maxscale.modules.d",                   │
│              │     "ms_timestamp": true,                                            │
│              │     "passive": false,                                                │
│              │     "persistdir": "/var/lib/maxscale/maxscale.cnf.d",                │
│              │     "piddir": "/var/run/maxscale",                                   │
│              │     "query_classifier": "qc_sqlite",                                 │
│              │     "query_classifier_args": null,                                   │
│              │     "query_classifier_cache_size": 0,                                │
│              │     "query_retries": 1,                                              │
│              │     "query_retry_timeout": 5000,                                     │
│              │     "rebalance_period": 0,                                           │
│              │     "rebalance_threshold": 20,                                       │
│              │     "rebalance_window": 10,                                          │
│              │     "retain_last_statements": 0,                                     │
│              │     "session_trace": 0,                                              │
│              │     "skip_permission_checks": false,                                 │
│              │     "sql_mode": "default",                                           │
│              │     "syslog": true,                                                  │
│              │     "threads": 1,                                                    │
│              │     "users_refresh_interval": 0,                                     │
│              │     "users_refresh_time": 30000,                                     │
│              │     "writeq_high_water": 16777216,                                   │
│              │     "writeq_low_water": 8192                                         │
│              │ }                                                                    │
└──────────────┴──────────────────────────────────────────────────────────────────────┘

Alter MaxScale parameters

  • auth_connect_timeout
  • auth_read_timeout
  • auth_write_timeout
  • admin_auth
  • admin_log_auth_failures
  • passive

The rest of the parameters must be set inside /etc/maxscale.conf, which requires a MaxScale restart to apply the new changes.

MaxScale GUI

MaxGUI is a new browser-based tool for configuring and managing MaxScale, introduced in version 2.5. It's accessible via port 8989 of the MaxScale host on the localhost interface, 127.0.0.1. By default, it is required to set admin_secure_gui=true and configure both the admin_ssl_key and admin_ssl_cert parameters. However, in this blog post, we are going to allow connectivity via the plain HTTP by adding the following line under [maxctrl] directive inside /etc/maxscale.cnf:

admin_secure_gui = false

Restart MaxScale service to load the change:

$ systemctl restart maxscale

Since the GUI is listening on the localhost interface, we can use SSH tunneling to access the GUI from our local workstation:

$ ssh -L 8989:localhost:8989 [email protected]<Maxscale public IP address>

Then, open a web browser, point the URL to http://127.0.0.1:8989/ and log in. MaxGUI uses the same credentials as maxctrl, thus the default password is "admin" with the password "mariadb". For security purposes, one should create a new admin user with a stronger password specifically for this purpose. Once logged in, you should see the MaxGUI dashboard as below:

Most of the MaxCtrl management commands that we have shown in this blog series can be performed directly from this GUI. If you click on the "Create New" button, you will be presented with the following dialog:

As you can see, all of the important MaxScale components can be managed directly from the GUI, with a nice intuitive clean look, makes things much simpler and more straightforward to manage. For example, associating a filter can be done directly from the UI, without the need to restart the MaxScale service, as shown under "Attach a filter to a service" section in this blog post.

For more information about this new GUI, check out this MaxGUI guide.

Logging Management

Show Logging Parameters

To display the logging parameters, use the "show logging" command:

 maxctrl: show logging
┌────────────────────┬────────────────────────────────┐
│ Current Log File   │ /var/log/maxscale/maxscale.log │
├────────────────────┼────────────────────────────────┤
│ Enabled Log Levels │ alert                          │
│                    │ error                          │
│                    │ warning                        │
│                    │ notice                         │
├────────────────────┼────────────────────────────────┤
│ Parameters         │ {                              │
│                    │     "highprecision": true,     │
│                    │     "log_debug": false,        │
│                    │     "log_info": false,         │
│                    │     "log_notice": true,        │
│                    │     "log_warning": true,       │
│                    │     "maxlog": true,            │
│                    │     "syslog": true,            │
│                    │     "throttling": {            │
│                    │         "count": 10,           │
│                    │         "suppress_ms": 10000,  │
│                    │         "window_ms": 1000      │
│                    │     }                          │
│                    │ }                              │
└────────────────────┴────────────────────────────────┘

Edit Logging Parameters

All of the logging parameters as shown above can be configured via the MaxCtrl command in runtime. For example, we can turn on the log_info by using the "alter logging" command:

maxctrl: alter logging log_info true

Rotate Logs

By default, the MaxScale provides a log rotate configuration file under /etc/logrotate.d/maxscale_logrotate. Based on the log rotation configuration, the log file is rotated monthly and makes use of MaxCtrl's "rotate logs" command. We can force log rotation to happen immediately with the following command:

$ logrotate --force /etc/logrotate.d/maxscale_logrotate

Verify with the following command:

$ ls -al /var/log/maxscale/
total 1544
drwxr-xr-x  2 maxscale maxscale    4096 Sep 21 05:53 ./
drwxrwxr-x 10 root     syslog      4096 Sep 20 06:25 ../
-rw-r--r--  1 maxscale maxscale      75 Sep 21 05:53 maxscale.log
-rw-r--r--  1 maxscale maxscale  253250 Sep 21 05:53 maxscale.log.1
-rw-r--r--  1 maxscale maxscale 1034364 Sep 18 06:25 maxscale.log.2
-rw-r--r--  1 maxscale maxscale  262676 Aug  1 06:25 maxscale.log.3

Conclusion

We have reached the end of the series of MaxScale deployment and management using the MaxCtrl client. Across this blog series, we have used a couple of different latest MaxScale versions (relative to the write-up date) and we have seen many significant improvements in every version. 

Kudos to the MariaDB MaxScale team for their hard work in making MaxScale one of the best database load balancer tools in the market.

ClusterControl
The only management system you’ll ever need to take control of your open source database infrastructure.