Binlog Filter

Binlog Filter

This filter was introduced in MariaDB MaxScale 2.3.0.

Overview

The binlogfilter can be combined with a binlogrouter service to selectively replicate the binary log events to slave 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.

Configuration

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 slaves 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 .

CC BY-SA / Gnu FDL

Insert Stream Filter

Insert Stream Filter

This filter was introduced in MariaDB MaxScale 2.1.

Note

Maxscale CDC Connector

Maxscale CDC Connector

The C++ connector for the .

Change Data Capture (CDC) users

Change Data Capture (CDC) users

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

Module commands

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.

Cat

Cat

The cat router is a special router that concatenates result sets.

About MariaDB MaxScale

About MariaDB MaxScale

MariaDB MaxScale is a database proxy that forwards database statements to one or more database servers.

The forwarding is performed using rules based on the semantic understanding of the database statements and on the roles of the servers within the backend cluster of databases.

MariaDB MaxScale is designed to provide, transparently to applications, load balancing and high availability functionality. MariaDB MaxScale has a scalable and flexible architecture, with plugin components to support different protocols and routing approaches.

MariaDB MaxScale makes extensive use of the asynchronous I/O capabilities of the Linux operating system, combined with a fixed number of worker threads. epoll is used to provide the event driven framework for the input and output via sockets.

Many of the services provided by MariaDB MaxScale are implemented as external shared object modules loaded at runtime. These modules support a fixed interface, communicating the entry points via a structure consisting of a set of function pointers. This structure is called the "module object". Additional modules can be created to work with MariaDB MaxScale.

Commonly used module types are protocol, router and filter. Protocol modules implement the communication between clients and MariaDB MaxScale, and between MariaDB MaxScale and backend servers. Routers inspect the queries from clients and decide the target backend. The decisions are usually based on routing rules and backend server status. Filters work on data as it passes through MariaDB MaxScale. Filter are often used for logging queries or modifying server responses.

A Google Group exists for MariaDB MaxScale. The Group is used to discuss ideas, issues and communicate with the MariaDB MaxScale community. Send email to or use the interface.

Bugs can be reported in the MariaDB Jira

Installing MariaDB MaxScale

Information about installing MariaDB MaxScale, either from a repository or by building from source code, is included in the .

The same guide also provides basic information on running MariaDB MaxScale. More detailed information about configuring MariaDB MaxScale can be found in the .

CC BY-SA / Gnu FDL

Change Data Capture (CDC) Protocol

Change Data Capture (CDC) Protocol

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.

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.

For more details, refer to the .

Protocol Phases

Connection and Authentication

• 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 .

CC BY-SA / Gnu FDL

MariaDB Protocol Module

MariaDB Protocol Module

The mariadbprotocol module implements the MariaDB client-server protocol.

The legacy protocol names mysqlclient, mariadb and mariadbclient are all aliases to mariadbprotocol.

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.

allow_replication

• Type:

• Mandatory: No

• Dynamic: Yes

• Default: true

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).

CC BY-SA / Gnu FDL

Configuring the MariaDB Monitor

Configuring the MariaDB Monitor

This document describes how to configure a MariaDB master-slave cluster monitor to be used with MaxScale.

Configuring the Monitor

Define the monitor that monitors the servers.

The mandatory parameters are the object type, the monitor module to use, the list of servers to monitor and the username and password to use when connecting to the servers. The monitor_interval parameter controls for how long the monitor waits between each monitoring loop.

Monitor User

The monitor user requires the REPLICATION CLIENT privileges to do basic monitoring. To create a user with the proper grants, execute the following SQL.

Note: If the automatic failover of the MariaDB Monitor will used, the user will require additional grants. Execute the following SQL to grant them.

CC BY-SA / Gnu FDL

Configuring the Galera Monitor

Configuring the Galera Monitor

This document describes how to configure a Galera cluster monitor.

Configuring the Monitor

Define the monitor that monitors the servers.

The mandatory parameters are the object type, the monitor module to use, the list of servers to monitor and the username and password to use when connecting to the servers. The monitor_interval parameter controls for how long the monitor waits between each monitoring loop.

This monitor module will assign one node within the Galera Cluster as the current master and other nodes as slave. Only those nodes that are active members of the cluster are considered when making the choice of master node. The master node will be the node with the lowest value of wsrep_local_index.

Monitor User

The monitor user does not require any special grants to monitor a Galera cluster. To create a user for the monitor, execute the following SQL.

CC BY-SA / Gnu FDL

Installing MariaDB MaxScale using a tarball

Installing MariaDB MaxScale using a tarball

MariaDB MaxScale is also made available as a tarball, which is named likemaxscale-x.y.z.OS.tar.gz

Aurora Monitor

Aurora Monitor

Note The Aurora Monitor is deprecated in 22.08.2 and will be removed in 23.02.0.

This module monitors the status of Aurora cluster replicas. These replicas do not use the standard MySQL protocol replication but rely on a mechanism provided by AWS to replicate changes.

HintRouter

HintRouter

HintRouter was introduced in 2.2 and is still beta.

Connection Routing with MariaDB MaxScale

Connection Routing with MariaDB MaxScale

The goal of this tutorial is to configure a system that has two ports available, one for write connections and another for read connections. The read connections are load- balanced across slave servers.

Consistent Critical Read Filter

Consistent Critical Read Filter

This filter was introduced in MariaDB MaxScale 2.1.

Encrypting Passwords

Encrypting Passwords

Note: The password encryption format changed in MaxScale 2.5. All encrypted passwords created with MaxScale 2.4 or older need to be re-encrypted.

Upgrading MariaDB MaxScale from 2.4 to 2.5

Upgrading MariaDB MaxScale from 2.4 to 2.5

This document describes possible issues when upgrading MariaDB MaxScale from version 2.4 to 2.5.

Upgrading MariaDB MaxScale from 6 to 22.08

Upgrading MariaDB MaxScale from 6 to 22.08

This document describes possible issues when upgrading MariaDB MaxScale from version 6 to 22.08.

Hintfilter

Hintfilter

This filter adds routing hints to a service. The filter has no parameters.

Hint Syntax

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 themariadb 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 master

A master value in a routing hint will route the query to a master server. This can be used to direct read queries to a master server for a up-to-date result with no replication lag.

Route to slave

A slave value will route the query to a slave 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 slave 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 master:

Support for binary protocol prepared statements was added in MaxScale 6.0 ().

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

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 master as a result of it having the route to master hint.

Examples

Routing SELECT queries to master

In this example, MariaDB MaxScale is configured with the readwritesplit router and the hint filter.

Behind MariaDB MaxScale is a master server and a slave server. If there is replication lag between the master and the slave, read queries sent to the slave 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 master. The following SELECT query would normally be routed to the slave but with the added routing hint it will be routed to the master. This way we can do an INSERT and a SELECT right after it and still get up-to-date data.

CC BY-SA / Gnu FDL

Building MariaDB MaxScale from Source Code

Building MariaDB MaxScale from Source Code

MariaDB MaxScale can be built on any system that meets the requirements. The main requirements are as follows:

• CMake version 3.16 or later (Packaging requires CMake 3.25.1 or later)

• GCC version 4.9 or later

• OpenSSL version 1.0.1 or later

• GNUTLS

This is the minimum set of requirements that must be met to build the MaxScale core package. Some modules in MaxScale require optional extra dependencies.

• libuuid (binlogrouter)

• boost (binlogrouter)

• Bison 2.7 or later (dbfwfilter)

• Flex 2.5.35 or later (dbfwfilter)

Some of these dependencies are not available on all operating systems and are downloaded automatically during the build step. To skip the building of modules that need automatic downloading of the dependencies, use -DBUNDLE=N when configuring CMake.

Quickstart

This installs MaxScale as if it was installed from a package. Install git before running the following commands.

Required Packages

For a definitive list of packages, consult the script.

Configuring the Build

The tests and other parts of the build can be controlled via CMake arguments.

Here is a small table with the names of the most common parameters and what they control. These should all be given as parameters to the -D switch inNAME=VALUE format (e.g. -DBUILD_TESTS=Y).

Note: You can look into for a list of the CMake variables.

Running unit tests

To run the MaxScale unit test suite, configure the build with -DBUILD_TESTS=Y, compile and then run the make test command.

Building MariaDB MaxScale packages

If you wish to build packages, just add -DPACKAGE=Y to the CMake invocation and build the package with make package instead of installing MaxScale withmake install. This process will create a RPM/DEB package depending on your system.

To build a tarball, add -DTARBALL=Y to the cmake invokation. This will create a maxscale-x.y.z.tar.gz file where x.y.z is the version number.

Some Debian and Ubuntu systems suffer from a bug where make package fails with errors from dpkg-shlibdeps. This can be fixed by running make beforemake package and adding the path to the libmaxscale-common.so library to the LD_LIBRARY_PATH environment variable.

Installing optional components

The MaxScale build system is split into multiple components. The main component is the core MaxScale package which contains MaxScale and all the modules. This is the default component that is build, installed and packaged. There is also the experimental component that contains all experimental modules which are not considered as part of the core MaxScale package and are either alpha or beta quality modules.

To build the experimental modules along with the MaxScale core components, invoke CMake with -DTARGET_COMPONENT=core,experimental.

CC BY-SA / Gnu FDL

Throttle

Throttle

This filter was added in MariaDB MaxScale 2.3

MariaDB MaxScale Installation Guide

MariaDB MaxScale Installation Guide

We recommend to install MaxScale on a separate server, to ensure that there can be no competition of resources between MaxScale and a MariaDB Server that it manages.

MySQL Authenticator

MariaDB/MySQL Authenticator

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.

Authenticator options

The following settings may be given in the authenticator_options of the listener.

log_password_mismatch

• Type:

• Mandatory: No

• Dynamic: No

• Default: false

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.

CC BY-SA / Gnu FDL

GSSAPI Client Authenticator

GSSAPI Client Authenticator

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 is_GSSAPIAuth_.

Preparing the GSSAPI system

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 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:

To take GSSAPI authentication into use, add the following to the listener.

The principal name should be the same as on the MariaDB servers.

Authenticator options

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 will override 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 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).

CC BY-SA / Gnu FDL

Comment Filter

Comment Filter

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.

Filter Parameters

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:

It would look like

when received by server.

CC BY-SA / Gnu FDL

Regex Filter

Regex Filter

MaxScale 22.08 Contents

Contents

SmartRouter

SmartRouter

Readconnroute

Readconnroute

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.

Maxrows

Maxrows

This filter was introduced in MariaDB MaxScale 2.1.

MaxScale 22.08 Hint Syntax

Hint Syntax

Refer to the documentation for the MaxScale hint syntax.

CC BY-SA / Gnu FDL

Configuring Servers

Configuring Servers

The first step is to define the servers that make up the cluster. These servers will be used by the services and are monitored by the monitor.

Named Server Filter

Named Server Filter

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.

Configuration

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 master 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 .

Filter Parameters

NamedServerFilter requires at least one matchXY - targetXY pair.

matchXY

• Type:

• Mandatory: No

• Dynamic: Yes

• Default: None

matchXY defines a 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.

options

• Type:

• Mandatory: No

• Dynamic: Yes

• Values: ignorecase, case, extended

for matchXY.

targetXY

• Type: string

• Mandatory: No

• Dynamic: Yes

• Default: None

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)

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.

CC BY-SA / Gnu FDL

Admin User Resource

Admin User Resource

Admin users represent administrative users that are able to query and change MaxScale's configuration.

Resource Operations

Get network user

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 enabled UNIX account

Note: This endpoint has been deprecated and does nothing.

Get all enabled UNIX accounts

Note: This endpoint has been deprecated and does nothing.

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.type

• Type of the object, must be inet

Only admin accounts can perform POST, PUT, DELETE and PATCH requests. If a basic account performs one of the aforementioned request, the REST API will respond with a 401 Unauthorized error.

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

Enable a UNIX account

This enables an existing UNIX account on the system for administrative operations. The request body must define at least the following fields.

• data.id

• The username

• data.type

• Type of the object, must be unix

Here is an example request body enabling the UNIX account jdoe for read-only operations.

Response

Delete a network user

The :name part of the URI must be a valid user name.

Response

Disable a UNIX account

The :name part of the URI must be a valid user name.

Response

Update a network user

Update network user. Currently, only the password can be updated. This means that the request body must define the data.attributes.password field.

Here is an example request body that updates the password.

Response

CC BY-SA / Gnu FDL

Limitations and Known Issues within MariaDB MaxScale

Limitations and Known Issues within MariaDB MaxScale

This document lists known issues and limitations in MariaDB MaxScale and its plugins. Since limitations are related to specific plugins, this document is divided into several sections.

Configuration limitations

In versions 2.1.2 and earlier, the configuration files are limited to 1024 characters per line. This limitation was increased to 16384 characters in MaxScale 2.1.3. MaxScale 2.3.0 increased this limit to 16777216 characters.

In versions 2.2.12 and earlier, the section names in the configuration files were limited to 49 characters. This limitation was increased to 1023 characters in MaxScale 2.2.13.

Multiple MaxScales on same server

Starting with MaxScale 2.4.0, on systems with Linux kernels 3.9 or newer due to the addition of SO_REUSEPORT support, it is possible for multiple MaxScale instances to listen on the same network port if the directories used by both instances are completely separate and there are no conflicts which can cause unexpected splitting of connections. This will only happen if users explicitly tell MaxScale to ignore the default directories and will not happen in normal use.

Security limitiations

MariaDB 10.2

The parser of MaxScale correctly parses WITH statements, but fails to collect columns, functions and tables used in the SELECT defining theWITH clause.

Consequently, the database firewall will not block WITH statements where the SELECT of the WITH clause refers to forbidden columns.

MariaDB Default Values

MaxScale assumes that certain configuration parameters in MariaDB are set to their default values. These include but are not limited to:

• autocommit: Autocommit is enabled for all new connections.

• tx_read_only: Transactions use READ WRITE permissions by default.

Query Classification

Transaction Boundary Detection

If a module in MaxScale requires tracking of transaction boundaries but does not require query classification, a custom parser is used to detect them. Currently the only situation in which this parser is used is when a readconnroute service uses the cache filter.

The custom parser detects a subset of the full SQL syntax used to start transactions. This means that more complex statements will not be fully parsed and will cause the transaction state to not match the real state on the database. For example, SET @my_var = (SELECT 1), autocommit = 0 is not parsed by the custom parser and causes the autocommit modification to not be noticed.

XA Transactions

MaxScale will treat statements executed after XA START and before XA END as if they were executed in a normal read-write transaction started with START TRANSACTION. This means that only XA transactions in the ACTIVE state will be routed as transactions and all statements after XA END are routed normally.

XA transactions and normal transactions are mutually exclusive in MariaDB. This means that a START TRANSACTION command will fail if the connection already has an open XA transaction. MaxScale currently only inspects the SQL and deduces the transaction state from that. If a transaction fails to start due to an open XA transaction, the state in MaxScale and in MariaDB can be different and MaxScale will keep routing statements as if they were inside of a transaction. However, as this is an unlikely scenario, usually no action needs to be taken.

Prepared Statements

For its proper functioning, MaxScale needs in general to be aware of the transaction state and autocommit mode. In order to be that, MaxScale parses statements going through it.

However, if a transaction is commited or rolled back, or the autocommit mode is changed using a prepared statement, MaxScale will miss that and its internal state will be incorrect, until the transaction state or autocommit mode is changed using an explicit statement.

For instance, after the following sequence of commands, MaxScale will still think autocommit is on:

To ensure that MaxScale functions properly, do not commit or rollback a transaction or change the autocommit mode using a prepared statement.

Protocol limitations

Limitations with MySQL/MariaDB Protocol support (MariaDBClient)

• Compression is not included in the server handshake.

• If a KILL [CONNECTION] <ID> statement is executed, MaxScale will intercept it. If the ID matches a MaxScale session ID, it will be closed by sending modified KILL commands of the same type to all backend server to which the session in question is connected to. This results in behavior that is similar to how MariaDB does it. If the KILL CONNECTION USER <user> form is given, all connections with a matching username will be closed instead.

• MariaDB MaxScale does not support KILL QUERY ID <query_id>

Authenticator limitations

Limitations in the MySQL authenticator (MariaDBAuth)

• MySQL old style passwords are not supported. MySQL versions 4.1 and newer use a new authentication protocol which does not support pre-4.1 style passwords.

• When users have different passwords based on the host from which they connect MariaDB MaxScale is unable to determine which password it should use to connect to the backend database. This results in failed connections and unusable usernames in MariaDB MaxScale.

Filter limitations

Tee filter limitations (tee)

The Tee filter does not support binary protocol prepared statements. The execution of a prepared statements through a service that uses the tee filter is not guaranteed to succeed on the service where the filter branches to as it does on the original service.

This possibility exists due to the fact that the binary protocol prepared statements are identified by a server-generated ID. The ID sent to the client from the main service is not guaranteed to be the same that is sent by the branch service.

Monitor limitations

A server can only be monitored by one monitor. Two or more monitors monitoring the same server is considered an error.

Limitations with Galera Cluster Monitoring (galeramon)

The default master selection is based only on MIN(wsrep_local_index). This can be influenced with the server priority mechanic described in the manual.

Router limitations

Refer to individual router documentation for a list of their limitations.

CC BY-SA / Gnu FDL

MaxScale 22.08 Authentication Modules

Authentication Modules

This document describes general MySQL protocol authentication in MaxScale. For REST-api authentication, see the configuration guide and the REST-api guide.

Similar to the MariaDB Server, MaxScale uses authentication plugins to implement different authentication schemes for incoming clients. The same plugins also handle authenticating the clients to backend servers. The authentication plugins available in MaxScale are , and .

Most of the authentication processing is performed on the protocol level, before handing it over to one of the plugins. This shared part is described in this document. For information on an individual plugin, see its documentation.

User account management

Every MaxScale service with a MariaDB protocol listener requires knowledge of the user accounts defined on the backend databases. The service maintains this information in an internal component called the user account manager (UAM). The UAM queries relevant data from the mysql-database of the backends and stores it. Typically, only the current master server is queried, as all servers are assumed to have the same users. The service settings user and password define the credentials used when fetching user accounts.

The service uses the stored data when authenticating clients, checking their passwords and database access rights. This results in an authentication process very similar to the MariaDB Server itself. Unauthorized users are generally detected already at the MaxScale level instead of the backend servers. This may not apply in some cases, for example if MaxScale is using old user account data.

If authentication fails, the UAM updates its data from a backend. MaxScale may attempt authenticating the client again with the refreshed data without communicating the first failure to the client. This transparent user data update does not always work, in which case the client should try to log in again.

As the UAM is shared between all listeners of a service, its settings are defined in the service configuration. For more information, search the for users_refresh_time, users_refresh_interval and_auth_all_servers_. Other settings which affect how the UAM connects to backends are the global settings auth_connect_timeout and local_address, and the various server-level ssl-settings.

Required grants

To properly fetch user account information, the MaxScale service user must be able to read from various tables in the mysql-database: user, db,tables_priv, columns_priv, procs_priv, proxies_priv and roles_mapping. The user should also have the SHOW DATABASES-grant.

If using MariaDB ColumnStore, the following grant is required:

Limitations and troubleshooting

When a client logs in to MaxScale, MaxScale sees the client's IP address. When MaxScale then connects the client to backends (using the client's username and password), the backends see the connection coming from the IP address of MaxScale. If the client user account is to a wildcard host ('alice'@'%'), this is not an issue. If the host is restricted ('alice'@'123.123.123.123'), authentication to backends will fail.

There are two primary ways to deal with this:

1. Duplicate user accounts. For every user account with a restricted hostname an equivalent user account for MaxScale is added ('alice'@'maxscale-ip').

2. Use .

Option 1 limits the passwords for user accounts with shared usernames. Such accounts must use the same password since they will effectively share the MaxScale-to-backend user account. Option 2 requires server support.

See for additional information on how to solve authentication issues.

Wildcard database grants

MaxScale supports wildcards _ and % for database-level grants. As with MariaDB Server, grant select on test_.* to 'alice'@'%'; gives access totest_ as well as test1, test2 and so on. If the GRANT command escapes the wildcard (grant select on test_.* to 'alice'@'%';) both MaxScale and the MariaDB Server interpret it as only allowing access to test_. _ and % are only interpreted as wildcards when the grant is to a database:grant select on test_.t1 to 'alice'@'%'; only grants access to the_test_.t1_-table, not to test1.t1.

Authenticator options

The listener configuration defines authentication options which only affect the listener. authenticator defines the authentication plugins to use.authenticator_options sets various options. These options may affect an individual authentication plugin or the authentication as a whole. The latter are explained below. Multiple options can be given as a comma-separated list.

skip_authentication

• Type:

• Mandatory: No

• Dynamic: No

• Default: false

If enabled, MaxScale will not check the passwords of incoming clients and just assumes that they are correct. Wrong passwords are instead detected when MaxScale tries to authenticate to the backend servers.

This setting is mainly meant for failure tolerance in situations where the password check is performed outside of MaxScale. If, for example, MaxScale cannot use an LDAP-server but the backend databases can, enabling this setting allows clients to log in. Even with this setting enabled, a user account matching the incoming client username and IP must exist on the backends for MaxScale to accept the client.

This setting is incompatible with standard MariaDB/MySQL authentication plugin (MariaDBAuth in MaxScale). If enabled, MaxScale cannot authenticate clients to backend servers using standard authentication.

match_host

• Type:

• Mandatory: No

• Dynamic: No

• Default: true

If disabled, MaxScale does not require that a valid user account entry for incoming clients exists on the backends. Specifically, only the client username needs to match a user account, hostname/IP is ignored.

This setting may be used to force clients to connect through MaxScale. Normally, creating the user jdoe@% will allow the user jdoe to connect from any IP-address. By disabling match_host and replacing the user with_jdoe@maxscale-IP_, the user can still connect from any client IP but will be forced to go through MaxScale.

lower_case_table_names

• Type: number

• Mandatory: No

• Dynamic: No

• Default: 0

Controls database name matching for authentication when an incoming client logs in to a non-empty database. The setting functions similar to the MariaDB Server setting and should be set to the value used by the backends.

The setting accepts the values 0, 1 or 2:

• 0: case-sensitive matching (default)

• 1: convert the requested database name to lower case before using case-insensitive matching. Assumes that database names on the server are stored in lower case.

• 2: use case-insensitive matching.

true and false are also accepted for backwards compatibility. These map to 1 and 0, respectively.

The identifier names are converted using an ASCII-only function. This means that non-ASCII characters will retain their case-sensitivity.

Starting with MaxScale versions 2.5.25, 6.4.6, 22.08.5 and 23.02.2, the behavior of lower_case_table_names=1 is identical with how the MariaDB server behaves. In older releases the comparisons were done in a case-sensitive manner after the requested database name was converted into lowercase. Usinglower_case_table_names=2 will behave identically in all versions which makes it a safe alternative to use when a mix of older and newer MaxScale versions is being used.

CC BY-SA / Gnu FDL

Read-Write Splitting with MariaDB MaxScale

Read-Write Splitting with MariaDB MaxScale

The goal of this tutorial is to configure a system that appears to the client as a single database. MariaDB MaxScale will split the statements such that write statements are sent to the master server and read statements are balanced across the slave servers.

Setting up MariaDB MaxScale

This tutorial is a part of . Please read it and follow the instructions. Return here once basic setup is complete.

Configuring the service

After configuring the servers and the monitor, we create a read-write-splitter service configuration. Create the following section in your configuration file. The section name is also the name of the service and should be meaningful. For this tutorial, we use the name Splitter-Service.

router defines the routing module used. Here we use readwritesplit for query-level read-write-splitting.

A service needs a list of servers where queries will be routed to. The server names must match the names of server sections in the configuration file and not the hostnames or addresses of the servers.

The user and password parameters define the credentials the service uses to populate user authentication data. These users were created at the start of the .

For increased security, see .

Configuring the Listener

To allow network connections to a service, a network ports must be associated with it. This is done by creating a separate listener section in the configuration file. A service may have multiple listeners but for this tutorial one is enough.

The service parameter tells which service the listener connects to. For the_Splitter-Listener_ we set it to Splitter-Service.

A listener must define the network port to listen on.

The optional address-parameter defines the local address the listener should bind to. This may be required when the host machine has multiple network interfaces. The default behavior is to listen on all network interfaces (the IPv6 address ::).

Starting MariaDB MaxScale

For the last steps, please return to .

CC BY-SA / Gnu FDL

Filter Resource

Filter Resource

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.

KafkaImporter

KafkaImporter

Automatic Failover With MariaDB Monitor

Automatic Failover With MariaDB Monitor

The is not only capable of monitoring the state of a MariaDB master-slave cluster but is also capable of performing

Transaction Performance Monitoring Filter

Transaction Performance Monitoring Filter

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.

Configuring the Xpand Monitor

Configuring the Xpand Monitor

This document describes how to configure the Xpand monitor for use with a Xpand cluster.

Upgrading MariaDB MaxScale from 2.5 to 6

Upgrading MariaDB MaxScale from 2.5 to 6

This document describes possible issues when upgrading MariaDB MaxScale from version 2.5 to 6.