Configuring the Xpand Monitor

Configuring the Xpand Monitor

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

Configuring the Monitor

Contrary to the other monitors of MaxScale, the Xpand monitor will autonomously figure out the cluster configuration and for each Xpand node create the corresponding MaxScale server object.

In order to do that, a sufficient number of "bootstrap" server instances must be specified in the MaxScale configuration file for the Xpand monitor to start with. One server instance is in principle sufficient, but if the corresponding node happens to be down when MaxScale starts, the monitor will not be able to function.

The server configuration is identical with that of any other server, but since these servers are only used for bootstrapping the Xpand monitor it is adviceable to use names that clearly will identify them as such.

The actual Xpand monitor configuration looks as follows:

The mandatory parameters are the object type, the monitor module to use, the list of servers to use for bootstrapping and the username and password to use when connecting to the servers.

The monitor_interval parameter specifies how frequently the monitor should ping the health check port of each server and the cluster_monitor_interval specifies how frequently the monitor should do a complete cluster check, that is, access the system tables of the Cluster for checking the Cluster configuration. The default values are 2000 and 60000, that is, 2 seconds and 1 minute, respectively.

For each detected Xpand node a corresponding MaxScale server object will be created, whose name is @@<Monitor-Name>:node-<id>, where _Monitor-Name_ is the name of the monitor, in this exampleXpand` and id is the node id of the Xpand node. So, with a cluster of three nodes, the created servers might be named like.

Note that as these are created at runtime and may disappear at any moment, depending on changes happening in and made to the Xpand cluster, they should never be referred to directly from service configurations. Instead, services should refer to the monitor, as shown in the following:

Instead of listing the servers of the service explicitly using the servers parameter as usually is the case, the service refers to the Xpand monitor using the cluster parameter. This will cause the service to use the Xpand nodes that the Xpand monitor discovers at runtime.

For additional details, please consult the monitor documentation.

CC BY-SA / Gnu FDL

MaxScale and Xpand Tutorial

MaxScale and Xpand Tutorial

Since version 2.4, MaxScale has built-in support for Xpand. This tutorial explains how to setup MaxScale in front of a Xpand cluster.

There is no Xpand specific router, but both the and the routers can be used.

Xpand and Readconnroute

With readconnroute you get simple connection based routing, where each new connection is created (by default) to the Xpand node with the least amount of existing connections. That is, with readconnroute the behaviour will be very similar to the behaviour when is used as the Xpand load balancer.

Bootstrap servers

The Xpand monitor is capable of autonomously figuring out the cluster configuration, but in order to get going there must be at least one_server_-section referring to a node in the Xpand cluster.

That server defintion will be used by the monitor in order to connect to the Xpand cluster. There can be more than one such "bootstrap" definition to cater for the case that the node used as a bootstrap server is down when MaxScale starts.

NOTE These bootstrap servers should only be referred to from the Xpand monitor configuration, but never from a service.

Monitor

In the Xpand monitor section, the bootstrap servers are referred to in the same way as "ordinary" servers are referred to in other monitors.

The user defined by the user parameter needs the following grants:

In case the same user is used both for the monitor and the service (see below), then the user must be given the grants required by the service as well.

The bootstrap servers are only used for connecting to the Xpand cluster; thereafter the Xpand monitor will dynamically find out the cluster configuration.

The discovered cluster configuration will be stored (the ips and ports of the Xpand nodes) and upon subsequent restarts the Xpand monitor will use that information if the bootstrap servers happen to be unavailable.

With the configuration above maxctrl list servers might output the following:

All servers whose name start with @@ have been detected dynamically.

Note that the address 10.2.224.101 appears twice; once forBootstrap-1 and another time for @@Xpand:node-6. The Xpand monitor will create a dynamic server instance for all nodes in the Xpand cluster; also for the ones used in bootstrap server sections.

Service

The service is specified as follows:

The user defined by the user parameter needs the following grants:

In case the same user is used both for the monitor (see above) and the service, then the user must be given the grants required by the monitor as well.

Note that the service does not list any specific servers, but instead refers, using the argument cluster, to the Xpand monitor.

In practice this means that the service will use the servers of the monitor named Xpand and in the case of a Xpand monitor those servers will be the ones that the monitor has detected dynamically. That is, when setup like this, the service will automatically adjust to any changes taking place in the Xpand cluster.

NOTE There is no need to specify any router_options, but the default router_options=running provides the desired behaviour. In particular do not specify router_options=master as that will cause only a single node to be used.

Listener

To complete the configuration, a listener must be specified.

Xpand and Readwritesplit

The primary purpose of the router readwritesplit is to split statements between one master and multiple slaves. In the case of Xpand, all servers will be masters, but readwritesplit may still be the right choise.

Namely, as readwritesplit is transaction aware and capable of replaying transactions, it can be used for hiding certain events taking place in Xpand from the clients that use it.

For instance, whenever a node is removed from or added to a Xpand cluster there will be a group change, which is visible to a client as a transaction rollback. However, if readwritesplit is used and transaction replay is enabled, then MaxScale may be able to hide the group change so that the client only detects a slight delay.

Apart from the service section, the configuration when using_readwritesplit_ is identical to the readconnroute configuration described above.

Service

The service is specified as follows:

With this configuration, subject to the boundary conditions of transaction replaying, a client will neither notice group change events nor the disappearance of the very node the client is connected to. In that latter case, MaxScale will simply connect to another node and replay the current transaction (if one is active). For detailed information about the transaction replay functionality, please refer to the readwritesplit.

NOTE It is vital to haveslave_selection_criteria=LEAST_GLOBAL_CONNECTIONS, as otherwise connections will not be distributed evenly across all Xpand nodes.

As a rule of thumb, use readwritesplit if it is important that changes taking place in the cluster configuration are hidden from the applications, otherwise use readconnroute.

CC BY-SA / Gnu FDL

Setting up MariaDB MaxScale

Setting up MariaDB MaxScale

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

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

CC BY-SA / Gnu FDL

Simple Sharding with Two Servers

Simple Sharding with Two Servers

Sharding is the method of splitting a single database server into separate parts. 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 database server.

MariaDB MaxScale will appear to the client as a database server with the combination of all the schemas in all the configured servers.

Environment & Solution Space

This document is designed as a simple tutorial on schema-based sharding using MariaDB MaxScale in an environment in which you have two servers. The object of this tutorial is to have a system that, to the client side, acts like a single MariaDB database but actually is sharded between the two servers.

The database users should be configured according to . The contains easy to follow instructions on how to set up MaxScale.

This tutorial will assume the user is using of the binary distributions available and has installed this in the default location. The process of configuring MariaDB MaxScale will be covered within this document. The installation and configuration of the MariaDB servers will not be covered in-depth.

Preparing MaxScale

Follow the to install and prepare the required database users for MaxScale. You don't need to create the configuration file for MaxScale as it will be covered in the next section.

Creating Your MariaDB MaxScale Configuration

The first step in the creation of your maxscale.cnf file is to define the global maxscale section. This section configures the number of threads MariaDB MaxScale uses. A good rule of thumb is to use at most as may threads as you have CPUs. MariaDB MaxScale uses few threads for internal operations so one or two threads less than the maximum should be enough.

After this we configure two servers we will use to shard our database. The accounts_east server will hold one schema and the accounts_west will hold another schema. We will use these two servers to create our sharded database.

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. The router we use in this tutorial is the schemarouter.

After this we configure a listener for the service. The listener is the actual port 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, define the credentials to use and we set the monitoring cycle interval.

After this we have a fully working configuration and we can move on to starting MariaDB MaxScale.

Starting MariaDB MaxScale

Upon completion of the configuration process MariaDB MaxScale is ready to be started . This may either be done manually by running the maxscale command or via the service interface. The service scripts are located in the /etc/init.d/ folder and are accessible through both the service and systemctl commands.

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.

If MariaDB MaxScale fails to start, check the error log in /var/log/maxscale to see what sort of errors were detected.

Note: As the sharding solution in MaxScale is relatively simple, cross-database queries between two or more shards are not supported.

CC BY-SA / Gnu FDL

Configuring the Galera Monitor

Configuring the Galera Monitor

This document describes how to configure a Galera cluster monitor.

Configuring the Monitor

Define the monitor that monitors the servers.

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

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

Monitor User

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

CC BY-SA / Gnu FDL

Configuring Servers

Configuring Servers

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

Read-Write Splitting with MariaDB MaxScale

Read-Write Splitting with MariaDB MaxScale

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

Avrorouter Tutorial

Avrorouter Tutorial

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

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

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 master server. The second service will read the binlogs as they are streamed from the master 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 master server

Before starting the MaxScale process, we need to make sure that the binary logs of the master 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 slave, configure it to replicate from the master and point MaxScale to this slave (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 master 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.

CC BY-SA / Gnu FDL

Filters

Filters

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.

CC BY-SA / Gnu FDL

Connection Routing with MariaDB MaxScale

Connection Routing with MariaDB MaxScale

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

Automatic Failover With MariaDB Monitor

Automatic Failover With MariaDB Monitor

The MariaDB Monitor is not only capable of monitoring the state of a MariaDB master-slave cluster but is also capable of performing failover and switchover. In addition, in some circumstances it is capable of rejoining a master 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 master and several slaves.

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 and server4 - of which server1 is the initial master and the other servers are slaves. In addition there is a monitor called TheMonitor that monitors those servers.

Somewhat simplified, the MaxScale configuration file would look like:

Manual Failover

If everything is in order, the state of the cluster will look something like this:

If the master now for any reason goes down, then the cluster state will look like this:

Note that the status for server1 is Down.

Since failover is by default not enabled, the failover mechanism must be invoked manually:

There are quite a few arguments, so let's look at each one separately_call command indicates that it is a module command that is to be_ &#xNAN;invoked, mariadbmon indicates the module whose command we want to invoke (that is the MariaDB Monitor),failover is the command we want to invoke, and TheMonitor is the first and only argument to that command, the name of the monitor as specified in the configuration file.

The MariaDB Monitor will now autonomously deduce which slave is the most appropriate one to be promoted to master, promote it to master and modify the other slaves accordingly.

If we now check the cluster state we will see that one of the remaining slaves has been made into master.

If server1 now reappears, it will not be rejoined to the cluster, as shown by the following output:

Had auto_rejoin=true been specified in the monitor section, then an attempt to rejoin server1 would have been made.

In MaxScale 2.2.1, rejoining cannot be initiated manually, but in a subsequent version a command to that effect will be provided.

Automatic Failover

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

When everything is running fine, the cluster state looks like follows:

If server1 now goes down, failover will automatically be performed and an existing slave promoted to new master.

If you are continuously monitoring the server states, you may notice for a brief period that the state of server1 is Down and the state ofserver2 is still Slave, Running.

Rejoin

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

When automatic rejoin is enabled, the MariaDB Monitor will attempt to rejoin a failed master as a slave, if it reappears.

When everything is running fine, the cluster state looks like follows:

Assuming auto_failover=true has been specified in the configuration file, when server1 goes down for some reason, failover will be performed and we end up with the following cluster state:

If server1 now reappears, the MariaDB Monitor will detect that and attempt to rejoin the old master as a slave.

Whether rejoining will succeed depends upon the actual state of the old master. For instance, if the old master was modified and the changes had not been replicated to the new master, before the old master went down, then automatic rejoin will not be possible.

If rejoining can be performed, then the cluster state will end up looking like:

Switchover

Switchover is for cases when you explicitly want to move the master role from one server to another.

If we continue from the cluster state at the end of the previous example and want to make server1 master again, then we must issue the following command:

There are quite a few arguments, so let's look at each one separately_call command indicates that it is a module command that is to be_ &#xNAN;invoked, mariadbmon indicates the module whose command we want to invoke,switchover is the command we want to invoke, and TheMonitor is the first argument to the command, the name of the monitor as specified in the configuration file,server1 is the second argument to the command, the name of the server we &#xNAN;want to make into master, and server2 is the third argument to the command, the name of the currentmaster.

If the command executes successfully, we will end up with the following cluster state:

CC BY-SA / Gnu FDL

REST API Tutorial

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

CC BY-SA / Gnu FDL

Configuring the MariaDB Monitor

Configuring the MariaDB Monitor

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

Configuring the Monitor

Define the monitor that monitors the servers.

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

Monitor User

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

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

CC BY-SA / Gnu FDL

Using MaxGUI Tutorial

Using MaxGUI Tutorial

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.

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 mouseover event. 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 mouseover event allowing to swap the running primary server with a designated secondary server.

2. Edit parameters button. This button is shown on the mouseover event 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 mouseover event 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 mouseover event 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.

Query Editor

A SQL editor tool to run queries and perform other SQL operations.

Annotation

1. Worksheet tab navigation. Each worksheet is bound to a connection, so sessions querying within a worksheet is not yet supported.

2. Add a new worksheet button.

3. Connection manager dropdown. With this dropdown, you can create a new connection or change the connection for the current active worksheet. A new connection can be created by selecting the last option in the dropdown labeled as New connection. Once a connection is created, it automatically binds the connection to the current active worksheet.

• Each object has its own context menu providing different options. e.g. For the table object as shown in the figure above, it has options toPreview Data (top 1000) and View Details. The query result for these options is shown in the Data Preview result tab which is annotated as number 12. The context menu can be shown by right-clicking on the object or clicking on the three dots icon placed on the right side of the object.

• Quick access to the Preview Data (top 1000) context menu option. For a table object, its preview data can also be seen by clicking on its name.

1. Refresh schema objects button. After deleting or creating schema object, theSchemas sidebar needs to be manually refreshed.

2. Collapse the Schemas sidebar button.

3. SQL editor. The editor is powered by which means its functionalities are similar to VS code. Available commands can be seen by pressing F1 while the cursor is active on the editor. This is an intention to prevent conflict between the browser's shortcut keys and the SQL editor's. This also means the editor shortcut key commands are valid only when the cursor is active on the

How to kill a session

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

Annotation

1. Kill session button. This button is shown on the mouseover event.

2. Confirm killing the session dialog.

CC BY-SA / Gnu FDL

Encrypting Passwords

Encrypting Passwords

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

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

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

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

The username and password, either encrypted or plain text, are stored in the service section using the 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.

CC BY-SA / Gnu FDL

MariaDB MaxScale Administration Tutorial

MariaDB MaxScale Administration Tutorial

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.

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 the Log File

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

CC BY-SA / Gnu FDL