Binlog Filter

Binlog Filter

This filter was introduced in MariaDB MaxScale 2.3.0.

Overview

The binlogfilter can be combined with a binlogrouter service to selectively replicate the binary log events to slave servers.

The filter uses two settings, match and exclude, to determine which events are replicated. If a binlog event does not match or is excluded, the event is replaced with an empty data event. The empty event is always 35 bytes which translates to a space reduction in most cases.

When statement-based replication is used, any query events that are filtered out are replaced with a SQL comment. This causes the query event to do nothing and thus the event will not modify the contents of the database. The GTID position of the replicating database will still advance which means that downstream servers replicating from it keep functioning correctly.

The filter works with both row based and statement based replication but we recommend using row based replication with the binlogfilter. This guarantees that there are no ambiguities in the event filtering.

Configuration

match

• Type:

• Mandatory: No

• Dynamic: Yes

• Default: None

Include queries that match the regex. See next entry, exclude, for more information.

exclude

• Type:

• Mandatory: No

• Dynamic: Yes

• Default: None

Exclude queries that match the regex.

If neither match nor exclude are defined, the filter does nothing and all events are replicated. This filter does not accept regular expression options as a separate setting, such settings must be defined in the patterns themselves. See the for more information.

The two settings are matched against the database and table name concatenated with a period. For example, the string the patterns are matched against for the database test and table t1 is test.t1.

For statement based replication, the pattern is matched against all the tables in the statements. If any of the tables matches the match pattern, the event is replicated. If any of the tables matches the exclude pattern, the event is not replicated.

rewrite_src

• Type:

• Mandatory: No

• Dynamic: Yes

• Default: None

See the next entry, rewrite_dest, for more information.

rewrite_dest

• Type:

• Mandatory: No

• Dynamic: Yes

• Default: None

rewrite_src and rewrite_dest control the statement rewriting of the binlogfilter. The rewrite_src setting is a PCRE2 regular expression that is matched against the default database and the SQL of statement based replication events (query events). rewrite_dest is the replacement string which supports the normal PCRE2 backreferences (e.g the first capture group is $1, the second is $2, etc.).

Both rewrite_src and rewrite_dest must be defined to enable statement rewriting.

When statement rewriting is enabled must be used. The filter will disallow replication for all slaves that attempt to replicate with traditional file-and-position based replication.

The replacement is done both on the default database as well as the SQL statement in the query event. This means that great care must be taken when defining the rewriting rules. To prevent accidental modification of the SQL into a form that is no longer valid, use database and table names that never occur in the inserted data and is never used as a constant value.

Example Configuration

With the following configuration, only events belonging to database customers are replicated. In addition to this, events for the table orders are excluded and thus are not replicated.

For more information about the binlogrouter and how to use it, refer to the .

CC BY-SA / Gnu FDL

Comment Filter

Comment Filter

Overview

With the comment filter it is possible to define comments that are injected before the actual statements. These comments appear as sql comments when they are received by the server.

Filter Parameters

The Comment filter requires one mandatory parameter to be defined.

inject

• Type: string

• Mandatory: Yes

• Dynamic: Yes

A parameter that contains the comment injected before the statements. There is also defined variable $IP that can be used to comment the IP address of the client in the injected comment. Variables must be written in all caps.

Examples

Example 1 - Inject IP address of the connected client into statements

as comment.

The following configuration adds the IP address of the client to the comment.

In this example when MaxScale receives statement like:

It would look like

when received by server.

CC BY-SA / Gnu FDL

Upgrading MariaDB MaxScale from 6 to 22.08

Upgrading MariaDB MaxScale from 6 to 22.08

This document describes possible issues when upgrading MariaDB MaxScale from version 6 to 22.08.

MaxScale 22.08 Hint Syntax

Hint Syntax

Refer to the Hintfilter documentation for the MaxScale hint syntax.

CC BY-SA / Gnu FDL

MariaDB Protocol Module

MariaDB Protocol Module

The mariadbprotocol module implements the MariaDB client-server protocol.

The legacy protocol names mysqlclient, mariadb and mariadbclient are all aliases to mariadbprotocol.

Configuration

Protocol level parameters are defined in the listeners. They must be defined using the scoped parameter syntax where the protocol name is used as the prefix.

For the MariaDB protocol module, the prefix is always mariadbprotocol.

allow_replication

• Type:

• Mandatory: No

• Dynamic: Yes

• Default: true

Whether the use of the replication protocol is allowed through this listener. If disabled with mariadbprotocol.allow_replication=false, all attempts to start replication will be rejected with a ER_FEATURE_DISABLED error (error number 1289).

CC BY-SA / Gnu FDL

Aurora Monitor

Aurora Monitor

Note The Aurora Monitor is deprecated in 22.08.2 and will be removed in 23.02.0.

This module monitors the status of Aurora cluster replicas. These replicas do not use the standard MySQL protocol replication but rely on a mechanism provided by AWS to replicate changes.

How Aurora Is Monitored

Each node in an Aurora cluster has the variable @@aurora_server_id which is the unique identifier for that node. An Aurora replica stores information relevant to replication in the information_schema.replica_host_status table. The table contains information about the status of all replicas in the cluster. The server_id column in this table holds the values of@@aurora_server_id variables from all nodes. The session_id column contains an unique string for all read-only replicas. For the master node, this value will be MASTER_SESSION_ID. By executing the following query, we are able to retrieve the @@aurora_server_id of the master node along with the@@aurora_server_id of the current node.

The node which returns a row with two identical fields is the master. All other nodes are read-only replicas and will be labeled as slave servers.

In addition to replica status information, theinformation_schema.replica_host_status table contains information about replication lag between the master and the read-only nodes. This value is stored in the replica_lag_in_milliseconds column. This can be used to detect read replicas that are lagging behind the master node. This information can then be used by the routing modules to route reads to up-to-date nodes.

Configuring the Aurora Monitor

The Aurora monitor should connect directly to the unique endpoints of the Aurora replicas. The cluster end point should not be included in the set of monitored servers. Read the for more information about how to retrieve the unique endpoints of your cluster.

The Aurora monitor requires no parameters apart from the standard monitor parameters. It supports the monitor script functionality described in documentation.

Here is an example Aurora monitor configuration.

The servers cluster-1, cluster-2 and cluster-3 are the unique Aurora endpoints configured as MaxScale servers. The monitor will use the_aurora_:borealis credentials to connect to each of the endpoint. The status of the nodes is inspected every 2500 milliseconds.

CC BY-SA / Gnu FDL

About MariaDB MaxScale

About MariaDB MaxScale

MariaDB MaxScale is a database proxy that forwards database statements to one or more database servers.

The forwarding is performed using rules based on the semantic understanding of the database statements and on the roles of the servers within the backend cluster of databases.

MariaDB MaxScale is designed to provide, transparently to applications, load balancing and high availability functionality. MariaDB MaxScale has a scalable and flexible architecture, with plugin components to support different protocols and routing approaches.

MariaDB MaxScale makes extensive use of the asynchronous I/O capabilities of the Linux operating system, combined with a fixed number of worker threads. epoll is used to provide the event driven framework for the input and output via sockets.

Many of the services provided by MariaDB MaxScale are implemented as external shared object modules loaded at runtime. These modules support a fixed interface, communicating the entry points via a structure consisting of a set of function pointers. This structure is called the "module object". Additional modules can be created to work with MariaDB MaxScale.

Commonly used module types are protocol, router and filter. Protocol modules implement the communication between clients and MariaDB MaxScale, and between MariaDB MaxScale and backend servers. Routers inspect the queries from clients and decide the target backend. The decisions are usually based on routing rules and backend server status. Filters work on data as it passes through MariaDB MaxScale. Filter are often used for logging queries or modifying server responses.

A Google Group exists for MariaDB MaxScale. The Group is used to discuss ideas, issues and communicate with the MariaDB MaxScale community. Send email to or use the interface.

Bugs can be reported in the MariaDB Jira

Installing MariaDB MaxScale

Information about installing MariaDB MaxScale, either from a repository or by building from source code, is included in the .

The same guide also provides basic information on running MariaDB MaxScale. More detailed information about configuring MariaDB MaxScale can be found in the .

CC BY-SA / Gnu FDL

Configuring the MariaDB Monitor

Configuring the MariaDB Monitor

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

Configuring the Monitor

Define the monitor that monitors the servers.

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

Monitor User

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

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

CC BY-SA / Gnu FDL

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=3306

The address and port parameters tell where the server is located.

Enabling TLS

To enable encryption for the MaxScale-to-MariaDB communication, add ssl=true to the server section. To enable server certificate verification, addssl_verify_peer_certificate=true.

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

For more information about TLS, refer to the .

CC BY-SA / Gnu FDL

Change Data Capture (CDC) users

Change Data Capture (CDC) users

Change Data Capture (CDC) is a new MaxScale protocol that allows compatible clients to authenticate and register for Change Data Capture events. The new protocol must be use in conjunction with AVRO router which currently converts MariaDB binlog events into AVRO records. Clients connect to CDC listener and authenticate using credentials provided in a format described in the .

Note: If no users are found in that file or if it doesn't exist, the only available user will be the service user:

Creating new CDC users

Starting with MaxScale 2.1, users can also be created through maxctrl:

The should be the service name where the user is created. Older versions of MaxScale should use the cdc_users.py script.

The output of this command should be appended to the cdcusers file at/var/lib/maxscale/<service name>/.

Users can be deleted by removing the related rows in 'cdcusers' file. For more details on the format of the cdcusers file, read the .

CC BY-SA / Gnu FDL

Configuring the Galera Monitor

Configuring the Galera Monitor

This document describes how to configure a Galera cluster monitor.

Configuring the Monitor

Define the monitor that monitors the servers.

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

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

Monitor User

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

CC BY-SA / Gnu FDL

Encrypting Passwords

Encrypting Passwords

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

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

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

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

The username and password, either encrypted or plain text, are stored in the service section using the user and password parameters.

If a custom location was used for the key file, give it as the first argument tomaxpasswd and pass the password to be encrypted as the second argument. For more information, see maxkeys --help.

Here is an example configuration that uses an encrypted password.

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

CC BY-SA / Gnu FDL

Maxscale CDC Connector

Maxscale CDC Connector

The C++ connector for the .

Module commands

Module commands

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

Insert Stream Filter

Insert Stream Filter

This filter was introduced in MariaDB MaxScale 2.1.

Note

Cat

Cat

The cat router is a special router that concatenates result sets.

Configuring the Xpand Monitor

Configuring the Xpand Monitor

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

GSSAPI Client Authenticator

GSSAPI Client Authenticator

GSSAPI is an authentication protocol that is commonly implemented with Kerberos on Unix or Active Directory on Windows. This document describes GSSAPI authentication in MaxScale. The authentication module name in MaxScale is_GSSAPIAuth_.

MySQL Authenticator

MariaDB/MySQL Authenticator

The MariaDBAuth-module implements the client and backend authentication for the server plugin mysql_native_password. This is the default authentication plugin used by both MariaDB and MySQL.

Upgrading MariaDB MaxScale from 2.3 to 2.4

Upgrading MariaDB MaxScale from 2.3 to 2.4

This document describes possible issues when upgrading MariaDB MaxScale from version 2.3 to 2.4.

HintRouter

HintRouter

HintRouter was introduced in 2.2 and is still beta.

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.

Upgrading MariaDB MaxScale from 2.4 to 2.5

Upgrading MariaDB MaxScale from 2.4 to 2.5

This document describes possible issues when upgrading MariaDB MaxScale from version 2.4 to 2.5.

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.

Upgrading MariaDB MaxScale from 2.5 to 6

Upgrading MariaDB MaxScale from 2.5 to 6

This document describes possible issues when upgrading MariaDB MaxScale from version 2.5 to 6.

Note that the versioning scheme has changed and that version 6 immediately follows version 2.5. Effectively, the non-changing 2.-prefix has been dropped and henceforth at a major release, the major, instead of the minor version number, will be bumped. This change also affects how maintenance releases are versioned. For instance, 2.5.1, the first GA version of MaxScale 2.5, was followed by the maintenace release 2.5.2. 6.1, the first GA version of MaxScale 6, will be followed by the maintenance release 6.2.

For more information about MaxScale 6, refer to the .

Before starting the upgrade, any existing configuration files should be backed up.

Duration Type Parameters

Using duration type parameters without an explicit suffix has been deprecated in MaxScale 2.4. In MaxScale 6 they are no longer allowed when used with the REST API or MaxCtrl. This means that any create or alter commands in MaxCtrl that use a duration type parameter must explicitly specify the suffix of the unit.

For example, the following command:

should be replaced with:

Duration type parameters can still be defined in the configuration file without an explicit suffix but this behavior is deprecated. The recommended approach is to add explicit suffixes to all duration type parameters when upgrading to MaxScale 6.

Changed Parameters

threads

The default value of threads was changed to auto.

Removed Parameters

Core Parameters

The following deprecated core parameters have been removed:

• thread_stack_size

Schemarouter

The deprecated aliases for the schemarouter parameters ignore_databases andignore_databases_regex have been removed. They can be replaced withignore_tables and ignore_tables_regex.

In addition, the preferred_server parameter that was deprecated in 2.5 has also been removed.

Session Command History

The prune_sescmd_history, max_sescmd_history and disable_sescmd_history have been made into generic service parameters that are shared between all routers that support it.

The default value of prune_sescmd_history was changed from false totrue. This was done as most MaxScale installations either benefit from it being enabled or are not affected by it.

CC BY-SA / Gnu FDL

Change Data Capture (CDC) Protocol

Change Data Capture (CDC) Protocol

CDC is a new protocol that allows compatible clients to authenticate and register for Change Data Capture events. The new protocol must be use in conjunction with AVRO router which currently converts MariaDB binlog events into AVRO records. Change Data Capture protocol is used by clients in order to interact with stored AVRO file and also allows registered clients to be notified with the new events coming from MariaDB 10.0/10.1 database.

Creating Users

The users and their hashed passwords are stored in /var/cache/maxscale/<service name>/cdcusers where <service name> is the name of the service.

For example, the following service entry will look into /var/cache/maxscale/CDC-Service/ for a file called cdcusers. If that file is found, the users in that file will be used for authentication.

If the cdcusers file cannot be found, the service user (maxuser:maxpwd in the example) can be used to connect through the CDC protocol.

For more details, refer to the .

Protocol Phases

Connection and Authentication

• Client connects to MaxScale CDC protocol listener.

• Send the authentication message which includes the user and the SHA1 of the password

In the future, optional flags could be implemented.

Registration

• Sending UUID

• Specify the output format (AVRO or JSON) for data retrieval.

Data Request

• Send CDC commands to retrieve router statistics or to query for data events

Protocol Details

Authentication

The authentication starts when the client sends the hexadecimal representation of the username concatenated with a colon (:) and the SHA1 of the password.

bin2hex(username + ':' + SHA1(password))

For example the user foobar with a password of foopasswd should send the following hexadecimal string

Server returns OK on success and ERR on failure.

Registration

REGISTER

REGISTER UUID=UUID, TYPE={JSON | AVRO}

Register as a client to the service.

Example:

Server returns OK on success and ERR on failure.

Change Data Capture Commands

REQUEST-DATA

REQUEST-DATA DATABASE.TABLE[.VERSION] [GTID]

This command fetches data from specified table in a database and returns the output in the requested format (AVRO or JSON). Data records are sent to clients and if new AVRO versions are found (e.g. mydb.mytable.0000002.avro) the new schema and data will be sent as well.

The data will be streamed until the client closes the connection.

Clients should continue reading from network in order to automatically gets new events.

Example:

Example Client

MaxScale includes an example CDC client application written in Python 3. You can find the source code for it .

CC BY-SA / Gnu FDL

MariaDB MaxScale MaxGUI Guide

MariaDB MaxScale MaxGUI Guide

Introduction

MaxGUI is a browser-based interface for MaxScale REST-API and query execution.

Enabling MaxGUI

To enable MaxGUI in a testing mode, add admin_host=0.0.0.0 andadmin_secure_gui=false under the [maxscale] section of the MaxScale configuration file. Once enabled, MaxGUI will be available on port 8989:http://127.0.0.1:8989/

Securing the GUI

To make MaxGUI secure, set admin_secure_gui=true and configure both theadmin_ssl_key and admin_ssl_cert parameters.

See and for instructions on how to harden your MaxScale installation for production use.

Authentication

MaxGUI uses the same credentials as maxctrl. The default username is admin with mariadb as the password.

Internally, MaxGUI uses as the authentication method for persisting the user's session. If the Remember me checkbox is ticked, the session will persist for 24 hours. Otherwise, the session will expire as soon as MaxGUI is closed.

To log out, simply click the username section in the top right corner of the page header to access the logout menu.

Pages

Dashboard

This page provides an overview of MaxScale configuration which includes Monitors, Servers, Services, Sessions, Listeners, and Filters.

By default, the refresh interval is 10 seconds.

Detail

This page shows information on each and allow to edit its parameter, relationships and perform other manipulation operations.

Access this page by clicking on the MaxScale object name on the

Visualization

This page visualizes MaxScale configuration and clusters.

• Configuration: Visualizing MaxScale configuration.

• Cluster: Visualizing a replication cluster into a tree graph and provides manual cluster manipulation operations such asswitchover, reset-replication, release-locks, failover, rejoin . At the moment, it supports only servers monitored by Monitor using module.

Access this page by clicking the graph icon on the sidebar navigation.

Settings

This page shows and allows editing of MaxScale parameters.

Access this page by clicking the gear icon on the sidebar navigation.

Logs Archive

Realtime MaxScale logs can be accessed by clicking the logs icon on the sidebar navigation.

Query Editor

Query Editor is a SQL editor tool allowing to run queries on a server, service, or listener. The query results can be visualized into a line, bar, or scatter graph and exported as CSV or JSON.

CC BY-SA / Gnu FDL

Simple Sharding with Two Servers

Simple Sharding with Two Servers

Sharding is the method of splitting a single database server into separate parts. This tutorial describes a very simple way of sharding. Each schema is located on a different database server and MariaDB MaxScale's

Maxrows

Maxrows

This filter was introduced in MariaDB MaxScale 2.1.

Throttle

Throttle

This filter was added in MariaDB MaxScale 2.3

MariaDB MaxScale Installation Guide

MariaDB MaxScale Installation Guide

We recommend to install MaxScale on a separate server, to ensure that there can be no competition of resources between MaxScale and a MariaDB Server that it manages.

Building MariaDB MaxScale from Source Code

Building MariaDB MaxScale from Source Code

MariaDB MaxScale can be built on any system that meets the requirements. The main requirements are as follows:

Hintfilter

Hintfilter

This filter adds routing hints to a service. The filter has no parameters.

Hint Syntax

Note: If a query has more than one comment only the first comment is processed. Always place any MaxScale related comments first before any other comments that might appear in the query.

Comments and comment types

The client connection will need to have comments enabled. For example themariadb and mysql command line clients have comments disabled by default and they need to be enabled by passing the --comments or -c option to it. Most, if not all, connectors keep all comments intact in executed queries.

For comment types, use either -- (notice the whitespace after the double hyphen) or # after the semicolon or /* ... */ before the semicolon.

Inline comment blocks, i.e. /* .. */, do not require a whitespace character after the start tag or before the end tag but adding the whitespace is advised.

Hint body

All hints must start with the maxscale tag.

The hints have two types, ones that define a server type and others that contain name-value pairs.

Routing destination hints

These hints will instruct the router to route a query to a certain type of a server.

Route to master

A master value in a routing hint will route the query to a master server. This can be used to direct read queries to a master server for a up-to-date result with no replication lag.

Route to slave

A slave value will route the query to a slave server. Please note that the hints will override any decisions taken by the routers which means that it is possible to force writes to a slave server.

Route to named server

A server value will route the query to a named server. The value of<server name> needs to be the same as the server section name in maxscale.cnf. If the server is not used by the service, the hint is ignored.

Route to last used server

A last value will route the query to the server that processed the last query. This hint can be used to force certain queries to be grouped to the same server.

Name-value hints

These control the behavior and affect the routing decisions made by the router. Currently the only accepted parameter is the readwritesplit parametermax_slave_replication_lag. This will route the query to a server with a lower replication lag than this parameter's value.

Hint stack

Hints can be either single-use hints, which makes them affect only one query, or named hints, which can be pushed on and off a stack of active hints.

Defining named hints:

Pushing a hint onto the stack:

Popping the topmost hint off the stack:

You can define and activate a hint in a single command using the following:

You can also push anonymous hints onto the stack which are only used as long as they are on the stack:

Prepared Statements

The hintfilter supports routing hints in prepared statements for both thePREPARE and EXECUTE SQL commands as well as the binary protocol prepared statements.

Binary Protocol

With binary protocol prepared statements, a routing hint in the prepared statement is applied to the execution of the statement but not the preparation of it. The preparation of the statement is routed normally and is sent to all servers.

For example, when the following prepared statement is prepared with the MariaDB Connector-C function mariadb_stmt_prepare and then executed withmariadb_stmt_execute the result is always returned from the master:

Support for binary protocol prepared statements was added in MaxScale 6.0 ().

The protocol commands that the routing hints are applied to are:

• COM_STMT_EXECUTE

• COM_STMT_BULK_EXECUTE

• COM_STMT_SEND_LONG_DATA

• COM_STMT_FETCH

Support for direct execution of prepared statements was added in MaxScale 6.2.0. For example the MariaDB Connector-C uses direct execution whenmariadb_stmt_execute_direct is used.

Text Protocol

Text protocol prepared statements (i.e. the PREPARE and EXECUTE SQL commands) behave differently. If a PREPARE command has a routing hint, it will be routed according to the routing hint. Any subsequent EXECUTE command will not be affected by the routing hint in the PREPARE statement. This means they must have their own routing hints.

The following example is the recommended method of executing text protocol prepared statements with hints:

The PREPARE is routed normally and will be routed to all servers. TheEXECUTE will be routed to the master as a result of it having the route to master hint.

Examples

Routing SELECT queries to master

In this example, MariaDB MaxScale is configured with the readwritesplit router and the hint filter.

Behind MariaDB MaxScale is a master server and a slave server. If there is replication lag between the master and the slave, read queries sent to the slave might return old data. To guarantee up-to-date data, we can add a routing hint to the query.

The first INSERT query will be routed to the master. The following SELECT query would normally be routed to the slave but with the added routing hint it will be routed to the master. This way we can do an INSERT and a SELECT right after it and still get up-to-date data.

CC BY-SA / Gnu FDL

Transaction Performance Monitoring Filter

Transaction Performance Monitoring Filter

Note: This module is experimental and must be built from source. The module is deprecated in MaxScale 23.08 and might be removed in a future release.

Overview

The Transaction Performance Monitoring (TPM) filter is a filter module for MaxScale that monitors every SQL statement that passes through the filter. The filter groups a series of SQL statements into a transaction by detecting 'commit' or 'rollback' statements. It logs all committed transactions with necessary information, such as timestamp, client, SQL statements, latency, etc., which can be used later for transaction performance analysis.

Configuration

The configuration block for the TPM filter requires the minimal filter options in it's section within the maxscale.cnf file, stored in /etc/maxscale.cnf.

Filter Options

The TPM filter does not support any filter options currently.

Filter Parameters

The TPM filter accepts a number of optional parameters.

Filename

The name of the output file created for performance logging. The default filename is tpm.log.

Source

The optional source parameter defines an address that is used to match against the address from which the client connection to MaxScale originates. Only sessions that originate from this address will be logged.

User

The optional user parameter defines a user name that is used to match against the user from which the client connection to MaxScale originates. Only sessions that are connected using this username are logged.

Delimiter

The optional delimiter parameter defines a delimiter that is used to distinguish columns in the log. The default delimiter is :::.

Query_delimiter

The optional query_delimiter defines a delimiter that is used to distinguish different SQL statements in a transaction. The default query delimiter is @@@.

Named_pipe

named_pipe is the path to a named pipe, which TPM filter uses to communicate with 3rd-party applications (e.g., ). Logging is enabled when the router receives the character '1' and logging is disabled when the router receives the character '0' from this named pipe. The default named pipe is /tmp/tpmfilter and logging is disabled by default.

For example, the following command enables the logging:

Similarly, the following command disables the logging:

Log Output Format

For each transaction, the TPM filter prints its log in the following format:

<timestamp> | <server_name> | <user_name> | <latency of the transaction> | <latencies of individual statements in the transaction> (delimited by 'query_delimiter') | <actual SQL statements>

Examples

Example 1 - Log Transactions for Performance Analysis

You want to log every transaction with its SQL statements and latency for future transaction performance analysis.

Add a filter with the following definition:

After the filter reads the character '1' from its named pipe, the following is an example log that is generated from the above TPM filter with the above configuration:

Note that 3 and 6 are latencies of each transaction in milliseconds, while 0.165 and 0.123 are latencies of the first statement of each transaction in milliseconds.

CC BY-SA / Gnu FDL

Consistent Critical Read Filter

Consistent Critical Read Filter

This filter was introduced in MariaDB MaxScale 2.1.

Overview

The Consistent Critical Read (CCR) filter allows consistent critical reads to be done through MaxScale while still allowing scaleout of non-critical reads.

When the filter detects a statement that would modify the database, it attaches a routing hint to all following statements done by that connection. This routing hint guides the routing module to route the statement to the master server where data is guaranteed to be in an up-to-date state. Writes from one session do not, by default, propagate to other sessions.

Note: This filter does not work with prepared statements. Only text protocol queries are handled by this filter.

Controlling the Filter with SQL Comments

The triggering of the filter can be limited further by adding MaxScale supported comments to queries and/or by using regular expressions. The query comments take precedence: if a comment is found it is obeyed even if a regular expression parameter might give a different result. Even a comment cannot cause a SELECT-query to trigger the filter. Such a comment is considered an error and ignored.

The comments must follow the and the HintFilter needs to be in the filter chain before the CCR-filter. If a query has a MaxScale supported comment line which defines the parameter ccr, that comment is caught by the CCR-filter. Parameter values match and ignore are supported, causing the filter to trigger (match) or not trigger (ignore) on receiving the write query. For example, the query

would normally cause the filter to trigger, but does not because of the comment. The match-comment typically has no effect, since write queries by default trigger the filter anyway. It can be used to override an ignore-type regular expression that would otherwise prevent triggering.

Filter Parameters

The CCR filter has no mandatory parameters.

time

• Type:

• Mandatory: No

• Dynamic: Yes

• Default: 60s

The time window during which queries are routed to the master. The duration can be specified as documented but the value will always be rounded to the nearest second. If no explicit unit has been specified, the value is interpreted as seconds in MaxScale 2.4. In subsequent versions a value without a unit may be rejected. The default value for this parameter is 60 seconds.

When a data modifying SQL statement is processed, a timer is set to the value of_time_. Once the timer has elapsed, all statements are routed normally. If a new data modifying SQL statement is processed within the time window, the timer is reset to the value of time.

Enabling this parameter in combination with the count parameter causes both the time window and number of queries to be inspected. If either of the two conditions are met, the query is re-routed to the master.

count

• Type: count

• Mandatory: No

• Dynamic: Yes

• Default: 0

The number of SQL statements to route to master after detecting a data modifying SQL statement. This feature is disabled by default.

After processing a data modifying SQL statement, a counter is set to the value of count and all statements are routed to the master. Each executed statement after a data modifying SQL statement cause the counter to be decremented. Once the counter reaches zero, the statements are routed normally. If a new data modifying SQL statement is processed, the counter is reset to the value of_count_.

match, ignore

• Type:

• Mandatory: No

• Dynamic: No

• Default: ""

These control which statements trigger statement re-routing. Only non-SELECT statements are inspected. For CCRFilter, the exclude-parameter is instead named ignore, yet works similarly.

options

• Type:

• Mandatory: No

• Dynamic: No

• Values: ignorecase, case, extended

Regular expression options for match and ignore.

global

• Type:

• Mandatory: No

• Dynamic: Yes

• Default: false

global is a boolean parameter that when enabled causes writes from one connection to propagate to all other connections. This can be used to work around cases where one connection writes data and another reads it, expecting the write done by the other connection to be visible.

This parameter only works with the time parameter. The use of global andcount at the same time is not allowed and will be treated as an error.

Example Configuration

Here is a minimal filter configuration for the CCRFilter which should solve most problems with critical reads after writes.

With this configuration, whenever a connection does a write, all subsequent reads done by that connection will be forced to the master for 5 seconds.

This prevents read scaling until the modifications have been replicated to the slaves. For best performance, the value of time should be slightly greater than the actual replication lag between the master and its slaves. If the number of critical read statements is known, the count parameter could be used to control the number reads that are sent to the master.

CC BY-SA / Gnu FDL

Installing MariaDB MaxScale using a tarball

Installing MariaDB MaxScale using a tarball

MariaDB MaxScale is also made available as a tarball, which is named likemaxscale-x.y.z.OS.tar.gz where x.y.z is the same as the corresponding version and OS identifies the operating system, e.g. maxscale-2.5.6.centos.7.tar.gz.

In order to use the tarball, the following libraries are required:

• libcurl

• libaio

• OpenSSL

• gnutls

The tarball has been built with the assumption that it will be installed in /usr/local. However, it is possible to install it in any directory, but in that case MariaDB MaxScale must be invoked with a flag.

Installing as root in /usr/local

If you have root access to the system you probably want to install MariaDB MaxScale under the user and group maxscale.

The required steps are as follows:

Creating the symbolic link is necessary, since MariaDB MaxScale has been built with the assumption that the plugin directory is /usr/local/maxscale/lib/maxscale.

The symbolic link also makes it easy to switch between different versions of MariaDB MaxScale that have been installed side by side in /usr/local; just make the symbolic link point to another installation.

In addition, the first time you install MariaDB MaxScale from a tarball you need to create the following directories:

and make maxscale the owner of them:

The following step is to create the MariaDB MaxScale configuration file /etc/maxscale.cnf. The file etc/maxscale.cnf.template can be used as a base. Please refer to for details.

When the configuration file has been created, MariaDB MaxScale can be started.

The -d flag causes maxscale not to turn itself into a daemon, which is adviseable the first time MariaDB MaxScale is started, as it makes it easier to spot problems.

If you want to place the configuration file somewhere else but in /etc you can invoke MariaDB MaxScale with the --config flag, for instance, --config=/usr/local/maxscale/etc/maxscale.cnf.

Note also that if you want to keep everything under /usr/local/maxscale you can invoke MariaDB MaxScale using the flag --basedir.

That will cause MariaDB MaxScale to look for its configuration file in/usr/local/maxscale/etc and to store all runtime files under /usr/local/maxscale/var.

Installing in any Directory

Enter a directory where you have the right to create a subdirectory. Then do as follows.

The next step is to create the MaxScale configuration file maxscale-x.y.z/etc/maxscale.cnf. The file maxscale-x.y.z/etc/maxscale.cnf.template can be used as a base. Please refer to for details.

When the configuration file has been created, MariaDB MaxScale can be started.

With the flag --basedir, MariaDB MaxScale is told where the lib, etc and var directories are found. Unless it is specified, MariaDB MaxScale assumes the lib directory is found in /usr/local/maxscale, and the var and etc directories in /.

It is also possible to specify the directories and the location of the configuration file individually. Invoke MaxScale like

to find out the appropriate flags.

CC BY-SA / Gnu FDL

Setting up MariaDB MaxScale

Setting up MariaDB MaxScale

This document is designed as a quick introduction to setting up MariaDB MaxScale.

The installation and configuration of the MariaDB Server is not covered in this document. See the following MariaDB documentation articles for more information on setting up a master-slave-cluster or a Galera-cluster: and .

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

Building from source code in GitHub is covered in .

Installing MaxScale

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

Creating a user account for MaxScale

MaxScale checks that incoming clients are valid. To do this, MaxScale needs to retrieve user authentication information from the backend databases. Create a special user account for this purpose by executing the following SQL commands on the master server of your database cluster. The following tutorials will use these credentials.

MariaDB versions 10.2.2 to 10.2.10 also require GRANT SELECT ON mysql.* TO 'maxscale'@'%';

Creating client user accounts

Because MariaDB MaxScale sits between the clients and the backend databases, the backend databases will see all clients as if they were connecting from MaxScale's address. This usually means that two sets of grants for each user are required.

For example, assume that the user 'jdoe'@'client-host' exists and MaxScale is located at_maxscale-host_. If 'jdoe'@'client-host' needs to be able to connect through MaxScale, another user, 'jdoe'@'maxscale-host', must be created. The second user must have the same password and similar grants as 'jdoe'@'client-host'.

The quickest way to do this is to first create the new user:

Then do a SHOW GRANTS query:

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

An alternative to generating two separate accounts is to use one account with a wildcard host ('jdoe'@'%') which covers both hosts. This is more convenient but less secure than having specific user accounts as it allows access from all hosts.

Creating the configuration file

MaxScale reads its configuration from /etc/maxscale.cnf. A template configuration is provided with the MaxScale installation.

A global maxscale section is included in every MaxScale configuration file. This section sets the values of various global parameters, such as the number of threads MaxScale uses to handle client requests. To set thread count to the number of available cpu cores, set the following.

Configuring the servers

Read the mini-tutorial for server configuration instructions.

Configuring the monitor

The type of monitor used depends on the type of cluster used. For a master-slave cluster read . For a Galera cluster read .

Configuring the services and listeners

This part is covered in two different tutorials. For a fully automated read-write-splitting setup, read the . For a simple connection based setup, read the .

Starting MaxScale

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

For older SysV systems, use the service command.

If MaxScale fails to start, check the error log in /var/log/maxscale/maxscale.log to see if any errors are detected in the configuration file.

Checking MaxScale status with MaxCtrl

The maxctrl-command can be used to confirm that MaxScale is running and the services, listeners and servers have been correctly configured. The following shows expected output when using a read-write-splitting configuration.

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

More options can be found in the , and .

For more information about MaxCtrl and how to secure it, see the .

CC BY-SA / Gnu FDL

Filters

Filters

What Are Filters?

The filter mechanism in MariaDB MaxScale is a means by which processing can be inserted into the flow of requests and responses between the client connection to MariaDB MaxScale and the MariaDB MaxScale connection to the backend database servers. The path from the client side of MariaDB MaxScale out to the actual database servers can be considered a pipeline, filters can then be placed in that pipeline to monitor, modify, copy or block the content that flows through that pipeline.

Types Of Filter

Filters can be divided into a number of categories

Logging filters

Logging filters do not in any way alter the statement or results of the statements that are passed through MariaDB MaxScale. They merely log some information about some or all of the statements and/or result sets.

Two examples of logging filters are contained within the MariaDB MaxScale, a filter that will log all statements and another that will log only a number of statements, based on the duration of the execution of the query.

Statement rewriting filters

Statement rewriting filters modify the statements that are passed through the filter. This allows a filter to be used as a mechanism to alter the statements that are seen by the database, an example of the use of this might be to allow an application to remain unchanged when the underlying database changes or to compensate for the migration from one database schema to another.

Result set manipulation filters

A result set manipulation filter is very similar to a statement rewriting but applies to the result set returned rather than the statement executed. An example of this may be obfuscating the values in a column.

Routing hint filters

Routing hint filters are filters that embed hints in the request that can be used by the router onto which the query is passed. These hints include suggested destinations as well as metric that may be used by the routing process.

Firewall filters

A firewall filter is a mechanism that allows queries to be blocked within MariaDB MaxScale before they are sent on to the database server for execution. They allow constructs or individual queries to be intercepted and give a level of access control that is more flexible than the traditional database grant mechanism.

Pipeline control filters

A pipeline filter is one that has an affect on how the requests are routed within the internal MariaDB MaxScale components. The most obvious version of this is the ability to add a "tee" connector in the pipeline, duplicating the request and sending it to a second MariaDB MaxScale service for processing.

Filter Definition

Filters are defined in the configuration file, typically maxscale.cnf, using a section for each filter instance. The content of the filter sections in the configuration file various from filter to filter, however there are always to entries present for every filter, the type and module.

The type is used by the configuration manager within MariaDB MaxScale to determine what this section is defining and the module is the name of the plugin that implements the filter.

When a filter is used within a service in MariaDB MaxScale the entry filters= is added to the service definition in the ini file section for the service. Multiple filters can be defined using a syntax akin to the Linux shell pipe syntax.

The names used in the filters= parameter are the names of the filter definition sections in the ini file. The same filter definition can be used in multiple services and the same filter module can have multiple instances, each with its own section in the ini file.

Filter Examples

The filters that are bundled with the MariaDB MaxScale are documented separately, in this section a short overview of how these might be used for some simple tasks will be discussed. These are just examples of how these filters might be used, other filters may also be easily added that will enhance the MariaDB MaxScale functionality still further.

Log The 30 Longest Running Queries

The top filter can be used to measure the execution time of every statement within a connection and log the details of the longest running statements.

The first thing to do is to define a filter entry in the ini file for the top filter. In this case we will call it "top30". The type is filter and the module that implements the filter is called topfilter.

In the definition above we have defined two filter specific parameters, the count of the number of statement to be logged and a filebase that is used to define where to log the information. This filename is a stem to which a session id is added for each new connection that uses the filter.

The filter keeps track of every statement that is executed, monitors the time it takes for a response to come back and uses this as the measure of execution time for the statement. If the time is longer than the other statements that have been recorded, then this is added to the ordered list within the filter. Once 30 statements have been recorded those statements that have been recorded with the least time are discarded from the list. The result is that at any time the filter has a list of the 30 longest running statements in each session.

When the session ends a report will be written for the session into the logfile defined. That report will include the top 30 longest running statements, plus summary data for the session;

• The time the connection was opened.

• The host the connection was from.

• The username used in the connection.

• The duration of the connection.

Duplicate Data From Your Application Into Cassandra

The scenario we are using in this example is one in which you have an online gaming application that is designed to work with a MariaDB database. The database schema includes a high score table which you would like to have access to in a Cassandra cluster. The application is already using MariaDB MaxScale to connect to a MariaDB Galera cluster, using a service names BubbleGame. The definition of that service is as follows

The table you wish to store in Cassandra in called HighScore and will contain the same columns in both the MariaDB table and the Cassandra table. The first step is to install a MariaDB instance with the Cassandra storage engine to act as a bridge server between the relational database and Cassandra. In this bridge server add a table definition for the HighScore table with the engine type set to Cassandra. See for details. Add this server into the MariaDB MaxScale configuration and create a service that will connect to this server.

Next add a filter definition for the tee filter that will duplication insert statements that are destined for the HighScore table to this new service.

The above filter definition will cause all statements that match the regular expression inset.*HighScore.*values to be duplication and sent not just to the original destination, via the router but also to the service named Cassandra.

The final step is to add the filter to the BubbleGame service to enable the use of the filter.

CC BY-SA / Gnu FDL

MaxScale and Xpand Tutorial

MaxScale and Xpand Tutorial

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

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

Xpand and Readconnroute

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

Bootstrap servers

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

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

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

Monitor

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

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

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

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

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

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

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

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

Service

The service is specified as follows:

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

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

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

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

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

Listener

To complete the configuration, a listener must be specified.

Xpand and Readwritesplit

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

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

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

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

Service

The service is specified as follows:

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

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

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

CC BY-SA / Gnu FDL

Avrorouter Tutorial

Avrorouter Tutorial

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

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

Configuration

Preparing the master server

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

You can find out more about replication formats from the

Configuring MaxScale

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

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

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

For more information on the avrorouter options, read the .

Preparing the data in the master server

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

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

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

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

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

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

Starting MariaDB MaxScale

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

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

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

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

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

Creating and Processing Data

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

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

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

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

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

CC BY-SA / Gnu FDL

Regex Filter

Regex Filter

Lua Filter

Lua Filter

The luafilter is a filter that calls a set of functions in a Lua script.

Read the for information on how to write Lua scripts.

Xpand Monitor

Xpand Monitor