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

Configuring the Monitor

Define the monitor that monitors the servers.

[Replication-Monitor]
type=monitor
module=mariadbmon
servers=dbserv1,dbserv2,dbserv3
user=monitor_user
password=my_password
monitor_interval=2000ms

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 how long the monitor waits between each monitor tick.

Monitor User

The monitor user requires the REPLICA MONITOR privilege to do basic monitoring. To create a user with the proper grants, run:

If the automatic failover feature is used, the monitor user needs additional grants. See monitor documentation for more information.

_{This page is licensed: CC BY-SA / Gnu FDL}

Introduction

The goal of this guide is to set up a replication cluster managed by MaxScale that is reasonably tolerant of failures. That is, even if one part fails, the cluster continues to work. Additionally, transaction data are preserved whenever possible. All of this works automatically.

This guide assumes that the reader is familiar with MariaDB replication and GTIDs, MariaDB Monitor, and failover.

The Problem with Normal Replication

The basic problem of replication is that the primary and the replica are not always in the same state. When a commit is performed on the primary, the primary updates both the actual database file and the binary log. These items are updated in a transactional manner, either both succeed or fail. Then, the primary sends the binary log event to the replicas and they update their own databases and logs.

A replica may crash or lose connection to the primary. Fortunately, this is not a big issue, because once the replica returns, it can simply resume replication from where it left off. The replica cannot diverge as it is always either in the same state as the primary, or behind. Only if the primary lacks the binary logs from the moment the replica went down, the replica is lost.

If the primary crashes or loses network connection, failover may lose data. This depends on the point in time at which the crash happens:

1. If the primary managed to send all committed transactions to a replica, all is still well. The replica has all the data, and can be promoted to primary e.g. by MaxScale (which, in that case, promotes the most up-to-date replica). Once the old primary returns, it can rejoin the cluster.

2. If the primary crashes just after it committed a transaction and updated its binary log, but before it sent the binary log event to a replica, failover loses data and the old primary can no longer rejoin the cluster.

Let’s look at situation 2 in more detail. server1 is the original primary and server2 and server3 are its replicas, with server ids 1, 2 and 3, respectively. server1 is at gtid 1-1-101 when it crashes, while the others have replicated to the previous event 1-1-100. The example server status output below is for demonstration only, since in reality it would be unlikely that the monitor would manage to update the gtid-position of server1 right at the moment of crash.

server1 stays down long enough for failover to activate. (In MaxScale, the time is roughly monitor_interval * failcount.) server2 gets promoted, and MaxScale routes any new writes to it. server2 starts generating binary log events with gtids 1-2-101, 1-2-102 and so on. If server1 now comes back online, it can no longer rejoin as it is at gtid 1-1-101, which conflicts with 1-2-101.

At this point, the DBA could forcefully alter the gtid of server1, setting it to 1-1-100, which is in server2’s binary log, enabling rejoin. This is usually ill-advised, as changing the gtid does not roll back the actual data in server2’s database, meaning that data conflicts can still happen, perhaps days later. A more reliable way to handle this case is to rebuild server1 from one of the other servers. (MaxScale can help with this process, but it requires configuration and manual launching.)

If the old primary returns before failover activates, replication can continue regardless of what exact moment the crash happened. This means that the DBA should configure automatic failover to happen only after the primary has been down so long that the downsides of service outage outweigh the threat of losing data and the need of having to rebuild the old primary. monitor_interval * failcount should at a minimum be large enough that failover does not trigger due to a momentary network failure.

Semi-Synchronous Replication

Semi-synchronous replication offers a more reliable, but also a more complicated way to keep the cluster in sync. Semi-sync replication means that after the primary commits a transaction, it does not immediately return an OK to the client. Instead, the primary sends the binary log update to the replicas and waits for an acknowledgement from at least one replica before sending the OK message back to the client. This means that once the client gets the OK, the transaction data is typically on at least two servers. This is not absolutely certain, though, as the primary does not wait forever for the replica acknowledgement. If no replica responds in time, the primary switches to normal replication and returns OK to the client. This timeout is controlled by MariaDB Server setting rpl_semi_sync_master_timeout. If this limit is being hit, the client should notice it by the transaction visibly stalling. Even if this limit is not hit, throughput suffers compared to normal replication, due to the additional waiting.

Semi-synchronous replication by itself does not remove the possibility of the primary diverging after a crash. Scenario 2 presented in the previous section can still happen if the primary crashes after applying the transaction but before sending it to replicas. To increase crash safety, a few settings need to be tuned to change the behavior of the primary server both during replication and during startup after a crash (crash recovery).

Enable Semi-Synchronous Replication

To enable semi-synchronous replication, add the following to the configuration files of all servers:

These settings allow the servers to act as both semi-sync primary and replica, which is useful when combined with automatic failover. Restart the servers and run SHOW STATUS LIKE 'rpl%';, a statement that shows semi-sync related status variables. Check the values of Rpl_semi_sync_master_clients, Rpl_semi_sync_master_status and Rpl_semi_sync_slave_status. On the primary, their values should be:

1. Rpl_semi_sync_master_clients: <number of replicas>

2. Rpl_semi_sync_master_status: ON

3. Rpl_semi_sync_slave_status: OFF

On the replicas, the values should be:

1. Rpl_semi_sync_master_clients: 0

2. Rpl_semi_sync_master_status: ON

3. Rpl_semi_sync_slave_status: ON

Configure Wait Point and Startup Role

Next, change the point at which the primary server waits for the replica acknowledgement. By default, this is after the transaction is written to storage, which is not much different compared to normal replication. Set the following in the server configuration files:

AFTER_SYNC means that the primary sends the binary log event to replicas, after writing it to the binary log but before committing the actual transaction to its own storage. The primary only updates its internal storage when at least one replica gives the reply or when the rpl_semi_sync_master_timeout is reached. More importantly, this means that if the primary crashes while waiting for the reply, its binary log and storage engine will be out of sync. On startup, the server must thus decide what to do: either consider the binary log correct and apply the missing transactions to storage, or discard the unverified binary log events.

As of MariaDB Server 10.6.19, 10.11.9, 11.1.6, 11.2.5, 11.4.3 and 11.5.2, this decision is controlled by the startup option init-rpl-role. If set to MASTER, the server applies the transactions during startup, as it assumes to still be the primary. If init-rpl-role is set to SLAVE, the server discards the transactions. The former option does not improve the situation after a failover, as the primary could apply transactions that never made it to a replica. The latter option, on the other hand, ensures that, when the old primary comes back online, it does not have conflicting transactions and can rejoin the cluster as a replica. So, add the following to all server configurations:

Configure Service Restart Delay

This scheme is not entirely without issues. init-rpl-role=SLAVE means that the old primary (server1) always discards the unverified transactions during startup, even if the data did successfully replicate to a replica before the crash (server1 crashed after sending the data but before receiving the reply). This is not an issue if failover has already occurred by this point, as server1 can just fetch the same writes from the new primary (server2). However, if server1 comes back online quickly, before failover, it could be behind server2: server1 at gtid 1-1-100 and server2 at 1-1-101.

As server1 is still the primary, MaxScale sends any new writes to it. The next write will get gtid 1-1-101, meaning that both servers are on the same gtid but their actual contents likely differ. This will cause a replication error at some point.

This means that if a primary server crashes, it must stay down long enough for failover to occur, and to ensure MaxScale does not treat it as the primary once it returns. This can be enforced by changing the service settings. Run sudo systemctl edit mariadb.service on all server machines and add the following:

The configured time needs to be comfortably longer than monitor_interval * failcount configured to the MariaDB Monitor in MaxScale.

Failure Scenarios

The setup described above protects against a primary server crashing and its diversion from the rest of the cluster. It does not protect from data loss due to network outages. If the connection to the primary server is lost during a transaction commit (before the data was replicated), the primary will eventually apply the transaction to its storage. If a failover occurred during the network outage, the rest of the cluster has already continued under a new primary, leaving the old one diverged. This is similar to normal replication.

Some queries are not transactional and may still risk diverging replication. These are typically DDL queries such as CREATE TABLE, ALTER TABLE, and so on. DDL queries can be transactional if the changes they do are “small”, such as renaming a column. Large-scale DDL queries (e.g. ADD COLUMN to a table with a billion rows) are more vulnerable. The settings presented in this document were only tested against simple DML queries that updated a single row.

Client Perspective

If the client gets an OK to its commit command, then it knows that (assuming no semi-sync timeout happened) the transaction is in at least two servers. Only the primary has with certainty processed the update at this point; the replica may just have the event in its relay log. This means that SELECT queries routed to a replica (e.g. by the ReadWriteSplit router) may see old data.

If the client does not get an OK due to primary crash, the situation is more ambiguous:

1. Primary crashed before starting the commit

2. Primary crashed just before receiving the replica acknowledgement

3. Primary crashed just as it was about to send the OK

In case 1, the transaction is lost. In case 2, the transaction is present on the replica and will become visible at some point. In case 3, the transaction is present on both servers. Since MaxScale cannot know which case is in effect, it does not attempt transaction replay (even if configured) and disconnects the client. It’s up to the client to then reconnect and figure out the status of the transaction.

Test Summary

The server settings used during testing are below. rpl_semi_sync_master_timeout is given in milliseconds, rpl_semi_sync_slave_kill_conn_timeout in seconds.

The MariaDB Monitor in MaxScale was configured with both auto_failover and auto_rejoin enabled. Hundreds of failovers with continuous write traffic succeeded without a diverging old primary. When init-rpl-role was changed to MASTER, replication eventually broke, although this could take some time.

_{This page is licensed: CC BY-SA / Gnu FDL}

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 primary server and read statements are balanced across the replica servers.

Setting up MariaDB MaxScale

This tutorial is a part of MariaDB MaxScale Tutorial. 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 MaxScale Tutorial.

For increased security, see password encryption.

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 MaxScale Tutorial.

_{This page is licensed: CC BY-SA / Gnu FDL}

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 The Linux-PAM System Administrators' Guide for more information. A simple service definition used for testing this module is below.

Settings

pam_use_cleartext_plugin

• Type: boolean

• Mandatory: No

• Dynamic: No

• Default: false

If enabled, MaxScale communicates with the client as if using mysql_clear_password. 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: enumeration

• Mandatory: No

• Dynamic: No

• Values: password, password_2FA, suid

• Default: password

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 the limitations section 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: enumeration

• Mandatory: No

• Dynamic: No

• Values: none, mariadb

• Default: none

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

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 user mapping 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 user_mapping_file, 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 user mapping. 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; returns at least one row with GRANT PROXY ON ...)

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 Configuration. 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 this guide 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}

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.

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:

It would look like

when received by server.

_{This page is licensed: CC BY-SA / Gnu FDL}

Before upgrading to MariaDB MaxScale, it is critical to review the changes. This guide outlines new features, altered parameters, and deprecated functionality to ensure a smooth transition.

For more information about what has changed, please refer to the ChangeLog and release notes of the releases you are upgrading from and upgrading to.

Before starting the upgrade, any existing configuration files should be backed up.

Upgrading MariaDB MaxScale from 25.01 to 25.10

Service User Grants

The service users now require a SELECT grant on the mysql.global_priv table in order to be able to support authentication of users with multiple authentication mechanisms. If this grant is not given to the service users, a warning is logged. The following SQL shows how the grant is given to a user:

GRANT SELECT ON mysql.global_priv TO 'maxscale_user'@'%';

Monitor timeouts

In MaxScale 25.10, only one monitor backend timeout remains: backend_timeout. This replaces the old backend_connect_timeout, backend_write_timeout and backend_read_timeout, using the same value for all underlying timeouts. backend_connect_timeout is still supported as an alias for backend_timeout, but any values given to backend_write_timeout and backend_read_timeout are ignored.

Upgrading MariaDB MaxScale from 24.02 to 25.01

Readwritesplit

reuse_prepared_statements

The reuse_prepared_statements parameter has been replaced with the use of the PsReuse filter module.

The functionality that previously was enabled with:

[My-Readwritesplit]
type=service
router=readwritesplit
reuse_prepared_statements=true

Should now be implemented with:

[PsReuse]
type=filter
module=psreuse

[My-Readwritesplit]
type=service
router=readwritesplit
filters=PsReuse

optimistic_trx

The optimistic_trx parameter has been replaced with the use of the OptimisticTrx filter module.

The functionality that previously was enabled with:

[My-Readwritesplit]
type=service
router=readwritesplit
optimistic_trx=true

Should now be implemented with:

[OptimisticTrx]
type=filter
module=optimistictrx

[My-Readwritesplit]
type=service
router=readwritesplit
transaction_replay=true
filters=OptimisticTrx

Upgrading MariaDB MaxScale from 23.08 to 24.02

Downgrading to older major versions

The MaxScale packaging has been modified in 24.02 to include all of the necessary files in the package itself. This removes the need for a post-installation script that installs them while also clearly stating what's included in the package.

However, as a result of this change, downgrades from 24.02 to older major versions may cause the removal of necessary directories, namely the /var/cache/maxscale/ directory.

To downgrade from MaxScale 24.02 to an older MaxScale major release:

• Remove MaxScale 24.02 (e.g. dnf remove maxscale or apt -y remove maxscale)

• Install the older MaxScale version

Upgrading MariaDB MaxScale from 23.02 to 23.08

MariaDB Monitor switchover requires an additional grant on MariaDB Server 10.5 and later. See Cluster Manipulation Grants for more information.

Upgrading MariaDB MaxScale from 22.08 to 23.02

Removed Features

• The csmon and auroramon monitors have been removed.

• The obsolete maxctrl drain command has been removed.

• The maxctrl cluster commands have been removed.

Upgrading MariaDB MaxScale from 21.06 to 22.08

Removed Features

• The support for legacy encryption keys generated with maxkeys from pre-2.5 versions has been removed. This feature was deprecated in MaxScale 2.5 when the new key storage format was introduced. To migrate to the new key storage format, create a new key file with maxkeys and re-encrypt the passwords withmaxpasswd.

• The deprecated Database Firewall filter has been removed.

Upgrading MariaDB MaxScale from 2.5 to 21.06

Duration Type Parameters

Using duration type parameters without an explicit suffix has been deprecated in MaxScale 2.4. In MaxScale 6 they are no longer allowed when used with the REST API or MaxCtrl. This means that any create or alter commands in MaxCtrl that use a duration type parameter must explicitly specify the suffix of the unit.

For example, the following command:

maxctrl alter service My-Service connection_keepalive 30000

should be replaced with:

maxctrl alter service My-Service connection_keepalive 30000ms

Duration type parameters can still be defined in the configuration file without an explicit suffix but this behavior is deprecated. The recommended approach is to add explicit suffixes to all duration type parameters when upgrading to MaxScale 6.

Changed Parameters

threads

The default value of threads was changed to auto.

Removed Parameters

Core Parameters

The following deprecated core parameters have been removed:

• thread_stack_size

Schemarouter

The deprecated aliases for the schemarouter parameters ignore_databases andignore_databases_regex have been removed. They can be replaced withignore_tables and ignore_tables_regex.

In addition, the preferred_server parameter that was deprecated in 2.5 has also been removed.

mariadbmon

• MariaDBMonitor settings ignore_external_masters, detect_replication_lagdetect_standalone_master, detect_stale_master and detect_stale_slave have been removed. The first two were ineffective, the latter three are replaced by master_conditions and slave_conditions.

Session Command History

The prune_sescmd_history, max_sescmd_history and disable_sescmd_history have been made into generic service parameters that are shared between all routers that support it.

The default value of prune_sescmd_history was changed from false totrue. This was done as most MaxScale installations either benefit from it being enabled or are not affected by it.

Upgrading MariaDB MaxScale from 2.4 to 2.5

MaxAdmin

The deprecated MaxAdmin interface has been removed in 2.5.0 in favor of the REST API and the MaxCtrl command line client. The cli and maxscaled modules can no longer be used.

Authentication

The credentials used by services now require additional grants. For a full list of required grants, refer to the protocol documentation.

MariaDB-Monitor

The settings detect_stale_master, detect_standalone_master anddetect_stale_slave are replaced by master_conditions andslave_conditions. The old settings may still be used, but will be removed in a later version.

Password encryption

The encrypted passwords feature has been updated to be more secure. Users are recommended to generate a new encryption key and re-encrypt their passwords using the maxkeys and maxpasswd utilities. Old passwords still work.

Default Server State

The default state of servers in 2.4 was Running and in 2.5 it is nowDown. This was done to prevent newly added servers from being accidentally used before they were monitored.

Columnstore Monitor

It is now mandatory to specify in the configuration what version the monitored Columnstore cluster is.

[CSMonitor]
type=monitor
module=csmon
version=1.5
...

New binlog router

The binlog router delivered with MaxScale 2.5 is completely new and not 100% backward compatible with the binlog router delivered with earlier MaxScale versions. If you use the binlog router, carefully assess whether the functionality provided by the new one fulfills your requirements, before upgrading MaxScale.

Tee Filter

The tee filter parameter service has been deprecated in favor of the target parameter. All usages of service can be replaced with target.

Upgrading MariaDB MaxScale from 2.3 to 2.4

Section Names

Reserved Names

Section and object names starting with @@ are now reserved for internal use by MaxScale.

In case such names have been used, they must manually be changed in all configuration files of MaxScale, before MaxScale 2.4 is started.

Those files are:

• The main configuration file; typically /etc/maxscale.cnf.

• All nested configuration files; typically /etc/maxscale.cnf.d/*.

• All dynamic configuration files; typically /var/lib/maxscale/maxscale.cnd.d/*.

Whitespace in Names

Whitespace in section names that was deprecated in MaxScale 2.2 will now be rejected, which will cause the startup of MaxScale to fail.

To prevent that, section names like

[My Server]
...

[My Service]
...
servers=My Server

must be changed, for instance, to

[MyServer]
...

[MyService]
...
servers=MyServer

Durations

Durations can now be specified using one of the suffixes h, m, s and ms for specifying durations in hours, minutes, seconds and milliseconds, respectively.

Not providing an explicit unit has been deprecated in MaxScale 2.4, so it is advisable to add suffixes to durations. For instance,

some_param=60s
some_param=60000ms

Improved Admin User Encryption

MaxScale 2.4 will use a SHA2-512 hash for new admin user passwords. To upgrade a user to use the better hashing algorithm, either recreate the user or use themaxctrl alter user command.

MariaDB-Monitor

The following settings have been removed and cause a startup error if defined:

• mysql51_replication

• multimaster

• allow_cluster_recovery.

ReadWriteSplit

• If multiple masters are available for a readwritesplit service, the one with the lowest connection count is selected.

• If a master server is placed into maintenance mode, all open transactions are allowed to gracefully finish before the session is closed. To forcefully close the connections, use the --force option for maxctrl set server.

• The lazy_connect feature can be used as a workaround to . It also reduces the overall load on the system when connections are rapidly opened and closed.

• Transaction replays now have a limit on how many times a replay is attempted. The default values is five attempts and is controlled by thetransaction_replay_attempts parameter.

• If transaction replay is enabled and a deadlock occurs (SQLSTATE 40XXX), the transaction is automatically retried.

Upgrading MariaDB MaxScale from 2.2 to 2.3

Increased Memory Use

Starting with MaxScale 2.3.0 up to 40% of the memory can be used for caching parsed queries. The most noticeable change is that it improves performance in almost all cases where queries need to be parsed. Most of the time this happens when the readwritesplit router or filters are used.

The amount of memory that MaxScale uses can be controlled with thequery_classifier_cache_size parameter. For example, to limit the total memory to 1GB, add query_classifier_cache_size=1G to your configuration. To disable it, set the value to 0.

In addition to the aforementioned query classifier caching, the readwritesplit session command history is enabled by default in 2.3 but is limited to a maximum of 50 commands after which the history is disabled. This is unlikely to show in any metrics but it contributes to the increased memory footprint of MaxScale.

Unknown Global Parameters

All unknown parameters are now treated as errors. Check your configuration for errors if MaxScale fails to start after upgrading to 2.3.1.

passwd is deprecated

In the configuration file, passwords for monitors and services should be specified using password; the support for the deprecatedpasswd will be removed in the future. That is, the following

[The-Service]
type=service
passwd=some-service-password
...

[The-Monitor]
type=monitor
passwd=some-monitor-password
...

should be changed to

[The-Service]
type=service
password=some-service-password
...

[The-Monitor]
type=monitor
password=some-monitor-password
...

authenticator_options for servers is ignored

Authenticator options are now only used with listeners.

Upgrading MariaDB MaxScale from 2.1 to 2.2

Administrative Users

The file format for the administrative users used by MaxScale has been changed. Old style files are automatically upgraded and a backup of the old file is stored in /var/lib/maxscale/passwd.backup.

Regular Expression Parameters

Modules may now use a built-in regular expression string parameter type instead of a normal string when accepting patterns. The modules that use the new regex parameter type are qlafilter and tee. When inputting pattern, enclose the string in slashes, e.g. match=/^select/ defines the pattern ^select.

Binlog Server

Binlog server automatically accepts GTID connection from MariaDB 10 slave servers by saving all incoming GTIDs into a SQLite map database.

MaxCtrl Included in Main Package

In the 2.2.1 beta version MaxCtrl was in its own package whereas in 2.2.2 it is in the main maxscale package. If you have a previous installation of MaxCtrl, please remove it before upgrading to MaxScale 2.2.2.

Upgrading MariaDB MaxScale from 2.0 to 2.1

IPv6 Support

MaxScale 2.1.2 added support for IPv6 addresses. The default interface that listeners bind to was changed from the IPv4 address 0.0.0.0 to the IPv6 address ::. To bind to the old IPv4 address, add address=0.0.0.0 to the listener definition.

Persisted Configuration Files

Starting with MaxScale 2.1, any changes made with the newly added runtime configuration change will be persisted in a configuration file. These files are located in /var/lib/maxscale/maxscale.cnf.d/.

MaxScale Log Files

The name of the log file was changed from maxscaleN.log to maxscale.log. The default location for the log file is /var/log/maxscale/maxscale.log.

Rotating the log files will cause MaxScale to reopen the file instead of renaming them. This makes the MaxScale logging facility logrotate compatible.

ReadWriteSplit

The disable_sescmd_history option is now enabled by default. This means that slaves will not be recovered mid-session even if a replacement slave is available. To enable the legacy behavior, add the disable_sescmd_history=true parameter to the service definition.

Persistent Connections

The MariaDB session state is reset in MaxScale 2.1 for persistent connections. This means that any modifications to the session state (default database, user variable etc.) will not survive if the connection is put into the connection pool. For most users, this is the expected behavior.

User Data Cache

The location of the MariaDB user data cache was moved from/var/cache/maxscale/<Service> to /var/cache/maxscale/<Service>/<Listener>.

Galeramon Monitoring Algorithm

Galeramon will assign the master status only to the node which has a_wsrep_local_index_ value of 0. This will guarantee consistent writes with multiple MaxScales but it also causes slower changes of the master node.

To enable the legacy behavior, add root_node_as_master=false to the Galera monitor configuration.

MaxAdmin Editing Mode

The default editing mode was changed from vim to emacs mode. To start maxadmin in the legacy mode, use the -i option.

Upgrading MariaDB MaxScale from 1.4 to 2.0

MaxAdmin

The default way the communication between MaxAdmin and MariaDB MaxScale is handled has been changed from an internet socket to a Unix domain socket. The former alternative is still available but has been deprecated.

If no arguments are given to MaxAdmin, it will attempt to connect to MariaDB MaxScale using a Unix domain socket. After the upgrade you will need to provide at least one internet socket related flag - -h, -P,-u or -p - to force MaxAdmin to use the internet socket approach.

E.g.

user@host $ maxadmin -u admin

MySQL Monitor

The MySQL Monitor now assigns the stale state to the master server by default. In addition to this, the slave servers receive the stale slave state when they lose the connection to the master. This should not cause changes in behavior but the output of MaxAdmin will show new states when replication is broken.

Upgrading MaxScale from 1.3 to 1.4

Service user permissions

The service users now also need SELECT privileges on mysql.tables_priv. This is required for the resolution of table level grants. To grant SELECT privileges for the service user, replace the user and hostname in the following example.

GRANT SELECT ON mysql.tables_priv TO 'username'@'maxscalehost';

Password encryption

MaxScale 1.4 upgrades the used password encryption algorithms to more secure ones. This requires that the password files are recreated with the maxkeys tool.

SSL

The SSL configuration parameters are now a part of the listeners. If a service used the old style SSL configuration parameters, the values should be moved to the listener which is associated with that service.

Here is an example of an old style configuration.

[RW-Split-Router]
type=service
router=readwritesplit
servers=server1,server2,server3,server4
user=jdoe
passwd=BD26E4139A15280CA882264AA1551C70
ssl=required
ssl_cert=/home/user/certs/server-cert.pem
ssl_key=/home/user/certs/server-key.pem
ssl_ca_cert=/home/user/certs/ca.pem
ssl_version=TLSv12

[RW-Split-Listener]
type=listener
service=RW-Split-Router
port=3306

And here is the new, 1.4 compatible configuration style.

[RW-Split-Router]
type=service
router=readwritesplit
servers=server1,server2,server3,server4
user=jdoe
passwd=BD26E4139A15280CA882264AA1551C70

[RW-Split-Listener]
type=listener
service=RW-Split-Router
port=3306
ssl=required
ssl_cert=/home/user/certs/server-cert.pem
ssl_key=/home/user/certs/server-key.pem
ssl_ca_cert=/home/user/certs/ca.pem
ssl_version=TLSv12

Please also note that the enabled SSL mode is no longer supported due to the inherent security issues with allowing SSL and non-SSL connections on the same port. In addition to this, SSLv3 is no longer supported due to vulnerabilities found in it.

Upgrading MaxScale from 1.2 to 1.3

Binlog Router

The master server details are now provided with a master.ini file located in the binlog directory and it can be changed using a CHANGE MASTER TO command issued via a MySQL connection to MaxScale.

This file, properly filled, is now mandatory and without it the binlog router cannot connect to the master database.

Before starting binlog router after MaxScale 1.3 upgrade, please add relevant information to master.ini, example:

[binlog_configuration]
master_host=127.0.0.1
master_port=3308
master_user=repl
master_password=somepass
filestem=repl-bin

Additionally, the option servers=masterdb in the service definition is no longer required.

Upgrading MaxScale from 1.1 to 1.2

This document describes upgrading MaxScale from version 1.1.1 to 1.2 and the major differences in the new version compared to the old version. The major changes can be found in the Changelog.txt file in the installation directory and the official release notes in the ReleaseNotes.txt file.

Installation

Upgrading MaxScale will copy the MaxScale.cnf file in/usr/local/mariadb-maxscale/etc/ to /etc/ and renamed to maxscale.cnf. Binary log files are not automatically copied and should be manually moved from /usr/local/mariadb-maxscale to /var/lib/maxscale/.

File location changes

MaxScale 1.2 follows the FHS-standard and installs to /usr/ and /var/ subfolders. Here are the major changes and file locations.

• Configuration files are located in /etc/ and use lowercase letters: /etc/maxscale.cnf

• Binary files are in /usr/bin/

• Libraries and modules are in /usr/lib64/maxscale/. If you are using custom modules, please make sure they are in this directory before starting MaxScale.

• Log files are in the var/log/maxscale/ folder

• MaxScale's PID file is located in /var/run/maxscale/maxscale.pid

• Data files and other persistent files are in /var/lib/maxscale/

Running MaxScale without root permissions

MaxScale can run as a non-root user with the 1.2 version. RPM and DEB packages install the maxscale user and maxscale group which are used by the init scripts and systemd configuration files. If you are installing from a binary tarball, you can run the postinst script included in it to manually create these groups.

Upgrading MaxScale from 1.0 to 1.1

This document describes upgrading MaxScale from version 1.0.5 to 1.1.0 and the major differences in the new version compared to the old version. The major changes can be found in the Changelog.txt file in the installation directory and the official release notes in the ReleaseNotes.txt file.

Installation

If you are installing MaxScale from a RPM package, we recommend you back up your configuration and log files and that you remove the old installation of MaxScale completely. If you choose to upgrade MaxScale instead of removing it and re-installing it afterwards, the init scripts in /etc/init.d folder will be missing. This is due to the RPM packaging system but the script can be re-installed by running the postinst script found in the/usr/local/mariadb-maxscale folder.

# Re-install init scripts
cd /usr/local/mariadb-maxscale
./postinst

The 1.1.0 version of MaxScale installs into /usr/local/mariadb-maxscale instead of /usr/local/skysql/maxscale. This will cause external references to MaxScale's home directory to stop working so remember to update all paths with the new version.

MaxAdmin changes

The MaxAdmin client's default password in MaxScale 1.1.0 is mariadb instead of skysql.

_{This page is licensed: CC BY-SA / Gnu FDL}

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

[Throttle]
type = filter
module = throttlefilter
max_qps = 500
throttling_duration = 60000
...

[Routing-Service]
type = service
filters = Throttle

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.

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

• Type:

• Mandatory: Yes

• Dynamic: Yes

This defines how long a session is allowed to be throttled before MaxScale disconnects the session.

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

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

Overview

MariaDB MaxScale is an advanced proxy, router, and load balancer:

• MaxScale performs automated failover for MariaDB replication. When the primary server fails, MaxScale promotes a replica to be the new primary and redirects the remaining replicas to it.

• MaxScale's ReadWriteSplit router performs query-based load balancing. ReadWriteSplit routes each write statement to the current primary server and load balances read statements by routing them to the replica servers.

• MaxScale's ReadConnRoute router performs connection-based load balancing. ReadConnRoute routes each connection to a single primary or replica node, depending on configuration.

• MaxScale can import data from Kafka and export data into Kafka. MaxScale's KafkaCDC router streams data from MariaDB database products to a Kafka broker. MaxScale's KafkaImporter router streams data from Kafka to MariaDB database products.

• MaxScale provides built-in mechanisms to perform server maintenance without disrupting applications or clients. Servers can be set to maintenance mode using the command-line interface with MaxCtrl, a web browser with MaxGUI, or REST API.

• MaxScale's Cache filter can improve SELECT performance by caching and reusing results.

• Security and traffic controls for database connections and queries can be implemented with MaxScale. MaxScale's QLAfilter can be used to create an audit trail by logging all queries. MaxScale's RegexFilter can also perform audit logging or protect against SQL injection by matching queries against a regular expression and performing various actions on the query, such as logging it, modifying it, or routing it to a specific server.

MariaDB MaxScale can be deployed in the cloud or on-premises.

MariaDB MaxScale follows the MariaDB Enterprise release schedule, which can be found .

The supported MaxScale versions can be found in the latest .

_{This page is: Copyright © 2025 MariaDB. All rights reserved.}

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 transparent 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 an 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. Filters 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 an email to or use the interface.

Bugs can be reported in the MariaDB Jira

Information about installing MariaDB MaxScale 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 .

_{This page is licensed: CC BY-SA / Gnu FDL}

Overview

MaxScale's MaxScale's REST API is used by both MaxCtrl and MaxGUI.

The REST API is enabled by default. However, the default configuration is not optimal for production systems, because:

• It only allows requests from the local host address by default.

• It does not use TLS by default.

• It used a hard-coded user (admin) and password (mariadb) by default.

1. for remote connections by configuring several global parameters in maxscale.cnf.

For example:

1. Restart the MaxScale instance.

_{This page is: Copyright © 2025 MariaDB. All rights reserved.}

Overview

MariaDB MaxScale provides various administrative interfaces can be used to perform the following tasks:

• Setting a server to maintenance mode.

• Reconfiguring monitors.

• Reconfiguring routers.

• And much more.

MaxScale supports different administrative interfaces for different kinds of environments and preferences:

_{This page is: Copyright © 2025 MariaDB. All rights reserved.}

• REST API

  • Configuring the REST-API

  • Creating a REST API User

• TLS

• MaxGUI

• Operations

_{This page is: Copyright © 2025 MariaDB. All rights reserved.}

While MariaDB MaxScale is a powerful tool for managing MariaDB deployments, it's essential to be aware of its limitations to ensure proper configuration and avoid unexpected behavior. This guide highlights key considerations when deploying and using MaxScale.

• Limitation: Older versions of MaxScale (2.1.2 and earlier) had a strict line length limit of 1024 characters in the configuration file (maxscale.cnf).

• Consideration: Ensure your MaxScale configuration lines do not exceed this limit for older versions. Modern versions (e.g., 2.3.0 and later) have significantly increased this limit (to 16,777,216 characters), making it less of a concern for current deployments. Always check the documentation for your specific MaxScale version.

MaxGUI is a browser-based interface for MaxScale REST-API and query execution.

To enable MaxGUI in a testing mode, add admin_host=0.0.0.0 and admin_secure_gui=false under the [maxscale] section of the MaxScale configuration file. Once enabled, MaxGUI will be available on port 8989: http://127.0.0.1:8989/

To make MaxGUI secure, set admin_secure_gui=true and configure both theadmin_ssl_key

MariaDB MaxScale does not require any external software at version-pinned ranges that you must track separately from the package itself. The platform packages (RPM, DEB) and the container image declare their runtime requirements through the standard packaging metadata, and your package manager resolves and installs the right versions for your operating system release.

This page describes how to enumerate those dependencies on each supported platform, so you can answer questions such as "Which exact libraries does MaxScale link against on this host?" or "What needs to be available before I can install or upgrade?" without guessing.

Before installation, query the package's declared dependencies from the repository or a downloaded .deb file.

After installation, inspect the installed package metadata:

The Depends:

Overview

MaxScale's Native Authenticator is compatible with MariaDB Enterprise Server and MariaDB Xpand. It authenticates database users with the mysql_native_password authentication plugin.

_{This page is: Copyright © 2025 MariaDB. All rights reserved.}

MariaDB MaxScale is a database proxy that extends the high availability, scalability, and security of MariaDB Server while at the same time simplifying application development by decoupling it from underlying database infrastructure.

MariaDB MaxScale is engineered with an extensible architecture to support plugins, extending its functionality beyond transparent load balancing to become, for example, a database firewall. With built-in plugins for multiple routers, filters and protocols, MariaDB MaxScale can be configured to forward database requests and modify database responses based on business and technical requirements — for example, to mask sensitive data or scale reads.

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 where x.y.z is the same as the corresponding version and OS identifies the operating system, e.g. maxscale-2.5.6.centos.7.tar.gz.

In order to use the tarball, the following libraries are required:

• libcurl

• libaio

• OpenSSL

• gnutls

• libatomic

• unixODBC

In addition, in order to use the maxctrl command line client, you'll need to install Node.js. The minimum required version is Node.js 10 but we recommend using the latest LTS release of Node.js if the one in the operating system repositories is old.

The tarball has been built with the assumption that it will be installed in /usr/local. However, it is possible to install it in any directory, but in that case MariaDB MaxScale must be invoked with a flag.

If you have root access to the system you probably want to install MariaDB MaxScale under the user and group maxscale.

The required steps are as follows:

Creating the symbolic link is necessary, since MariaDB MaxScale has been built with the assumption that the plugin directory is /usr/local/maxscale/lib/maxscale.

The symbolic link also makes it easy to switch between different versions of MariaDB MaxScale that have been installed side by side in /usr/local; just make the symbolic link point to another installation.

In addition, the first time you install MariaDB MaxScale from a tarball you need to create the following directories:

and make maxscale the owner of them:

The following step is to create the MariaDB MaxScale configuration file /etc/maxscale.cnf. The file etc/maxscale.cnf.template can be used as a base. Please refer to for details.

When the configuration file has been created, MariaDB MaxScale can be started.

The -d flag causes maxscale not to turn itself into a daemon, which is advisable the first time MariaDB MaxScale is started, as it makes it easier to spot problems.

If you want to place the configuration file somewhere else but in /etc you can invoke MariaDB MaxScale with the --config flag, for instance, --config=/usr/local/maxscale/etc/maxscale.cnf.

Note also that if you want to keep everything under /usr/local/maxscale you can invoke MariaDB MaxScale using the flag --basedir.

That will cause MariaDB MaxScale to look for its configuration file in/usr/local/maxscale/etc and to store all runtime files under /usr/local/maxscale/var.

Enter a directory where you have the right to create a subdirectory. Then do as follows.

The next step is to create the MaxScale configuration file maxscale-x.y.z/etc/maxscale.cnf. The file maxscale-x.y.z/etc/maxscale.cnf.template can be used as a base. Please refer to for details.

When the configuration file has been created, MariaDB MaxScale can be started.

With the flag --basedir, MariaDB MaxScale is told where the lib, etc and var directories are found. Unless it is specified, MariaDB MaxScale assumes the lib directory is found in /usr/local/maxscale, and the var and etc directories in /.

It is also possible to specify the directories and the location of the configuration file individually. Invoke MaxScale like

to find out the appropriate flags.

_{This page is licensed: CC BY-SA / Gnu FDL}

Quickstart Guide: MariaDB MaxScale Overview

MariaDB MaxScale is an advanced, database proxy, router, and load balancer designed to enhance the scalability, high availability, and security of your MariaDB deployments. It acts as an intelligent intermediary between your applications and your MariaDB servers, abstracting the underlying database topology.

1. What is MariaDB MaxScale?

MaxScale is not a database itself, but a sophisticated gateway that understands the MariaDB protocol. It intercepts client connections and routes them to the appropriate backend MariaDB servers based on configured rules, health checks, and workload types. This allows for flexible and dynamic management of database traffic without requiring changes to the application code.

2. Core Functionalities and Benefits

MariaDB MaxScale provides several key functionalities that contribute to optimizing MariaDB environments:

• Intelligent Routing and Load Balancing:

  • ReadWriteSplit Router: This is a primary feature that automatically distinguishes between read (SELECT) and write (INSERT, UPDATE, DELETE, DDL) statements. It intelligently routes all write statements to the designated primary server and distributes read statements across multiple replica servers, significantly improving read scalability and reducing the load on the primary.

  • Other Routers: MaxScale offers various routers for different use cases, such as routing to specific databases, connection-based routing, or custom routing logic.

• High Availability and Automated Failover:

  • MaxScale constantly monitors the health of your MariaDB servers. In a replication setup (e.g., primary-replica or Galera Cluster), if the primary server fails, MaxScale can automatically detect the failure, promote a healthy replica to become the new primary, and seamlessly redirect client connections to the newly promoted server. This minimizes downtime and ensures continuous operation.

• Seamless Server Maintenance:

  • MaxScale provides built-in mechanisms to place backend servers into maintenance mode without interrupting connected applications or clients. This allows administrators to perform tasks like patching, upgrades, or reconfigurations on individual servers while MaxScale intelligently redirects traffic away from them. Management can be done via the maxctrl command-line interface, MaxGUI (web interface), or REST API.

• Security and Traffic Control:

  • MaxScale can implement granular security policies and traffic controls for database connections and queries. This includes features like firewalling, query filtering, and authentication proxying.

  • QLAfilter (Query Log Anonymizer Filter): This filter can be used to create an audit trail by logging all queries, with options to anonymize sensitive data, aiding in security audits and performance analysis.

• Protocol Compatibility: MaxScale is designed to be compatible with standard MariaDB and MySQL client protocols, making it transparent to most applications.

By abstracting the database topology and intelligently managing connections, MariaDB MaxScale helps organizations achieve higher availability, better performance, and enhanced security for their MariaDB deployments.

MariaDB MaxScale is an advanced, open-source database proxy that provides intelligent routing, load balancing, high availability, and security features for your MariaDB and MySQL deployments. It acts as an intermediary, forwarding database statements to one or more backend database servers based on configured rules and server roles, all transparently to your applications.

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.

The recommended approach is to use to install MaxScale. After enabling the repository by following the instructions, MaxScale can be installed with the following commands.

• For RHEL/Rocky Linux/Alma Linux, use dnf install maxscale.

After MaxScale has been installed, test that MaxScale starts by executing sudo systemctl start maxscale, followed by sudo systemctl status maxscale.

Stop MaxScale with sudo systemctl stop maxscale. The log file is written at /var/log/maxscale/maxscale.log. If the startup failed, the log should explain why. With the default configuration file, MaxScale does not yet do anything interesting.

A functional configuration of MaxScale should include a listener, a service, a monitor and one or more servers. An incoming client connects to a listener port. Once the connection is established, the listener passes the client to a service. The service then handles all client traffic, from authentication to disconnection. Client queries are routed to servers and query results from servers back to the client. A monitor regularly checks the status of the servers.

MariaDB MaxScale incorporates robust authentication modules to manage client access and ensure secure communication with your backend MariaDB servers. Understanding these modules is crucial for securing your database deployments when using MaxScale.

MaxScale's authentication modules (often referred to as "authenticator plugins") are components that handle client authentication. They determine how incoming clients verify their identity to MaxScale and, in turn, how MaxScale authenticates itself to the backend MariaDB servers. This process is similar to how authentication works directly with MariaDB Server using the MySQL protocol.

MaxScale employs a User Account Manager (UAM) for services that use a MariaDB protocol listener.

• The UAM is responsible for storing and managing user account information.

When using MaxScale, it is often necessary to temporarily remove a server from the load balancing pool without actually shutting down the server. This is usually needed to perform maintenance on the server, such as when upgrading the server's software or when performing schema upgrades.

MaxScale allows users to set servers to "maintenance mode", which prevents MaxScale from routing traffic to the server and prevents it from being elected as the new primary server during failover or switchover.

MaxCtrl is a command-line utility that can perform administrative tasks using MaxScale's REST API. It can be used to set a server to maintenance mode.

1. Configure the REST API if the default configuration is not sufficient.

The routes write queries to the primary server and load balances read-only queries between one or more replica servers. If a server fails, then the router may need to retry failed queries on a different server. The retry may need to be delayed in some cases, such as when is in progress.

1. Configure delayed retries for failed queries by configuring several parameters for the Read/Write Split Router in maxscale.cnf.

The Read/Write Split Router (readwritesplit) routes write queries to the primary server and load balances read-only queries between one or more replica servers. If the primary server fails, then the router can automatically reconnect existing client connections to the new primary server.

1. Configure automatic primary server re-connection by configuring several parameters for the Read/Write Split Router in maxscale.cnf.

MaxScale's performs query-based load balancing. For each client connected to MaxScale, it opens up connections to multiple back-end database servers. When the client sends a write query to MaxScale, it routes the query to the connection opened with the primary server. When the client sends a read query to MaxScale, it routes the query to a connection opened with one of the replicas.

The supports:

• deployments.

The output of the maxctrl create report command produces a JSON payload that contains the current state of MaxScale. This includes the runtime configuration and the status all objects in MaxScale.

The report can be created with:

After the command completes, the data is in maxctrl-report.json.

The file in which the output is stored is the only argument to this command. Recent versions of maxctrl pipe the output to the standard output if no filename is given. This can be useful for environments where copying files may be difficult (e.g. docker).

The easiest way to inspect the JSON output is to use the jq program:

This document describes how to configure a Galera cluster 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 primary and other nodes as replica. Only those nodes that are active members of the cluster are considered when making the choice of primary node. The primary node will be the node with the lowest value of wsrep_local_index.

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.

There are two options for representing the password, either plain text or encrypted passwords may be used. In order to use encrypted passwords a set of keys must be generated that will be used by the encryption and decryption process. To generate the keys, use the maxkeys command.

By default the key file will be generated in /var/lib/maxscale. If a different directory is required, it can be given as the first argument to the program. For more information, see maxkeys --help.

Once the keys have been created the maxpasswd command can be used to generate the encrypted password.

The username and password, either encrypted or plain text, are stored in the service section using the

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.

To add the filter to a service, define an instance of the filter and then add it to a service's filters

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

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.

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.

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.

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.

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 #

In MariaDB MaxScale, authenticators perform the following tasks:

• Authenticating clients that connect to MaxScale

• Authenticating connections to back-end MariaDB Enterprise Server and MariaDB Xpand nodes

The routes write queries to the primary server and load balances read-only queries between one or more replica servers. If a read-only query fails, then the router can retry the query on a different server.

1. Configure retries for failed reads by configuring the retry_failed_reads parameter for the Read/Write Split Router in maxscale.cnf.

For example:

Overview

This guide explains how to deploy MariaDB MaxScale using the official Docker image available via the MariaDB Enterprise Registry. MaxScale is a database proxy that offers load balancing, high availability, and security between MariaDB database servers and your applications.

Prerequisites

Before deploying MaxScale with Docker, ensure the following requirements are met:

• Docker installed and running on your system

• A MariaDB Enterprise subscription

• Your MariaDB Enterprise token and account credentials

MariaDB Docker images are stored in the private MariaDB Enterprise Docker Registry. To retrieve MaxScale Docker images, you must first authenticate with the MariaDB Enterprise Registry using the Customer Download Token.

1. Log in to the .

2. Locate your Customer Download Token.

3. Copy the token to use for authentication when logging in to Docker.

As a best practice, ensure that your token is stored securely.

For authentication, use your Customer Download Token to the MariaDB Enterprise Registry:

Replace:

• <ENTERPRISE_TOKEN> with your Customer Download Token.

• <your-mariadb.id-email> with the email associated with your MariaDB account.

The following tags are available for MaxScale Docker image:

Select the appropriate Docker image tag from the maxscale repository.

Pull the latest MaxScale image using docker pull:

Or pull a specific version:

The default setup of MaxScale, which just activates the REST API, is used by this command:

For a quick start that includes database proxy port access:

MaxScale must be configured to define backend servers, monitoring, routing services, and listeners. For production deployments, you can either use the default basic configuration or provide a custom configuration file.

The REST API, which listens on port 8989, is the only feature enabled by the container's minimum default configuration. The default login credentials are:

• Username: admin

• Password: mariadb

Run MaxScale with default configuration using docker run:

Parameters:

• -d: Run container in detached mode

• --name mxs: Container name "mxs"

• -p 3306:3306: Map database proxy port

After launching the container, test the REST API using curl:

Before starting the container:

1. Create a directory on your system to store the configuration file.

2. Place your-maxscale.cnf file in this directory.

3. The container will mount this directory in order to enable persistent configuration.

Create a custom MaxScale configuration file and mount it into the container:

If you need to modify the maxscale section, override the main configuration file:

To access the running the container's shell:

After starting the container, verify that MaxScale is running correctly. Check the container status by executing the below command:

To stop the running container, use:

Delete the container after use with the following command:

Overview

When using MaxScale, it is often necessary to temporarily remove a server from the load balancing pool without actually shutting down the server. This is usually needed to perform maintenance on the server, such as when upgrading the server's software or when performing schema upgrades.

MaxScale allows users to set servers to "maintenance mode", which prevents MaxScale from routing traffic to the server and prevents it from being elected as the new primary server during failover or switchover.

MaxGUI is a graphical utility that can perform administrative tasks using MaxScale's MaxScale's REST API. It can be used to set a server to maintenance mode.

Setting a Server to Maintenance Mode

1. Configure MaxScale for MaxGUI.

2. Visit MaxGUI in your web browser. For example, if you are accessing it from local host with the default port, then visit this address:

3. Enter your username and password to log in.

4. On the dashboard, the "Servers" tab is shown by default.

5. Click the server that you want to set to maintenance mode. This will bring up a page for the specific server.
6. Click the gear icon at the top left corner of the page next to the server name. This will show some options in a popup.
7. Click the wrench icon. This will open a popup window.
8. Click the "Set" button. If the specified server is a primary server, then MaxScale will allow open transactions to complete before closing any connections.

2. Visit MaxGUI in your web browser. For example, if you are accessing it from local host with the default port, then visit this address:

3. Enter your user and password to login.

_{This page is: Copyright © 2025 MariaDB. All rights reserved.}

The Read/Write Split Router (readwritesplit) routes write queries to the primary server and load balances read-only queries between one or more replica servers. If a server fails, then the router may need to replay in-progress transactions on a different server.

Session Command History

The Read/Write Split Router (readwritesplit) maintains connection state on replica servers by keeping a session command history. If the router has to create a new connection, then it replays these session commands from the previous connection on the new connection.

Minimizing Memory Usage of Session Command History

The session command history can require a lot of memory if connections are long-lived. In these cases, there are two options to limit memory usage:

• Configure a maximum size for the session command history

• Disable the session command history. This option is not recommended, because you would lose out on the benefits of the session command history.

1. Configure transaction replay by configuring several parameters for the Read/Write Split Router in maxscale.cnf.

For example:

1. Restart the MaxScale instance.

_{This page is: Copyright © 2025 MariaDB. All rights reserved.}

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:// or gs://, 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.

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:

This feature has been removed in MaxScale 24.02.

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.

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

• Type: string

• Mandatory: No

• Dynamic: Yes