This document is designed as a quick introduction to setting up MariaDB MaxScale.

The installation and configuration of the MariaDB Server is not covered in this document. See the following MariaDB articles for more information on setting up a primary-replica-cluster or a Galera-cluster: Setting Up Replication and Getting Started With MariaDB Galera Cluster

This tutorial assumes that one of the standard MaxScale binary distributions is used and that MaxScale is installed using default options.

Building from source code in GitHub is covered in Building from Source.

Installing MaxScale

The precise installation process varies from one distribution to another. Details on package installation can be found in the .

Creating a user account for MaxScale

MaxScale checks that incoming clients are valid. To do this, MaxScale needs to retrieve user authentication information from the backend databases. Create a special user account for this purpose by executing the following SQL commands on the primary server of your database cluster. The following tutorials will use these credentials.

MariaDB versions 10.2.2 to 10.2.10 also require GRANT SELECT ON mysql.* TO 'maxscale'@'%';

Creating client user accounts

Because MariaDB MaxScale sits between the clients and the backend databases, the backend databases will see all clients as if they were connecting from MaxScale's address. This usually means that two sets of grants for each user are required.

For example, assume that the user 'jdoe'@'client-host' exists and MaxScale is located at maxscale-host. If 'jdoe'@'client-host' needs to be able to connect through MaxScale, another user, 'jdoe'@'maxscale-host', must be created. The second user must have the same password and similar grants as 'jdoe'@'client-host'.

The quickest way to do this is to first create the new user:

Then do a SHOW GRANTS query:

Then copy the same grants to the 'jdoe'@'maxscale-host' user.

An alternative to generating two separate accounts is to use one account with a wildcard host ('jdoe'@'%') which covers both hosts. This is more convenient but less secure than having specific user accounts as it allows access from all hosts.

Creating the configuration file

MaxScale reads its configuration from /etc/maxscale.cnf. A template configuration is provided with the MaxScale installation.

A global maxscale section is included in every MaxScale configuration file. This section sets the values of various global parameters, such as the number of threads MaxScale uses to handle client requests. To set thread count to the number of available cpu cores, set the following.

Configuring the servers

Read the mini-tutorial for server configuration instructions.

Configuring the monitor

The type of monitor used depends on the type of cluster used. For a primary-replica cluster read . For a Galera cluster read .

Configuring the services and listeners

This part is covered in two different tutorials. For a fully automated read-write-splitting setup, read the . For a simple connection based setup, read the .

Starting MaxScale

After configuration is complete, MariaDB MaxScale is ready to start. For systems that use systemd, use the systemctl command.

For older SysV systems, use the service command.

If MaxScale fails to start, check the error log in /var/log/maxscale/maxscale.log to see if any errors are detected in the configuration file.

Checking MaxScale status with MaxCtrl

The maxctrl-command can be used to confirm that MaxScale is running and the services, listeners and servers have been correctly configured. The following shows expected output when using a read-write-splitting configuration.

MariaDB MaxScale is now ready to start accepting client connections and route queries to the backend cluster.

More options can be found in the , and .

For more information about MaxCtrl and how to secure it, see the .

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

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 maxctrl create report command was added in MaxScale 2.5.20.

Creating a MaxCtrl Report

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

Using jq

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

It is usually available as a package in most operating systems.

List servers

List services

List monitors

List listeners

List filters

List keys of objects

This can be combined with the object field access to list the fields of sub-objects. The following lists the keys in the first server object.

Get a specific service

Change the RW-Split-Router to the name of the service you're looking for.

Get a specific monitor

Change the MariaDB-Monitor to the name of the monitor you're looking for.

Get a specific server

Change the DB-1 to the name of the server you're looking for.

Find the monitor for a server

Change DB-1 to the name of the server you're looking for.

Memory used by the query classifier cache

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

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

MariaDB Monitor can do more than just monitor the state of a MariaDB replication cluster. The monitor can perform cluster manipulation operations such as failover, switchover and rejoin. By default, these operations are launched manually, but they can be configured to also trigger automatically. All replication modifying operations assume GTID-based replication, and refuse to launch or may work incorrectly when using file-and-position-based replication. Also, the operations are mainly designed to work with simple topologies, for instance 1 primary and one to multiple replicas. More complicated setups (multilayered replication, multimaster rings etc.) may work, but should be tested separately to ensure the results are predictable.

The following examples have four servers: server1 is the initial primary and server2 to server4 are replicas. The servers are monitored by TheMonitor. The MaxScale configuration file looks as follows:

Manual Failover

If everything is in order, the state of the cluster looks like this:

If the primary server goes down, the cluster looks like this:

Since automatic failover is not enabled, failover needs to be invoked manually:

The MaxCtrl command invocation is composed of the following parts:

1. call command launches a module command

2. mariadbmon is the module which implements the command

3. failover is the command to invoke

In MaxScale 25.10 and later, the configured monitor name can be used as the module name. The above command invocation can thus be shortened to maxctrl call command TheMonitor failover. This alternate form works for module commands in general.

During failover, TheMonitor selects the best replica, promotes it to primary and modifies the other replicas to replicate from the new primary. The main criteria for best replica is being most up-to-date. If the best replica has unprocessed events in its relay log, meaning it has received binary log events from the old primary but not processed them, failover stalls until the processing is complete. If multiple replicas are equally good, the monitor prefers to promote servers in the order stated in the servers-setting.

After failover completes, the cluster looks like this:

If server1 comes back online, it is not rejoined to the cluster:

This case can be handled by the . For more details on what exactly failover does, see .

Automatic Failover

To enable automatic failover, add auto_failover=true to the monitor section in the configuration file:

When everything is running fine, the cluster state is as follows:

If server1 goes down, the monitor performs failover automatically and promotes an existing replica to primary.

If you are continuously monitoring the server states, you may notice that, for a brief period, the state of server1 is Down and the state of server2 is still Slave, Running. This is because the monitor does not begin failover immediately when the server goes down, as the problem could be temporary. The monitor waits for server1 to come back for monitor intervals. The recommended value for failcount depends on monitor_interval and the stability of the network.

Rejoin

To enable automatic rejoin, simply add auto_rejoin=true to the monitor section in the configuration file:

When automatic rejoin is enabled, MariaDB Monitor attempts to rejoin a failed primary as a replica if it comes back online.

In the next example, failover (either automatic or manual) has promoted server2 to replace failed primary server1:

If server1 reappears, the monitor detects that, and attempts to rejoin the old primary as a replica. Rejoin is not always possible: If the old primary processed a write (just before crashing), and that write was never replicated to the new primary, then automatic rejoin is not possible because the old and new primaries have diverged.

If rejoin succeeds, the cluster state ends up looking like:

Switchover

Switchover is for cases when you explicitly want to move the primary role from one server to another. Switchover is safer than failover, as switchover prevents writes to the cluster during the operation.

Continuing from the cluster state at the end of the previous example, to make server1 primary again, issue the following command:

The MaxCtrl command invocation is composed of the following parts:

1. call command launches a module command

2. mariadbmon is the module which implements the command

3. switchover is the command to invoke

Specifying the current primary is optional. The name of the new primary server can also be left out if autoselection is tolerable, leaving just maxctrl call command mariadbmon switchover TheMonitor. As with failover, in MaxScale 25.10, the configured monitor name can be used as the module name: maxctrl call command TheMonitor switchover.

If the switchover succeeds, we end up with the following cluster state:

For more details on what exactly switchover does, see .

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

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.

The address and port parameters tell where the server is located.

Enabling TLS

To enable encryption for the MaxScale-to-MariaDB communication, add ssl=true to the server section. To enable server certificate verification, add

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.

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.

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 .

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 .

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

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.

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.

maxkeys

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 user and password parameters.

If a custom location was used for the key file, give it as the first argument tomaxpasswd and pass the password to be encrypted as the second argument. For more information, see maxkeys --help.

Here is an example configuration that uses an encrypted password.

If the key file is not in the default location, the parameter must be set to the directory that contains it.

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

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 replica servers.

Setting up MariaDB MaxScale

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

What Are Filters?

The filter mechanism in MariaDB MaxScale is a means by which processing can be inserted into the flow of requests and responses between the client connection to MariaDB MaxScale and the MariaDB MaxScale connection to the backend database servers. The path from the client side of MariaDB MaxScale out to the actual database servers can be considered a pipeline, filters can then be placed in that pipeline to monitor, modify, copy or block the content that flows through that pipeline.

Types Of Filter

Filters can be divided into a number of categories

Logging filters

Logging filters do not in any way alter the statement or results of the statements that are passed through MariaDB MaxScale. They merely log some information about some or all of the statements and/or result sets.

Two examples of logging filters are contained within the MariaDB MaxScale, a filter that will log all statements and another that will log only a number of statements, based on the duration of the execution of the query.

Statement rewriting filters

Statement rewriting filters modify the statements that are passed through the filter. This allows a filter to be used as a mechanism to alter the statements that are seen by the database, an example of the use of this might be to allow an application to remain unchanged when the underlying database changes or to compensate for the migration from one database schema to another.

Result set manipulation filters

A result set manipulation filter is very similar to a statement rewriting but applies to the result set returned rather than the statement executed. An example of this may be obfuscating the values in a column.

Routing hint filters

Routing hint filters are filters that embed hints in the request that can be used by the router onto which the query is passed. These hints include suggested destinations as well as metric that may be used by the routing process.

Firewall filters

A firewall filter is a mechanism that allows queries to be blocked within MariaDB MaxScale before they are sent on to the database server for execution. They allow constructs or individual queries to be intercepted and give a level of access control that is more flexible than the traditional database grant mechanism.

Pipeline control filters

A pipeline filter is one that has an affect on how the requests are routed within the internal MariaDB MaxScale components. The most obvious version of this is the ability to add a "tee" connector in the pipeline, duplicating the request and sending it to a second MariaDB MaxScale service for processing.

Filter Definition

Filters are defined in the configuration file, typically maxscale.cnf, using a section for each filter instance. The content of the filter sections in the configuration file various from filter to filter, however there are always to entries present for every filter, the type and module.

The type is used by the configuration manager within MariaDB MaxScale to determine what this section is defining and the module is the name of the plugin that implements the filter.

When a filter is used within a service in MariaDB MaxScale the entry filters= is added to the service definition in the ini file section for the service. Multiple filters can be defined using a syntax akin to the Linux shell pipe syntax.

The names used in the filters= parameter are the names of the filter definition sections in the ini file. The same filter definition can be used in multiple services and the same filter module can have multiple instances, each with its own section in the ini file.

Filter Examples

The filters that are bundled with the MariaDB MaxScale are documented separately, in this section a short overview of how these might be used for some simple tasks will be discussed. These are just examples of how these filters might be used, other filters may also be easily added that will enhance the MariaDB MaxScale functionality still further.

Log The 30 Longest Running Queries

The top filter can be used to measure the execution time of every statement within a connection and log the details of the longest running statements.

The first thing to do is to define a filter entry in the ini file for the top filter. In this case we will call it "top30". The type is filter and the module that implements the filter is called topfilter.

In the definition above we have defined two filter specific parameters, the count of the number of statement to be logged and a filebase that is used to define where to log the information. This filename is a stem to which a session id is added for each new connection that uses the filter.

The filter keeps track of every statement that is executed, monitors the time it takes for a response to come back and uses this as the measure of execution time for the statement. If the time is longer than the other statements that have been recorded, then this is added to the ordered list within the filter. Once 30 statements have been recorded those statements that have been recorded with the least time are discarded from the list. The result is that at any time the filter has a list of the 30 longest running statements in each session.

When the session ends a report will be written for the session into the logfile defined. That report will include the top 30 longest running statements, plus summary data for the session;

• The time the connection was opened.

• The host the connection was from.

• The username used in the connection.

• The duration of the connection.

Duplicate Data From Your Application Into Cassandra

The scenario we are using in this example is one in which you have an online gaming application that is designed to work with a MariaDB database. The database schema includes a high score table which you would like to have access to in a Cassandra cluster. The application is already using MariaDB MaxScale to connect to a MariaDB Galera cluster, using a service names BubbleGame. The definition of that service is as follows

The table you wish to store in Cassandra in called HighScore and will contain the same columns in both the MariaDB table and the Cassandra table. The first step is to install a MariaDB instance with the Cassandra storage engine to act as a bridge server between the relational database and Cassandra. In this bridge server add a table definition for the HighScore table with the engine type set to Cassandra. See for details. Add this server into the MariaDB MaxScale configuration and create a service that will connect to this server.

Next add a filter definition for the tee filter that will duplication insert statements that are destined for the HighScore table to this new service.

The above filter definition will cause all statements that match the regular expression inset.*HighScore.*values to be duplication and sent not just to the original destination, via the router but also to the service named Cassandra.

The final step is to add the filter to the BubbleGame service to enable the use of the filter.

_{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 B in more detail. server1 is the original primary as and its replicas are server2 and server3, 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 .)

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

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. The scenario B 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 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 . 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 A, the transaction is lost. In case B, the transaction is present on the replica and will become visible at some point. In case C, 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}

Sharding is the method of splitting a single logical database server into separate physical databases. This tutorial describes a very simple way of sharding. Each schema is located on a different database server and MariaDB MaxScale's schemarouter module is used to combine them into a single logical database server.

Environment

This tutorial was written for Ubuntu 22.04, MaxScale 23.08 and . In addition to the MaxScale server, you'll need two MariaDB servers which will be used for the sharding. The installation of MariaDB is not covered by this tutorial.

This tutorial is a quick overview of what the MaxScale REST API offers, how it can be used to inspect the state of MaxScale and how to use it to modify the runtime configuration of MaxScale. The tutorial uses the curl command line client to demonstrate how the API is used.

Configuration and Hardening

The MaxScale REST API listens on port 8989 on the local host. The admin_port and admin_host parameters control which port and address the REST API listens on. Note that for security reasons the API only listens for local connections with the default configuration. It is critical that the default credentials are changed and TLS/SSL encryption is configured before exposing the REST API to a network.

The default user for the REST API is admin and the password is mariadb. The easiest way to secure the REST API is to use the maxctrl command line client to create a new admin user and delete the default one. To do this, run the following commands:

This will create the user my_user with the password my_password that is an administrative account. After this account is created, the default admin account is removed with the next command.

The next step is to enable TLS encryption. To do this, you need a CA certificate, a private key and a public certificate file all in PEM format. Add the following three parameters under the [maxscale] section of the MaxScale configuration file and restart MaxScale.

Use maxctrl to verify that the TLS encryption is enabled. In this tutorial our server certificates are self-signed so the --tls-verify-server-cert=false option is required.

If no errors are raised, this means that the communication via the REST API is now secure and can be used across networks.

Requesting Data

Note: For the sake of brevity, the rest of this tutorial will omit theTLS/SSL options from the curl command line. For more information, refer to thecurl manpage.

The most basic task to do with the REST API is to see whether MaxScale is up and running. To do this, we do a HTTP request on the root resource (the -i option shows the HTTP headers).

curl -i 127.0.0.1:8989/v1/

To query a resource collection endpoint, append it to the URL. The /v1/filters/ endpoint shows the list of filters configured in MaxScale. This is a resource &#xNAN;collection endpoint: it contains the list of all resources of a particular type.

curl 127.0.0.1:8989/v1/filters

The data holds the actual list of resources: the Hint and Logger filters. Each object has the id field which is the unique name of that object. It is the same as the section name in maxscale.cnf.

Each resource in the list has a relationships object. This shows the relationship links between resources. In our example, the Hint filter is used by a service named RW-Split-Hint-Router and the Logger is not currently in use.

To request an individual resource, we add the object name to the resource collection URL. For example, if we want to get only the Logger filter we execute the following command.

curl 127.0.0.1:8989/v1/filters/Logger

Note that this time the data member holds an object instead of an array of objects. All other parts of the response are similar to what was shown in the previous example.

Creating Objects

One of the uses of the REST API is to create new objects in MaxScale at runtime. This allows new servers, services, filters, monitor and listeners to be created without restarting MaxScale.

For example, to create a new server in MaxScale the JSON definition of a server must be sent to the REST API at the /v1/servers/ endpoint. The request body defines the server name as well as the parameters for it.

To create objects with curl, first write the JSON definition into a file.

To send the data, use the following command.

The -d option takes a file name prefixed with a @ as an argument. Here we have @new_server.txt which is the name of the file where the JSON definition was stored. The -X option defines the HTTP verb to use and to create a new object we must use the POST verb.

To verify the data request the newly created object.

Modifying Data

The easiest way to modify an object is to first request it, store the result in a file, edit it and then send the updated object back to the REST API.

Let's say we want to modify the port that the server we created earlier listens on. First we request the current object and store the result in a file.

After that we edit the file and change the port from 3003 to 3306. Next the modified JSON object is sent to the REST API as a PATCH command. To do this, execute the following command.

To verify that the data was updated correctly, request the updated object.

Object Relationships

To continue with our previous example, we add the updated server to a service. To do this, the relationships object of the server must be modified to include the service we want to add the server to.

To define a relationship between a server and a service, the data member must have the relationships field and it must contain an object with the servicesfield (some fields omitted for brevity).

The data.relationships.services.data field contains a list of objects that define the id and type fields. The id is the name of the object (a service or a monitor for servers) and the type tells which type it is. Only services type objects should be present in the services object.

In our example we are linking the server1 server to the RW-Split-Router service. As was seen with the previous example, the easiest way to do this is to store the result, edit it and then send it back with a HTTP PATCH.

If we want to remove a server from all services and monitors, we can set thedata member of the services and monitors relationships to an empty array:

This is useful if you want to delete the server which can only be done if it has no relationships to other objects.

Deleting Objects

To delete an object, simply execute a HTTP DELETE request on the resource you want to delete. For example, to delete the server1 server, execute the following command.

In order to delete an object, it must not have any relationships to other. objects.

Further Reading

The full list of all available endpoints in MaxScale can be found in the .

The maxctrl command line client is self-documenting and the maxctrl help command is a good tool for exploring the various commands that are available in it. The maxctrl api get command can be useful way to explore the REST API as it provides a way to easily extract values out of the JSON data generated by the REST API.

There is a multitude of REST API clients readily available and most of them are far more convenient to use than curl. We recommend investigating what you need and how you intend to either integrate or use the MaxScale REST API. Most modern languages either have a built-in HTTP library or there exists a de facto standard library.

The MaxScale REST API follows the JSON API specification and there exist libraries that are built specifically for these sorts of APIs

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

The purpose of this tutorial is to introduce the MariaDB MaxScale Administrator to a few of the common administration tasks. This is intended to be an introduction for administrators who are new to MariaDB MaxScale and not a reference to all the tasks that may be performed.

Administration audit file

The REST API calls that MaxCtrl and MaxGui issue to MaxScale can be logged by enabling .

The generated file is a csv file that can be opened in most spread sheet programs.

[Rotating Log Files](#Rotating Log Files) also applies to the audit file. The admin audit file will never be overwritten as a result of a rotate, unlike the regular log file (in case a rotate is issued, but the file name has not been moved). There is also the option to change the audit file name, which effectively rotates it independently of the regular log file. For example: