Access the complete reference for MariaDB MaxScale. Find detailed documentation on configuration parameters, MaxCtrl commands, routers, monitors,
MaxScale Authenticators
Secure client connections with MaxScale authentication modules. This reference details configuration for Native, PAM, GSSAPI, and Ed25519 plugins to validate user credentials.
MaxScale Module Commands
Learn about special module-specific commands in MaxScale. This guide explains how to list and execute commands using MaxCtrl for modules like authenticators, filters, and monitors.
Module commands
Introduced in MaxScale 2.1, the module commands are special, module-specific commands. They allow the modules to expand beyond the capabilities of the module API. Currently, only MaxCtrl implements an interface to the module commands.
All registered module commands can be shown with maxctrl list commands and they can be executed with maxctrl call command <module> <name> ARGS... whereis the name of the module and is the name of the command.ARGS is a command specific list of arguments.
Developer reference
The module command API is defined in the modulecmd.h header. It consists of various functions to register and call module commands. Read the function documentation in the header for more details.
The following example registers the module command my_command for module_my_module_.
The array my_args of type modulecmd_arg_type_t is used to tell what kinds of arguments the command expects. The first argument is a boolean and the second argument is an optional string.
Arguments are passed to the parsing function as an array of void pointers. They are interpreted as the types the command expects.
When the module command is executed, the argv parameter for the_my_simple_cmd_ contains the parsed arguments received from the caller of the command.
This page is licensed: CC BY-SA / Gnu FDL
#include <maxscale/modulecmd.hh>
bool my_simple_cmd(const MODULECMD_ARG *argv)
{
printf("%d arguments given\n", argv->argc);
}
int main(int argc, char **argv)
{
modulecmd_arg_type_t my_args[] =
{
{MODULECMD_ARG_BOOLEAN, "This is a boolean parameter"},
{MODULECMD_ARG_STRING | MODULECMD_ARG_OPTIONAL, "This is an optional string parameter"}
};
// Register the command
modulecmd_register_command("my_module", "my_command", my_simple_cmd, 2, my_args);
// Find the registered command
const MODULECMD *cmd = modulecmd_find_command("my_module", "my_command");
// Parse the arguments for the command
const void *arglist[] = {"true", "optional string"};
MODULECMD_ARG *arg = modulecmd_arg_parse(cmd, arglist, 2);
// Call the module command
modulecmd_call_command(cmd, arg);
// Free the parsed arguments
modulecmd_arg_free(arg);
return 0;
}
MaxScale Filters
Filters in MariaDB MaxScale intercept and modify database traffic. Use them to transform, block, or log queries, enabling fine-grained control over your database workload and security.
MaxScale Monitors
Ensure high availability with MaxScale Monitors. This reference details monitoring modules for MariaDB replication and Galera clusters, covering failover, role detection, and health checks.
MaxScale REST API
Access the complete reference for the MariaDB MaxScale REST API. Find documentation for all resources, authentication methods, and endpoints for programmatic management.
MaxScale Protocols
Explore the protocol modules available in MariaDB MaxScale. This reference covers configuration for the standard MariaDB protocol, the NoSQL listener, and legacy CDC integration.
MaxScale Routers
Explore the available routing modules in MariaDB MaxScale. This reference covers routers for read-write splitting, connection balancing, sharding, and specialized traffic management.
MaxScale Psreuse Filter
Optimize prepared statement execution. This filter caches prepared statements on the proxy layer reducing the overhead of re-preparing statements on backend servers.
Overview
The psreuse filter reuses identical prepared statements inside the same client connection. This filter only works with binary protocol prepared statements and not with text protocol prepared statements executed with the PREPARE SQL command.
When this filter is enabled and the connection prepares an identical prepared statement multiple times, instead of preparing it on the server the existing prepared statement handle is reused. This also means that whenever prepared statements are closed by the client, they will be left open by readwritesplit.
Enabling this feature will increase memory usage of a session. The amount of memory stored per prepared statement is proportional to the length of the prepared SQL statement and the number of parameters the statement has.
Configuration
To add the filter to a service, define an instance of the filter and then add it to a service's filters list:
Limitations
If the SQL in the prepared statement is larger than 1677723 bytes, the prepared statement will not be cached.
If the same SQL is prepared more than once at the same time, only one of them will succeed. This happens as the prepared statement reuse uses the SQL string in the comparison to detect if a statement is already prepared.
This page is licensed: CC BY-SA / Gnu FDL
MaxScale PARSEC Authenticator
Enable modern, secure authentication with PARSEC. This guide explains the parsecauth module, which uses elliptic curve signatures and salted passwords to prevent replay attacks.
PARSEC Authenticator
The PARSEC (Password Authentication using Response Signed with Elliptic Curve) authentication plugin uses salted passwords, key derivation, extensible password storage format, and both server-side and client-side scrambles.
Similarly to the ed25519 authentication plugin, it signs the response with a ed25519 digital signature, but unlike the ed25519 authentication plugin, it uses the stock unmodified ed25519 as provided by OpenSSL.
This authentication plugin is intended to be used with MariaDB version 12 or later and requires that the service user has the SET USER grant.
MariaDB versions 11.6 or later that include the PARSEC authentication plugin are supported but the passwords for the users must be provided via the . The documentation for the contains an example of how to configure the user mapping.
Configuration
To enable PARSEC authentication on a listener, the authenticator list must include parsecauth.
To only allow PARSEC authentication, use authenticator=parsecauth.
MaxScale Optimistic Transaction Execution Filter
Improve performance for read-heavy transactions. This filter attempts to execute transactions on replicas first falling back to the primary only if a write occurs.
Overview
The optimistictrx filter implements optimistic transaction execution. The filter is designed for a use-case where most of the transactions are read-only and writes happen rarely but each set of read-only statements is still grouped into a read-write transaction (i.e. START TRANSACTION, BEGIN orSET autocommit=0).
This filter will replace the BEGIN and START TRANSACTION SQL commands withSTART TRANSACTION READ ONLY. If the transaction is fully read-only, the transaction completes normally. However, if a write happens in the middle of a transaction, the filter issues a ROLLBACK command and then replays the read-only part of the transaction, including the original BEGIN statement. If the results of the replayed read-only part of the transaction is identical to the one that was returned to the client, the transaction proceeds normally. If the result checksum does not match, the connection is closed to prevent a write with the wrong transaction state from happening.
Configuration
To add the filter to a service, define an instance of the filter and then add it to a service's filters list:
This can also be done at runtime with:
This page is licensed: CC BY-SA / Gnu FDL
MaxScale Readonly
Enforce read-only access at the proxy level. This filter rejects modification statements like INSERT or UPDATE protecting the database from accidental writes.
Overview
This filter prevents writes from being done and returns a read-only error if one is attempted. The error that is returned is ER_CANT_EXECUTE_IN_READ_ONLY_TRANSACTION, error number 1792 with SQLSTATE 25006 and the message Cannot execute statement in a READ ONLY transaction.
Configuration
This filter is an implicit filter and has no configuration parameters and does not need its own configuration section. To enable it, simply add it to the service using the module name.
Limitations
The filter prevents writes in two ways: by parsing the SQL and detecting writes and by setting the default mode of transactions to read-only. The parsing will prevent any writes that are detected from reaching the database and the read-only mode of transactions will prevent writes in stored procedures and user functions as well as act as a backup method in case the parsing is ineffective in detecting a write.
The SET TRANSACTION READ WRITE and START TRANSACTION READ WRITE statements are blocked by this filter. This is done as a safeguard against accidental removal of the read-only transaction mode. However, if the client disables the read-only mode directly via SET tx_read_only=0, the change is not detected by the filter and writes are possible.
Given that the detection of writes is based on parsing and that parsing is not 100% effective in detecting all possible permutations of this statement (e.g. PREPARE and EXECUTE from user variables is not detected), this cannot be used to prevent a mischievous user from being able to turn off the read-only mode.
This page is licensed: CC BY-SA / Gnu FDL
MaxScale GSSAPI Client Authenticator
Integrate MaxScale with Kerberos or Active Directory using GSSAPI. Learn to configure the GSSAPIAuth module, manage keytab files, and set up service principals for secure login.
Overview
GSSAPI is an authentication protocol that is commonly implemented with Kerberos on Unix or Active Directory on Windows. This document describes GSSAPI authentication in MaxScale. The authentication module name in MaxScale SSAPIAuth.
MaxScale MariaDB/MySQL Authenticator
Configure standard native password authentication. This guide covers the MariaDBAuth module, which handles the default mysql_native_password plugin used by MariaDB and MySQL.
Overview
The MariaDBAuth module implements the client and backend authentication for the server plugin mysql_native_password. This is the default authentication plugin used by both MariaDB and MySQL.
MaxScale Change Data Capture (CDC) users
Manage user authentication for the legacy CDC protocol. This reference explains how to create, store, and manage user credentials for clients connecting to the CDC listener.
Overview
Change Data Capture (CDC) is a new MaxScale protocol that allows compatible clients to authenticate and register for Change Data Capture events. The new protocol must be use in conjunction with AVRO router which currently converts MariaDB binlog events into AVRO records. Clients connect to CDC listener and authenticate using credentials provided in a format described in the .
Note: If no users are found in that file or if it doesn't exist, the only available user will be the service user:
MaxScale Cat
Concatenate result sets from multiple backend servers into a single response. This router is useful for querying partitioned data or aggregating results from distributed nodes.
Overview
The cat router is a special router that concatenates result sets.
Note: This module is experimental and must be built from source. The module is deprecated in MaxScale 23.08 and might be removed in a future release.
MaxScale Comment Filter
Inject custom comments into SQL statements. This filter allows administrators to tag queries with metadata for tracking, auditing, or debugging purposes.
Overview
With the comment filter it is possible to define comments that are injected before the actual statements. These comments appear as sql comments when they are received by the server.
For Unix systems, the usual GSSAPI implementation is Kerberos. This is a short guide on how to set up Kerberos for MaxScale.
The first step is to configure MariaDB to use GSSAPI authentication. The MariaDB documentation for the GSSAPI Authentication Plugin is a good example on how to set it up.
The next step is to copy the keytab file from the server where MariaDB is installed to the server where MaxScale is located. The keytab file must be placed in the configured default location which almost always is /etc/krb5.keytab. Alternatively, the keytab filepath can be given as an authenticator option.
The location of the keytab file can be changed with the KRB5_KTNAME environment variable: keytab_def.html
To take GSSAPI authentication into use, add the following to the listener.
The principal name should be the same as on the MariaDB servers.
Settings
principal_name
Type: string
Mandatory: No
Dynamic: No
Default: mariadb/localhost.localdomain
The service principal name to send to the client. This parameter is a string parameter which is used by the client to request the token.
This parameter must be the same as the principal name that the backend MariaDB server uses.
gssapi_keytab_path
Type: path
Mandatory: No
Dynamic: No
Default: Kerberos Default
Keytab file location. This should be an absolute path to the file containing the keytab. If not defined, Kerberos will search from a default location, usually /etc/krb5.keytab. This path is set to an environment variable. This means that multiple listeners with GSSAPIAuth overrides each other. If using multiple GSSAPI authenticators, either do not set this option or use the same value for all listeners.
Implementation Details
Read the Authentication Modules document for more details on how authentication modules work in MaxScale.
GSSAPI authentication
The GSSAPI plugin authentication starts when the database server sends the service principal name in the AuthSwitchRequest packet. The principal name will usually be in the form service@REALM.COM.
The client searches its local cache for a token for the service or may request it from the GSSAPI server. If found, the client sends the token to the database server. The database server verifies the authenticity of the token using its keytab file and sends the final OK packet to the client.
Building the module
The GSSAPI authenticator modules require the GSSAPI development libraries (krb5-devel on CentOS 7).
This page is licensed: CC BY-SA / Gnu FDL
Settings
The following settings may be given in the authenticator_options of the listener.
clear_pw_passthrough
Boolean, default value is false. Activates passthrough mode. In this mode, MaxScale does not check client credentials at all and defers authentication to the backend server. It may be useful in any situation where MaxScale cannot check the existence of client user account nor authenticate the client.
When a client connects to a listener with this setting enabled, MaxScale changes the authentication method to mysql_clear_password, causing the client to send their cleartext password to MaxScale. MaxScale will then attempt to use the password to authenticate to backends. The authentication result of the first backend to respond is sent to the client. The backend may ask MaxScale for either cleartext password or standard (mysql_native_password) authentication token. MaxScale can work with both backend plugins since it has the original password.
This feature is incompatible with service setting lazy_connect. Either leave it unspecified or set lazy_connect=false in the linked service. Also, multiple client authenticators are not allowed on the listener when passthrough-mode is on.
Because passwords are sent in cleartext, the listener should be configured for ssl.
The service setting log_auth_warnings must also be enabled for this setting to have effect. When both settings are enabled, password hashes are logged if a client gives a wrong password. This feature may be useful when diagnosing authentication issues. It should only be enabled on a secure system as the logging of password hashes may be a security risk.
cache_dir
Deprecated and ignored.
inject_service_user
Deprecated and ignored.
This page is licensed: CC BY-SA / Gnu FDL
Creating new CDC users
Starting with MaxScale 2.1, users can also be created through maxctrl:
The should be the service name where the user is created. Older versions of MaxScale should use the cdc_users.py script.
The output of this command should be appended to the cdcusers file at/var/lib/maxscale/<service name>/.
Users can be deleted by removing the related rows in 'cdcusers' file. For more details on the format of the cdcusers file, read the CDC Protocol documentation.
The router has no special parameters. To use it, define a service with router=cat and add the servers you want to use.
Behavior
The order the servers are defined in is the order in which the servers are queried. This means that the results are ordered based on the servers parameter of the service. The result will only be completed once all servers have executed this.
All commands executed via this router will be executed on all servers. This means that an INSERT through the cat router will send it to all servers. In the case of commands that do not return resultsets, the response of the last server is sent to the client. This means that if one of the earlier servers returns a different result, the client will not see it.
As the intended use-case of the router is to mainly reduce multiple result sets into one, it has no mechanisms to prevent writes from being executed on slave servers (which would cause data corruption or replication failure). Take great care when performing administrative operations though this router.
If a connection to one of the servers is lost, the client connection will also be closed.
Example
Here is a simple example service definition that uses the servers from the Configuring Servers tutorial and the credentials from the MaxScale Tutorial.
This page is licensed: CC BY-SA / Gnu FDL
Settings
The Comment filter requires one mandatory parameter to be defined.
inject
Type: string
Mandatory: Yes
Dynamic: Yes
A parameter that contains the comment injected before the statements. There is also defined variable $IP that can be used to comment the IP address of the client in the injected comment. Variables must be written in all caps.
Examples
Example 1 - Inject IP address of the connected client into statements
as comment.
The following configuration adds the IP address of the client to the comment.
In this example when MaxScale receives statement like:
Regulate the rate of incoming queries. This filter prevents database overload by limiting the number of queries processed per second based on defined thresholds.
Overview
The throttle filter is used to limit the maximum query frequency (QPS - queries per second) of a database session to a configurable value. The main use cases are to prevent a rogue session (client side error) and a DoS attack from overloading the system.
The throttling is dynamic. The query frequency is not limited to an absolute value. Depending on the configuration the throttle will allow some amount of high frequency queries, or especially short bursts with no frequency limitation.
Configuration
Basic Configuration
This configuration states that the query frequency will be throttled to around 500 qps, and that the time limit a query is allowed to stay at the maximum frequency is 60 seconds. All values involving time are configured in milliseconds. With the basic configuration the throttling will be nearly immediate, i.e. a session will only be allowed very short bursts of high frequency querying.
When a session has been continuously throttled for throttling_duration milliseconds, or 60 seconds in this example, MaxScale will disconnect the session.
Allowing high frequency bursts
The two parameters max_qps and sampling_duration together define how a session is throttled.
Suppose max qps is 400 qps and sampling duration is 10 seconds. Since QPS is not an instantaneous measure, but one could say it has a granularity of 10 seconds, we see that over the 10 seconds 10*400 = 4000 queries are allowed before throttling kicks in.
With these values, a fresh session can start off with a speed of 2000 qps, and maintain that speed for 2 seconds before throttling starts.
If the client continues to query at high speed and throttling duration is set to 10 seconds, Maxscale will disconnect the session 12 seconds after it started.
Settings
max_qps
Type: number
Mandatory: Yes
Dynamic: Yes
Maximum queries per second.
This is the frequency to which a session will be limited over a given time period. QPS is not measured as an instantaneous value but over a configurable sampling duration (see sampling_duration).
throttling_duration
Type:
Mandatory: Yes
Dynamic: Yes
This defines how long a session is allowed to be throttled before MaxScale disconnects the session.
sampling_duration
Type:
Mandatory: No
Dynamic: Yes
Default: 250ms
Sampling duration defines the window of time over which QPS is measured. This parameter directly affects the amount of time that high frequency queries are allowed before throttling kicks in.
The lower this value is, the more strict throttling becomes. Conversely, the longer this time is, the longer bursts of high frequency querying is allowed.
continuous_duration
Type:
Mandatory: No
Dynamic: Yes
Default: 2s
This value defines what continuous throttling means. Continuous throttling starts as soon as the filter throttles the frequency. Continuous throttling ends when no throttling has been performed in the past continuous_duration time.
This page is licensed: CC BY-SA / Gnu FDL
MaxScale Ed25519 Authenticator
Implement high-security authentication using Ed25519 signatures. This guide explains how to configure the ed25519auth module and handle backend authentication via user mapping.
Overview
Ed25519 is a highly secure authentication method based on public key cryptography. It is used with the auth_ed25519 plugin of MariaDB Server.
When a client authenticates via ed25519, MaxScale first sends them a random message. The client signs the message using their password as private key and sends the signature back. MaxScale then checks the signature using the public key fetched from the mysql.user
MaxScale LDI Filter
Manage LOAD DATA INFILE operations. This filter splits large data loads into smaller chunks to prevent blocking and improve stability during bulk data ingestion.
Overview
The ldi (LOAD DATA INFILE) filter was introduced in MaxScale 23.08.0 and it extends the MariaDB LOAD DATA INFILE syntax to support loading data from any object storage that supports the S3 API. This includes cloud offerings like AWS S3 and Google Cloud Storage as well as locally run services like Minio.
If the filename starts with either S3://
MaxScale Consistent Critical Read Filter
Ensure data consistency by routing reads to the primary server after a write. This filter guarantees that a client sees its own modifications immediately.
Overview
The Consistent Critical Read (CCR) filter allows consistent critical reads to be done through MaxScale while still allowing scaleout of non-critical reads.
When the filter detects a statement that would modify the database, it attaches a routing hint to all following statements done by that connection. This routing hint guides the routing module to route the statement to the primary server where data is guaranteed to be in an up-to-date state. Writes from one session do not, by default, propagate to other sessions.
MaxScale Maxrows Filter
Limit the size of result sets returned to clients. This filter prevents excessive resource consumption by truncating results that exceed a configured row count.
Overview
The Maxrows filter is capable of restricting the amount of rows that a SELECT, a prepared statement or stored procedure could return to the client application.
If a resultset from a backend server has more rows than the configured limit or the resultset size exceeds the configured size, an empty result will be sent to the client.
MaxScale MariaDB Protocol Module
Configure the core MariaDB client-server protocol in MaxScale. Learn about settings for compression, connection redirection, and replication support for standard database listeners.
Overview
The mariadbprotocol module implements the MariaDB client-server protocol.
The legacy protocol names mysqlclient, mariadb
MaxScale Change Data Capture (CDC) Protocol
Learn about the legacy CDC protocol in MaxScale. This guide covers the authentication and registration phases for clients consuming AVRO-formatted replication events.
Overview
The CDC protocol was deprecated in MaxScale 24.08 and will be removed in the next major release. can be used instead.
CDC is a new protocol that allows compatible clients to authenticate and register for Change Data Capture events. The new protocol must be use in conjunction with AVRO router which currently converts MariaDB binlog events into AVRO records. Change Data Capture protocol is used by clients in order to interact with stored AVRO file and also allows registered clients to be notified with the new events coming from MariaDB 10.0/10.1 database.
, the path is interpreted as a S3 object file. The prefix is case-insensitive. For example, the following command would load the file
my-data.csv
from the bucket
my-bucket
into the table
t1
.
How to Upload Data
Here is a minimal configuration for the filter that can be used to load data from AWS S3:
The first step is to move the file to be loaded into the same region that MaxScale and the MariaDB servers are in. One factor in the speed of the upload is the network latency and minimizing it by moving the source and the destination closer improves the data loading speed.
The next step is to connect to MaxScale and prepare the session for an upload by providing the service account access and secret keys.
Once the credentials are configured, the data loading can be started:
Data Uploads with MariaDB Xpand
This feature has been removed in MaxScale 24.02.
Common Problems With Data Loading
Missing Files
If you are using self-hosted object storage programs like Minio, a common problem is that they do not necessarily support the newer virtual-hosted-style requests that is used by AWS. This usually manifests as an error either about a missing file or a missing bucket.
If the host parameter is set to a hostname, it's assumed that the object storage supports the newer virtual-hosted-style requests. If this not the case, the filter must be configured with protocol_version=1.
Conversely, if the host parameter is set to a plain IP address, it is assumed that it does not support the newer virtual-hosted-style request. If the host does support it, the filter must be configured with protocol_version=2.
Settings
key
Type: string
Mandatory: No
Dynamic: Yes
The S3 access key used to perform all requests to it.
This must be either configured in the MaxScale configuration file or set withSET @maxscale.ldi.s3_key='<key>' before starting the data load.
secret
Type: string
Mandatory: No
Dynamic: Yes
The S3 secret key used to perform all requests to it.
This must be either configured in the MaxScale configuration file or set withSET @maxscale.ldi.s3_secret='<secret>' before starting the data load.
region
Type: string
Mandatory: No
Dynamic: Yes
Default: us-east-1
The S3 region where the data is located.
The value can be overridden with SET @maxscale.ldi.s3_region='<region>' before starting the data load.
host
Type: string
Mandatory: No
Dynamic: Yes
Default: s3.amazonaws.com
The location of the S3 object storage. By default the original AWS S3 host is used. The corresponding value for Google Cloud Storage isstorage.googleapis.com.
The value can be overridden with SET @maxscale.ldi.s3_host='<host>' before starting the data load.
port
Type: integer
Mandatory: No
Dynamic: Yes
Default: 0
The port on which the S3 object storage is listening. If unset or set to the value of 0, the default S3 port is used.
The value can be overridden with SET @maxscale.ldi.s3_port=<port> before starting the data load. Note that unlike the other values, the value for this variable must be an SQL integer and not an SQL string.
If set to true, communication with the object storage is done unencrypted using HTTP instead of HTTPS.
protocol_version
Type: integer
Mandatory: No
Dynamic: Yes
Default: 0
Values: 0, 1, 2
Which protocol version to use. By default the protocol version is derived from the value of host but this automatic protocol version deduction will not always produce the correct result. For the legacy path-style requests used by older S3 storage buckets, the value must be set to 1. All new buckets use the protocol version 2.
For object storage programs like Minio, the value must be set to 1 as the bucket name cannot be resolved via the subdomain like it is done for object stores in the cloud.
import_user
This parameter has been removed in MaxScale 24.02.
import_password
This parameter has been removed in MaxScale 24.02.
This page is licensed: CC BY-SA / Gnu FDL
Note:
This filter does not work with prepared statements. Only text protocol queries are handled by this filter.
Controlling the Filter with SQL Comments
The triggering of the filter can be limited further by adding MaxScale supported comments to queries and/or by using regular expressions. The query comments take precedence: if a comment is found it is obeyed even if a regular expression parameter might give a different result. Even a comment cannot cause a SELECT-query to trigger the filter. Such a comment is considered an error and ignored.
The comments must follow the MaxScale hint syntax and the HintFilter needs to be in the filter chain before the CCR-filter. If a query has a MaxScale supported comment line which defines the parameter ccr, that comment is caught by the CCR-filter. Parameter values match and ignore are supported, causing the filter to trigger (match) or not trigger (ignore) on receiving the write query. For example, the query
would normally cause the filter to trigger, but does not because of the comment. The match-comment typically has no effect, since write queries by default trigger the filter anyway. It can be used to override an ignore-type regular expression that would otherwise prevent triggering.
The time window during which queries are routed to the primary. The duration value will always be rounded to the nearest second. If no explicit unit has been specified, the value is interpreted as seconds in MaxScale 2.4. In subsequent versions a value without a unit may be rejected. The default value for this parameter is 60 seconds.
When a data modifying SQL statement is processed, a timer is set to the value time. Once the timer has elapsed, all statements are routed normally. If a new data modifying SQL statement is processed within the time window, the timer is reset to the value of time.
Enabling this parameter in combination with the count parameter causes both the time window and number of queries to be inspected. If either of the two conditions are met, the query is re-routed to the primary.
count
Type: count
Mandatory: No
Dynamic: Yes
Default: 0
The number of SQL statements to route to primary after detecting a data modifying SQL statement. This feature is disabled by default.
After processing a data modifying SQL statement, a counter is set to the value of count and all statements are routed to the primary. Each executed statement after a data modifying SQL statement cause the counter to be decremented. Once the counter reaches zero, the statements are routed normally. If a new data modifying SQL statement is processed, the counter is reset to the value of count.
These regular expression settings control which statements trigger statement re-routing. Only non-SELECT statements are inspected. For CCRFilter, the exclude-parameter is instead named ignore, yet works similarly.
global is a boolean parameter that when enabled causes writes from one connection to propagate to all other connections. This can be used to work around cases where one connection writes data and another reads it, expecting the write done by the other connection to be visible.
This parameter only works with the time parameter. The use of global andcount at the same time is not allowed and will be treated as an error.
Example Configuration
Here is a minimal filter configuration for the CCRFilter which should solve most problems with critical reads after writes.
With this configuration, whenever a connection does a write, all subsequent reads done by that connection will be forced to the primary for 5 seconds.
This prevents read scaling until the modifications have been replicated to the replicas. For best performance, the value of time should be slightly greater than the actual replication lag between the primary and its replicas. If the number of critical read statements is known, the count parameter could be used to control the number reads that are sent to the primary.
This page is licensed: CC BY-SA / Gnu FDL
Configuration
The Maxrows filter is easy to configure and to add to any existing service.
Settings
The Maxrows filter has no mandatory parameters. Optional parameters are:
max_resultset_rows
Type: number
Mandatory: No
Dynamic: Yes
Default: (no limit)
Specifies the maximum number of rows a resultset can have in order to be returned to the user.
If a resultset is larger than this an empty result will be sent instead.
Specifies the maximum size a resultset can have in order to be sent to the client. A resultset larger than this, will not be sent: an empty resultset will be sent instead.
Specifies what the filter sends to the client when the rows or size limit is hit, possible values:
an empty result set
an error packet with input SQL
an OK packet
Example output with ERR packet:
debug
Type: number
Mandatory: No
Dynamic: Yes
Default: 0
An integer value, using which the level of debug logging made by the Maxrows filter can be controlled. The value is actually a bitfield with different bits denoting different logging.
0 (0b00000) No logging is made.
1 (0b00001) A decision to handle data form server is logged.
2 (0b00010) Reached max_resultset_rows or max_resultset_size is logged.
To log everything, give debug a value of 3.
Example Configuration
Here is an example of filter configuration where the maximum number of returned rows is 10000 and maximum allowed resultset size is 256KB
This page is licensed: CC BY-SA / Gnu FDL
and
mariadbclient
are all aliases to
mariadbprotocol
.
Connection Redirection
The Connection Redirection introduced in MariaDB 11.4 allows client connections to be redirected to another server if the server in question is going into maintenance. As MaxScale is intended to be the gateway through which clients connect to the database cluster, the use of redirect_url directly on the backend database servers poses some challenges when used with MaxScale.
To prevent the accidental redirection of clients away from MaxScale, the notification about the change of the system variable redirect_url is intercepted by MaxScale and renamed into mxs_rdir_url. This prevents any automated redirects from taking place while still allowing clients to see the information if they need it.
To redirect clients away from MaxScale, use the redirect_url parameter.
Configuration
Protocol level parameters are defined in the listeners. They must be defined using the scoped parameter syntax where the protocol name is used as the prefix.
For the MariaDB protocol module, the prefix is always mariadbprotocol.
Whether the use of the replication protocol is allowed through this listener. If disabled with mariadbprotocol.allow_replication=false, all attempts to start replication will be rejected with a ER_FEATURE_DISABLED error (error number 1289).
The set of enabled compression protocols. The zlib compression protocol is the traditional MySQL/MariaDB zlib based compression algorithm that uses a fixed compression level. The zstd is the newer compression protocol that allows the compression level to be configured by the client.
To disable protocol level compression, set mariadbprotocol.compression=none.
In MariaDB and MySQL, the upper limit for uncompressed payloads is always 50 bytes. If the response is smaller than that, the result is sent uncompressed and if it's larger than that, it is sent compressed. MaxScale allows this limit to be configured so that larger results can be sent uncompressed.
For example, with mariadbprotocol.compression_threshold=256Ki, only events that are larger than 256 kibibytes get compressed. Most small resultsets thus will be sent uncompressed but larger ones will be compressed before being sent. This makes it possible to customize the bandwidth usage of MaxScale when protocol level compression is used.
This page is licensed: CC BY-SA / Gnu FDL
Creating Users
The users and their hashed passwords are stored in /var/cache/maxscale/<service name>/cdcusers where <service name> is the name of the service.
For example, the following service entry will look into /var/cache/maxscale/CDC-Service/ for a file called cdcusers. If that file is found, the users in that file will be used for authentication.
If the cdcusers file cannot be found, the service user (maxuser:maxpwd in the example) can be used to connect through the CDC protocol.
Client connects to MaxScale CDC protocol listener.
Send the authentication message which includes the user and the SHA1 of the password
In the future, optional flags could be implemented.
Registration
Sending UUID
Specify the output format (AVRO or JSON) for data retrieval.
Data Request
Send CDC commands to retrieve router statistics or to query for data events
Protocol Details
Authentication
The authentication starts when the client sends the hexadecimal representation of the username concatenated with a colon (:) and the SHA1 of the password.
bin2hex(username + ':' + SHA1(password))
For example the user foobar with a password of foopasswd should send the following hexadecimal string
Server returns OK on success and ERR on failure.
Registration
REGISTER
REGISTER UUID=UUID, TYPE={JSON | AVRO}
Register as a client to the service.
Example:
Server returns OK on success and ERR on failure.
Change Data Capture Commands
REQUEST-DATA
REQUEST-DATA DATABASE.TABLE[.VERSION] [GTID]
This command fetches data from specified table in a database and returns the output in the requested format (AVRO or JSON). Data records are sent to clients and if new AVRO versions are found (e.g. mydb.mytable.0000002.avro) the new schema and data will be sent as well.
The data will be streamed until the client closes the connection.
Clients should continue reading from network in order to automatically gets new events.
Example:
Example Client
MaxScale includes an example CDC client application written in Python 3. You can find the source code for it in the MaxScale repository.
table. The client password or an equivalent token is never exposed. For more information, see the
documentation.
The security of this authentication scheme presents a problem for a proxy such as MaxScale since MaxScale needs to log in to backend servers on behalf of the client. Since each server generates their own random messages, MaxScale cannot simply forward the original signature. Either the real password is required, or a different authentication scheme must be used between MaxScale and backends. The MaxScale ed25519auth plugin supports both alternatives.
Configuration
To begin, add ed25519auth to the list of authenticators for a listener.
MaxScale now authenticates incoming clients with ed25519 if their user account has plugin set to ed25519 in the mysql.user table. However, routing queries will fail since MaxScale cannot authenticate to backends. To continue, either use a mapping file or enable sha256 mode. Sha256 mode is enabled with the following settings.
ed_mode
This setting defines the authentication mode used. Two values are supported:
ed25519 (default) Digital signature based authentication. Requires mapping for backend support.
sha256 Authenticate client with caching_sha2_password plugin instead. Requires either SSL or configured RSA keys.
ed_rsa_privkey_path and ed_rsa_pubkey_path
Defines the RSA keys used for encrypting the client password if SSL is not in use. Should point to files with the private and public keys.
Using a Mapping File
To enable MaxScale to authenticate to backends, user mapping can be used. The mapping and backend passwords are given in a JSON file. The client can map to an identical username or to another user, and the backend authentication scheme can be something else than ed25519.
The following example maps user "alpha" to "beta" and MaxScale then uses standard authentication to log into backends as "beta". User "alpha" authenticates to MaxScale using whatever method configured in the server. User "gamma" does not map to another user, just the password is given.
MaxScale configuration:
/home/joe/mapping.json:
Using sha256 Authentication
The mapping-based solution requires the DBA to maintain a file with user passwords, which has security and upkeep implications. To avoid this, MaxScale can instead use the caching_sha2_password plugin to authenticate the client. This authentication scheme transmits the client password to MaxScale in full, allowing MaxScale to log into backends using ed25519. MaxScale effectively lies to the client about its authentication plugin and then uses the correct plugin with the backends. Enable sha256 authentication by setting authentication option ed_mode to sha256.
Sha256 authentication is best used with encrypted connections. The example below shows a listener configured for sha256 mode and SSL.
If SSL is not in use, caching_sha2_password transmits the password using RSA-encryption. In this case, MaxScale needs the public and private RSA-keys. MaxScale sends the public key to the client if they don't already have it and the client uses it to encrypt the password. MaxScale then uses the private key to decrypt the password. The example below shows a listener configured for sha256 mode without SSL.
The key files can be generated with OpenSSL using the following commands.
This page is licensed: CC BY-SA / Gnu FDL
MaxScale Binlog Filter
Selectively replicate binary log events to replica servers. This filter uses regex matching to include or exclude specific events from the replication stream.
Overview
The binlogfilter can be combined with a binlogrouter service to selectively replicate the binary log events to replica servers.
The filter uses two settings, match and exclude, to determine which events are replicated. If a binlog event does not match or is excluded, the event is replaced with an empty data event. The empty event is always 35 bytes which translates to a space reduction in most cases.
When statement-based replication is used, any query events that are filtered out are replaced with a SQL comment. This causes the query event to do nothing and thus the event will not modify the contents of the database. The GTID position of the replicating database will still advance which means that downstream servers replicating from it keep functioning correctly.
The filter works with both row based and statement based replication but we recommend using row based replication with the binlogfilter. This guarantees that there are no ambiguities in the event filtering.
Settings
match
Type:
Mandatory: No
Dynamic: Yes
Default: None
Include queries that match the regex. See next entry, exclude, for more information.
exclude
Type:
Mandatory: No
Dynamic: Yes
Default: None
Exclude queries that match the regex.
If neither match nor exclude are defined, the filter does nothing and all events are replicated. This filter does not accept regular expression options as a separate setting, such settings must be defined in the patterns themselves. See the for more information.
The two settings are matched against the database and table name concatenated with a period. For example, the string the patterns are matched against for the database test and table t1 is test.t1.
For statement based replication, the pattern is matched against all the tables in the statements. If any of the tables matches the match pattern, the event is replicated. If any of the tables matches the exclude pattern, the event is not replicated.
rewrite_src
Type:
Mandatory: No
Dynamic: Yes
Default: None
See the next entry, rewrite_dest, for more information.
rewrite_dest
Type:
Mandatory: No
Dynamic: Yes
Default: None
rewrite_src and rewrite_dest control the statement rewriting of the binlogfilter. The rewrite_src setting is a PCRE2 regular expression that is matched against the default database and the SQL of statement based replication events (query events). rewrite_dest is the replacement string which supports the normal PCRE2 backreferences (e.g the first capture group is $1, the second is $2, etc.).
Both rewrite_src and rewrite_dest must be defined to enable statement rewriting.
When statement rewriting is enabled must be used. The filter will disallow replication for all replicas that attempt to replicate with traditional file-and-position based replication.
The replacement is done both on the default database as well as the SQL statement in the query event. This means that great care must be taken when defining the rewriting rules. To prevent accidental modification of the SQL into a form that is no longer valid, use database and table names that never occur in the inserted data and is never used as a constant value.
Example Configuration
With the following configuration, only events belonging to database customers are replicated. In addition to this, events for the table orders are excluded and thus are not replicated.
For more information about the binlogrouter and how to use it, refer to the .
This page is licensed: CC BY-SA / Gnu FDL
MaxScale Hintfilter
Control query routing using SQL comments. This filter interprets hints embedded in queries to direct traffic to specific servers, primaries, or replicas.
Overview
Hint Syntax
MaxScale Role Resource
Define and assign access roles. This resource allows you to manage permissions for MaxScale administrative users, controlling their access to API resources.
Admin roles represent a set of permissions that define which operations are allowed on the REST-API.
Resource Operations
MaxScale Regex Filter
Rewrite or block queries using regular expressions. This versatile filter modifies SQL statements on the fly or rejects them based on pattern matching rules.
Overview
The Regex filter is a filter module for MariaDB MaxScale that is able to rewrite query content using regular expression matches and text substitution. The regular expressions use the .
PCRE2 library uses a different syntax than POSIX to refer to capture groups in the replacement string. The main difference is the usage of the dollar character instead of the backslash character for references e.g. $1 instead of\1
Note: If a query has more than one comment only the first comment is processed. Always place any MaxScale related comments first before any other comments that might appear in the query.
Comments and comment types
The client connection will need to have comments enabled. For example the mariadb and mysql command line clients have comments disabled by default and they need to be enabled by passing the --comments or -c option to it. Most, if not all, connectors keep all comments intact in executed queries.
For comment types, use either -- (notice the whitespace after the double hyphen) or # after the semicolon or /* ... */ before the semicolon.
Inline comment blocks, i.e. /* .. */, do not require a whitespace character after the start tag or before the end tag but adding the whitespace is advised.
Hint body
All hints must start with the maxscale tag.
The hints have two types, ones that define a server type and others that contain name-value pairs.
Routing destination hints
These hints will instruct the router to route a query to a certain type of a server.
Route to primary
A master value in a routing hint will route the query to a primary server. This can be used to direct read queries to a primary server for a up-to-date result with no replication lag.
Route to replica
A slave value will route the query to a replica server. Please note that the hints will override any decisions taken by the routers which means that it is possible to force writes to a replica server.
Route to named server
A server value will route the query to a named server. The value of<server name> needs to be the same as the server section name in maxscale.cnf. If the server is not used by the service, the hint is ignored.
Route to last used server
A last value will route the query to the server that processed the last query. This hint can be used to force certain queries to be grouped to the same server.
Name-value hints
These control the behavior and affect the routing decisions made by the router. Currently the only accepted parameter is the readwritesplit parametermax_slave_replication_lag. This will route the query to a server with a lower replication lag than this parameter's value.
Hint stack
Hints can be either single-use hints, which makes them affect only one query, or named hints, which can be pushed on and off a stack of active hints.
Defining named hints:
Pushing a hint onto the stack:
Popping the topmost hint off the stack:
You can define and activate a hint in a single command using the following:
You can also push anonymous hints onto the stack which are only used as long as they are on the stack:
Prepared Statements
The hintfilter supports routing hints in prepared statements for both thePREPARE and EXECUTE SQL commands as well as the binary protocol prepared statements.
Binary Protocol
With binary protocol prepared statements, a routing hint in the prepared statement is applied to the execution of the statement but not the preparation of it. The preparation of the statement is routed normally and is sent to all servers.
For example, when the following prepared statement is prepared with the MariaDB Connector-C function mariadb_stmt_prepare and then executed withmariadb_stmt_execute the result is always returned from the primary:
Support for binary protocol prepared statements was added in MaxScale 6.0 (MXS-2838).
The protocol commands that the routing hints are applied to are:
COM_STMT_EXECUTE
COM_STMT_BULK_EXECUTE
COM_STMT_SEND_LONG_DATA
COM_STMT_FETCH
COM_STMT_RESET
Support for direct execution of prepared statements was added in MaxScale 6.2.0. For example the MariaDB Connector-C uses direct execution whenmariadb_stmt_execute_direct is used.
Text Protocol
Text protocol prepared statements (i.e. the PREPARE and EXECUTE SQL commands) behave differently. If a PREPARE command has a routing hint, it will be routed according to the routing hint. Any subsequent EXECUTE command will not be affected by the routing hint in the PREPARE statement. This means they must have their own routing hints.
The following example is the recommended method of executing text protocol prepared statements with hints:
The PREPARE is routed normally and will be routed to all servers. TheEXECUTE will be routed to the primary as a result of it having the route to master hint.
Examples
Routing SELECT queries to primary
In this example, MariaDB MaxScale is configured with the readwritesplit router and the hint filter.
Behind MariaDB MaxScale is a primary server and a replica server. If there is replication lag between the primary and the replica, read queries sent to the replica might return old data. To guarantee up-to-date data, we can add a routing hint to the query.
The first INSERT query will be routed to the primary. The following SELECT query would normally be routed to the replica but with the added routing hint it will be routed to the primary. This way we can do an INSERT and a SELECT right after it and still get up-to-date data.
This page is licensed: CC BY-SA / Gnu FDL
Get role
Get a single role. The :name in the URI must be a valid role name.
Response
Status: 200 OK
Get all roles
Get all roles.
Response
Status: 200 OK
Create a role
Create a new role. The request body must define the following fields.
data.id
The role name
data.attributes.permissions
A JSON array of strings that define the permissions. Any permissions that are unknown are stored as extra user-defined permissions that are available in the /roles endpoint. These extra permissions can then be used by external systems or as a way to label account types.
The supported permissions are:
admin: Access to the administrative endpoints /users and /roles which are used to create new user accounts and roles.
edit: Write access to all endpoints that create objects except the administrative endpoints /users and /roles. This permission is required for creating, modifying or destroying objects via the REST-API.
sql: Read-only access to the /maxscale, /servers, /services and /listeners endpoints as well as full permissions on the /sql endpoint. This permission is needed by the Query Editor feature.
view: Read-only access to all endpoints except the administrative endpoints /users and /roles. This permission is required for most read-only operations in the GUI.
Here is an example request that defines a new role my-role that can view and edit objects but cannot use the Query Editor.
Response
Update a role
Update a role. Only the data.attributes.permissions field can be modified. Modifying a role requires administrative privileges.
Here is an example request body that updates the permissions of a role.
Response
Delete a role
The :name part of the URI must be a valid role name.
Response
This page is licensed: CC BY-SA / Gnu FDL
. For more details about the replacement string differences, please read the
chapter in the PCRE2 manual.
Configuration
The following demonstrates a minimal configuration.
Settings
The Regex filter has two mandatory parameters: match and replace.
The options-parameter affects how the patterns are compiled as usual.
replace
Type: string
Mandatory: Yes
Dynamic: Yes
This is the text that should replace the part of the SQL-query matching the pattern defined in match.
source
Type: string
Mandatory: No
Dynamic: Yes
Default: None
The optional source parameter defines an address that is used to match against the address from which the client connection to MariaDB MaxScale originates. Only sessions that originate from this address will have the match and replacement applied to them.
user
Type: string
Mandatory: No
Dynamic: Yes
Default: None
The optional user parameter defines a username that is used to match against the user from which the client connection to MariaDB MaxScale originates. Only sessions that are connected using this username will have the match and replacement applied to them.
log_file
Type: string
Mandatory: No
Dynamic: Yes
Default: None
The optional log_file parameter defines a log file in which the filter writes all queries that are not matched and matching queries with their replacement queries. All sessions will log to this file so this should only be used for diagnostic purposes.
log_trace
Type: string
Mandatory: No
Dynamic: Yes
Default: None
The optional log_trace parameter toggles the logging of non-matching and matching queries with their replacements into the log file on the info level. This is the preferred method of diagnosing the matching of queries since the log level can be changed at runtime. For more details about logging levels and session specific logging, please read the Configuration Guide.
Examples
Example 1 - Replace MySQL 5.1 create table syntax with that for later versions
MySQL 5.1 used the parameter TYPE = to set the storage engine that should be used for a table. In later versions this changed to be ENGINE =. Imagine you have an application that you cannot change for some reason, but you wish to migrate to a newer version of MySQL. The regexfilter can be used to transform the create table statements into the form that could be used by MySQL 5.5
# The --comments flag is needed for the command line client
mariadb --comments -u my-user -psecret -e "SELECT @@hostname -- maxscale route to server db1"
-- maxscale <hint body>
-- maxscale route to [master | slave | server <server name>]
-- maxscale route to master
-- maxscale route to slave
-- maxscale route to server <server name>
-- maxscale route to last
-- maxscale <param>=<value>
-- maxscale <hint name> prepare <hint content>
-- maxscale <hint name> begin
-- maxscale end
-- maxscale <hint name> begin <hint content>
-- maxscale begin <hint content>
SELECT user FROM accounts WHERE id = ? -- maxscale route to master
PREPARE my_ps FROM 'SELECT user FROM accounts WHERE id = ?';
EXECUTE my_ps USING 123; -- maxscale route to master
Intelligently route queries based on workload type. SmartRouter directs transactional queries to MariaDB and analytical queries to column-store engines for hybrid processing.
Overview
SmartRouter is the query router of the SmartQuery framework. Based on the type of the query, each query is routed to the server or cluster that can best handle it.
For workloads where both transactional and analytical queries are needed, SmartRouter unites the Transactional (OLTP) and Analytical (OLAP) workloads into a single entry point in MaxScale. This allows a MaxScale client to freely mix transactional and analytical queries using the same connection. This is known as Hybrid Transactional and Analytical Processing, HTAP.
Settings
SmartRouter is configured as a service that either routes to other MaxScale routers or plain servers. Although one can configure SmartRouter to use a plain server directly, we refer to the configured "servers" as clusters.
For details about the standard service parameters, refer to the .
master
Type: target
Mandatory: Yes
Dynamic: No
One of the clusters must be designated as the master. All writes go to the primary cluster, which for all practical purposes should be a primary-replica ReadWriteSplit. This document does not go into details about setting up primary-replica clusters, but suffice to say, that when setting up the ColumnStore servers they should be configured to be replicas of a MariaDB server running an InnoDB engine. The ReadWriteSplit has more on primary-replica setup.
Example
Suppose we have a Transactional service like
for which we have defined the listener
That is, that service can be accessed using the socket /tmp/rws-row.sock.
The Analytical service could look like this
Then we can define the SmartQuery service as follows
Note that the SmartQuery listener listens on a port, while the Row and Column service listeners listen on Unix domain sockets. The reason is that there is a significant performance benefit when SmartRouter accesses the services over a Unix domain socket compared to accessing them over a TCP/IP socket.
A complete configuration example can be found at the end of this document.
Cluster selection - how queries are routed
SmartRouter keeps track of the performance, or the execution time, of queries to the clusters. Measurements are stored with the canonical of a query as the key. The canonical of a query is the sql with all user-defined constants replaced with question marks. When SmartRouter sees a read-query whose canonical has not been seen before, it will send the query to all clusters. The first response from a cluster will designate that cluster as the best one for that canonical. Also, when the first response is received, the other queries are cancelled. The response is sent to the client once all clusters have responded to the query or the cancel.
There is obviously overhead when a new canonical is seen. This means that queries after a MaxScale start will be slightly slower than normal. The execution time of a query depends on the database engine, and on the contents of the tables being queried. As a result, MaxScale will periodically re-measure queries.
The performance behavior of queries under dynamic conditions, and their effect on different storage engines is being studied at MariaDB. As we learn more, we will be able to better categorize queries and move that knowledge into SmartRouter.
Limitations
LOAD DATA LOCAL INFILE is not supported.
The performance data is not persisted. The measurements will be performed anew after each startup.
Complete configuration example
This page is licensed: CC BY-SA / Gnu FDL
MaxScale Top Filter
Identify the most expensive queries. This filter logs the top N slowest queries passing through MaxScale helping to pinpoint performance bottlenecks.
Overview
The top filter is a filter module for MariaDB MaxScale that monitors every SQL statement that passes through the filter. It measures the duration of that statement, the time between the statement being sent and the first result being returned. The top N times are kept, along with the SQL text itself and a list sorted on the execution times of the query is written to a file upon closure of the client session.
MaxScale KafkaImporter
Import data from Apache Kafka into MariaDB. This router consumes messages from Kafka topics and applies them as transactions to your database tables.
Overview
The KafkaImporter module reads messages from Kafka and streams them into a MariaDB server. The messages are inserted into a table designated by either the topic name or the message key (see for details). By default the table will be automatically created with the following SQL:
The payload of the message is inserted into the data field from which the id
MaxScale Admin User Resource
Manage administrative access to MaxScale. Learn to create, update, and delete admin users and manage their credentials for the REST API and MaxCtrl.
Overview
Admin users represent administrative users that are able to query and change MaxScale's configuration.
MaxScale Named Server Filter
Route queries to specific servers based on regex patterns. This filter allows you to direct traffic matching defined rules to a particular backend server by name.
Overview
The namedserverfilter is a MariaDB MaxScale filter module able to route queries to servers based on regular expression (regex) matches. Since it is a filter instead of a router, the NamedServerFilter only sets routing suggestions. It requires a compatible router to be effective. Currently, bothreadwritesplit and hintrouter take advantage of routing hints in the data packets. This filter uses the PCRE2 library for regular expression matching.
MaxScale Readconnroute
Balance connections across backend servers. This lightweight router distributes new client connections to available nodes without inspecting individual SQL statements.
Overview
This document provides an overview of the readconnroute router module and its intended use case scenarios. It also displays all router configuration parameters with their descriptions.
The readconnroute router provides simple and lightweight load balancing across a set of servers. The router can also be configured to balance connections based on a weighting parameter defined in the server's section.
The top filter has one mandatory parameter, filebase, and a number of optional parameters.
filebase
Type: string
Mandatory: Yes
Dynamic: Yes
The basename of the output file created for each session. The session ID is added to the filename for each file written. This is a mandatory parameter.
The filebase may also be set as the filter, the mechanism to set the filebase via the filter option is superseded by the parameter. If both are set the parameter setting will be used and the filter option ignored.
count
Type: number
Mandatory: No
Dynamic: Yes
Default: 10
The number of SQL statements to store and report upon.
Defines an address that is used to match against the address from which the client connection to MariaDB MaxScale originates. Only sessions that originate from this address will be logged.
user
Type: string
Mandatory: No
Dynamic: Yes
Default: None
Defines a username that is used to match against the user from which the client connection to MariaDB MaxScale originates. Only sessions that are connected using this username will result in results being generated.
Examples
Example 1 - Heavily Contended Table
You have an order system and believe the updates of the PRODUCTS table is causing some performance issues for the rest of your application. You would like to know which of the many updates in your application is causing the issue.
Add a filter with the following definition:
Note the exclude entry, this is to prevent updates to the PRODUCTS_STOCK table from being included in the report.
Example 2 - One Application Server is Slow
One of your applications servers is slower than the rest, you believe it is related to database access but you are not sure what is taking the time.
Add a filter with the following definition:
In order to produce a comparison with an unaffected application server you can also add a second filter as a control.
In the service definition add both filters
You will then have two sets of logs files written, one which profiles the top 20 queries of the slow application server and another that gives you the top 20 queries of your control application server. These two sets of files can then be compared to determine what if anything is different between the two.
Output Report
The following is an example report for a number of fictitious queries executed against the employees example database available for MySQL.
This page is licensed: CC BY-SA / Gnu FDL
field is calculated. The payload must be a valid JSON object and it must either contain a unique
_id
field or it must not exist or the value must be a JSON null. This is similar to the MongoDB document format where the
_id
field is the primary key of the document collection.
If a message is read from Kafka and the insertion into the table fails due to a violation of one of the constraints, the message is ignored. Similarly, messages with duplicate _id value are also ignored: this is done to avoid inserting the same document multiple times whenever the connection to either Kafka or MariaDB is lost.
The limitations on the data can be removed by either creating the table before the KafkaImporter is started, in which case the CREATE TABLE IF NOT EXISTS does nothing, or by altering the structure of the existing table. The minimum requirement that must be met is that the table contains the data field to which string values can be inserted into.
The database server where the data is inserted is chosen from the set of servers available to the service. The first server labeled as the Master with the best rank will be chosen. This means that a monitor must be configured for the MariaDB server where the data is to be inserted.
In MaxScale versions 21.06.18, 22.08.15, 23.02.12, 23.08.8, 24.02.4 and 25.01.1 the _id field is not required to be present. Older versions of MaxScale used the following SQL where the _id field was mandatory:
Required Grants
The user defined by the user parameter of the service must have INSERT andCREATE privileges on all tables that are created.
Settings
bootstrap_servers
Type: string
Mandatory: Yes
Dynamic: Yes
The list of Kafka brokers as a CSV list in host:port format.
topics
Type: stringlist
Mandatory: Yes
Dynamic: Yes
The comma separated list of topics to subscribe to.
batch_size
Type: count
Mandatory: No
Dynamic: Yes
Default: 100
Maximum number of uncommitted records. The KafkaImporter will buffer records into batches and commit them once either enough records are gathered (controlled by this parameter) or when the KafkaImporter goes idle. Any uncommitted records will be read again if a reconnection to either Kafka or MariaDB occurs.
The Kafka message part that is used to locate the table to insert the data into.
Enumeration Values:
topic: The topic named is used as the fully qualified table name.
key: The message key is used as the fully qualified table name. If the Kafka message does not have a key, the message is ignored.
For example, all messages with a fully qualified table name of my_db.my_table will be inserted into the table my_table located in the my_db database. If the table or database names have special characters that must be escaped to make them valid identifiers, the name must also contain those escape characters. For example, to insert into a table named my table in the database my database, the name would be:
Timeout for both Kafka and MariaDB network communication.
engine
Type: string
Default: InnoDB
Mandatory: No
Dynamic: Yes
The storage engine used for tables that are created by the KafkaImporter.
This defines the ENGINE table option and must be the name of a valid storage engine in MariaDB. When the storage engine is something other than InnoDB, the table is created without the generated column and the check constraints:
This is done to avoid conflicts where the custom engine does not support all the features that InnoDB supports.
Limitations
The backend servers used by this service must be MariaDB version 10.2 or newer.
The filter accepts settings in two modes: legacy and indexed. Only one of the modes may be used for a given filter instance. The legacy mode is meant for backwards compatibility and allows only one regular expression and one server name in the configuration. In indexed mode, up to 25 regex-server pairs are allowed in the form match01 - target01, match02 - target02 and so on. Also, in indexed mode, the server names (targets) may contain a list of names or special tags ->master or ->slave.
All parameters except the deprecated match and target parameters can be modified at runtime. Any modifications to the filter configuration will only affect sessions created after the change has completed.
Below is a configuration example for the filter in indexed-mode. The legacy mode is not recommended and may be removed in a future release. In the example, a SELECT on TableOne (match01) results in routing hints to two named servers, while a SELECT on TableTwo is suggested to be routed to the primary server of the service. Whether a list of server names is interpreted as a route-to-any or route-to-all is up to the attached router. The HintRouter sees a list as a suggestion to route-to-any. For additional information on hints and how they can also be embedded into SQL-queries, see Hint-Syntax.
Settings
NamedServerFilter requires at least one matchXY - targetXY pair.
matchXY defines a PCRE2 regular expression against which the incoming SQL query is matched. XY must be a number in the range 01 - 25. Each match-setting pairs with a similarly indexed target-setting. If one is defined, the other must be defined as well. If a query matches the pattern, the filter attaches a routing hint defined by the target-setting to the query. The options-parameter affects how the patterns are compiled.
The hint which is attached to the queries matching the regular expression defined by matchXY. If a compatible router is used in the service the query will be routed accordingly. The target can be one of the following:
a server or service name (adds a HINT_ROUTE_TO_NAMED_SERVER hint)
a list of server names, comma-separated (adds severalHINT_ROUTE_TO_NAMED_SERVER hints)
->master (adds a HINT_ROUTE_TO_MASTER hint)
->slave (adds a HINT_ROUTE_TO_SLAVE hint)
->all (legacy hint that never did anything, ignored)
The support for service names was added in MaxScale 6.3.2. Older versions of MaxScale did not accept service names in the target parameters.
source
Type: string
Mandatory: No
Dynamic: Yes
Default: None
This optional parameter defines an IP address or mask which a connecting client's IP address is matched against. Only sessions whose address matches this setting will have this filter active and performing the regex matching. Traffic from other client IPs is simply left as is and routed straight through.
Since MaxScale 2.1 it's also possible to use % wildcards:
Note that using source=% to match any IP is not allowed.
Since MaxScale 2.3 it's also possible to specify multiple addresses separated by comma. Incoming client connections are subsequently checked against each.
user
Type: string
Mandatory: No
Dynamic: Yes
Default: None
This optional parameter defines a username the connecting client username is matched against. Only sessions that are connected using this username will have the match and routing hints applied to them. Traffic from other users is simply left as is and routed straight through.
Additional remarks
The maximum number of accepted match - target pairs is 25.
In the configuration file, the indexed match and target settings may be in any order and may skip numbers. During SQL-query matching, however, the regexes are tested in ascending order: match01, match02, match03 and so on. As soon as a match is found for a given query, the routing hints are written and the packet is forwarded to the next filter or router. Any remaining match regexes are ignored. This means the match - target pairs should be indexed in priority order, or, if priority is not a factor, in order of decreasing match probability.
Binary-mode prepared statements (COM_STMT_PREPARE) are handled by matching the prepared sql against the match-parameters. If a match is found, the routing hints are attached to any execution of that prepared statement. Text- mode prepared statements are not supported in this way. To divert them, use regular expressions which match the specific "EXECUTE"-query.
Examples
Example 1 - Route queries targeting a specific table to a server
This will route all queries matching the regular expression *from *users to the server named server2. The filter will ignore character case in queries.
A query like SELECT * FROM users would be routed to server2 where as a query like SELECT * FROM accounts would be routed according to the normal rules of the router.
[RWS-Column]
type = service
router = readwritesplit
servers = column_server_1, column_server_2, ...
[RWS-Column-Listener]
type = listener
service = RWS-Column
socket = /tmp/rws-col.sock
[SmartQuery]
type = service
router = smartrouter
targets = RWS-Row, RWS-Column
master = RWS-Row
[SmartQuery-Listener]
type = listener
service = SmartQuery
port = <port>
[maxscale]
[row_server_1]
type = server
address = <ip>
port = <port>
[row_server_2]
type = server
address = <ip>
port = <port>
[Row-Monitor]
type = monitor
module = mariadbmon
servers = row_server_1, row_server_2
user = <user>
password = <password>
monitor_interval = 2000ms
[column_server_1]
type = server
address = <ip>
port = <port>
[Column-Monitor]
type = monitor
module = csmon
servers = column_server_1
user = <user>
password = <password>
monitor_interval = 2000ms
# Row Read write split
[RWS-Row]
type = service
router = readwritesplit
servers = row_server_1, row_server_2
user = <user>
password = <password>
[RWS-Row-Listener]
type = listener
service = RWS-Row
socket = /tmp/rws-row.sock
# Columnstore Read write split
[RWS-Column]
type = service
router = readwritesplit
servers = column_server_1
user = <user>
password = <password>
[RWS-Column-Listener]
type = listener
service = RWS-Column
socket = /tmp/rws-col.sock
# Smart Query router
[SmartQuery]
type = service
router = smartrouter
targets = RWS-Row, RWS-Column
master = RWS-Row
user = <user>
password = <password>
[SmartQuery-Listener]
type = listener
service = SmartQuery
port = <port>
-bash-4.1$ cat /var/logs/top/Employees-top-10.137
Top 10 longest running queries in session.
==========================================
Time (sec) | Query
-----------+-----------------------------------------------------------------
22.985 | select sum(salary), year(from_date) from salaries s, (select distinct year(from_date) as y1 from salaries) y where (makedate(y.y1, 1) between s.from_date and s.to_date) group by y.y1
5.304 | select d.dept_name as "Department", y.y1 as "Year", count(*) as "Count" from departments d, dept_emp de, (select distinct year(from_date) as y1 from dept_emp order by 1) y where d.dept_no = de.dept_no and (makedate(y.y1, 1) between de.from_date and de.to_date) group by y.y1, d.dept_name order by 1, 2
2.896 | select year(now()) - year(birth_date) as age, gender, avg(salary) as "Average Salary" from employees e, salaries s where e.emp_no = s.emp_no and ("1988-08-01" between from_date AND to_date) group by year(now()) - year(birth_date), gender order by 1,2
2.160 | select dept_name as "Department", sum(salary) / 12 as "Salary Bill" from employees e, departments d, dept_emp de, salaries s where e.emp_no = de.emp_no and de.dept_no = d.dept_no and ("1988-08-01" between de.from_date AND de.to_date) and ("1988-08-01" between s.from_date AND s.to_date) and s.emp_no = e.emp_no group by dept_name order by 1
0.845 | select dept_name as "Department", avg(year(now()) - year(birth_date)) as "Average Age", gender from employees e, departments d, dept_emp de where e.emp_no = de.emp_no and de.dept_no = d.dept_no and ("1988-08-01" between from_date AND to_date) group by dept_name, gender
0.668 | select year(hire_date) as "Hired", d.dept_name, count(*) as "Count" from employees e, departments d, dept_emp de where de.emp_no = e.emp_no and de.dept_no = d.dept_no group by d.dept_name, year(hire_date)
0.249 | select moves.n_depts As "No. of Departments", count(moves.emp_no) as "No. of Employees" from (select de1.emp_no as emp_no, count(de1.emp_no) as n_depts from dept_emp de1 group by de1.emp_no) as moves group by moves.n_depts order by 1
0.245 | select year(now()) - year(birth_date) as age, gender, count(*) as "Count" from employees group by year(now()) - year(birth_date), gender order by 1,2
0.179 | select year(hire_date) as "Hired", count(*) as "Count" from employees group by year(hire_date)
0.160 | select year(hire_date) - year(birth_date) as "Age", count(*) as Count from employees group by year(hire_date) - year(birth_date) order by 1
-----------+-----------------------------------------------------------------
Session started Wed Jun 18 18:41:03 2014
Connection from 127.0.0.1
Username massi
Total of 24 statements executed.
Total statement execution time 35.701 seconds
Average statement execution time 1.488 seconds
Total connection time 46.500 seconds
-bash-4.1$
CREATE TABLE IF NOT EXISTS my_table (
data JSON NOT NULL,
id VARCHAR(1024) AS (JSON_EXTRACT(data, '$._id')) UNIQUE KEY
);
CREATE TABLE IF NOT EXISTS my_table (
data LONGTEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL,
id VARCHAR(1024) AS (JSON_EXTRACT(data, '$._id')) UNIQUE KEY,
CONSTRAINT data_is_json CHECK(JSON_VALID(data)),
CONSTRAINT id_is_not_null CHECK(JSON_EXTRACT(data, '$._id') IS NOT NULL)
);
`my database`.`my table`
CREATE TABLE IF NOT EXISTS my_table (data JSON NOT NULL);
Get a single network user. The :name in the URI must be a valid network user name.
Response
Status: 200 OK
Get all network users
Get all network users.
Response
Status: 200 OK
Get all users
Get all administrative users.
Response
Status: 200 OK
Create a network user
Create a new network user. The request body must define at least the following fields.
data.id
The username.
data.attributes.password
The password for this user.
data.attributes.role or data.attributes.account
The role that this user account uses. The set of available roles can be retrieved with GET /v1/roles. The old fixed set of roles that older versions of MaxScale used are also read from the account field, if used. If both the role and the account
Here is an example request body defining the network user my-user with the password my-password that is allowed to execute only read-only operations.
Response
Delete a network user
The :name part of the URI must be a valid user name.
Response
Update a network user
Update network user. The following fields can be modified:
data.attributes.password
Changes the password for this user.
data.attributes.role or data.attributes.account
Changes the role for this user. If both fields are defined, the value of role is used.
Modifying a user requires administrative privileges.
Here is an example request body that updates the password.
Response
This page is licensed: CC BY-SA / Gnu FDL
balances
connections
and not
statements
. When a client connects, the router selects a server based upon the router configuration and current server load, but the single created connection is fixed and will not be changed for the duration of the session. If the connection between MaxScale and the server breaks, the connection cannot be re-established and the session will be closed. The fact that the server is fixed when the client connects also means that routing hints are ignored.
Warning: By default readconnroute will not prevent writes from being done even if you define router_options=slave. To prevent writes, add filters=readonly to the service to load the ReadOnly module that will block all writes. Otherwise, the client application is responsible for making sure that it only performs read-only queries in such cases. readconnroute is simple by design: it selects a server for each client connection and routes all queries there. If something more complex is required, the readwritesplit router is usually the right choice.
Settings
For more details about the standard service parameters, refer to the Configuration Guide.
router_options can contain a comma separated list of valid server roles. These roles are used as the valid types of servers the router will form connections to when new sessions are created.
Examples:
Here is a list of all possible values for the router_options.
Role
Description
master
A server assigned as a primary by one of MariaDB MaxScale monitors. Depending on the monitor implementation, this could be a primary server of a Primary-Replica replication cluster or a Write-Primary of a Galera cluster.
slave
A server assigned as a replica of a primary. If all replicas are down, but the primary is still available, then the router will use the primary.
synced
A Galera cluster node which is in a synced state with the cluster.
running
If no router_options parameter is configured in the service definition, the router will use the default value of running. This means that it will load balance connections across all running servers defined in the servers parameter of the service.
When a connection is being created and the candidate server is being chosen, the list of servers is processed in from first entry to last. This means that if two servers with equal weight and status are found, the one that's listed first in the servers parameter for the service is chosen.
This option can be used to prevent queries from being sent to the current primary. If router_options does not contain master, the readconnroute instance is usually meant for reading. Setting master_accept_reads=false excludes the primary from server selection (and thus from receiving reads).
If router_options contains master, the setting of master_accept_reads has no effect.
The maximum acceptable replication lag. The value is in seconds. The default value is 0s, which means that the lag is ignored.
The replication lag of a server must be less than the configured value in order for it to be used for routing. To configure the router to not allow any lag, use the smallest duration larger than 0, that is, max_replication_lag=1s.
preferred_labels
Type: string list
Mandatory: No
Dynamic: Yes
Default: None
Comma-separated list of labels that defines the preference of servers.
If defined, servers with these labels are preferred over those that do not have them. This allows different readconnroute services to prefer different servers based on user-configurable labels in the servers and services. This also makes it possible to have a separate readconnroute service for example reporting or logical backups that prefers a specific set of servers and another readconnroute service for the production client workload, all while still allowing both of them to use the same server in case of an outage.
Multiple label values can be given and the servers are grouped based on that. For example with preferred_labels=banana,mango,kiwi, servers with the banana label are used first after which the mangoones are used and then the kiwi ones. If no servers with these labels are found, any server that is valid based on router_options is used.
Examples
The most common use for the readconnroute is to provide either a read or write port for an application. This provides a more lightweight routing solution than the more complex readwritesplit router but requires the application to be able to use distinct write and read ports.
To configure a read-only service that tolerates primary failures, we first need to add a new section into the configuration file.
Here the router_options designates replicas as the only valid server type. With this configuration, the queries are load balanced across the replica servers.
Router Diagnostics
The router_diagnostics output for readconnroute has the following fields.
queries: Number of queries executed through this service.
Limitations
Sending of binary data with LOAD DATA LOCAL INFILE is not supported.
The router will never reconnect to the server it initially connected to.
This page is licensed: CC BY-SA / Gnu FDL
MaxScale PAM Authenticator
Authenticate users via Pluggable Authentication Modules (PAM). This reference details the PAMAuth module, supporting standard passwords, two-factor authentication, and SUID mode.
Overview
Pluggable authentication module (PAM) is a general purpose authentication API. An application using PAM can authenticate a user without knowledge about the underlying authentication implementation. The actual authentication scheme is defined in the operating system PAM config (e.g. /etc/pam.d/), and can be quite elaborate. MaxScale supports a very limited form of the PAM protocol, which this document details.
Configuration
The MaxScale PAM module requires little configuration. All that is required is to change the listener authenticator module to "PAMAuth".
MaxScale uses the PAM authenticator plugin to authenticate users with plugin set to pam in the mysql.user table. The PAM service name of a user is read from the authentication_string column. The matching PAM service in the operating system PAM config is used for authenticating the user. If the authentication_string for a user is empty, the fallback service "mysql" is used.
PAM service configuration is out of the scope of this document, see for more information. A simple service definition used for testing this module is below.
Settings
pam_use_cleartext_plugin
Type:
Mandatory: No
Dynamic: No
Default:
If enabled, MaxScale communicates with the client as if using . This setting has no effect on MaxScale-to-backend communication, which adapts to either "dialog" or mysql_clear_password, depending on which one the backend suggests. This setting is meant to be used with the similarly named MariaDB Server setting.
pam_mode
Type:
Mandatory: No
Dynamic: No
Values:
This setting defines the authentication mode used. Two values are supported:
password Normal password-based authentication
password_2FA Password + 2FA-code based authentication
suid Authenticate using suid sandbox subprocess
If set to password_2FA, any users authenticating via PAM will be asked two passwords ("Password" and "Verification code") during login. MaxScale uses the normal password when either the local PAM api or a backend asks for "Password". MaxScale answers any other password prompt (e.g. "Verification code") with the second password. See for more details. Two-factor mode is incompatible with _use_cleartext_plugin_.
If set to suid, MaxScale will launch a separate subprocess for every client to handle pam authentication. This subprocess runs the binary maxscale_pam_auth_tool (installed in the binary directory), which calls the system pam libraries. The binary is installed with the SUID bit set, which means that it runs with root-privileges regardless of the user launching it. This should bypass any file grant issues (e.g. reading etc/shadow) that may arise with the password or password_2FA options. The suid option may also perform faster if many clients authenticate with pam simultaneously due to better separation of clients. It may also resist buggy pam plugins crashing, as the crash would be limited to the subprocess only. T he MariaDB Server uses a similar pam authentication scheme. suid mode supports two-factor authentication.
pam_backend_mapping
Type:
Mandatory: No
Dynamic: No
Values:
Defines backend authentication mapping, i.e. switch of authentication method between client-to-MaxScale and MaxScale-to-backend. Supported values:
none No mapping
mariadb Map users to normal MariaDB accounts
If set to "mariadb", MaxScale will authenticate clients to backends using standard MariaDB authentication. Authentication to MaxScale itself still uses PAM. MaxScale asks the local PAM system if the client username was mapped to another username during authentication, and use the mapped username when logging in to backends. Passwords for the mapped users can be given in a file, see pam_mapped_pw_file below. If passwords are not given, MaxScale will try to authenticate without a password. Because of this, normal PAM users and mapped users cannot be used on the same listener.
Because the client still needs to authenticate to MaxScale normally, an anonymous user may be required. If the backends do not allow such a user, one can be manually added using the service setting .
To map usernames, the PAM service needs to use a module such as _user_map.so_. This module is not a standard Linux component and needs to be installed separately. It is included in recent MariaDB Server packages and can also be compiled from source. See for more information on how to configure the module. If the goal is to only map users from PAM to MariaDB in MaxScale, then configuring user mapping on just the machine running MaxScale is enough.
Instead of using pam_backend_mapping, consider using the listener setting , as it is easier to configure. pam_backend_mapping should only be used when the user mapping needs to be defined by pam.
pam_mapped_pw_file
Type: path
Mandatory: No
Dynamic: No
Default: None
Path to a json-text file with user passwords. Default value is empty, which disables the feature.
This feature only works together with pam_backend_mapping=mariadb. The file is only read during listener creation (typically MaxScale start) or when a listener is modified during runtime. The file should contain passwords for the mapped users. When a client is authenticating, MaxScale searches the password data for a matching username. If one is found, MaxScale uses the supplied password when logging in to backends. Otherwise, MaxScale tries to authenticate without a password.
One array, "users_and_passwords", is read from the file. Each array element in the array must define the following fields:
"user": String. Mapped client username.
"password": String. Backend server password. Can be encrypted with maxpasswd.
An example file is below.
Anonymous User Mapping
When backend authenticator mapping is not in use (authenticator_options=pam_backend_mapping=none), the PAM authenticator supports a limited version of . It requires less configuration but is also less accurate than proper mapping. Anonymous mapping is enabled in MaxScale if the following user exists:
Empty username (e.g. ''@'%' or ''@'myhost.com')
plugin = 'pam'
Proxy grant is on (The query SHOW GRANTS FOR user@host;
When the authenticator detects such users, anonymous account mapping is enabled for the hosts of the anonymous users. To verify this, enable the info log (log_info=1 in MaxScale config file). When a client is logging in using the anonymous user account, MaxScale logs a message starting with "Found matching anonymous user ...".
When mapping is on, the MaxScale PAM authenticator does not require client accounts to exist in the mysql.user table received from the backend. MaxScale only requires that the hostname of the incoming client matches the host field of one of the anonymous users (comparison performed using LIKE). If a match is found, MaxScale attempts to authenticate the client to the local machine with the username and password supplied. The PAM service used for authentication is read from the authentication_string field of the anonymous user. If authentication was successful, MaxScale then uses the username and password to log to the backends.
Anonymous mapping is only attempted if the client username is not found in themysql.user table as explained in . This means, that if a user is found and the authentication fails, anonymous authentication is not attempted even when it could use a different PAM service with a different outcome.
Setting up PAM group mapping for the MariaDB server is a more involved process as the server requires details on which Unix user or group is mapped to which MariaDB user. See for more details. Performing all the steps in the guide also on the MaxScale machine is not required, as the MaxScale PAM plugin only checks that the client host matches an anonymous user and that the client (with the username and password it provided) can log into the local PAM configuration. If using normal password authentication, simply generating the Unix user and password should be enough.
Implementation Details and Limitations
The general PAM authentication scheme is difficult for a proxy such as MaxScale. An application using the PAM interface needs to define a conversation function to allow the OS PAM modules to communicate with the client, possibly exchanging multiple messages. This works when a client logs in to a normal server, but not with MaxScale since it needs to autonomously log into multiple backends. For MaxScale to successfully log into the servers, the messages and answers need to be predefined. The passwords given to MaxScale need to work as is when MaxScale logs into the backends. This requirement prevents the use of one-time passwords.
The MaxScale PAM authentication module supports two password modes. In normal mode, client authentication begins with MaxScale sending an AuthSwitchRequest packet. In addition to the command, the packet contains the client plugin name ("dialog" or "mysql_clear_password"), a message type byte (4) and the message "Password: ". In the next packet, the client should send the password, which MaxScale will forward to the PAM api running on the local machine. If the password is correct, an OK packet is sent to the client. If the local PAM api asks for additional credentials as is typical in two-factor authentication schemes, authentication fails. Informational messages such as password expiration notifications are allowed. These are simply printed to the log.
On the backend side, MaxScale expects the servers to act as MaxScale did towards the client. The servers should send an AuthSwitchRequest packet as defined above, MaxScale responds with the password received by the client authenticator and finally backend replies with OK. Informational messages from backends are only printed to the info-log.
Two-factor Authentication Support
MaxScale supports a limited form of two-factor authentication with thepam_mode=password_2FA-option. Since MaxScale uses the 2FA-code given by the client to log in to the local PAM api as well as all the backends, the code must be reusable. This prevents the use of any kind of centrally checked one-use codes. Time-based codes work, assuming the backends are checking the codes independently of each other. Automatic reconnection features (e.g. readwritesplit router) does not work, as the code has likely changed since original authentication.
Optionally, the PAM configuration on the backend servers can be weakened such that the servers only asks for the normal password. This way, MaxScale checks the 2FA-code of the incoming client, while MaxScale logs into the backends using only the password.
Due to technical reasons, MaxScale does not forward the password prompts from the PAM api to the client. MaxScale will always ask for "Password" and "Verification code", even if the PAM api asks for other items. This prevents the use of authentication schemes where a specific question must be answered (e.g. "Input code Nr. 5"). This is not a significant limitation, as such schemes would not work with backend servers anyway.
Test Tool
MaxScale binary directory contains the test_pam_login-executable. This simple program asks for a username, password and PAM service and then uses the given credentials to login to the given service. test_pam_login uses the same code as MaxScale itself to communicate with the OS PAM interface and may be useful for diagnosing PAM login issues.
This page is licensed: CC BY-SA / Gnu FDL
MaxScale Filter Resource
Configure database traffic filters programmatically. This resource enables you to retrieve, create, and modify filter instances and their runtime parameters.
Overview
A filter resource represents an instance of a filter inside MaxScale. Multiple services can use the same filter and a single service can use multiple filters.
Resource Operations
The :name in all of the URIs must be the name of a filter in MaxScale.
Get a filter
Get a single filter.
Response
Status: 200 OK
Get all filters
Get all filters.
Response
Status: 200 OK
Get filter relationships
The :type in the URI must be services as filters only have service relationships.
Response
Status: 200 OK
Create a filter
Create a new filter. The posted object must define at least the following fields.
data.id
Name of the filter
data.type
All of the filter parameters should be defined at creation time in the data.attributes.parameters object.
As the service to filter relationship is ordered (filters are applied in the order they are listed), filter to service relationships cannot be defined at creation time.
The following example defines a request body which creates a new filter.
Response
Filter is created:
Status: 204 No Content
Update a filter
Filter parameters can be updated at runtime if the module supports it. Refer to the individual module documentation for more details on whether it supports runtime configuration and which parameters can be updated.
The following example modifies a filter by changing the match parameter to .*users.*.
Response
Filter is modified:
Status: 204 No Content
Destroy a filter
The :filter in the URI must map to the name of the filter to be destroyed.
A filter can only be destroyed if no service uses it. This means that the data.relationships object for the filter must be empty. Note that the service → filter relationship cannot be modified from the filters resource and must be done via the services resource.
This endpoint also supports the force=yes parameter that will unconditionally delete the filter by first removing it from all services that it uses.
Response
Filter is destroyed:
Status: 204 No Content
This page is licensed: CC BY-SA / Gnu FDL
MaxScale Mirror
Duplicate client queries to multiple targets. The Mirror router sends traffic to a primary server while asynchronously forwarding the same queries to a secondary server for testing.
Overview
The mirror router is designed for data consistency and database behavior verification during system upgrades. It allows statement duplication to multiple servers in a manner similar to that of the Tee filter with exporting of collected query metrics.
For each executed query the router exports a JSON object that describes the query results and has the following fields:
Key
Description
The objects in the results array describe an individual query result and have the following fields:
Key
Description
Settings
main
Type: target
Mandatory: Yes
Dynamic: Yes
The main target from which results are returned to the client. This is a mandatory parameter and must define one of the targets configured in thetargets parameter of the service.
If the connection to the main target cannot be created or is lost mid-session, the client connection will be closed. Connection failures to other targets are not fatal errors and any open connections to them will be closed. The router does not create new connections after the initial connections are created.
exporter
Type:
Mandatory: Yes
Dynamic: Yes
Values:
The exporter where the data is exported. This is a mandatory parameter. Possible values are:
log
Exports metrics to MaxScale log on INFO level. No configuration parameters.
file
file
Type: string
Default: No default value
Mandatory: No
Dynamic: Yes
The output file where the metrics will be written. The file must be writable by the user that is running MaxScale, usually the maxscale user.
When the file parameter is altered at runtime, the old file is closed before the new file is opened. This makes it a convenient way of rotating the file where the metrics are exported. Note that the file name alteration must change the value for it to take effect.
This is a mandatory parameter when configured with exporter=file.
kafka_broker
Type: string
Default: No default value
Mandatory: No
Dynamic: Yes
The Kafka broker list. Must be given as a comma-separated list of broker hosts with optional ports in host:port format.
This is a mandatory parameter when configured with exporter=kafka.
kafka_topic
Type: string
Default: No default value
Mandatory: No
Dynamic: Yes
The kafka topic where the metrics are sent.
This is a mandatory parameter when configured with exporter=kafka.
on_error
Type:
Default: ignore
Mandatory: No
What to do when a backend network connection fails. Accepted values are:
ignore
Ignore the failing backend if it's not the backend that the main parameter points to.
close
This parameter was added in MaxScale 6.0. Older versions always ignored failing backends.
report
Type:
Default: always
Mandatory: No
When to report the result of the queries. Accepted values are:
always
Always report the result for all queries.
on_conflict
This parameter was added in MaxScale 6.0. Older versions always reported the result.
Example Configuration
Limitations
Broken network connections are not recreated.
Prepared statements are not supported.
Contents of non-SQL statements are not added to the exported metrics.
This page is licensed: CC BY-SA / Gnu FDL
MaxScale Tee Filter
Duplicate query traffic to multiple targets. This filter sends a copy of incoming requests to a secondary service or server useful for testing and auditing.
Overview
The tee filter is a "plumbing" fitting in the MariaDB MaxScale filter toolkit. It can be used in a filter pipeline of a service to make copies of requests from the client and send the copies to another service within MariaDB MaxScale.
Please Note: Starting with MaxScale 2.2.0, any client that connects to a service which uses a tee filter will require a grant for the loopback address, i.e. 127.0.0.1.
Configuration
The configuration block for the TEE filter requires the minimal filter parameters in its section within the MaxScale configuration file. The service to send the duplicates to must be defined.
Settings
The tee filter requires a mandatory parameter to define the service to replicate statements to and accepts a number of optional parameters.
target
Type: target
Mandatory: No
Dynamic: Yes
Default: none
The target where the filter will duplicate all queries. The target can be either a service or a server. The duplicate connection that is created to this target will be referred to as the "branch target" in this document.
service
Type: service
Mandatory: No
Dynamic: Yes
Default: none
The service where the filter will duplicate all queries. This parameter is deprecated in favor of the target parameter and will be removed in a future release. Both target and service cannot be defined.
match
Type:
Mandatory: No
Dynamic: Yes
Default: None
What queries should be included.
exclude
Type:
Mandatory: No
Dynamic: Yes
Default: None
What queries should be excluded.
options
Type:
Mandatory: No
Dynamic: Yes
Values:
How regular expressions should be interpreted.
source
Type: string
Mandatory: No
Dynamic: Yes
Default: None
The optional source parameter defines an address that is used to match against the address from which the client connection to MariaDB MaxScale originates. Only sessions that originate from this address will be replicated.
user
Type: string
Mandatory: No
Dynamic: Yes
Default: None
The optional user parameter defines a user name that is used to match against the user from which the client connection to MariaDB MaxScale originates. Only sessions that are connected using this username are replicated.
sync
Type:
Mandatory: No
Dynamic: Yes
Default:
Enable synchronous routing mode. When configured with sync=true, the filter will queue new queries until the response from both the main and the branch target has been received. This means that for n executed queries, n - 1 queries are guaranteed to be synchronized. Adding one extra statement (e.g. SELECT 1) to a batch of statements guarantees that all previous SQL statements have been successfully executed on both targets.
In the synchronous routing mode, a failure of the branch target will cause the client session to be closed.
Limitations
All statements that are executed on the branch target are done in an asynchronous manner. This means that when the client receives the response there is no guarantee that the statement has completed on the branch target. The sync feature provides some synchronization guarantees that can be used to verify successful execution on both targets.
Any errors on the branch target will cause the connection to it to be closed. If target is a service, it is up to the router to decide whether the connection is closed. For direct connections to servers, any network errors cause the connection to be closed. When the connection is closed, no new queries will be routed to the branch target.
With sync=true, a failure of the branch target will cause the whole session to be closed.
Module commands
Read documentation for details about module commands.
The tee filter supports the following module commands.
tee disable [FILTER]
This command disables a tee filter instance. A disabled tee filter will not send any queries to the target service.
tee enable [FILTER]
Enable a disabled tee filter. This resumes the sending of queries to the target service.
Examples
Example 1 - Replicate all inserts into the orders table
Assume an order processing system that has a table called orders. You also have another database server, the datamart server, that requires all inserts into orders to be replicated to it. Deletes and updates are not, however, required.
Set up a service in MariaDB MaxScale, called Orders, to communicate with the order processing system with the tee filter applied to it. Also set up a service to talk to the datamart server, using the DataMart service. The tee filter would have as its service entry the DataMart service, by adding a match parameter of "insert into orders" would then result in all requests being sent to the order processing system, and insert statements that include the orders table being additionally sent to the datamart server.
This page is licensed: CC BY-SA / Gnu FDL
MaxScale Rewrite Filter
Transform SQL queries using template-based rules. This filter modifies incoming statements to optimize performance or fix compatibility issues without changing application code.
Overview
The rewrite filter allows modification of sql queries on the fly. Reasons for modifying queries can be to rewrite a query for performance, or to change a specific query when the client query is incorrect and cannot be changed in a timely manner.
The examples will use Rewrite Filter file format. See below.
MaxScale Firewall Filter
Protect your database by blocking unauthorized queries. This filter inspects incoming SQL against a defined set of rules to prevent SQL injection and unauthorized access.
{
"data": {
"id": "my-user", // The user to create
"attributes": {
"password": "my-password", // The password to use for the user
"role": "admin" // The type of the account
}
}
}
Exports metrics to a file. Configured with the file parameter.
kafka
Exports metrics to a Kafka broker. Configured with the kafka_broker and kafka_topic parameters.
Dynamic: Yes
Values: ignore, close
Close the client connection when the first backend fails.
Dynamic: Yes
Values: always, on_conflict
Only report when one or more backends returns a conflicting result.
Data synchronization in dynamic environments (e.g. when replication is in use) is not guaranteed. This means that result mismatches can be reported when the data is only eventually consistent.
query
The executed SQL if an SQL statement was executed
command
The SQL command
session
The connection ID of the session that executed the query
Rewriter native syntax uses placeholders to grab and replace parts of text.
Placeholders
The syntax for a plain placeholder is @{N} where N is a positive integer.
The syntax for a placeholder regex is @{N:regex}. It allows more control when needed.
The below is a valid entry in rf format. For demonstration, all options are set. This entry is a do-nothing entry, but illustrates placeholders.
If the input sql is select id, name from my_table where id = 42 then @{2} = "id, name" and @{3} = "42". Since the replace template is identical to the match template the end result is that the output sql will be the same as the input sql.
Placeholders can be used as forward references.@{1:^}select @{2}, count(*) from @{3} group by @{2}. For a match, the two @{2} text grabs must be equal.
Match template
The match template is used to match against the sql to be rewritten.
The match template can be partial from mytable. But the actual underlying regex match is always for the whole sql. If the match template does not start or end with a placeholder, placeholders are automatically added so that the above becomes @{1}from mytable@{2}. The automatically added placeholders cannot be used in the replace template.
Matching the whole input also means that Native syntax does not support (and is not intended to support) scan and replace. Only the first occurrence of the above from mytable can be modified in the replace template. However, one can selectively choose to modify e.g. the first through third occurrence of from mytable by writingfrom mytable @{1} from mytable @{2} from mytable @{3}.
For scan and replace use a different regex_grammar (see below).
Replace template
The replace template uses the placeholders from the match template to rewrite sql.
An important option for smooth matching is ignore_whitespace, which is on (true) by default. It creates the match regex in such a way that the amount and kind of whitespace does not affect matching. However, to make ignore_whitespace always work, it is important to add whitespace where allowed. If "id=42" is in the match template then only the exact "id=42" can match. But if "id = 42" is used, andignore_whitespace is on, both "id=42" and "id = 42" will match.
Another example, and what not to do:
That works, but because the match lacks specific detail about the expected sql, things are likely to break. In this caseshow indexes from my_table would no longer work.
The minimum detail in this case could be:
but if more detail is known, like something specific in the where clause, that too should be added.
Placeholder Regex
Syntax: @{N:regex}
In a placeholder regex the character } must be escaped to \} (for literal matching). Plain parenthesis "()" indicate capturing groups, which are internally used by the Native grammar. Thus plain parentheses in a placeholder regex will break matching. However, non-capturing groups can be used: e.g. @{1:(:?Jane|Joe)}. To match a literal parenthesis use an escape, e.g. \(.
Suppose an application is misbehaving after an upgrade and a quick fix is needed. This query select zip from address_book where str_id = "AZ-124" is correct, but if the id is an integer the where clause should be id = 1234.
Using plain regular expressions
For scan and replace the regex_grammar must be set to something else than Native. An example will illustrate the usage.
Replace all occurrences of "wrong_table_name" with "correct_table_name". Further, if the replacement was made then replace all occurrences of wrong_column_name with correct_column_name.
Do not make the replacement, only log what would have been replaced (NOTICE level).
Rewrite file format
The rf format for an entry is:
The character # starts a single line comment when it is the first character on a line.
Empty lines are ignored.
The rf format does not need any additional escaping to what the basic format requires (see Placeholder Regex).
Options are specified as follows:
The colon must stick to the option name.
The separators % and %% must be the exact content of their respective separator lines.
The templates can span multiple lines. Whitespace does not matter as long as ignore_whitespace = true. Always use space where space is allowed to maximize the utility ofignore_whitespace.
Example
Json file format
The json file format is harder to read and edit manually. It will be needed if support for editing of rewrite templates is added to the GUI.
All double quotes and escape characters have to be escaped in json, i.e '"' and '\'.
The same example as above is:
Reload template file
The configuration is re-read if any dynamic value is updated even if the value does not change.
Grep Same as Posix with the addition of newline '\n' as an alternation separator.
EGrep Same as EPosix with the addition of newline '\n' as an alternation separator in addition to '|'.
This page is licensed: CC BY-SA / Gnu FDL
The database firewall acts as a protective barrier for a database, ensuring that only expected queries are allowed through. It learns the typical patterns of queries during a training period and then uses this knowledge to block any queries that do not match what it has learned. This helps safeguard the database from unauthorized access or potentially harmful operations.
The firewall has two primary modes; a learning mode during which it learns what kind of statements are allowed and an enforcing mode during which it rejects or warns about non-allowed statements. In addition there is an idle mode, when the filter neither learns, nor enforces and a supervising mode, when the filter checks the incoming statements but takes no action but only warns about non-allowed statements.
When in the learning mode a representative workload should be run through the filter. While the learning mode is active, the filter collects the canonical form of all statements that pass through it. The canonical form of a statement is the statement with all literals replaced with a question mark. For instance, the following two statements
are different, but the canonical form of both is SELECT * FROM t WHERE f = ?.
When the learning mode is finished, the allowed statements are persistently stored in a file. The file is not specific to the MaxScale instance where it was created and can be copied to other MaxScale installations.
When in the enforcing mode, the filter checks whether the canonical form of a statement is found in the set of allowed canonical statements. If it is, the statement is let through. If it is not, the behaviour depends upon the value of action.
As an example, suppose the firewall is in the learning mode and it encounters the following statements:
That results in the following canonical statements:
When switched to the enforcing mode, the following statements will be let through
but the following will not
While learning, the user information will be retained and saved together with the statements. When enforcing, the value of user_scope defines whether the allowed statements are user specific or whether the union of all statements will be allowed for all users.
Allowlist
The allowed canonical statements will be stored in a file in the sub-directory firewall in the data directory of MaxScale, which typically is /var/lib/maxscale. The name of the file will be <Filter>-allowed_statements.json, where <Filter> is the name of the firewall filter in the MaxScale configuration file.
For instance, with a configuration like this:
The name of the file will be MyFirewall-allowed_statements.json.
The file is not specific to the MaxScale instance where it was created, but can, for instance, in an HA setup be copied to another MaxScale.
The firewall filter saves the allow list when its mode is switched from learning to something else but idle. If the old allow list needs to be retained, it should be copied before the firewall filter is taken out from the learning mode.
The format of the file is JSON and sufficiently self-explanatory that it can be edited manually. With caution, since, if there are syntax errors that prevent it from being read, MaxScale will not start.
This enumeration option specifies the mode of the firewall.
idle: The firewall is idle, it neither learns nor enforces.
learn-clear: The firewall first clears the existing allow list and then learns the valid statements from the traffic passing through it.
learn-append: The firewall learns the valid statements from the traffic passing through it and appends them to the existing list.
supervise: The firewall only warns about statements that are not allowed.
enforce: The firewall acts as specified by when it encounters a statement that is not allowed.
If the firewall is in a learning mode, switching it to any other non-learning mode butidle, will cause the allow list to be saved. Switching the firewall from a learning mode to idle, will cause the learnt statements to be discarded and a possible existing allow list will be left unchanged.
Even if there is no allow list, the firewall can be put in the supervise or the enforce mode. The result is equivalent with having and empty allow list, that is, no statements will be allowed.
This enumeration option specifies whether the firewall blocks a user based upon the statements executed by that user during the learning mode, or whether it blocks all users based upon the union of statements executed by all users during the learning mode.
If the scope is collective a user not active during the learning mode will be able to execute the same statements the users that were active during the learning mode will. However, if the scope is individual, a user not active during the learning mode, will not be able to execute any statements.
With exclude_users users, e.g. admins, whose statements should not be subject to the firewall can be listed.
exclude_users
Type: stringlist
Mandatory: No
Dynamic: Yes
Default: ""
This string list option specifies users that are excluded and whose statements will not be subject to the firewall. The users can be specified with or without a host and quoting can be used.
Logging
When the firewall is in supervise mode, a firewall violation will cause a warning to be logged:
When the firewall is in 'enforce' mode, a firewall violation will cause the event firewall_incident, which, by default, will cause a warning to be logged to the MaxScale log file, if maxlog is enabled, and a syslog event to be generated, if syslog is enabled.
Workflow
The first step when taking the firewall into use, is to create the filter
and add it to the service
Since the firewall filter was just configured, the firewall will start up in the idle mode.
The second step is to switch the firewall to the learning mode.
The firewall will switch to the learning mode and start collecting the canonical form of the statements that flow through it. While the firewall is learning, a representative workload should be run through it. In this case, since there was no allow list, it does not matter whether the mode is set to to learn-clear or learn-append.
The third step, once there is confidence that the firewall has seen all relevant statements, is to switch the firewall to supervising mode.
At this point, the canonical statements will be saved to the file <datadir>/firewall/MyFirewall-allowed_statements.json.
Since the firewall is now in the supervise mode, all statements will be checked, but no action will be taken in case of a statement that is not allowed. A warning will be logged, which allows it to be detected whether there are statements that currently are not allowed that should be allowed.
The last step is to switch the firewall to the enforcing mode.
This page is licensed: CC BY-SA / Gnu FDL
MaxScale Exasolrouter
Route analytical queries to an Exasol cluster. This router integrates Exasol with MaxScale often used alongside SmartRouter for hybrid transactional/analytical workloads.
This functionality is available from MaxScale 25.10.1.
Overview
ExasolRouter is a router that in itself is capable of using an . It is primarily intended to be used together with , with writes being directed to a regular MariaDB cluster and reads to either MariaDB or Exasol, depending on which one can provide the response faster.
Unlike the other routers of MaxScale, the targets ExasolRouter routes to are not specified using servers, targets, or cluster settings in the configuration file. Instead, Exasol is specified using the setting.
However, if ExasolRouter is used standalone, a MariaDB server or a service should be specified using targets. ExasolRouter will not route to it, but it will use it for authenticating clients. Exasol will still be accessed on behalf of all clients using the credentials specified in the .
Users
A user and password must always be specified, but will only be used if a MariaDB server/service has been specified as a target, and only for authenticating a client. If that functionality is not needed, e.g. when ExasolRouter is used with SmartRouter, they can be left empty, but must still be present.
The user and password to be used when accessing Exasol must be specified using UID and PWD in the .
Preprocessor Script
The SQL supported by Exasol is not identical with the SQL supported by MariaDB. To alleviate that, the Exasol router can install a preprocessor script that is capable of converting to some extent MariaDB SQL constructs to the Exasol equivalents. Currently, the script looks like:
By default, the Exasol router installs it every time it starts. The default behaviour can be alterered using the setting.
If the default script is used, the Exasol router will also ensure that the schema UTIL exists. If the default script is not used, the Exasol router assumes that a used schema exists.
Settings
connection_string
Type: string
Mandatory: Yes
Dynamic: No
Specifies the Exasol connection string. The exact content depends on the contents of odbc.ini and odbcinst.ini.
For example:
Here it is assumed the odbc.ini ODBC configuration file containing an ExasolDSN entry.
appearance
Type:
Mandatory: No
Dynamic: No
Values:
Specifies how the Exasol router appears to other components of MaxScale. This is of relevance only if another service uses an Exasol router service as target.
Note Irrespective of the value, the router does not in any way restrict what kind of queries can be run through the router.
preprocessor
Type: String
Mandatory: No
Dynamic: No
Values: auto
The values mean:
auto: The built-in preprocessor script is installed at service startup and is taken into use in each session.
activate-only: The preprocessor script is assumed to exist in Exasol and is taken into use in each session.
custom:<path>
preprocessor_script
Type: String
Mandatory: No
Dynamic: No
Default: "UTIL.maria_preprocessor"
If the name of a custom preprocessor script, specified using preprocessor=custom:/path, is not UTIL.maria_preprocessor, the name should be provided using this setting.
Transformations
The Exasol Router transparently translates some MariaDB constructs to equivalent Exasol constructs.
COM_INIT_DB
The MariaDB COM_INIT_DB packet, using which the default database is changed, is transformed into the statement OPEN SCHEMA <db>.
SQL
Currently a transformation will be made only if there is an exact match (apart from case and differences in whitespace) with the MariaDb SQL.
MariaDb
Exasol
ODBC
The Exasol router communicates with Exasol using ODBC. In practice that means that the way ODBC has been configured affects what actually must be specified in . It is possible to provide all needed information in the connection string, but it is advisable to at least have a /etc/odbcinst.ini or ~/.odbcinst.ini where the location of the ODBC driver is specified.
With that file present, the connection string could be like:
By creating an /etc/odbc.ini or ~/.odbc.ini, the information that must be provided in the connection string can further be reduced. For instance, with the following,
the connection string can be reduced to
Examples
SmartRouter
The primary purpose of the Exasol router is to be used together with . A minimal configuration looks as follows:
It is assumed there is an odbc.ini ODBC configuration file containing an ExasolDSN entry.
Here it is assumed there is an odbc.ini ODBC configuration file containing and ExasolDSN entry.
With this setup, all writes will always be sent to Server1. Reads will initially be sent to both Server1 and ExasolService and once SmartRouter has learnt what kind of reads are best sent to which target, it will exclusively send reads to either Server1 or ExasolService, depending on which one is likely to provide the response faster.
With this setup, all writes will always be sent to Server1. Reads will initially be sent to both Server1 and ExasolService and once SmartRouter has learnt what kind of reads are best sent to which target, it will exclusively send reads to either Server1 or ExasolService depending on which one is likely to provide the response faster.
Here, a single server was used as master. It could just as well be a service in front of a MariaDB cluster, which would provide HA.
Stand-Alone
A minimal stand-alone configuration looks as follows.
With this setup, it is possible to connect using the regular mariadb command line utility to the port 4008 and all queries will be sent to Exasol.
MaxScale Session Resource
Inspect and manage active client sessions. This resource allows you to view connected clients, check session details, and terminate specific connections.
Overview
A session is an abstraction of a client connection, any number of related backend connections, a router module session and possibly filter module sessions. Each session is created on a service and each service can have multiple sessions.
Resource Operations
Get a session
Get a single session. :id must be a valid session ID. The session ID is the same that is exposed to the client as the connection ID.
This endpoint also supports the rdns=true parameter, which instructs MaxScale to perform reverse DNS on the client IP address. As this requires communicating with an external server, the operation may be expensive.
Response
Status: 200 OK
Get all sessions
Get all sessions.
Response
Status: 200 OK
Update a Session
The request body must be a JSON object which represents the new configuration of the session. The :id must be a valid session ID that is active.
The log_debug, log_info, log_notice, log_warning and log_error boolean parameters control whether the associated logging level is enabled:
The filters that a session uses can be updated by re-defining the filter relationship of the session. This causes new filter sessions to be opened immediately. The old filter session are closed and replaced with the new filter session the next time the session is idle. The order in which the filters are defined in the request body is the order in which the filters are installed, similar to how the filter relationship for services behaves.
Response
Session is modified:
Status: 204 No Content
Restart a Session
This endpoint causes the session to re-read the configuration from the service. As a result of this, all backend connections will be closed and then opened again. All router and filter sessions will be created again which means that for modules that perform something whenever a new module session is opened, this behaves as if a new session was started.
This endpoint can be used to apply configuration changes that were done after the session was started. This can be useful for situations where the client connections live for a long time and connections are not recycled often enough.
Response
Session is was restarted:
Status: 204 No Content
Restart all Sessions
This endpoint does the same thing as the /v1/sessions/:id/restart endpoint except that it applies to all sessions.
Response
Session is was restarted:
Status: 204 No Content
Kill a Session
This endpoint causes the session to be forcefully closed.
Request Parameters
This endpoint supports the following request parameters.
ttl
The time after which the session is killed. If this parameter is not given, the session is killed immediately. This can be used to give the session time to finish the work it is performing before the connection is closed.
Response
Session was killed:
Status: 204 No Content
This page is licensed: CC BY-SA / Gnu FDL
MaxScale Listener Resource
Manage network listeners via the REST API. Use this resource to define ports, protocols, and service associations for handling incoming client connections.
Overview
A listener resource represents a listener of a service in MaxScale. All listeners point to a service in MaxScale.
MariaDB MaxScale Exasolrouter
Learn how to configure the Exasolrouter in MariaDB MaxScale to route analytical queries to Exasol while maintaining transactional workloads in MariaDB
This functionality is available from MaxScale 25.10.1.
In MaxScale configuration, this module is referred to as exasolrouter. For documentation purposes, it is styled as Exasolrouter to enhance readability.
%%
# options
regex_grammar: Native
case_sensitive: true
what_if: false
continue_if_matched: false
ignore_whitespace: true
%
# match template
@{1:^}select @{2} from my_table where id = @{3}
%
# replace template
select @{2} from my_table where id = @{3}
%%
# use default options by leaving this blank
%
@{1:^}select count(distinct @{2}) from @{3}
%
select count(*) from (select distinct @{1} from @{2}) as t123
Input: select count(distinct author) from books where entity != "AI"
Rewritten: select count(*) from (select distinct author from books where entity != "AI") as t123
%%
%
from mytable
%
from mytable force index (myindex)
Input: select name from mytable where id=42
Rewritten: select name from mytable force index (myindex) where id=42
%%
%
@{1:^}select @{2} from mytable
%
select @{2} from mytable force index (myindex)
%%
%
@{1:^}select zip_code from address_book where str_id = @{1:["]}@{2:[[:digit:]]+}@{3:["]}
%
select zip_code from address_book where id = @{2}
Input: select zip_code from address_book where str_id = "1234"
Rewritten: select zip_code from address_book where id = 1234
%%
case_sensitive: false
%
@{1:^}select @{2}
from mytable
where user = @{3}
%
select @{2} from mytable where user = @{3}
and @{3} in (select user from approved_users)
{ "templates" :
[
{
"case_sensitive" : false,
"match_template" : "@{1:^}select @{2} from mytable where user = @{3}",
"replace_template" : "select @{2} from mytable where user = @{3}
and @{3} in (select user from approved_users)"
}
]
}
maxctrl alter filter Rewrite log_replacement=false
SELECT * FROM t WHERE f = 10
SELECT * FROM t WHERE f = 20
SELECT * FROM t WHERE f > 5
SELECT * FROM t WHERE f = 10
INSERT INTO t VALUES (42)
DELETE FROM t WHERE f > 20
SELECT * FROM users WHERE username = 'input' AND password = 'input'
SELECT * FROM t WHERE f > ?
SELECT * FROM t WHERE f = ?
INSERT INTO t VALUES (?)
DELETE FROM t WHERE f > ?
SELECT * FROM users WHERE username = ? AND password = ?
SELECT * FROM t WHERE f > 100
SELECT * FROM t WHERE f = 42
INSERT INTO t VALUES (84)
DELETE FROM t WHERE f > 200
SELECT * FROM users WHERE username = 'joe' AND password = 'secret'
# != is neither > nor =
SELECT * FROM t WHERE f != 10
# During learning only one value was inserted
INSERT INTO t VALUES (1), (2)
# During learning DELETE was always accompanied by a WHERE clause
DELETE FROM t
# An apparent SQL-injection attack does not match what was learnt.
SELECT * FROM users WHERE username = '' OR '1'='1' AND password = ''
[MyFirewall]
type=filter
module=firewall
exclude_users=admin, 'super'@'192.168.02.1'
2024-11-18 08:01:47 warning: (1) [firewall] (Service); Firewall incident (user@127.0.0.1): DELETE FROM t