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

Somewhat simplified, the MaxScale configuration file would look like:

[server1]
type=server
address=192.168.121.51
port=3306

[server2]
...

[server3]
...

[server4]
...

[TheMonitor]
type=monitor
module=mariadbmon
servers=server1,server2,server3,server4
...

Manual Failover

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

$ maxctrl list servers
┌─────────┬─────────────────┬──────┬─────────────┬─────────────────┐
│ Server  │ Address         │ Port │ Connections │ State           │
├─────────┼─────────────────┼──────┼─────────────┼─────────────────┤
│ server1 │ 192.168.121.51  │ 3306 │ 0           │ Master, Running │
├─────────┼─────────────────┼──────┼─────────────┼─────────────────┤
│ server2 │ 192.168.121.190 │ 3306 │ 0           │ Slave, Running  │
├─────────┼─────────────────┼──────┼─────────────┼─────────────────┤
│ server3 │ 192.168.121.112 │ 3306 │ 0           │ Slave, Running  │
├─────────┼─────────────────┼──────┼─────────────┼─────────────────┤
│ server4 │ 192.168.121.201 │ 3306 │ 0           │ Slave, Running  │
└─────────┴─────────────────┴──────┴─────────────┴─────────────────┘

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

$ maxctrl list servers
┌─────────┬─────────────────┬──────┬─────────────┬────────────────┐
│ Server  │ Address         │ Port │ Connections │ State          │
├─────────┼─────────────────┼──────┼─────────────┼────────────────┤
│ server1 │ 192.168.121.51  │ 3306 │ 0           │ Down           │
├─────────┼─────────────────┼──────┼─────────────┼────────────────┤
│ server2 │ 192.168.121.190 │ 3306 │ 0           │ Slave, Running │
├─────────┼─────────────────┼──────┼─────────────┼────────────────┤
│ server3 │ 192.168.121.112 │ 3306 │ 0           │ Slave, Running │
├─────────┼─────────────────┼──────┼─────────────┼────────────────┤
│ server4 │ 192.168.121.201 │ 3306 │ 0           │ Slave, Running │
└─────────┴─────────────────┴──────┴─────────────┴────────────────┘

Note that the status for server1 is Down.

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

$ maxctrl call command mariadbmon failover TheMonitor
OK

There are quite a few arguments, so let's look at each one separatelycall 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 replica is the most appropriate one to be promoted to primary, promote it to primary and modify the other replicas accordingly.

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

$ maxctrl list servers
┌─────────┬─────────────────┬──────┬─────────────┬─────────────────┐
│ Server  │ Address         │ Port │ Connections │ State           │
├─────────┼─────────────────┼──────┼─────────────┼─────────────────┤
│ server1 │ 192.168.121.51  │ 3306 │ 0           │ Down            │
├─────────┼─────────────────┼──────┼─────────────┼─────────────────┤
│ server2 │ 192.168.121.190 │ 3306 │ 0           │ Master, Running │
├─────────┼─────────────────┼──────┼─────────────┼─────────────────┤
│ server3 │ 192.168.121.112 │ 3306 │ 0           │ Slave, Running  │
├─────────┼─────────────────┼──────┼─────────────┼─────────────────┤
│ server4 │ 192.168.121.201 │ 3306 │ 0           │ Slave, Running  │
└─────────┴─────────────────┴──────┴─────────────┴─────────────────┘

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

$ maxctrl list servers
┌─────────┬─────────────────┬──────┬─────────────┬─────────────────┐
│ Server  │ Address         │ Port │ Connections │ State           │
├─────────┼─────────────────┼──────┼─────────────┼─────────────────┤
│ server1 │ 192.168.121.51  │ 3306 │ 0           │ Running         │
├─────────┼─────────────────┼──────┼─────────────┼─────────────────┤
│ server2 │ 192.168.121.190 │ 3306 │ 0           │ Master, Running │
├─────────┼─────────────────┼──────┼─────────────┼─────────────────┤
│ server3 │ 192.168.121.112 │ 3306 │ 0           │ Slave, Running  │
├─────────┼─────────────────┼──────┼─────────────┼─────────────────┤
│ server4 │ 192.168.121.201 │ 3306 │ 0           │ Slave, Running  │
└─────────┴─────────────────┴──────┴─────────────┴─────────────────┘

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.

[TheMonitor]
type=monitor
module=mariadbmon
servers=server1,server2,server3,server4
auto_failover=true
...

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

$ maxctrl list servers
┌─────────┬─────────────────┬──────┬─────────────┬─────────────────┐
│ Server  │ Address         │ Port │ Connections │ State           │
├─────────┼─────────────────┼──────┼─────────────┼─────────────────┤
│ server1 │ 192.168.121.51  │ 3306 │ 0           │ Master, Running │
├─────────┼─────────────────┼──────┼─────────────┼─────────────────┤
│ server2 │ 192.168.121.190 │ 3306 │ 0           │ Slave, Running  │
├─────────┼─────────────────┼──────┼─────────────┼─────────────────┤
│ server3 │ 192.168.121.112 │ 3306 │ 0           │ Slave, Running  │
├─────────┼─────────────────┼──────┼─────────────┼─────────────────┤
│ server4 │ 192.168.121.201 │ 3306 │ 0           │ Slave, Running  │
└─────────┴─────────────────┴──────┴─────────────┴─────────────────┘

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

$ maxctrl list servers
┌─────────┬─────────────────┬──────┬─────────────┬────────────────────────┐
│ Server  │ Address         │ Port │ Connections │ State                  │
├─────────┼─────────────────┼──────┼─────────────┼────────────────────────┤
│ server1 │ 192.168.121.51  │ 3306 │ 0           │ Down                   │
├─────────┼─────────────────┼──────┼─────────────┼────────────────────────┤
│ server2 │ 192.168.121.190 │ 3306 │ 0           │ Master, Slave, Running │
├─────────┼─────────────────┼──────┼─────────────┼────────────────────────┤
│ server3 │ 192.168.121.112 │ 3306 │ 0           │ Slave, Running         │
├─────────┼─────────────────┼──────┼─────────────┼────────────────────────┤
│ server4 │ 192.168.121.201 │ 3306 │ 0           │ Slave, Running         │
└─────────┴─────────────────┴──────┴─────────────┴────────────────────────┘

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 section in the configuration file.

[TheMonitor]
type=monitor
module=mariadbmon
servers=server1,server2,server3,server4
auto_rejoin=true
...

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

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

$ maxctrl list servers
┌─────────┬─────────────────┬──────┬─────────────┬─────────────────┐
│ Server  │ Address         │ Port │ Connections │ State           │
├─────────┼─────────────────┼──────┼─────────────┼─────────────────┤
│ server1 │ 192.168.121.51  │ 3306 │ 0           │ Master, Running │
├─────────┼─────────────────┼──────┼─────────────┼─────────────────┤
│ server2 │ 192.168.121.190 │ 3306 │ 0           │ Slave, Running  │
├─────────┼─────────────────┼──────┼─────────────┼─────────────────┤
│ server3 │ 192.168.121.112 │ 3306 │ 0           │ Slave, Running  │
├─────────┼─────────────────┼──────┼─────────────┼─────────────────┤
│ server4 │ 192.168.121.201 │ 3306 │ 0           │ Slave, Running  │
└─────────┴─────────────────┴──────┴─────────────┴─────────────────┘

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:

$ maxctrl list servers
┌─────────┬─────────────────┬──────┬─────────────┬─────────────────┐
│ Server  │ Address         │ Port │ Connections │ State           │
├─────────┼─────────────────┼──────┼─────────────┼─────────────────┤
│ server1 │ 192.168.121.51  │ 3306 │ 0           │ Down            │
├─────────┼─────────────────┼──────┼─────────────┼─────────────────┤
│ server2 │ 192.168.121.190 │ 3306 │ 0           │ Master, Running │
├─────────┼─────────────────┼──────┼─────────────┼─────────────────┤
│ server3 │ 192.168.121.112 │ 3306 │ 0           │ Slave, Running  │
├─────────┼─────────────────┼──────┼─────────────┼─────────────────┤
│ server4 │ 192.168.121.201 │ 3306 │ 0           │ Slave, Running  │
└─────────┴─────────────────┴──────┴─────────────┴─────────────────┘

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

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

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

$ maxctrl list servers
┌─────────┬─────────────────┬──────┬─────────────┬─────────────────┐
│ Server  │ Address         │ Port │ Connections │ State           │
├─────────┼─────────────────┼──────┼─────────────┼─────────────────┤
│ server1 │ 192.168.121.51  │ 3306 │ 0           │ Slave, Running  │
├─────────┼─────────────────┼──────┼─────────────┼─────────────────┤
│ server2 │ 192.168.121.190 │ 3306 │ 0           │ Master, Running │
├─────────┼─────────────────┼──────┼─────────────┼─────────────────┤
│ server3 │ 192.168.121.112 │ 3306 │ 0           │ Slave, Running  │
├─────────┼─────────────────┼──────┼─────────────┼─────────────────┤
│ server4 │ 192.168.121.201 │ 3306 │ 0           │ Slave, Running  │
└─────────┴─────────────────┴──────┴─────────────┴─────────────────┘

Switchover

Switchover is for cases when you explicitly want to move the primary 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 primary again, then we must issue the following command:

$ maxctrl call command mariadbmon switchover TheMonitor server1 server2
OK

There are quite a few arguments, so let's look at each one separatelycall 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 primary, and server2 is the third argument to the command, the name of the currentprimary.

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

$ maxctrl list servers
┌─────────┬─────────────────┬──────┬─────────────┬─────────────────┐
│ Server  │ Address         │ Port │ Connections │ State           │
├─────────┼─────────────────┼──────┼─────────────┼─────────────────┤
│ server1 │ 192.168.121.51  │ 3306 │ 0           │ Master, Running │
├─────────┼─────────────────┼──────┼─────────────┼─────────────────┤
│ server2 │ 192.168.121.190 │ 3306 │ 0           │ Slave, Running  │
├─────────┼─────────────────┼──────┼─────────────┼─────────────────┤
│ server3 │ 192.168.121.112 │ 3306 │ 0           │ Slave, Running  │
├─────────┼─────────────────┼──────┼─────────────┼─────────────────┤
│ server4 │ 192.168.121.201 │ 3306 │ 0           │ Slave, Running  │
└─────────┴─────────────────┴──────┴─────────────┴─────────────────┘

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

This tutorial is a short introduction to theAvrorouter, 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.

binlog_format=row
binlog_row_image=full

You can find out more about replication formats from theMariaDB documentation

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 Replication Proxy service
[replication-service]
type=service
router=binlogrouter
server_id=4000
master_id=3000
filestem=binlog
user=maxuser
password=maxpwd

# The Avro conversion service
[avro-service]
type=service
router=avrorouter
source=replication-service
filestem=binlog
start_index=15

# The listener for the replication-service
[replication-listener]
type=listener
service=replication-service
port=3306

# The client listener for the avro-service
[avro-listener]
type=listener
service=avro-service
protocol=CDC
port=4001

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. Thefilestem 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 Avrorouter Documentation.

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 cdc_schema Go utility and copy the generated .avsc files to the avrodir

• Use the Python version of the schema generator 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 theavrodir 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.

CHANGE MASTER TO MASTER_HOST='172.18.0.1',
       MASTER_PORT=3000,
       MASTER_LOG_FILE='binlog.000015',
       MASTER_LOG_POS=4,
       MASTER_USER='maxuser',
       MASTER_PASSWORD='maxpwd';

START SLAVE;

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

CREATE TABLE test.t1 (id INT);
INSERT INTO test.t1 VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10);

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.

maxctrl call command cdc add_user avro-service maxuser maxpwd

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.

cdc.py -u maxuser -p maxpwd -h 127.0.0.1 -P 4001 test.t1

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

{"namespace": "MaxScaleChangeDataSchema.avro", "type": "record", "name": "ChangeRecord", "fields": [{"name": "domain", "type": "int"}, {"name": "server_id", "type": "int"}, {"name": "sequence", "type": "int"}, {"name": "event_number", "type": "int"}, {"name": "timestamp", "type": "int"}, {"name": "event_type", "type": {"type": "enum", "name": "EVENT_TYPES", "symbols": ["insert", "update_before", "update_after", "delete"]}}, {"name": "id", "type": "int", "real_type": "int", "length": -1}]}
{"domain": 0, "server_id": 3000, "sequence": 11, "event_number": 1, "timestamp": 1537429419, "event_type": "insert", "id": 1}
{"domain": 0, "server_id": 3000, "sequence": 11, "event_number": 2, "timestamp": 1537429419, "event_type": "insert", "id": 2}
{"domain": 0, "server_id": 3000, "sequence": 11, "event_number": 3, "timestamp": 1537429419, "event_type": "insert", "id": 3}
{"domain": 0, "server_id": 3000, "sequence": 11, "event_number": 4, "timestamp": 1537429419, "event_type": "insert", "id": 4}
{"domain": 0, "server_id": 3000, "sequence": 11, "event_number": 5, "timestamp": 1537429419, "event_type": "insert", "id": 5}
{"domain": 0, "server_id": 3000, "sequence": 11, "event_number": 6, "timestamp": 1537429419, "event_type": "insert", "id": 6}
{"domain": 0, "server_id": 3000, "sequence": 11, "event_number": 7, "timestamp": 1537429419, "event_type": "insert", "id": 7}
{"domain": 0, "server_id": 3000, "sequence": 11, "event_number": 8, "timestamp": 1537429419, "event_type": "insert", "id": 8}
{"domain": 0, "server_id": 3000, "sequence": 11, "event_number": 9, "timestamp": 1537429419, "event_type": "insert", "id": 9}
{"domain": 0, "server_id": 3000, "sequence": 11, "event_number": 10, "timestamp": 1537429419, "event_type": "insert", "id": 10}

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}

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 theConfiguration Guide.

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

CREATE USER 'monitor_user'@'%' IDENTIFIED BY 'my_password';

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

CREATE USER 'monitor_user'@'%' IDENTIFIED BY 'my_password';
GRANT REPLICATION CLIENT on *.* to 'monitor_user'@'%';

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

GRANT SUPER, RELOAD on *.* to 'monitor_user'@'%';

_{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 MariaDB MaxScale Tutorial. Please read it and follow the instructions. Return here once basic setup is complete.

Configuring services

We want two services and ports to which the client application can connect. One service routes client connections to the primary server, the other load balances between replica servers. To achieve this, we need to define two services in the configuration file.

Create the following two sections in your configuration file. The section names are the names of the services and should be meaningful. For this tutorial, we use the namesWrite-Service and Read-Service.

[Write-Service]
type=service
router=readconnroute
router_options=master
servers=dbserv1, dbserv2, dbserv3
user=maxscale
password=maxscale_pw

[Read-Service]
type=service
router=readconnroute
router_options=slave
servers=dbserv1, dbserv2, dbserv3
user=maxscale
password=maxscale_pw

router defines the routing module used. Here we use readconnroute for connection-level routing.

A service needs a list of servers to route queries 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 router_options-parameter tells the readconnroute-module which servers it should route a client connection to. For the write service we use the master-type and for the read service the slave-type.

The user and password parameters define the credentials the service uses to populate user authentication data. These users were created at the start of theMaxScale Tutorial.

For increased security, see password encryption.

Configuring the Listener

To allow network connections to a service, a network ports must be associated with it. This is done by creating a separate listener section in the configuration file. A service may have multiple listeners but for this tutorial one per service is enough.

[Write-Listener]
type=listener
service=Write-Service
port=3306

[Read-Listener]
type=listener
service=Read-Service
port=3307

The service parameter tells which service the listener connects to. For theWrite-Listener we set it to Write-Service and for the Read-Listener we set it to Read-Service.

A listener must define the network port to listen on.

The optional address-parameter defines the local address the listener should bind to. This may be required when the host machine has multiple network interfaces. The default behavior is to listen on all network interfaces (the IPv6 address ::).

Starting MariaDB MaxScale

For the last steps, please return to MaxScale Tutorial.

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

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

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

maxkeys

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

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

maxpasswd plainpassword
96F99AA1315BDC3604B006F427DD9484

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.

[My-Service]
type=service
router=readconnroute
router_options=master
servers=dbserv1, dbserv2, dbserv3
user=maxscale
password=96F99AA1315BDC3604B006F427DD9484

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

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

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.

[MyFilter]
type=filter
module=xxxfilter

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.

[Split-Service]
type=service
router=readwritesplit
servers=dbserver1,dbserver2,dbserver3,dbserver4
user=massi
password=6628C50E07CCE1F0392EDEEB9D1203F3
filters=hints | top10

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.

[top30]
type=filter
module=topfilter
count=30
filebase=/var/log/DBSessions/top30

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.

• The total number of statements executed in the connection.

• The average execution time for a statement in this 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

[BubbleGame]
type=service
router=readwritesplit
servers=dbbubble1,dbbubble2,dbbubble3,dbbubble4,dbbubble5
user=maxscale
password=6628C50E07CCE1F0392EDEEB9D1203F3

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 Cassandra Storage Engine Overview for details. Add this server into the MariaDB MaxScale configuration and create a service that will connect to this server.

[CassandraDB]
type=server
address=192.168.4.28
port=3306

[Cassandra]
type=service
router=readconnroute
router_options=running
servers=CassandraDB
user=maxscale
password=6628C50E07CCE1F0392EDEEB9D1203F3

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.

[HighScores]
type=filter
module=teefilter
match=insert.*HighScore.*values
service=Cassandra

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.

[BubbleGame]
type=service
router=readwritesplit
servers=dbbubble1,dbbubble2,dbbubble3,dbbubble4,dbbubble5
user=maxscale
password=6628C50E07CCE1F0392EDEEB9D1203F3
filters=HighScores

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

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

• MariaDB MaxScale Administration Tutorial

  • Administration audit file

  • Starting and Stopping MariaDB MaxScale

    • Additional Options for MaxScale

  • Checking The Status Of The MariaDB MaxScale Services

  • What Clients Are Connected To MariaDB MaxScale

  • Rotating Log Files

  • Taking Objects Temporarily Out of Use

    • Putting Servers into Maintenance

  • Stopping and Starting Services

    • Stopping and Starting Monitors

  • Runtime Configuration Modification

    • Core Parameter Configuration

    • Managing Servers

      • Create a new server

      • Modify a Server

      • Destroy a Server

      • Drain a Server

    • Managing Monitors

      • Create a new Monitor

      • Modify a Monitor

      • Add Server to a Monitor

      • Remove a Server from a Monitor

      • Destroy a Monitor

    • Managing Services

      • Create a New Service

      • Modify a Service

      • Add Servers to a Service

      • Remove Servers from a Service

      • Change the Filters of a Service

      • Destroy a Service

    • Managing Filters

      • Create a New Filter

      • Destroy a Filter

    • Managing Listeners

      • Create a New Listener

      • Destroy a Listener

  • Managing MaxCtrl and REST API Users

    • Create a New MaxCtrl User

    • Change the Password of an Existing User

    • Remove a User

Administration audit file

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

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 tothe systemctl man page andthe systemd documentation.

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.

$ maxctrl list services
┌────────────────────────┬────────────────┬─────────────┬───────────────────┬────────────────────────────────────┐
│ Service                │ Router         │ Connections │ Total Connections │ Servers                            │
├────────────────────────┼────────────────┼─────────────┼───────────────────┼────────────────────────────────────┤
│ CLI                    │ cli            │ 1           │ 1                 │                                    │
├────────────────────────┼────────────────┼─────────────┼───────────────────┼────────────────────────────────────┤
│ RW-Split-Router        │ readwritesplit │ 1           │ 1                 │ server1, server2, server3, server4 │
├────────────────────────┼────────────────┼─────────────┼───────────────────┼────────────────────────────────────┤
│ RW-Split-Hint-Router   │ readwritesplit │ 1           │ 1                 │ server1, server2, server3, server4 │
├────────────────────────┼────────────────┼─────────────┼───────────────────┼────────────────────────────────────┤
│ SchemaRouter-Router    │ schemarouter   │ 1           │ 1                 │ server1, server2, server3, server4 │
├────────────────────────┼────────────────┼─────────────┼───────────────────┼────────────────────────────────────┤
│ Read-Connection-Router │ readconnroute  │ 1           │ 1                 │ server1                            │
└────────────────────────┴────────────────┴─────────────┴───────────────────┴────────────────────────────────────┘

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.

$ maxctrl list sessions
┌────┬─────────┬──────────────────┬──────────────────────────┬──────┬─────────────────┐
│ Id │ User    │ Host             │ Connected                │ Idle │ Service         │
├────┼─────────┼──────────────────┼──────────────────────────┼──────┼─────────────────┤
│ 6  │ maxuser │ ::ffff:127.0.0.1 │ Thu Aug 27 10:39:16 2020 │ 4    │ RW-Split-Router │
└────┴─────────┴──────────────────┴──────────────────────────┴──────┴─────────────────┘

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.

maxctrl rotate logs

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.

/var/log/maxscale/maxscale.log {
monthly
rotate 5
missingok
nocompress
sharedscripts
postrotate
\# run if maxscale is running
if test -n "`ps acx|grep maxscale`"; then
/usr/bin/maxctrl rotate logs
fi
endscript
}

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

/var/log/maxscale/maxscale.log {
monthly
rotate 5
missingok
nocompress
sharedscripts
postrotate
kill -USR1 `cat /var/run/maxscale/maxscale.pid`
endscript
}

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.

maxctrl set server db-server-3 maintenance

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.

maxctrl clear server db-server-3 maintenance

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

maxctrl stop service db-service

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.

maxctrl start service db-service

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

Stopping and Starting Monitors

maxctrl stop monitor db-monitor

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.

maxctrl start monitor db-monitor

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:

maxctrl alter maxscale auth_connect_timeout 5s

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

Managing Servers

Create a new server

maxctrl create server db-server-1 192.168.0.100 3306

Modify a Server

maxctrl alter server db-server-1 port 3307

Destroy a Server

maxctrl destroy server db-server-1

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

maxctrl set server db-server-1 drain

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:

maxctrl clear server db-server-1 drain

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

maxctrl create monitor db-monitor mariadbmon user=db-user password=db-password

Modify a Monitor

maxctrl alter monitor db-monitor monitor_interval 1000

Add Server to a Monitor

maxctrl link monitor db-monitor db-server-1

Remove a Server from a Monitor

maxctrl unlink monitor db-monitor db-server-1

Destroy a Monitor

maxctrl destroy monitor db-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

maxctrl create service db-service readwritesplit user=db-user password=db-password

Modify a Service

maxctrl alter service db-service user new-db-user

Add Servers to a Service

maxctrl link service db-service db-server1

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

maxctrl unlink service db-service db-server1

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

maxctrl alter service-filters my-regexfilter my-qlafilter

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

maxctrl destroy service db-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

maxctrl create filter regexfilter match=ENGINE=MyISAM replace=ENGINE=InnoDB

Destroy a Filter

maxctrl destroy filter my-regexfilter

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

maxctrl create listener db-listener db-service 4006

Destroy a Listener

maxctrl destroy listener db-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

maxctrl create user basic-user basic-password

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:

maxctrl create user admin-user admin-password --type=admin

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

Change the Password of an Existing User

maxctrl alter user admin-user new-admin-password

Remove a User

maxctrl destroy user basic-user

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

[Splitter-Service]
type=service
router=readwritesplit
servers=dbserv1, dbserv2, dbserv3
user=maxscale
password=maxscale_pw

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

For increased security, see password encryption.

Configuring the Listener

To allow network connections to a service, a network ports must be associated with it. This is done by creating a separate listener section in the configuration file. A service may have multiple listeners but for this tutorial one is enough.

[Splitter-Listener]
type=listener
service=Splitter-Service
port=3306

The service parameter tells which service the listener connects to. For theSplitter-Listener we set it to Splitter-Service.

A listener must define the network port to listen on.

The optional address-parameter defines the local address the listener should bind to. This may be required when the host machine has multiple network interfaces. The default behavior is to listen on all network interfaces (the IPv6 address ::).

Starting MariaDB MaxScale

For the last steps, please return to MaxScale Tutorial.

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

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:

maxctrl create user my_user my_password --type=admin
maxctrl destroy user admin

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.

admin_ssl_key=/certs/server-key.pem
admin_ssl_cert=/certs/server-cert.pem
admin_ssl_ca_cert=/certs/ca-cert.pem

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.

maxctrl --user=my_user --password=my_password --secure --tls-ca-cert=/certs/ca-cert.pem --tls-verify-server-cert=false show maxscale

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/

HTTP/1.1 200 OK
Connection: Keep-Alive
Content-Length: 0
Last-Modified: Mon, 04 Mar 2019 08:23:09 GMT
ETag: "0"
Date: Mon, 04 Mar 19 08:29:41 GMT

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

{
    "links": {
        "self": "http://127.0.0.1:8989/v1/filters/"
    },
    "data": [
        {
            "id": "Hint",
            "type": "filters",
            "relationships": {
                "services": {
                    "links": {
                        "self": "http://127.0.0.1:8989/v1/services/"
                    },
                    "data": [
                        {
                            "id": "RW-Split-Hint-Router",
                            "type": "services"
                        }
                    ]
                }
            },
            "attributes": {
                "module": "hintfilter",
                "parameters": {}
            },
            "links": {
                "self": "http://127.0.0.1:8989/v1/filters/Hint"
            }
        },
        {
            "id": "Logger",
            "type": "filters",
            "relationships": {
                "services": {
                    "links": {
                        "self": "http://127.0.0.1:8989/v1/services/"
                    },
                    "data": []
                }
            },
            "attributes": {
                "module": "qlafilter",
                "parameters": {
                    "match": null,
                    "exclude": null,
                    "user": null,
                    "source": null,
                    "filebase": "/tmp/log",
                    "options": "ignorecase",
                    "log_type": "session",
                    "log_data": "date,user,query",
                    "newline_replacement": "\" \"",
                    "separator": ",",
                    "flush": false,
                    "append": false
                },
                "filter_diagnostics": {
                    "separator": ",",
                    "newline_replacement": "\" \""
                }
            },
            "links": {
                "self": "http://127.0.0.1:8989/v1/filters/Logger"
            }
        }
    ]
}

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

{
    "links": {
        "self": "http://127.0.0.1:8989/v1/filters/Logger"
    },
    "data": {
        "id": "Logger",
        "type": "filters",
        "relationships": {
            "services": {
                "links": {
                    "self": "http://127.0.0.1:8989/v1/services/"
                },
                "data": []
            }
        },
        "attributes": {
            "module": "qlafilter",
            "parameters": {
                "match": null,
                "exclude": null,
                "user": null,
                "source": null,
                "filebase": "/tmp/log",
                "options": "ignorecase",
                "log_type": "session",
                "log_data": "date,user,query",
                "newline_replacement": "\" \"",
                "separator": ",",
                "flush": false,
                "append": false
            },
            "filter_diagnostics": {
                "separator": ",",
                "newline_replacement": "\" \""
            }
        },
        "links": {
            "self": "http://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.

{
    "data": {
        "id": "server1",
        "type": "servers",
        "attributes": {
            "parameters": {
                "address": "127.0.0.1",
                "port": 3003
            }
        }
    }
}

To send the data, use the following command.

curl -X POST -d @new_server.txt 127.0.0.1:8989/v1/servers

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.

curl 127.0.0.1:8989/v1/servers/server1

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.

curl 127.0.0.1:8989/v1/servers/server1 > server1.txt

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.

curl -X PATCH -d @server1.txt 127.0.0.1:8989/v1/servers/server1

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

curl 127.0.0.1:8989/v1/servers/server1

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

{
    "data": {
        "id": "server1",
        "type": "servers",
        "relationships": {
            "services": {
                "data": [
                    {
                        "id": "RW-Split-Router",
                        "type": "services"
                    }
                ]
            }
        },
        "attributes":  ...
    }
}

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:

{
    "data": {
        "relationships": {
            "services": {
                "data": []
            },
            "monitors": {
                "data": []
            }
        }
    }
}

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.

curl -X DELETE 127.0.0.1:8989/v1/servers/server1

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 theREST API documentation.

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}

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:Setting Up Replication andGetting Started With MariaDB Galera Cluster .

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

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

Installing MaxScale

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

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.

CREATE USER 'maxscale'@'%' IDENTIFIED BY 'maxscale_pw';
GRANT SELECT ON mysql.user TO 'maxscale'@'%';
GRANT SELECT ON mysql.db TO 'maxscale'@'%';
GRANT SELECT ON mysql.tables_priv TO 'maxscale'@'%';
GRANT SELECT ON mysql.columns_priv TO 'maxscale'@'%';
GRANT SELECT ON mysql.procs_priv TO 'maxscale'@'%';
GRANT SELECT ON mysql.proxies_priv TO 'maxscale'@'%';
GRANT SELECT ON mysql.roles_mapping TO 'maxscale'@'%';
GRANT SHOW DATABASES ON *.* TO 'maxscale'@'%';

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

CREATE USER 'jdoe'@'maxscale-host' IDENTIFIED BY 'my_secret_password';

Then do a SHOW GRANTS query:

MariaDB [(none)]> SHOW GRANTS FOR 'jdoe'@'client-host';
+-----------------------------------------------------------------------+
| Grants for jdoe@client-host                                           |
+-----------------------------------------------------------------------+
| GRANT SELECT, INSERT, UPDATE, DELETE ON *.* TO 'jdoe'@'client-host'   |
+-----------------------------------------------------------------------+
1 row in set (0.01 sec)

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

GRANT SELECT, INSERT, UPDATE, DELETE ON *.* TO 'jdoe'@'maxscale-host';

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.

[maxscale]
threads=auto

Configuring the servers

Read the Configuring Servers 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 readConfiguring MariaDB Monitor. For a Galera cluster readConfiguring Galera Monitor.

Configuring the services and listeners

This part is covered in two different tutorials. For a fully automated read-write-splitting setup, read theRead Write Splitting Tutorial. For a simple connection based setup, read theConnection Routing Tutorial.

Starting MaxScale

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

sudo systemctl start maxscale

For older SysV systems, use the service command.

sudo service maxscale start

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.

% sudo maxctrl list services

┌──────────────────┬────────────────┬─────────────┬───────────────────┬───────────────────────────┐
│ Service          │ Router         │ Connections │ Total Connections │ Servers                   │
├──────────────────┼────────────────┼─────────────┼───────────────────┼───────────────────────────┤
│ Splitter-Service │ readwritesplit │ 1           │ 1                 │ dbserv1, dbserv2, dbserv3 │
└──────────────────┴────────────────┴─────────────┴───────────────────┴───────────────────────────┘

% sudo maxctrl list servers

┌─────────┬─────────────┬──────┬─────────────┬─────────────────┬───────────┐
│ Server  │ Address     │ Port │ Connections │ State           │ GTID      │
├─────────┼─────────────┼──────┼─────────────┼─────────────────┼───────────┤
│ dbserv1 │ 192.168.2.1 │ 3306 │ 0           │ Master, Running │ 0-3000-62 │
├─────────┼─────────────┼──────┼─────────────┼─────────────────┼───────────┤
│ dbserv2 │ 192.168.2.2 │ 3306 │ 0           │ Slave, Running  │ 0-3000-62 │
├─────────┼─────────────┼──────┼─────────────┼─────────────────┼───────────┤
│ dbserv3 │ 192.168.2.3 │ 3306 │ 0           │ Slave, Running  │ 0-3000-62 │
└─────────┴─────────────┴──────┴─────────────┴─────────────────┴───────────┘

% sudo maxctrl list listeners Splitter-Service

┌───────────────────┬──────┬──────┬─────────┐
│ Name              │ Port │ Host │ State   │
├───────────────────┼──────┼──────┼─────────┤
│ Splitter-Listener │ 3306 │      │ Running │
└───────────────────┴──────┴──────┴─────────┘

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

More options can be found in theConfiguration Guide,readwritesplit module documentation andreadconnroute module documentation.

For more information about MaxCtrl and how to secure it, see theREST-API Tutorial.

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

# Install MaxScale
apt update
apt -y install sudo curl
curl -LsS https://r.mariadb.com/downloads/mariadb_repo_setup | sudo bash
apt -y install maxscale

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 theMaxScale Configuration Guide.

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.

-- Create the user for the service
-- https://mariadb.com/kb/en/mariadb-maxscale-2308-authentication-modules/#required-grants
CREATE USER 'service_user'@'%' IDENTIFIED BY 'secret';
GRANT SELECT ON mysql.* TO 'service_user'@'%';
GRANT SHOW DATABASES ON *.* TO 'service_user'@'%';

-- Create the user for the monitor
-- https://mariadb.com/kb/en/mariadb-maxscale-2308-galera-monitor/#required-grants
CREATE USER 'monitor_user'@'%' IDENTIFIED BY 'secret';
GRANT REPLICATION CLIENT ON *.* TO 'monitor_user'@'%';

-- Create the application user
-- https://mariadb.com/kb/en/mariadb-maxscale-2308-authentication-modules/#limitations-and-troubleshooting
CREATE USER app_user@'%' IDENTIFIED BY 'secret';
GRANT SELECT, INSERT, UPDATE, DELETE ON *.* TO app_user@'%';

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 DATABASE IF NOT EXISTS customer_01;
CREATE TABLE IF NOT EXISTS customer_01.accounts(id INT, account_type INT, account_name VARCHAR(255));
INSERT INTO customer_01.accounts VALUES (1, 1, 'foo');

-- The shared schema that's on all shards
CREATE DATABASE IF NOT EXISTS shared_info;
CREATE TABLE IF NOT EXISTS shared_info.account_types(account_type INT, type_name VARCHAR(255));
INSERT INTO shared_info.account_types VALUES (1, 'admin'), (2, 'user');

Create the tables on the second server:

CREATE DATABASE IF NOT EXISTS customer_02;
CREATE TABLE IF NOT EXISTS customer_02.accounts(id INT, account_type INT, account_name VARCHAR(255));
INSERT INTO customer_02.accounts VALUES (2, 2, 'bar');

-- The shared schema that's on all shards
CREATE DATABASE IF NOT EXISTS shared_info;
CREATE TABLE IF NOT EXISTS shared_info.account_types(account_type INT, type_name VARCHAR(255));
INSERT INTO shared_info.account_types VALUES (1, 'admin'), (2, 'user');

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.

[db-01]
type=server
address=192.168.0.102
port=3306

[db-02]
type=server
address=192.168.0.103
port=3306

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=.*.

[Sharded-Service]
type=service
router=schemarouter
targets=db-02,db-01
user=service_user
password=secret
ignore_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.

[Sharded-Service-Listener]
type=listener
service=Sharded-Service
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.

[Shard-Monitor]
type=monitor
module=galeramon
servers=db-02,db-01
user=monitor_user
password=secret

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

[db-01]
type=server
address=192.168.0.102
port=3306

[db-02]
type=server
address=192.168.0.103
port=3306

[Sharded-Service]
type=service
router=schemarouter
targets=db-02,db-01
user=service_user
password=secret
ignore_tables_regex=.*

[Sharded-Service-Listener]
type=listener
service=Sharded-Service
protocol=MariaDBClient
port=4000

[Shard-Monitor]
type=monitor
module=galeramon
servers=db-02,db-01
user=monitor_user
password=secret

Then you're ready to start MaxScale.

systemctl start maxscale.service

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.

$ mariadb -A -u app_user -psecret -h 127.0.0.1 -P 4000
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 3
Server version: 10.11.7-MariaDB-1:10.11.7+maria~ubu2004-log mariadb.org binary distribution

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> USE customer_01;
Database changed
MariaDB [customer_01]> SELECT c.account_name, c.account_type, s.type_name FROM accounts c
    ->   JOIN shared_info.account_types s ON (c.account_type = s.account_type);
+--------------+--------------+-----------+
| account_name | account_type | type_name |
+--------------+--------------+-----------+
| foo          |            1 | admin     |
+--------------+--------------+-----------+
1 row in set (0.001 sec)

MariaDB [customer_01]> USE customer_02;
Database changed
MariaDB [customer_02]> SELECT c.account_name, c.account_type, s.type_name FROM accounts c
    ->   JOIN shared_info.account_types s ON (c.account_type = s.account_type);
+--------------+--------------+-----------+
| account_name | account_type | type_name |
+--------------+--------------+-----------+
| bar          |            2 | user      |
+--------------+--------------+-----------+
1 row in set (0.000 sec)

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

MariaDB [(none)]> SELECT c.account_name, c.account_type, s.type_name FROM customer_01.accounts c
    ->   JOIN shared_info.account_types s ON (c.account_type = s.account_type);
+--------------+--------------+-----------+
| account_name | account_type | type_name |
+--------------+--------------+-----------+
| foo          |            1 | admin     |
+--------------+--------------+-----------+
1 row in set (0.001 sec)

MariaDB [(none)]> SELECT c.account_name, c.account_type, s.type_name FROM customer_02.accounts c
    ->   JOIN shared_info.account_types s ON (c.account_type = s.account_type);
+--------------+--------------+-----------+
| account_name | account_type | type_name |
+--------------+--------------+-----------+
| bar          |            2 | user      |
+--------------+--------------+-----------+
1 row in set (0.001 sec)

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

MariaDB [(none)]> SELECT * FROM customer_01.accounts UNION SELECT * FROM customer_02.accounts;
ERROR 1146 (42S02): Table 'customer_01.accounts' doesn't exist
MariaDB [(none)]> USE customer_01;
Database changed
MariaDB [customer_01]> SELECT * FROM customer_01.accounts UNION SELECT * FROM customer_02.accounts;
ERROR 1146 (42S02): Table 'customer_02.accounts' doesn't exist
MariaDB [customer_01]> USE customer_02;
Database changed
MariaDB [customer_02]> SELECT * FROM customer_01.accounts UNION SELECT * FROM customer_02.accounts;
ERROR 1146 (42S02): Table 'customer_01.accounts' doesn't exist

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

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

• Using MaxGUI Tutorial

• Introduction

• Dashboard

  • Annotation

  • Create a new MaxScale object

  • View Replication Status

  • How to kill a session

    • Annotation

• Detail

  • Annotation

• Visualization

  • Configuration

    • Annotation

  • Clusters

    • Annotation

• Settings

  • Annotation

• Logs Archive

  • Annotation

• Workspace

  • Run Queries

    • Query Editor worksheet

      • Create a new connection

      • Schemas objects sidebar

        • Set the current database

        • Preview table data of the top 1000 rows

        • Describe table

        • Alter/Drop/Truncate table

        • Quickly insert an object into the editor

        • Show object creation statement and insights info

      • Editor

      • Re-execute old queries

      • Create query snippet

      • Generate an ERD

  • Data Migration

    • Data Migration worksheet

      • Connections

      • Objects Selection

      • Migration

      • Migration report

  • Create an ERD

    • ERD worksheet

      • Generate an ERD from the existing databases

      • Create a new ERD

      • Entity options

      • Foreign keys quick common options

      • Viewing foreign key constraint SQL

      • Quickly draw a foreign key link

      • Entity editor

      • Export options

      • Applying the script

      • Visual Enhancement options

Introduction

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

Dashboard

Annotation

1. MaxScale object. 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.

5. Dashboard graphs. Refresh interval is 10 seconds.

• 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 byMariaDB-Monitor 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 Dashboard, 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.

• Filter and Listener Detail page, there is a delete icon to delete the object.

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.

4. This button is used to link other MaxScale objects to the relationship.

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.

4. Hide filter visualization button.

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

6. Create a new MaxScale object button.

Clusters

This page shows all monitor clusters usingmariadbmon 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.

4. Anchor link of the server. Opening the detail page of the server in a new tab.

5. Collapse its children nodes.

6. Rejoin node. When the auto_rejoin parameter is disabled, the node can be manually rejoined by dragging it on top of the primary server.

7. Monitor manipulation operations button. Showing a dropdown with the following operations:

• Stop monitor.

• Start monitor.

• Reset Replication.

• Release Locks.

• Master failover. Manually performing a primary failover. This option is visible only when the auto_failover parameter is disabled.

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 Schemas objects sidebar, 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 Monaco editor, 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 insert an object into the editor 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 here and limitations here.

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.

• Set FK Column Mandatory or Set FK Column Optional. Toggling theNOT NULL option of the foreign key column.

• Set Referenced Column Mandatory or Set Referenced Column Optional Toggling the NOT NULL option of the referenced 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

• Zoom control

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