Hint Syntax

Refer to the Hintfilter documentation for the MaxScale hint syntax.

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

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

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.

Installing as root in /usr/local

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.

Installing in any Directory

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}

Module commands

Introduced in MaxScale 2.1, the module commands are special, module-specific commands. They allow the modules to expand beyond the capabilities of the module API. Currently, only MaxCtrl implements an interface to the module commands.

All registered module commands can be shown with maxctrl list commands and they can be executed with maxctrl call command <module> <name> ARGS... whereis the name of the module and is the name of the command.ARGS is a command specific list of arguments.

Developer reference

The module command API is defined in the modulecmd.h header. It consists of various functions to register and call module commands. Read the function documentation in the header for more details.

The following example registers the module command my_command for module_my_module_.

The array my_args of type modulecmd_arg_type_t is used to tell what kinds of arguments the command expects. The first argument is a boolean and the second argument is an optional string.

Arguments are passed to the parsing function as an array of void pointers. They are interpreted as the types the command expects.

When the module command is executed, the argv parameter for the_my_simple_cmd_ contains the parsed arguments received from the caller of the command.

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

Securing Your MaxScale Deployment

There are five main components that you need to make sure are completed before you go into production:

• Encrypting Plaintext Passwords

• Securing the GUI Interface

• Managing Users & Passwords

• Enabling Audit Logging

• Encrypting Database Connections

Encrypting Plaintext Passwords

Ensuring the security of your MaxScale setup involves stringent control over the key file permissions. Utilizing is an effective approach to generate a secure key file.

This generates a keyfile in /var/lib/maxscale

See for more information about maxkeys.

Once generated, this key file can be relocated to a secure location. This key file serves a dual purpose: it enables the encryption of passwords and facilitates MaxScale in decrypting those encrypted passwords.

To maintain confidentiality, it is crucial to adjust the ownership and permissions of the key file appropriately using chown. This step ensures that the key file remains secure and inaccessible to unauthorized users.

Following the secure setup of the key file, you can proceed to encrypt the plaintext passwords of users already created in your databases.

These encrypted passwords can then replace the plaintext passwords in your MaxScale configuration (CNF) files. This enhances the overall security of your database system by reducing the risk that passwords are accidentally shared.

Securing the GUI Interface

To enhance the security of your MaxScale environment, it’s crucial to configure the GUI host address properly. The default setting, 0.0.0.0, allows unrestricted access from any network, which poses a significant security risk. Instead, you should set the admin_host to a more secure address. Additionally, you can change the default port (8989) to another port for added security. For example, you can restrict access to the localhost by setting:

Alternatively, you can specify an internal network IP address to limit access within your internal network, such as:

If you need to allow external access, ensure that the network is adequately secured and that only authorized users can access the MaxScale interface. Consult with your network administrator to determine the most appropriate and secure configuration.

Enabling TLS Encryption

To further secure your MaxScale setup, enable TLS encryption for data in transit. Follow these steps to configure SSL:

1. Set Up SSL Keys and Certificates:

• Generate SSL keys and certificates. See

• Add them to the MaxScale configuration file.

2. Update the MaxScale Configuration:

• Enable secure connections by setting admin_secure_gui to true.

• Specify the paths to the SSL certificate and key files in your CNF file:

3. Verify Encryption:

• Use the Maxctrl command to verify that TLS encryption is functioning correctly:

4. Update Default Credentials:

• It’s essential to change the default admin passwords. Create a new user with a strong password and remove the default admin user for enhanced security.

Managing Users & Passwords

MaxScale allows you to manage user access to its GUI, offering different permission levels to suit various operational needs. Currently, MaxScale supports two primary roles: admin and basic. This functionality is particularly useful for organizations with hierarchical structures or distinct departments, enabling you to grant status view access without allowing execution or manipulation capabilities.

Creating and Deleting Users

To create or delete users in the MaxScale GUI, you can use the maxctrl command. Here’s an example of creating a user with administrative privileges:

To remove an existing user, such as the default admin user, you can use the following command:

Managing Users in the GUI

The MaxScale GUI also provides functionality to manage user access and update the admin password. Through the GUI, you can:

• Add Users: Create users with basic or admin access.

• Modify User Permissions: Change roles as needed to adapt to evolving security requirements.

• Update Admin Password: Enhance security by regularly updating the admin password.

By leveraging these features, you can ensure that your MaxScale environment remains secure and that user access is appropriately managed according to your organization’s needs.

Enabling Audit Logging

Turn on admin auditing to log all login, connection, and configuration changes. Choose an audit file location and set up log rotation.

Enabling and Managing Admin Auditing in MaxScale

Admin auditing in MaxScale provides comprehensive tracking of all administrative activities, including logins, connections, and modifications. These activities are recorded in an audit file for enhanced security and traceability.

To enable admin auditing, add the following configuration to your MaxScale configuration file:

This configuration activates auditing and specifies the location of the audit file. Ensure that the specified directory exists before restarting MaxScale.

Setting Up and Managing Audit Logs

1. Enable Auditing:

• Add the configuration lines to your MaxScale configuration file.

• Verify the directory specified in admin_audit_file exists.

2. Audit File Management:

• Implement log rotation to manage the size and number of audit files. This can be achieved using standard Linux log rotation tools. See

For manual log rotation, you can use the following MaxCtrl command:

Encrypting Database Connections

Configuring SSL Encryption for MaxScale with an Encrypted MariaDB Server

If you have already implemented encryption on your MariaDB server, it’s crucial to extend this encryption configuration to MaxScale to ensure secure communication. Once encryption is enabled on your MariaDB server, follow these steps to configure MaxScale to utilize SSL.

Steps to Configure SSL in MaxScale:

1. Enable SSL:

• Add ssl=true to each server section in your MaxScale configuration file.

2. Verify Peer Certificates:

• Add ssl_verify_peer_certificate=true to ensure that MaxScale verifies the server’s SSL certificates, providing an additional layer of security.

Your MaxScale configuration file should look something like this:

These settings instruct MaxScale to use SSL for connections to the MariaDB server and to verify peer certificates, enhancing the security of data in transit.

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

Diff - router for comparing servers

• Diff - router for comparing servers

Overview

The diff-router, hereafter referred to as Diff, compares the behaviour of one MariaDB server version to that of another.

Diff will send the workload both to the server currently being used - called main - and to another server - called other - whose behaviour needs to be assessed.

The responses from main are returned to the client, without waiting for the responses from other. While running, Diff collects latency histogram data that later can be used for evaluating the behaviour of main and other.

Although Diff is a normal MaxScale router that can be configured manually, typically it is created using commands provided by the router itself. As its only purpose is to compare the behaviour of different servers, it is only meaningful to start it provided certain conditions are fulfilled and those conditions are easily ensured using the router itself.

Histogram

Diff collects latency information separately for each canonical &#xNAN;statement, which simply means a statement where all literals have been replaced with question marks. For instance, the canonical statement ofSELECT f FROM t WHERE f = 10 and SELECT f FROM t WHERE f = 20 is in both cases SELECT f FROM t WHERE f = ?. The latency information of both of those statements will be collected under the same canonical statement.

Before starting to register histogram data, Diff will collect from main that will be used for defining the edges and the number of bins of the histogram.

Discrepancies

The responses from main and other are considered to be different

• if the checksum of the response from main and *other differ, or

• if the response time of _other* is outside the boundaries of the histogram edges calculated from the samples from main.

A difference in the response time of individual queries is not a meaningful criteria, as there for varying reasons (e.g. network traffic) can be a significant amount of variance in the results. It would only always cause a large number of false positives.

EXPLAIN

When a discrepancy is detected, an EXPLAIN statement will be executed if the query was a DELETE, SELECT, INSERT or UPDATE. The EXPLAIN will be executed using the same connection that was used for executing the original statement. In the normal case, the EXPLAIN will be executed immediately after the original statement, but if the client is streaming requests, an other statement may have been exceuted in between.

EXPLAINs are not always executed, but the frequency is controlled by and . The EXPLAIN results are included in the of Diff.

QPS

While running, Diff will also collect QPS information over a sliding window whose size is defined by .

Reporting

Diff produces two kinds of output:

• Output that is generated when Diff terminates or upon . That output can be visualized as explained .

• Diff can continuously report queries whose responses from main and other differ as described .

When Diff starts it will create a directory diff in MaxScale's data directory (typically /var/lib/maxscale). Under that it will create a directory whose name is the same as that of the service specified in . The output files are created in that directory.

Setup

The behaviour and usage of Diff is most easily explained using an example.

Consider the following simple configuration that only includes the very essential.

There is a service MyService that uses a single server MyServer1, which, for this example, is assumed to run MariaDB 10.5.

Suppose that the server should be upgraded to 11.2 and we want to find out whether there would be some issues with that.

Prerequisites

In order to use Diff for comparing the behaviour of MariaDB 10.5 and MariaDB 11.2, the following steps must be taken.

• Install MariaDB 11.2 on a host that performance wise is similar to the host on which MariaDB 10.5 is running.

• Configure the MariaDB 11.2 server to replicate from the MariaDB 10.5 server.

• Create a server entry for the MariaDB 11.2 server in the MaxScale configuration.

The created entry could be something like:

Note that this server must not be added to the service that uses the original server. That is, in this example, MariaDB_112 must not be added to the service MyService.

The new server can be added to the monitor used for monitoring the servers of the service, but that is not necessary. However, unless it is added, maxctrl list servers will show the server as being down.

With these steps Diff is ready to be used.

Running Diff

Diff is controlled using a number of module commands.

Create

Syntax: create new-service existing-service used-server new-server

Where:

• new-service: The name of the service using the Diff router, to be created.

• existing-service: The name of an existing service in whose context the new server is to be evaluated.

• used-server: A server used by existing-service

With this command, preparations for comparing the server MariaDB_112 against the server MyServer1 of the service MyService will be made. At this point it will be checked in what kind of replication relationshipMariaDB_112 is with respect to MyServer1. If the steps in were followed, it will be detected thatMariaDB_112 replicates from MyServer1.

If everything seems to be in order, the service DiffMyService will be created. Settings such as user and password that are needed by the service DiffMyService will be copied from MyService.

Using maxctrl we can check that the service indeed has been created.

Now the comparison can be started.

Start

Syntax: start diff-service

Where:

• diff-service: The name of the service created in thecreate` step.

When Diff is started, it performs the following steps:

1. All sessions of MyService are suspended.

2. In the MyService service, the server target MyServer1 is replaced with DiffMyService.

In the first step, all sessions that are idle will immediately be suspended, which simply means that nothing is read from the client socket. Sessions that are waiting for a response from the server and sessions that have an active transaction continue to run. Immediately when a session becomes idle, it is suspended.

Once all sessions have been suspended, the service is rewired. In the case of MyService above, it means that the targetMyServer1 is replaced with DiffMyService. That is, requests that earlier were sent to MyServer1, will, once the sessions are resumed, be sent to DiffMyService, which sends them forward to both MyServer1 and MariaDB_112.

Restarting the sessions means that the direct connections toMyServer1 will be closed and equivalent ones created via the service DiffMyService, which will also create connections to MariaDB_112.

When the sessions are resumed, client requests will again be processed, but they will now be routed via DiffMyService.

With maxctrl we can check that MyServer has been rewired.

The target of MyService is DiffMyService instead of MyServer1 that it used to be.

The output object returned by create tells the current state.

The sessions object shows how many sessions there are in total and how many that currently are suspended. Since there were no existing sessions in this example, they are both 0.

The state shows what Diff is currently doing. synchronizing means that it is in the process of changing MyService to useDiffMyService. sync_state shows that it is currently in the process of suspending sessions.

Status

Syntax: status diff-service

Where:

• diff-service: The name of the service created in thecreate` step.

When Diff has been started, its current status can be checked with the command status. The output is the same as what was returned when Diff was started.

The state is now comparing, which means that everything is ready and clients can connect in normal fashion.

Summary

Syntax: summary diff-service

Where:

• diff-service: The name of the service created in thecreate` step.

While Diff is running, it is possible at any point to request a summary.

The summary consists of two files, one for the main server and one for the other server. The files are written to a subdirectory with the same name as the Diff service, which is created in the subdirectory diff in the data directory of MaxScale.

Assuming the data directory is the default /var/lib/maxscale, the directory would in this example be/var/lib/maxscale/diff/DiffMyService.

The names of the files will be the server name, concatenated with a timestamp. In this example, the names of the files could be:

The visualization of the results is done using the program.

Stop

Syntax: stop diff-service

Where:

• diff-service: The name of the service created in thecreate` step.

The comparison can stopped with the command stop.

Stopping Diff reverses the effect of starting it:

1. All sessions are suspended.

2. In the service, 'DiffMyService' is replaced with 'MyServer1'.

3. The sessions are restarted.

4. The sessions are resumed.

As the sessions have to be suspended, it may take a while before the operation has completed. The status can be checked with the 'status' command.

Destroy

Syntax: destroy diff-service

Where:

• diff-service: The name of the service created in thecreate` step.

As the final step, the command destroy can be called to destroy the service.

Visualizing

The visualization of the data is done with the maxvisualize program, which is part of the Capture functionality. The visualization will open up a browser window to show the visualization.

If no browser opens up, the visualization URL is also printed into the command line which by default should be.

In the case of the example above, the directory where the output files are created would be /var/lib/maxscale/diff/MyService. And the files to be used when visualizing would be called something likeMyServer1_2024-05-07_140323.json andMariaDB_112_2024-05-07_140323.json. The timestamp will be different every time is executed.

The order is significant; the first argument is the baseline and the second argument the results compared to the baseline.

Continuous Reporting

If the value of is something else but never, Diff will continously log results to a file whose name is the concatenation for the main and other server followed by a timestamp. In the example above, the name would be something likeMyServer1_MariaDB_112_2024-02-15_152838.json.

Each line (here expanded for readability) in the file will look like:

The meaning of the fields are as follows:

• id: Running number, increases for each query, but will not be in strict increasing order if a statement needed to be EXPLAINed and the following did not.

• session: The session id.

• command: The protocol packet type.

Instead of an explain object, there may be an explained_by array, containing the ids of similar statements (i.e. their canonical statement is the same) that were EXPLAINed.

Mode

Diff can run in a read-only or read-write mode and the mode is deduced from the replication relationship between main and_other_.

If other replicates from main, it is assumed that main is the primary. In this case Diff will, when started, stop the replication from main to other. When the comparison ends Diff will, depending on the value of either reset the replication from main to other or leave the situation as it is.

If other and main replicates from a third seriver, it is assumed main is a replica. In this case, Diff will, when started, leave the replication as it is and do nothing when the comparison ends.

If the replication relationship between main and other is anything else, Diff will refuse to start.

Settings

main

• Type: server

• Mandatory: Yes

• Dynamic: No

The main target from which results are returned to the client. Must be a server and must be one of the servers listed in .

If the connection to the main target cannot be created or is lost mid-session, the client connection will be closed.

service

• Type: service

• Mandatory: Yes

• Dynamic: No

Specifies the service Diff will modify.

explain

• Type:

• Mandatory: No

• Dynamic: Yes

• Values: none

Specifies whether a request should be EXPLAINed on only other, both other and main or neither.

explain_entries

• Type: non-negative integer

• Mandatory: No

• Dynamic: Yes

• Default: 2

Specifies how many times at most a particular canonical statement is EXPLAINed during the period specified by .

explain_period

• Type:

• Mandatory: No

• Dynamic: Yes

• Default: 15m

Specifies the length of the period during which at most number of EXPLAINs are executed for a statement.

max_request_lag

• Type: non-negative integer

• Mandatory: No

• Dynamic: Yes

• Default: 10

Specifies the maximum number of requests other may be lagging behind main before the execution of SELECTs against other are skipped to bring it back in line with main.

on_error

• Type:

• Mandatory: No

• Dynamic: Yes

• Values: close

Specifies whether an error from other, will cause the session to be closed. By default it will not.

percentile

• Type: count

• Mandatory: No

• Dynamic: Yes

• Min: 1

Specifies the percentile of sampels that will be considered when calculating the width and number of bins of the histogram.

qps_window

• Type:

• Mandatory: No

• Dynamic: No

• Default: 15m

Specifies the size of the sliding window during which QPS is calculated and stored. When a is requested, the QPS information will also be saved.

report

• Type:

• Mandatory: No

• Dynamic: Yes

• Values: always

Specifies when the results of executing a statement on other and main should be logged; always, when there is a significant difference or_never_.

reset_replication

• Type:

• Mandatory: No

• Dynamic: Yes

• Default: true

If Diff has started in read-write mode and the value ofreset_replication is true, when the comparison ends it will execute the following on other:

If Diff has started in read-only mode, the value of reset_replication will be ignored.

Note that since Diff writes updates directly to both main and_other_ there is no guarantee that it will be possible to simply start the replication. Especially not if gtid_strict_mode is on.

retain_faster_statements

• Type: non-negative integer

• Mandatory: No

• Dynamic: Yes

• Default: 5

Specifies the number of faster statements that are retained in memory. The statements will be saved in the summary when the comparison ends, or when Diff is explicitly instructed to do so.

retain_slower_statements

• Type: non-negative integer

• Mandatory: No

• Dynamic: Yes

• Default: 5

samples

• Type: count

• Mandatory: No

• Dynamic: Yes

• Min: 100

Specifies the number of samples that will be collected in order to define the edges and number of bins of the histograms.

Limitations

Diff is not capable of adapting to any changes made in the cluster configuration. For instance, if Diff starts up in read-only mode and main is subsequently made primary, Diff will not sever the replication from main to other. The result will be that other receives the same writes twice; once via the replication from the server it is replicating from and once when Diff executes the same writes.

Diff is not compatible with . If configuration synchronization is enabled, an attempt to create a Diff router will fail.

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

Configuration Settings

MaxCtrl

MaxCtrl is a command line administrative client for MaxScale which uses the MaxScale REST API for communication. It has replaced the legacy MaxAdmin command line client that is no longer supported or included.