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.

[dbserv1]
type=server
address=192.168.2.1
port=3306

[dbserv2]
type=server
address=192.168.2.2
port=3306

[dbserv3]
type=server
address=192.168.2.3
port=3306

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, addssl_verify_peer_certificate=true.

The ssl and ssl_verify_peer_certificate parameters are similar to the--ssl and --ssl-verify-server-cert options of the mysql command line client.

For more information about TLS, refer to the .

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

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 the TLS/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 services field (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}

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.

Installing MaxScale

The easiest way to install MaxScale is to use the MariaDB repositories.

Creating Users

This tutorial uses a broader set of grants than is required for the sake of brevity and backwards compatibility. For the minimal set of grants, refer to the .

All MaxScale configurations require at least two accounts: one for reading authentication data and another for monitoring the state of the database. Services will use the first one and monitors will use the second one. In addition to this, we want to have a separate account that our application will use.

All of the users must be created on both of the MariaDB servers.

Creating the Schemas and Tables

Each server will hold one unique schema which contains the data of one specific customer. We'll also create a shared schema that is present on all shards that the shard-local tables can be joined into.

Create the tables on the first server:

Create the tables on the second server:

Configuring MaxScale

The MaxScale configuration is stored in /etc/maxscale.cnf.

First, we configure two servers we will use to shard our database. The db-01 server has the customer_01 schema and the db-02 server has the customer_02 schema.

The next step is to configure the service which the users connect to. This section defines which router to use, which servers to connect to and the credentials to use. For sharding, we use schemarouter router and the service_user credentials we defined earlier. By default the schemarouter warns if two or more nodes have duplicate schemas so we need to ignore them withignore_tables_regex=.*.

After this we configure a listener for the service. The listener is the actual port that the user connects to. We will use the port 4000.

The final step is to configure a monitor which will monitor the state of the servers. The monitor will notify MariaDB MaxScale if the servers are down. We add the two servers to the monitor and use the monitor_user credentials. For the sharding use-case, the galeramon module is suitable even if we're not using a Galera cluster. The schemarouter is only interested in whether the server is in the Running state or in the Down state.

After this we have a fully working configuration and the contents of/etc/maxscale.cnf should look like this.

Then you're ready to start MaxScale.

Testing the Sharding

MariaDB MaxScale is now ready to start accepting client connections and routing them. Queries are routed to the right servers based on the database they target and switching between the shards is seamless since MariaDB MaxScale keeps the session state intact between servers.

To test, we query the schema that's located on the local shard and join it to the shared table.

The sharding also works even if no default database is selected.

One limitation of this sort of simple sharding is that cross-shard joins are not possible.

In most multi-tenant situations, this is an acceptable limitation. If you do need cross-shard joins, the storage engine will provide you this.

_{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.

[Galera-Monitor]
type=monitor
module=galeramon
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 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.

Monitor User

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

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

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

Monitor User

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

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

_{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 .

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}

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.

This tutorial is a short introduction to the Avrorouter, how to set it up and how it interacts with the binlogrouter.

The first part configures the services and sets them up for the binary log to Avro file conversion. The second part of this tutorial uses the client listener interface for the avrorouter and shows how to communicate with the service over the network.

Configuration

Preparing the primary server

The primary server where we will be replicating from needs to have binary logging enabled, binlog_format set to row and binlog_row_image set tofull. These can be enabled by adding the two following lines to the my.cnf file of the primary.

You can find out more about replication formats from the

Configuring MaxScale

We start by adding two new services into the configuration file. The first service is the binlogrouter service which will read the binary logs from the primary server. The second service will read the binlogs as they are streamed from the primary and convert them into Avro format files.

The source parameter in the avro-service points to the replication-service we defined before. This service will be the data source for the avrorouter. The_filestem_ is the prefix in the binlog files and start_index is the binlog number to start from. With these parameters, the avrorouter will start reading events from binlog binlog.000015.

Note that the filestem and start_index must point to the file that is the first binlog that the binlogrouter will replicate. For example, if the first file you are replicating is my-binlog-file.001234, set the parameters tofilestem=my-binlog-file and start_index=1234.

For more information on the avrorouter options, read the .

Preparing the data in the primary server

Before starting the MaxScale process, we need to make sure that the binary logs of the primary server contain the DDL statements that define the table layouts. What this means is that the CREATE TABLE statements need to be in the binary logs before the conversion process is started.

If the binary logs contain data modification events for tables that aren't created in the binary logs, the Avro schema of the table needs to be manually created. There are multiple ways to do this:

• Dump the database to a replica, configure it to replicate from the primary and point MaxScale to this replica (this is the recommended method as it requires no extra steps)

• Use the and copy the generated .avsc files to the avrodir

• Use the and copy the generated .avsc files to the avrodir

If you used the schema generator scripts, all Avro schema files for tables that are not created in the binary logs need to be in the location pointed to by the_avrodir_ parameter. The files use the following naming:<database>.<table>.<schema_version>.avsc. For example, the schema file name of the test.t1 table would be test.t1.0000001.avsc.

Starting MariaDB MaxScale

The next step is to start MariaDB MaxScale and set up the binlogrouter. We do that by connecting to the MySQL listener of the replication_router service and executing a few commands.

NOTE: GTID replication is not currently supported and file-and-position replication must be used.

This will start the replication of binary logs from the primary server at 172.18.0.1 listening on port 3000. The first file that the binlogrouter replicates is binlog.000015. This is the same file that was configured as the starting file in the avrorouter.

For more details about the SQL commands, refer to the documentation.

After the binary log streaming has started, the avrorouter will automatically start processing the binlogs.

Creating and Processing Data

Next, create a simple test table and populated it with some data by executing the following statements.

To use the cdc.py command line client to connect to the CDC service, we must first create a user. This can be done via maxctrl by executing the following command.

This will create the maxuser:maxpwd credentials which can then be used to request a JSON data stream of the test.t1 table that was created earlier.

The output is a stream of JSON events describing the changes done to the database.

The first record is always the JSON format schema for the table describing the types and names of the fields. All records that follow it represent the changes that have happened on the database.

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

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.

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.

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}

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 documentation articles for more information on setting up a primary-replica-cluster or a Galera-cluster: and .

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 is not only capable of monitoring the state of a MariaDB primary-replica cluster but is also capable of performing failover and switchover. In addition, in some circumstances it is capable of rejoining a primary that has gone down and later reappears.

Note that the failover (and switchover and rejoin) functionality is only supported in conjunction with GTID-based replication and initially only for simple topologies, that is, 1 primary and several replicas.

The failover, switchover and rejoin functionality are inherent parts of the MariaDB Monitor, but neither automatic failover nor automatic rejoin are enabled by default.

The following examples have been written with the assumption that there are four servers - server1, server2, server3

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.

• MariaDB MaxScale Administration Tutorial

  • Administration audit file

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 e.g. maxctrl alter maxscale admin_audit_file=/var/log/maxscale/admin_audit.march.csv.

Starting and Stopping MariaDB MaxScale

MaxScale uses systemd for managing the process. This means that normalsystemctl commands can be used to start and stop MaxScale. To start MaxScale, use systemctl start maxscale. To stop it, use systemctl stop maxscale.

The systemd service file for MaxScale is located in/lib/systemd/system/maxscale.service.

Additional Options for MaxScale

Additional command line options and other systemd configuration options can be given to MariaDB MaxScale by creating a drop-in file for the service unit file. You can do this with the systemctl edit maxscale.service command. For more information about systemd drop-in files, refer to and .

Checking The Status Of The MariaDB MaxScale Services

It is possible to use the maxctrl command to obtain statistics about the services that are running within MaxScale. The maxctrl command list services will give very basic information regarding services. This command may be either run in interactive mode or passed on the maxctrl command line.

Network listeners count as a user of the service, therefore there will always be one user per network port in which the service listens. More details can be obtained by using the "show service" command.

What Clients Are Connected To MariaDB MaxScale

To determine what client are currently connected to MariaDB MaxScale, you can use the list sessions command within maxctrl. This will give you IP address and the ID of the session for that connection. As with any maxctrl command this can be passed on the command line or typed interactively in maxctrl.

Rotating Log Files

Log rotation applies to the MaxScale log file, admin audit file and qlafilter files.

MariaDB MaxScale logs messages of different priority into a single log file. With the exception if error messages that are always logged, whether messages of a particular priority should be logged or not can be enabled via the maxctrl interface or in the configuration file. By default, MaxScale keeps on writing to the same log file. To prevent the file from growing indefinitely, the administrator must take action.

The name of the log file is maxscale.log. When the log is rotated, MaxScale closes the current log file and opens a new one using the same name.

Log file rotation is achieved by use of the rotate logs command in maxctrl.

As there currently is only the maxscale log, that is the only one that will be rotated.

This may be integrated into the Linux logrotate mechanism by adding a configuration file to the /etc/logrotate.d directory. If we assume we want to rotate the log files once per month and wish to keep 5 log files worth of history, the configuration file would look as follows.

MariaDB MaxScale will also rotate all of its log files if it receives the USR1 signal. Using this the logrotate configuration script can be rewritten as

In older versions MaxScale renamed the log file, behavior which is not fully compliant with the assumptions of logrotate and may lead to issues, depending on the used logrotate configuration file. From version 2.1 onward, MaxScale will not itself rename the log file, but when the log is rotated, MaxScale will simply close and reopen the same log file. That will make the behavior fully compliant with logrotate.

Taking Objects Temporarily Out of Use

Putting Servers into Maintenance

MariaDB MaxScale supports the concept of maintenance mode for servers within a cluster. This allows for planned, temporary removal of a database from the cluster without the need to change the MariaDB MaxScale configuration.

To achieve this, you can use the set server command in maxctrl to set the maintenance mode flag for the server. This may be done interactively within maxctrl or by passing the command on the command line.

This will cause MariaDB MaxScale to stop routing any new requests to the server, however if there are currently requests executing on the server these will not be interrupted. Connections to servers in maintenance mode are closed as soon as the next request arrives. To close them immediately, use the --force option for maxctrl set server.

Clearing the maintenance mode for a server will bring it back into use. If multiple MariaDB MaxScale instances are configured to use the node then maintenance mode must be set within each MariaDB MaxScale instance.

Stopping and Starting Services

Services can be stopped to temporarily halt their use. Stopping a service will cause it to stop accepting new connections until it is started. New connections are not refused if the service is stopped and are queued instead. This means that connecting clients will wait until the service is started again.

Starting a service will cause it to accept all queued connections that were created while it was stopped.

Stopping and Starting Monitors

Stopping a monitor will cause it to stop monitoring the state of the servers assigned to it. This is useful when the state of the servers is assigned manually with maxctrl set server.

Starting a monitor will make it resume monitoring of the servers. Any manually assigned states will be overwritten by the monitor.

Runtime Configuration Modification

The MaxScale configuration can be changed at runtime by using the create,alter and destroy commands of maxctrl. These commands either create, modify or destroy objects (servers, services, monitors etc.) inside MaxScale. The exact syntax for each of the commands and any additional options that they take can be seen with maxctrl --help <command>.

Not all parameters can be modified at runtime. Refer to the module documentation for more information on which parameters can be modified at runtime. If a parameter cannot be modified at runtime, the object can be destroyed and recreated in order to change it.

All runtime changes are persisted in files stored by default in/var/lib/maxscale/maxscale.cnf.d/. This means that any changes done at runtime persist through restarts. Any changes done to objects in the main configuration file are ignored if a persisted entry is found for it.

For example, if the address of a server is modified with maxctrl alter server db-server-1 address 192.168.0.100, the file/var/lib/maxscale/maxscale.cnf.d/db-server-1.cnf is created with the complete configuration for the object. To remove all runtime changes for all objects, remove all files found in /var/lib/maxscale/maxscale.cnf.d.

Core Parameter Configuration

Modify global MaxScale parameters:

Some global parameters cannot be modified at runtime. Refer to the for a full list of parameters that can be modified at runtime.

Managing Servers

Create a new server

Modify a Server

Destroy a Server

A server can only be destroyed if it is not used by any services or monitors. To automatically remove the server from the services and monitors that use it, use the --force flag.

Drain a Server

When a server is set into the drain state, no new connections to it are created. Unlike to the maintenance state which immediately stops all new requests and closes all connections if used with the --force option, thedrain state allows existing connections to continue routing requests to them in order to be gracefully closed once the client disconnects.

To remove the drain state, use clear server command:

Servers with the Master state cannot be drained. To drain them, first perform a switchover to another node and then drain the server.

Managing Monitors

Create a new Monitor

Modify a Monitor

Add Server to a Monitor

Remove a Server from a Monitor

Destroy a Monitor

A monitor can only be destroyed if it is not monitoring any servers. To automatically remove the servers from the monitor, use the --force flag.

Managing Services

Create a New Service

Modify a Service

Add Servers to a Service

Any servers added to services will only be used by new sessions. Existing sessions will use the servers that were available when they connected.

Remove Servers from a Service

Similarly to adding servers, removing servers from a service will only affect new sessions. Existing sessions keep using the servers even if they are removed from a service.

Change the Filters of a Service

The order of the filters is significant: the first filter will be the first to receive the query. The new set of filters will only be used by new sessions. Existing sessions will keep using the filters that were configured when they connected.

Destroy a Service

The service can only be destroyed if it uses no servers or clusters and has no listeners associated with it. To force destruction of a service even if it does use servers or has listeners, use the --force flag. This will also destroy any listeners associated with the service.

Managing Filters

Create a New Filter

Destroy a Filter

A filter can only be destroyed if it is not used by any services. To automatically remove the filter from all services using it, use the --force flag.

Filters cannot be altered at runtime in MaxScale 2.5. To modify the parameters of a filter, destroy it and recreate it with the modified parameters.

Managing Listeners

Create a New Listener

Destroy a Listener

Destroying a listener will close the network socket and stop it from accepting new connections. Existing connections that were created through it will keep displaying it as the originating listener.

Listeners cannot be moved from one service to another. In order to do this, the listener must be destroyed and then recreated with the new service.

Managing MaxCtrl and REST API Users

MaxCtrl uses the same credentials as the MaxScale REST API. These users can be managed via MaxCtrl.

Create a New MaxCtrl User

By default new users are only allowed to read data. To make the account an administrative account, add the --type=admin option to the command:

Administrative accounts are allowed to use all MaxCtrl commands and modify any parts of MaxScale.

Change the Password of an Existing User

Remove a User

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

• Using MaxGUI Tutorial

• Introduction

• Dashboard

Introduction

This tutorial is an overview of what the MaxGUI offers as an alternative solution to .

Dashboard

Annotation

1. . i.e. Service, Server, Monitor, Filter, and Listener (Clicking on it will navigate to its detail page)

2. Create a new MaxScale object.

3. Dashboard Tab Navigation.

4. Search Input. This can be used as a quick way to search for a keyword in tables.

• SESSIONS graph illustrates the total number of current sessions.

• CONNECTIONS graph shows servers current connections.

• LOAD graph shows the last second load of thread.

1. Logout of the app.

2. Sidebar navigation menu. Access to the following pages: Dashboard, Visualization, Settings, Logs Archive, Query Editor

Create a new MaxScale object

Clicking on the Create New button (Annotation 2) to open a dialog for creating a new object.

View Replication Status

The replication status of a server monitored by can be viewed by mousing over the server name. A tooltip will be displayed with the following information: replication_state, seconds_behind_master, slave_io_running, slave_sql_running.

How to kill a session

A session can be killed easily on the "Current Sessions" list which can be found on the , Server detail, and Service detail page.

Annotation

1. Kill session button. This button is shown on the mouse hover.

2. Confirm killing the session dialog.

Detail

This page shows information on each MaxScale object and allow to edit its parameter, relationships and perform other manipulation operations. Most of the control buttons will be shown on the mouse hover. Below is a screenshot of a Monitor Detail page, other Detail pages also have a similar layout structure so this is used for illustration purpose.

Annotation

1. Settings option. Clicking on the gear icon will show icons allowing to do different operations depending on the type of the Detail page.

• Monitor Detail page, there are icons to Stop, Start, and Destroy monitor.

• Service Detail page, there are icons to Stop, Start, and Destroy service.

• Server Detail page, there are icons to Set maintenance mode, Clear server state, Drain and Delete server.

1. Switchover button. This button is shown on the mouse hover allowing to swap the running primary server with a designated secondary server.

2. Edit parameters button. This button is shown on the mouse hover allowing to edit the MaxScale object's parameter. Clicking on it will enable editable mode on the table. After finishing editing the parameters, simply click the Done Editing button.

3. A Detail page has tables showing "Relationship" between other MaxScale object. This "unlink" icon is shown on the mouse hover allowing to remove the relationship between two objects.

Visualization

This page visualizes MaxScale configuration and clusters.

Configuration

This page visualizes MaxScale configuration as shown in the figure below.

Annotation

1. A MaxScale object (a node graph). The position of the node in the graph can be changed by dragging and dropping it.

2. Anchor link. The detail page of each MaxScale object can be accessed by clicking on the name of the node.

3. Filter visualization button. By default, if the number of filters used by a service is larger than 3, filter nodes aren't visualized as shown in the figure. Clicking this button will visualize them.

Clusters

This page shows all monitor clusters using module in a card-like view. Clicking on the card will visualize the cluster into a tree graph as shown in the figure below.

Annotation

1. Drag a secondary server on top of a primary server to promote the secondary server as the new primary server.

2. Server manipulation operations button. Showing a dropdown with the following operations:

• Set maintenance mode: Setting a server to a maintenance mode.

• Clear server state: Clear current server state.

• Drain server: Drain the server of connections.

1. Quick access to query editor button. Opening the Query Editor page for this server. If the connection is already created for that server, it'll use it. Otherwise, it creates a blank worksheet and shows a connection dialog to connect to that server.

2. Carousel navigation button. Viewing more information about the server in the next slide.

3. Collapse the carousel.

• Stop monitor.

• Start monitor.

• Reset Replication.

• Release Locks.

1. Refresh rate dropdown. The frequency with which the data is refreshed.

2. Create a new MaxScale object button.

Settings

This page shows and allows editing of MaxScale parameters.

Annotation

1. Edit parameters button. This button is shown on the mouse hover allowing to edit the MaxScale parameter. Clicking on it will enable editable mode on the table..

2. Editable parameters are visible as it's illustrated in the screenshot.

3. After finishing editing the parameters, simply click the Done Editing button.

Logs Archive

This page show real-time MaxScale logs with filter options.

Annotation

1. Filter by dropdown. All logs types are selected to be shown by default

2. Uncheck the box to disable showing a particular log type.

Workspace

On this page, you may add numerous worksheets, each of which can be used for "Run queries", "Data migration" or "Create an ERD" task.

Run Queries

Clicking on the "Run Queries" card will open a dialog, providing options to establish a connection to different MaxScale object types, including "Listener, Server, Service".

The Query Editor worksheet will be rendered in the active worksheet after correctly connecting.

Query Editor worksheet

There are various features in the Query Editor worksheet, the most notable ones are listed below.

Create a new connection

If the connection of the Query Editor expires, or if you wish to make a new connection for the active worksheet, simply clicking on the button located on the right side of the query tabs navigation bar which features a server icon and an active connection name as a label. This will open the connection dialog and allow you to create a new connection.

Schemas objects sidebar

Set the current database

There are two ways to set the current database:

• Double-click on the name of the database.

• Right-click on the name of the database to show the context menu, then select the Use database option.

Preview table data of the top 1000 rows

There are two ways to preview data of a table:

• Click on the name of the table.

• Right-click on the name of the table to show the context menu, then select the Preview Data (top 1000) option.

Describe table

Right-click on the name of the table to show the context menu, then select theView Details option.

Alter/Drop/Truncate table

Right-click on the name of the table to show the context menu, then select the desired option.

Quickly insert an object into the editor

There are two ways to quickly insert an object to the editor:

• Drag the object and drop it in the desire position in the editor.

• Right-click on the object to show the context menu, then mouse hover the Place to Editor option and select the desired insert option.

Show object creation statement and insights info

To view the statement that creates the given object in the , right-clicking on schema or table node and select the View Insights option. For other objects such as view, stored procedure, function and trigger, select the Show Create option.

Editor

The editor is powered by , therefore, its features are similar to those of Visual Studio Code.

To see the command palette, press F1 while the cursor is active on the editor.

The editor also comes with various options to assist your querying tasks. To see available options, right-click on the editor to show the context menu.

Re-execute old queries

Every executed query will be saved in the browser's storage (IndexedDB). Query history can be seen in the History/Snippets tab. To re-execute a query, follow the same step to and click the execute query button in the editor.

Create query snippet

Press CTRL/CMD + D to save the current SQL in the editor to the snippets storage. A snippet is created with a prefix keyword, so when that keyword is typed in the editor, it will be suggested in the "code completion" menu.

Generate an ERD

To initiate the process, either right-click on the schema name and select theGenerate ERD option, or click on the icon button that resembles a line graph, located on the schemas sidebar. This will open a dialog for selecting the tables for the diagram.

Data Migration

Clicking on the "Data Migration" card will open a dialog, providing an option to name the task. The Data Migration worksheet will be rendered in the active worksheet after clicking the Create button in the dialog.

Data Migration worksheet

MaxScale uses ODBC for extracting and loading from the data source to a server in MaxScale. Before starting a migration, ensure that you have set up the necessary configurations on the MaxScale server. Instruction can be found and limitations .

Connections

Source connection shows the most common parameter inputs for creating an ODBC connection. For extra parameters, enable the Advanced mode to manually edit the Connection String input.

After successfully connected to both source and destination servers, click on the Select objects to migrate to navigate to the next stage.

Objects Selection

Select the objects you wish to migrate to the MariaDB server.

After selecting the desired objects, click on the Prepare Migration Script to navigate to the next stage. The migration scripts will be generated differently based on the value selected for the Create mode input. Hover over the question icon for additional information on the modes.

Migration

As shown in the screenshot, you can quickly modify the script for each object by selecting the corresponding object in the table and using the editors on the right-hand side to make any necessary changes.

After clicking the Start Migration button, the script for each object will be executed in parallel.

Migration report

If errors are reported for certain objects, review the output messages and adjust the script accordingly. Then, click the Manage button and select Restart.

To migrate additional objects, click the Manage button and selectMigrate other objects. Doing so will replace the current migration report for the current object with a new one.

To retain the report and terminate open connections after migration, click theManage button, then select Disconnect, and finally delete the worksheet.

Deleting the worksheet will not delete the migration task. To clean-up everything after migration, click the Manage button, then selectDelete.

Create an ERD

There are various features in the ERD worksheet, the most notable ones are listed below.

ERD worksheet

From an empty new worksheet, clicking on the "Create an ERD" card will open a connection dialog. After connecting successfully, the ERD worksheet will be rendered in the active worksheet. The connection is required to retrieve essential metadata, such as engines, character sets, and collation.

Generate an ERD from the existing databases

Click on the icon button featured as a line graph, located on the top toolbar next to the connection button. This will open a dialog for selecting the tables for the diagram.

Create a new ERD

New tables can be created by using either of the following methods:

• Click on the icon button that resembles a line graph, located on the top toolbar.

• Right-click on the diagram board and select the Create Table option.

Entity options

Two options are available: Edit Table and Remove from Diagram. These options can be accessed using either of the following methods:

• Right-click on the entity and choose the desired option.

• Hover over the entity, click the gear icon button, and select the desired option.

For quickly editing or viewing the table definitions, double-clicking on the entity. The entity editor will be shown at the bottom of the worksheet.

Foreign keys quick common options

• Edit Foreign Key, this opens an editor for viewing/editing foreign keys.

• Remove Foreign Key.

• Change to One To One or Change to One To Many. Toggling the uniqueness of the foreign key column.

To show the above foreign key common options, perform a right-click on the link within the diagram.

Viewing foreign key constraint SQL

Hover over the link in the diagram, the constraint SQL of that foreign key will be shown in a tooltip.

Quickly draw a foreign key link

As shown in the screenshot, a foreign key can be quickly established by performing the following actions:

1. Click on the entity that will have the foreign key.

2. Click on the connecting point of the desired foreign key column and drag it over the desired referenced column.

Entity editor

Table columns, foreign keys and indexes can be modified via the entity editor which can be accessed quickly by double-clicking on the entity.

Export options

Three options are available: Copy script to clipboard, Export script andExport as jpeg. These options can be accessed using either of the following methods:

• Right-click on the diagram board and choose the desired option.

• Click the export icon button, and select the desired option.

Applying the script

Click the icon button resembling a play icon to execute the generated script for all tables in the diagram. This action will prompt a confirmation dialog for script execution. If needed, the script can be manually edited using the editor within the dialog.

Visual Enhancement options

The first section of the top toolbar, there are options to improve the visual of the diagram as follows:

• Change the shape of links

• Drawing foreign keys to columns

• Auto-arrange entities

• Highlight relationship

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