blog
MaxScale Basic Management Using MaxCtrl for MariaDB Cluster – Part Two
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. |
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/rulesThen, create a text file:
$ vi /var/lib/maxscale/rules/masking.jsonSpecify 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
OKNote 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-cardA MaxScale restart is required to load the new change:
$ systemctl restart maxscaleTo 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 filtersOr 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
OKHowever, 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 maxscaleOnly then we can remove the Obfuscates-card filter from MaxScale using the “destroy filter” command:
maxctrl: destroy filter Obfuscates-card
OKMaxScale 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
OKTo create an administrator user, specify the –type=admin command:
maxctrl: create user dba mySecret --type=admin
OKDelete a MaxScale User
To delete a user, simply use the “destroy user” command:
maxctrl: destroy user dba
OKThe 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 = falseRestart MaxScale service to load the change:
$ systemctl restart maxscaleSince 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 ubuntu@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 trueRotate 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_logrotateVerify 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.3Conclusion
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.
