MariaDB MaxScale 22.08 Tutorials
MaxScale 22.08 Automatic Failover with MariaDB Monitor
Automatic Failover With MariaDB Monitor
Automatic Failover With MariaDB Monitor
The MariaDB Monitor is not only capable of monitoring the state of a MariaDB master-slave cluster but is also capable of performing failover and switchover. In addition, in some circumstances it is capable of rejoining a master that has gone down and later reappears.
Note that the failover (and switchover and rejoin) functionality is only supported in conjunction with GTID-based replication and initially only for simple topologies, that is, 1 master and several slaves.
The failover, switchover and rejoin functionality are inherent parts of the MariaDB Monitor, but neither automatic failover nor automatic rejoin are enabled by default.
The following examples have been written with the assumption that there
are four servers - server1, server2, server3 and server4 - of
which server1 is the initial master and the other servers are slaves.
In addition there is a monitor called TheMonitor that monitors those
servers.
Somewhat simplified, the MaxScale configuration file would look like:
[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 master 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
OKThere 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
invoked, mariadbmon indicates the module whose command we want to invoke (that
is the MariaDB Monitor),failover is the command we want to invoke, and TheMonitor is the first and only argument to that command, the name of
the monitor as specified in the configuration file.
The MariaDB Monitor will now autonomously deduce which slave is the most appropriate one to be promoted to master, promote it to master and modify the other slaves accordingly.
If we now check the cluster state we will see that one of the remaining slaves has been made into master.
$ 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 slave promoted to new master.
$ 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 secion 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 master as a slave, 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 master as a slave.
Whether rejoining will succeed depends upon the actual state of the old master. For instance, if the old master was modified and the changes had not been replicated to the new master, before the old master went down, then automatic rejoin will not be possible.
If rejoining can be performed, then the cluster state will end up looking like:
$ 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 master role from one server to another.
If we continue from the cluster state at the end of the previous example
and want to make server1 master again, then we must issue the following
command:
$ maxctrl call command mariadbmon switchover TheMonitor server1 server2
OKThere 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
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
want to make into master, and server2 is the third argument to the command, the name of the currentmaster.
If the command executes successfully, we will end up with the following cluster state:
$ 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 │
└─────────┴─────────────────┴──────┴─────────────┴─────────────────┘CC BY-SA / Gnu FDL
MaxScale 22.08 Avrouter Tutorial
Avrorouter Tutorial
Avrorouter Tutorial
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 master server
The master server where we will be replicating from needs to have binary logging
enabled, binlog_format set to row and binlog_row_image set tofull. These can be enabled by adding the two following lines to the my.cnf
file of the master.
binlog_format=row
binlog_row_image=fullYou 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 master server. The second service will read the binlogs as they are streamed from the master 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=4001The 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 master server
Before starting the MaxScale process, we need to make sure that the binary logs
of the master server contain the DDL statements that define the table
layouts. What this means is that the CREATE TABLE statements need to be in the
binary logs before the conversion process is started.
If the binary logs contain data modification events for tables that aren't created in the binary logs, the Avro schema of the table needs to be manually created. There are multiple ways to do this:
Dump the database to a slave, configure it to replicate from the master and point MaxScale to this slave (this is the recommended method as it requires no extra steps)
Use the 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 master server at
172.18.0.1 listening on port 3000. The first file that the binlogrouter
replicates is binlog.000015. This is the same file that was configured as the
starting file in the avrorouter.
For more details about the SQL commands, refer to 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 maxpwdThis 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.t1The 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.
CC BY-SA / Gnu FDL
MaxScale 22.08 Configuring Servers
Configuring Servers
Configuring Servers
The first step is to define the servers that make up the cluster. These servers will be used by the services and are monitored by the monitor.
[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=3306The 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.
CC BY-SA / Gnu FDL
MaxScale 22.08 Configuring the Galera Monitor
Configuring the Galera Monitor
Configuring the Galera Monitor
This document describes how to configure a Galera cluster monitor.
Configuring the Monitor
Define the monitor that monitors the servers.
[Galera-Monitor]
type=monitor
module=galeramon
servers=dbserv1, dbserv2, dbserv3
user=monitor_user
password=my_password
monitor_interval=2000msThe mandatory parameters are the object type, the monitor module to use, the
list of servers to monitor and the username and password to use when connecting
to the servers. The monitor_interval parameter controls for how long
the monitor waits between each monitoring loop.
This monitor module will assign one node within the Galera Cluster as the
current master and other nodes as slave. Only those nodes that are active
members of the cluster are considered when making the choice of master node. The
master node will be the node with the lowest value of wsrep_local_index.
Monitor User
The monitor user does not require any special grants to monitor a Galera cluster. To create a user for the monitor, execute the following SQL.
CREATE USER 'monitor_user'@'%' IDENTIFIED BY 'my_password';CC BY-SA / Gnu FDL
MaxScale 22.08 Configuring the MariaDB Monitor
Configuring the MariaDB Monitor
Configuring the MariaDB Monitor
This document describes how to configure a MariaDB master-slave cluster monitor to be used with MaxScale.
Configuring the Monitor
Define the monitor that monitors the servers.
[Replication-Monitor]
type=monitor
module=mariadbmon
servers=dbserv1, dbserv2, dbserv3
user=monitor_user
password=my_password
monitor_interval=2000msThe 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'@'%';CC BY-SA / Gnu FDL
MaxScale 22.08 Configuring the Xpand Monitor
Configuring the Xpand Monitor
Configuring the Xpand Monitor
This document describes how to configure the Xpand monitor for use with a Xpand cluster.
Configuring the Monitor
Contrary to the other monitors of MaxScale, the Xpand monitor will autonomously figure out the cluster configuration and for each Xpand node create the corresponding MaxScale server object.
In order to do that, a sufficient number of "bootstrap" server instances must be specified in the MaxScale configuration file for the Xpand monitor to start with. One server instance is in principle sufficient, but if the corresponding node happens to be down when MaxScale starts, the monitor will not be able to function.
[Bootstrap1]
type=server
address=10.2.224.101
port=3306
protocol=mariadbbackend
[Bootstrap2]
type=server
address=10.2.224.102
port=3306
protocol=mariadbbackendThe server configuration is identical with that of any other server, but since these servers are only used for bootstrapping the Xpand monitor it is adviceable to use names that clearly will identify them as such.
The actual Xpand monitor configuration looks as follows:
[Xpand]
type=monitor
module=xpandmon
servers=Bootstrap1, Bootstrap2
user=monitor_user
password=monitor_password
monitor_interval=2s
cluster_monitor_interval=60sThe mandatory parameters are the object type, the monitor module to use, the list of servers to use for bootstrapping and the username and password to use when connecting to the servers.
The monitor_interval parameter specifies how frequently the monitor should
ping the health check port of each server and the cluster_monitor_interval
specifies how frequently the monitor should do a complete cluster check, that
is, access the system tables of the Cluster for checking the Cluster
configuration. The default values are 2000 and 60000, that is, 2 seconds
and 1 minute, respectively.
For each detected Xpand node a corresponding MaxScale server object will be
created, whose name is @@<Monitor-Name>:node-<id>, where _Monitor-Name_ is the name of the monitor, in this exampleXpand` and id is the node id
of the Xpand node. So, with a cluster of three nodes, the created servers
might be named like.
@@Xpand:node-2`
@@Xpand:node-3`
@@Xpand:node-7`Note that as these are created at runtime and may disappear at any moment, depending on changes happening in and made to the Xpand cluster, they should never be referred to directly from service configurations. Instead, services should refer to the monitor, as shown in the following:
[MyService]
type=service
router=readconnroute
user=service_user
password=service_password
cluster=XpandInstead of listing the servers of the service explicitly using the servers
parameter as usually is the case, the service refers to the Xpand monitor
using the cluster parameter. This will cause the service to use the Xpand
nodes that the Xpand monitor discovers at runtime.
For additional details, please consult the monitordocumentation.
CC BY-SA / Gnu FDL
MaxScale 22.08 Connection Routing with MariaDB MaxScale
Connection Routing with MariaDB MaxScale
Connection Routing with MariaDB MaxScale
The goal of this tutorial is to configure a system that has two ports available, one for write connections and another for read connections. The read connections are load- balanced across slave servers.
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 master server, the other load balances between slave 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_pwrouter 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=3307The 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.
CC BY-SA / Gnu FDL
MaxScale 22.08 Encrypting Passwords
Encrypting Passwords
Encrypting Passwords
Note: The password encryption format changed in MaxScale 2.5. All encrypted passwords created with MaxScale 2.4 or older need to be re-encrypted.
There are two options for representing the password, either plain text or
encrypted passwords may be used. In order to use encrypted passwords a set of
keys must be generated that will be used by the encryption and decryption
process. To generate the keys, use the maxkeys command.
maxkeysBy 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
96F99AA1315BDC3604B006F427DD9484The 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=96F99AA1315BDC3604B006F427DD9484If the key file is not in the default location, thedatadir parameter must be set to the directory that contains it.
CC BY-SA / Gnu FDL
MaxScale 22.08 Filters
Filters
Filters
What Are Filters?
The filter mechanism in MariaDB MaxScale is a means by which processing can be inserted into the flow of requests and responses between the client connection to MariaDB MaxScale and the MariaDB MaxScale connection to the backend database servers. The path from the client side of MariaDB MaxScale out to the actual database servers can be considered a pipeline, filters can then be placed in that pipeline to monitor, modify, copy or block the content that flows through that pipeline.
Types Of Filter
Filters can be divided into a number of categories
Logging filters
Logging filters do not in any way alter the statement or results of the statements that are passed through MariaDB MaxScale. They merely log some information about some or all of the statements and/or result sets.
Two examples of logging filters are contained within the MariaDB MaxScale, a filter that will log all statements and another that will log only a number of statements, based on the duration of the execution of the query.
Statement rewriting filters
Statement rewriting filters modify the statements that are passed through the filter. This allows a filter to be used as a mechanism to alter the statements that are seen by the database, an example of the use of this might be to allow an application to remain unchanged when the underlying database changes or to compensate for the migration from one database schema to another.
Result set manipulation filters
A result set manipulation filter is very similar to a statement rewriting but applies to the result set returned rather than the statement executed. An example of this may be obfuscating the values in a column.
Routing hint filters
Routing hint filters are filters that embed hints in the request that can be used by the router onto which the query is passed. These hints include suggested destinations as well as metric that may be used by the routing process.
Firewall filters
A firewall filter is a mechanism that allows queries to be blocked within MariaDB MaxScale before they are sent on to the database server for execution. They allow constructs or individual queries to be intercepted and give a level of access control that is more flexible than the traditional database grant mechanism.
Pipeline control filters
A pipeline filter is one that has an affect on how the requests are routed within the internal MariaDB MaxScale components. The most obvious version of this is the ability to add a "tee" connector in the pipeline, duplicating the request and sending it to a second MariaDB MaxScale service for processing.
Filter Definition
Filters are defined in the configuration file, typically maxscale.cnf, using a section for each filter instance. The content of the filter sections in the configuration file various from filter to filter, however there are always to entries present for every filter, the type and module.
[MyFilter]
type=filter
module=xxxfilterThe 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 | top10The 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/top30In 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=6628C50E07CCE1F0392EDEEB9D1203F3The 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=6628C50E07CCE1F0392EDEEB9D1203F3Next 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=CassandraThe 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=HighScoresCC BY-SA / Gnu FDL
MaxScale 22.08 MariaDB MaxScale Administration Tutorial
MariaDB MaxScale Administration Tutorial
MariaDB MaxScale Administration Tutorial
The purpose of this tutorial is to introduce the MariaDB MaxScale Administrator to a few of the common administration tasks. This is intended to be an introduction for administrators who are new to MariaDB MaxScale and not a reference to all the tasks that may be performed.
Starting and Stopping MariaDB MaxScale
MaxScale uses systemd for managing the process. This means that normalsystemctl commands can be used to start and stop MaxScale. To start MaxScale,
use systemctl start maxscale. To stop it, use systemctl stop maxscale.
The systemd service file for MaxScale is located in/lib/systemd/system/maxscale.service.
Additional Options for MaxScale
Additional command line options and other systemd configuration options
can be given to MariaDB MaxScale by creating a drop-in file for the
service unit file. You can do this with the systemctl edit maxscale.service
command. For more information about systemd drop-in
files, refer 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 the Log File
MariaDB MaxScale logs messages of different priority into a single log file. With the exception if error messages that are always logged, whether messages of a particular priority should be logged or not can be enabled via the maxctrl interface or in the configuration file. By default, MaxScale keeps on writing to the same log file. To prevent the file from growing indefinitely, the administrator must take action.
The name of the log file is maxscale.log. When the log is rotated, MaxScale closes the current log file and opens a new one using the same name.
Log file rotation is achieved by use of the rotate logs command
in maxctrl.
maxctrl rotate logsAs 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 maintenanceTo 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 maintenanceClearing 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-serviceServices 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-serviceStarting 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-monitorStopping 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-monitorStarting 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 5sSome 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 3306Modify a Server
maxctrl alter server db-server-1 port 3307Destroy a Server
maxctrl destroy server db-server-1A 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 drainWhen 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 drainServers 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-passwordModify a Monitor
maxctrl alter monitor db-monitor monitor_interval 1000Add Server to a Monitor
maxctrl link monitor db-monitor db-server-1Remove a Server from a Monitor
maxctrl unlink monitor db-monitor db-server-1Destroy a Monitor
maxctrl destroy monitor db-monitorA 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-passwordModify a Service
maxctrl alter service db-service user new-db-userAdd Servers to a Service
maxctrl link service db-service db-server1Any 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-server1Similarly 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-qlafilterThe 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-serviceThe 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=InnoDBDestroy a Filter
maxctrl destroy filter my-regexfilterA 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 4006Destroy a Listener
maxctrl destroy listener db-listenerDestroying 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-passwordBy 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=adminAdministrative 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-passwordRemove a User
maxctrl destroy user basic-userCC BY-SA / Gnu FDL
MaxScale 22.08 MaxScale and Xpand Tutorial
MaxScale and Xpand Tutorial
MaxScale and Xpand Tutorial
Since version 2.4, MaxScale has built-in support for Xpand. This tutorial explains how to setup MaxScale in front of a Xpand cluster.
There is no Xpand specific router, but both thereadconnroute and the readwritesplit routers can be used.
Xpand and Readconnroute
With readconnroute you get simple connection based routing, where each new connection is created (by default) to the Xpand node with the least amount of existing connections. That is, with readconnroute the behaviour will be very similar to the behaviour whenHAProxy is used as the Xpand load balancer.
Bootstrap servers
The Xpand monitor is capable of autonomously figuring out the cluster configuration, but in order to get going there must be at least oneserver-section referring to a node in the Xpand cluster.
[Bootstrap-1]
type=server
address=IP-OF-NODE
port=3306
protocol=MySQLBackendThat server defintion will be used by the monitor in order to connect to the Xpand cluster. There can be more than one such "bootstrap" definition to cater for the case that the node used as a bootstrap server is down when MaxScale starts.
NOTE These bootstrap servers should only be referred to from the Xpand monitor configuration, but never from a service.
Monitor
In the Xpand monitor section, the bootstrap servers are referred to in the same way as "ordinary" servers are referred to in other monitors.
[Xpand]
type=monitor
module=xpandmon
servers=Bootstrap-1
user=USER
password=PASSWORDThe user defined by the user parameter needs the following grants:
CREATE USER 'maxscale-monitor'@'maxscalehost' IDENTIFIED BY 'maxscale-monitor-password';
GRANT SELECT ON system.membership TO 'maxscale-monitor'@'maxscalehost';
GRANT SELECT ON system.nodeinfo TO 'maxscale-monitor'@'maxscalehost';
GRANT SELECT ON system.softfailed_nodes TO 'maxscale-monitor'@'maxscalehost';In case the same user is used both for the monitor and the service (see below), then the user must be given the grants required by the service as well.
The bootstrap servers are only used for connecting to the Xpand cluster; thereafter the Xpand monitor will dynamically find out the cluster configuration.
The discovered cluster configuration will be stored (the ips and ports of the Xpand nodes) and upon subsequent restarts the Xpand monitor will use that information if the bootstrap servers happen to be unavailable.
With the configuration above maxctrl list servers might output
the following:
┌───────────────────┬──────────────┬──────┬─────────────┬─────────────────┬──────┐
│ Server │ Address │ Port │ Connections │ State │ GTID │
├───────────────────┼──────────────┼──────┼─────────────┼─────────────────┼──────┤
│ @@Xpand:node-7 │ 10.2.224.102 │ 3306 │ 0 │ Master, Running │ │
├───────────────────┼──────────────┼──────┼─────────────┼─────────────────┼──────┤
│ @@Xpand:node-8 │ 10.2.224.103 │ 3306 │ 0 │ Master, Running │ │
├───────────────────┼──────────────┼──────┼─────────────┼─────────────────┼──────┤
│ @@Xpand:node-6 │ 10.2.224.101 │ 3306 │ 0 │ Master, Running │ │
├───────────────────┼──────────────┼──────┼─────────────┼─────────────────┼──────┤
│ Bootstrap-1 │ 10.2.224.101 │ 3306 │ 0 │ Master, Running │ │
└───────────────────┴──────────────┴──────┴─────────────┴─────────────────┴──────┘All servers whose name start with @@ have been detected dynamically.
Note that the address 10.2.224.101 appears twice; once forBootstrap-1 and another time for @@Xpand:node-6. The Xpand
monitor will create a dynamic server instance for all nodes in the
Xpand cluster; also for the ones used in bootstrap server sections.
Service
The service is specified as follows:
[Xpand-Service]
type=service
router=readconnroute
user=USER
password=PASSWORD
cluster=XpandThe user defined by the user parameter needs the following grants:
CREATE USER 'maxscale-service'@'maxscalehost' IDENTIFIED BY 'maxscale-service-password';
GRANT SELECT ON system.users TO 'maxscale-service'@'maxscalehost';
GRANT SELECT ON system.user_acl TO 'maxscale-service'@'maxscalehost';In case the same user is used both for the monitor (see above) and the service, then the user must be given the grants required by the monitor as well.
Note that the service does not list any specific servers, but
instead refers, using the argument cluster, to the Xpand monitor.
In practice this means that the service will use the servers of the
monitor named Xpand and in the case of a Xpand monitor those
servers will be the ones that the monitor has detected
dynamically. That is, when setup like this, the service will
automatically adjust to any changes taking place in the Xpand
cluster.
NOTE There is no need to specify any router_options, but the
default router_options=running provides the desired behaviour.
In particular do not specify router_options=master as that will
cause only a single node to be used.
Listener
To complete the configuration, a listener must be specified.
[Xpand-Service-Listener]
type=listener
service=Xpand-Service
protocol=MariaDBClient
port=4008Xpand and Readwritesplit
The primary purpose of the router readwritesplit is to split statements between one master and multiple slaves. In the case of Xpand, all servers will be masters, but readwritesplit may still be the right choise.
Namely, as readwritesplit is transaction aware and capable of replaying transactions, it can be used for hiding certain events taking place in Xpand from the clients that use it.
For instance, whenever a node is removed from or added to a Xpand cluster there will be a group change, which is visible to a client as a transaction rollback. However, if readwritesplit is used and transaction replay is enabled, then MaxScale may be able to hide the group change so that the client only detects a slight delay.
Apart from the service section, the configuration when usingreadwritesplit is identical to the readconnroute configuration described above.
Service
The service is specified as follows:
[Xpand-Service]
type=service
router=readwritesplit
user=maxscale-service
password=maxscale-service-password
cluster=Xpand
transaction_replay=true
slave_selection_criteria=LEAST_GLOBAL_CONNECTIONSWith this configuration, subject to the boundary conditions of transaction replaying, a client will neither notice group change events nor the disappearance of the very node the client is connected to. In that latter case, MaxScale will simply connect to another node and replay the current transaction (if one is active). For detailed information about the transaction replay functionality, please refer to the readwritesplitdocumentation.
NOTE It is vital to haveslave_selection_criteria=LEAST_GLOBAL_CONNECTIONS, as otherwise
connections will not be distributed evenly across all Xpand
nodes.
As a rule of thumb, use readwritesplit if it is important that changes taking place in the cluster configuration are hidden from the applications, otherwise use readconnroute.
CC BY-SA / Gnu FDL
MaxScale 22.08 Read-Write Splitting with MariaDB MaxScale
Read-Write Splitting with MariaDB MaxScale
Read-Write Splitting with MariaDB MaxScale
The goal of this tutorial is to configure a system that appears to the client as a single database. MariaDB MaxScale will split the statements such that write statements are sent to the master server and read statements are balanced across the slave servers.
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_pwrouter 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=3306The 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.
CC BY-SA / Gnu FDL
MaxScale 22.08 Rest API Tutorial
REST API Tutorial
REST API Tutorial
This tutorial is a quick overview of what the MaxScale REST API offers, how it
can be used to inspect the state of MaxScale and how to use it to modify the
runtime configuration of MaxScale. The tutorial uses the curl command line
client to demonstrate how the API is used.
Configuration and Hardening
The MaxScale REST API listens on port 8989 on the local host. The admin_port
and admin_host parameters control which port and address the REST API listens
on. Note that for security reasons the API only listens for local connections
with the default configuration. It is critical that the default credentials are
changed and TLS/SSL encryption is configured before exposing the REST API to a
network.
The default user for the REST API is admin and the password is mariadb. The
easiest way to secure the REST API is to use the maxctrl command line client
to create a new admin user and delete the default one. To do this, run the
following commands:
maxctrl create user my_user my_password --type=admin
maxctrl destroy user adminThis 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.pemUse 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 maxscaleIf 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 GMTTo 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
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/serversThe -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/server1Modifying 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.txtAfter 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/server1To verify that the data was updated correctly, request the updated object.
curl 127.0.0.1:8989/v1/servers/server1Object 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/server1In 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
CC BY-SA / Gnu FDL
MaxScale 22.08 Setting Up MariaDB MaxScale
Setting up MariaDB MaxScale
Setting up MariaDB MaxScale
This document is designed as a quick introduction to setting up MariaDB MaxScale.
The installation and configuration of the MariaDB Server is not covered in this document. See the following MariaDB documentation articles for more information on setting up a master-slave-cluster or a Galera-cluster: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 master 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=autoConfiguring 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 master-slave 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 maxscaleFor older SysV systems, use the service command.
sudo service maxscale startIf 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.
CC BY-SA / Gnu FDL
MaxScale 22.08 Simple Sharding with Two Servers
Simple Sharding with Two Servers
Simple Sharding with Two Servers
Sharding is the method of splitting a single database server into separate parts. This tutorial describes a very simple way of sharding. Each schema is located on a different database server and MariaDB MaxScale's schemarouter module is used to combine them into a single database server.
MariaDB MaxScale will appear to the client as a database server with the combination of all the schemas in all the configured servers.
Environment & Solution Space
This document is designed as a simple tutorial on schema-based sharding using MariaDB MaxScale in an environment in which you have two servers. The object of this tutorial is to have a system that, to the client side, acts like a single MariaDB database but actually is sharded between the two servers.
The database users should be configured according to the configuration guide. The MaxScale Tutorial contains easy to follow instructions on how to set up MaxScale.
This tutorial will assume the user is using of the binary distributions available and has installed this in the default location. The process of configuring MariaDB MaxScale will be covered within this document. The installation and configuration of the MariaDB servers will not be covered in-depth.
Preparing MaxScale
Follow the MaxScale Tutorial to install and prepare the required database users for MaxScale. You don't need to create the configuration file for MaxScale as it will be covered in the next section.
Creating Your MariaDB MaxScale Configuration
The first step in the creation of your maxscale.cnf file is to define the global maxscale section. This section configures the number of threads MariaDB MaxScale uses. A good rule of thumb is to use at most as may threads as you have CPUs. MariaDB MaxScale uses few threads for internal operations so one or two threads less than the maximum should be enough.
[maxscale]
threads=8After this we configure two servers we will use to shard our database. The accounts_east server will hold one schema and the accounts_west will hold another schema. We will use these two servers to create our sharded database.
[accounts_east]
type=server
address=192.168.56.102
port=3306
[accounts_west]
type=server
address=192.168.122.85
port=3306The next step is to configure the service which the users connect to. This section defines which router to use, which servers to connect to and the credentials to use. The router we use in this tutorial is the schemarouter.
[Sharded-Service]
type=service
router=schemarouter
servers=accounts_west,accounts_east
user=sharduser
password=YqztlYGDvZ8tVMe3GUm9XCwQiAfter this we configure a listener for the service. The listener is the actual port the user connects to. We will use the port 4000.
[Sharded-Service-Listener]
type=listener
service=Sharded-Service
port=4000The final step is to configure a monitor which will monitor the state of the servers. The monitor will notify MariaDB MaxScale if the servers are down. We add the two servers to the monitor, define the credentials to use and we set the monitoring cycle interval.
[MySQL-Monitor]
type=monitor
module=mariadbmon
servers=accounts_west,accounts_east
user=monitoruser
password=7SP1Zcsow8TG+9EkEBVEbaKa
monitor_interval=1sAfter this we have a fully working configuration and we can move on to starting MariaDB MaxScale.
Starting MariaDB MaxScale
Upon completion of the configuration process MariaDB MaxScale is ready to be started . This may either be done manually by running the maxscale command or via the service interface. The service scripts are located in the /etc/init.d/ folder and are accessible through both the service and systemctl commands.
MariaDB MaxScale is now ready to start accepting client connections and routing them. Queries are routed to the right servers based on the database they target and switching between the shards is seamless since MariaDB MaxScale keeps the session state intact between servers.
If MariaDB MaxScale fails to start, check the error log in /var/log/maxscale to see what sort of errors were detected.
Note: As the sharding solution in MaxScale is relatively simple, cross-database queries between two or more shards are not supported.
CC BY-SA / Gnu FDL
MaxScale 22.08 Using MaxGUI Tutorial
Using MaxGUI Tutorial
Using MaxGUI Tutorial
Introduction
This tutorial is an overview of what the MaxGUI offers as an alternative solution to MaxCtrl.
Dashboard
Annotation
MaxScale object. i.e. Service, Server, Monitor, Filter, and Listener (Clicking on it will navigate to its detail page)
Create a new MaxScale object.
Dashboard Tab Navigation.
Search Input. This can be used as a quick way to search for a keyword in tables.
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.
Logout of the app.
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.
Detail
This page shows information on each MaxScale object and allow to edit its parameter, relationships and perform other manipulation operations. Most of the control buttons will be shown on the mouseover event. Below is a screenshot of a Monitor Detail page, other Detail pages also have a similar layout structure so this is used for illustration purpose.
Annotation
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.
Switchover button. This button is shown on the mouseover event allowing to swap the running primary server with a designated secondary server.
Edit parameters button. This button is shown on the mouseover event allowing to edit the MaxScale object's parameter. Clicking on it will enable editable mode on the table. After finishing editing the parameters, simply click the Done Editing button.
A Detail page has tables showing "Relationship" between other MaxScale object. This "unlink" icon is shown on the mouseover event allowing to remove the relationship between two objects.
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
A MaxScale object (a node graph). The position of the node in the graph can be changed by dragging and dropping it.
Anchor link. The detail page of each MaxScale object can be accessed by clicking on the name of the node.
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.
Hide filter visualization button.
Refresh rate dropdown. The frequency with which the data is refreshed.
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
Drag a secondary server on top of a primary server to promote the secondary server as the new primary server.
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.
Quick access to query editor button. Opening the
Query Editorpage 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.Carousel navigation button. Viewing more information about the server in the next slide.
Collapse the carousel.
Anchor link of the server. Opening the detail page of the server in a new tab.
Collapse its children nodes.
Rejoin node. When the
auto_rejoinparameter is disabled, the node can be manually rejoined by dragging it on top of the primary server.Monitor manipulation operations button. Showing a dropdown with the following operations:
Stop monitor.
Start monitor.
Reset Replication.
Release Locks.
Master failover. Manually performing a master failover. This option is visible only when the
auto_failoverparameter is disabled.
Refresh rate dropdown. The frequency with which the data is refreshed.
Create a new MaxScale object button.
Settings
This page shows and allows editing of MaxScale parameters.
Annotation
Edit parameters button. This button is shown on the mouseover event allowing to edit the MaxScale parameter. Clicking on it will enable editable mode on the table..
Editable parameters are visible as it's illustrated in the screenshot.
After finishing editing the parameters, simply click the Done Editing button.
Logs Archive
This page show real-time MaxScale logs with filter options.
Annotation
Filter by dropdown. All logs types are selected to be shown by default
Uncheck the box to disable showing a particular log type.
Query Editor
A SQL editor tool to run queries and perform other SQL operations.
Annotation
Worksheet tab navigation. Each worksheet is bound to a connection, so sessions querying within a worksheet is not yet supported.
Add a new worksheet button.
Connection manager dropdown. With this dropdown, you can create a new connection or change the connection for the current active worksheet. A new connection can be created by selecting the last option in the dropdown labeled as
New connection. Once a connection is created, it automatically binds the connection to the current active worksheet.Active database dropdown. Right-click on the database name and click
Use databaseoption to quickly change the default (current) databaseSchemas sidebar. Showing available schemas on the current connection. As shown in the figure above, these items can be explored to show tables, stored procedures, columns, and triggers within the schema.
Schemas sidebar object.
Each object has its own context menu providing different options. e.g. For the table object as shown in the figure above, it has options to
Preview Data (top 1000)andView Details. The query result for these options is shown in theData Previewresult tab which is annotated as number 12. The context menu can be shown by right-clicking on the object or clicking on the three dots icon placed on the right side of the object.Quick access to the
Preview Data (top 1000)context menu option. For a table object, its preview data can also be seen by clicking on its name.Quick overview tooltip. Each object has its own tooltip providing an overview of the object.
Refresh schema objects button. After deleting or creating schema object, the
Schemas sidebarneeds to be manually refreshed.Collapse the
Schemas sidebarbutton.SQL editor. The editor is powered byMonaco editor which means its functionalities are similar to VS code. Available commands can be seen by pressing F1 while the cursor is active on the editor. This is an intention to prevent conflict between the browser's shortcut keys and the SQL editor's. This also means the editor shortcut key commands are valid only when the cursor is active on the
SQL editorwith an exception for theRun all statements,Run selected statementsandSave statements to favoritecommands.Query Results. Showing the query results of queries written in the SQL editor.
Data Preview. Showing the query results of
Preview Data (top 1000)andView Detailsoptions of the schema sidebar context menu.History/Snippets. Showing query history and snippet queries.
Result tab navigation. Navigating between SQL queries results.
Filter query history logs. The query history is divided into two types of logs The
User query logscontains logs for queries written in theSQL editorwhile theAction logscontains logs for auto-generated SQL, such asPreview Data (top 1000),View Details,Drop TableandTruncate Table.Export query result button. Exporting as
json,csvwith a custom delimiter.Filter query result columns dropdown. Selecting columns to be visible.
Vertical query result button. Switching to vertical mode.
Run button. Running the queries written in the
SQL editor. Alternatively, pressingCtrl/CMD+Shift+EntertoRun all statementsorCtrl/CMD+EntertoRun selected statements.Visualize query result button. Visualizing a query result into a line, scatter, vertical bar, and horizontal bar graph.
Create a query snippet from the queries written in the
SQL editor. Alternatively, pressCtrl/CMD+D.Open Script button.
Save Script button. This writes content into the opened file. This only works on Chrome or any browsers based on Chromium served over a secure connection (https)
Save Script As button. Save the content as a new file.
sql_select_limit input. Changing the maximum number of rows to return from SELECT statements.
Add a new session button.
Query Editor settings button. Open
Query configurationdialog to change the value ofMax rows(sql_select_limit),Query history retention period (in days),Show confirmation before executing the statementsandShow system schemas.Maximize Query Editor window.
How to kill a session
A session can be killed easily on the "Current Sessions" table which can be found on the Dashboard, Server detail, and Service detail page.
Annotation
Kill session button. This button is shown on the mouseover event.
Confirm killing the session dialog.
CC BY-SA / Gnu FDL