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 maxscale@googlegroups.com or use the forum interface.
Information about installing MariaDB MaxScale, either from a repository or by
building from source code, is included in the MariaDB MaxScale Installation
Guide.
The same guide also provides basic information on running MariaDB MaxScale. More
detailed information about configuring MariaDB MaxScale can be found in the Configuration Guide.
This page is licensed: CC BY-SA / Gnu FDL
Limitations and Known Issues within MariaDB MaxScale
This document lists known issues and limitations in MariaDB MaxScale and its
plugins. Since limitations are related to specific plugins, this document is
divided into several sections.
In versions 2.1.2 and earlier, the configuration files are limited to 1024
characters per line. This limitation was increased to 16384 characters in
MaxScale 2.1.3. MaxScale 2.3.0 increased this limit to 16777216 characters.
In versions 2.2.12 and earlier, the section names in the configuration files
were limited to 49 characters. This limitation was increased to 1023 characters
in MaxScale 2.2.13.
Multiple MaxScales on same server
Starting with MaxScale 2.4.0, on systems with Linux kernels 3.9 or newer due to
the addition of SO_REUSEPORT support, it is possible for multiple MaxScale
instances to listen on the same network port if the directories used by both
instances are completely separate and there are no conflicts which can cause
unexpected splitting of connections. This will only happen if users explicitly
tell MaxScale to ignore the default directories and will not happen in normal
use.
Security limitiations
MariaDB 10.2
The parser of MaxScale correctly parses WITH statements, but fails to
collect columns, functions and tables used in the SELECT defining theWITH clause.
Consequently, the database firewall will not block WITH statements
where the SELECT of the WITH clause refers to forbidden columns.
MariaDB Default Values
MaxScale assumes that certain configuration parameters in MariaDB are set to
their default values. These include but are not limited to:
autocommit: Autocommit is enabled for all new connections.
tx_read_only: Transactions use READ WRITE permissions by default.
Query Classification
Follow the MXS-1350 Jira issue
to track the progress on this limitation.
XA transactions are not detected as transactions by MaxScale. This means
that all XA commands will be treated as unknown commands and will be
treated as operations that potentially modify the database (in the case of
readwritesplit, the statements are routed to the master).
MaxScale will not track the XA transaction state which means that any
SELECT queries done inside an XA transaction can be routed to servers that
are not part of the XA transaction.
This limitation can be avoided on the client side by disabling autocommit
before any XA transactions are done. The following example shows how a
simple XA transaction is done via MaxScale by disabling autocommit for the
duration of the XA transaction.
Prepared Statements
For its proper functioning, MaxScale needs in general to be aware of the
transaction state and autocommit mode. In order to be that, MaxScale
parses statements going through it.
However, if a transaction is commited or rolled back, or the autocommit
mode is changed using a prepared statement, MaxScale will miss that and its
internal state will be incorrect, until the transaction state or autocommit
mode is changed using an explicit statement.
For instance, after the following sequence of commands, MaxScale will still
think autocommit is on:
To ensure that MaxScale functions properly, do not commit or rollback a
transaction or change the autocommit mode using a prepared statement.
Protocol limitations
Limitations with MySQL/MariaDB Protocol support (MariaDBClient)
Compression is not included in the server handshake.
If a KILL [CONNECTION] <ID> statement is executed, MaxScale will intercept
it. If the ID matches a MaxScale session ID, it will be closed by sending
modified KILL commands of the same type to all backend server to which the
session in question is connected to. This results in behavior that is similar
to how MariaDB does it. If the KILL CONNECTION USER <user> form is given,
all connections with a matching username will be closed instead.
MariaDB MaxScale does not support KILL QUERY ID <query_id> type
statements. If a query by a query ID is to be killed, it needs to be done
directly on the backend databases.
Any KILL commands executed using a prepared statement are ignored by
MaxScale. If any are executed, it is highly likely that the wrong connection
ends up being killed.
If a KILL connection kills a session that is connected to a readwritesplit
service that has transaction_replay or delayed_retry enabled, it is
possible that the query is retried even if the connection is killed. To avoid
this, use KILL QUERY instead.
A KILL on one service can cause a connection from another service to be
closed even if it uses a different protocol.
The change user command (COM_CHANGE_USER) only works with standard
authentication.
If a COM_CHANGE_USER succeeds on MaxScale yet fails on the server the session
ends up in an inconsistent state. This can happen if the password of the
target user is changed and MaxScale uses old user account data when processing
the change user. In such a situation, MaxScale and server will disagree on the
current user. This can affect e.g. reconnections.
Authenticator limitations
Limitations in the MySQL authenticator (MariaDBAuth)
MySQL old style passwords are not supported. MySQL versions 4.1 and newer use
a new authentication protocol which does not support pre-4.1 style passwords.
When users have different passwords based on the host from which they connect
MariaDB MaxScale is unable to determine which password it should use to connect
to the backend database. This results in failed connections and unusable
usernames in MariaDB MaxScale.
Filter limitations
Database Firewall limitations (dbfwfilter)
The Database Firewall filter does not support multi-statements. Using them will
result in an error being sent to the client.
Tee filter limitations (tee)
The Tee filter does not support binary protocol prepared statements. The
execution of a prepared statements through a service that uses the tee filter is
not guaranteed to succeed on the service where the filter branches to as it does
on the original service.
This possibility exists due to the fact that the binary protocol prepared
statements are identified by a server-generated ID. The ID sent to the client
from the main service is not guaranteed to be the same that is sent by the
branch service.
Monitor limitations
A server can only be monitored by one monitor. Two or more monitors monitoring
the same server is considered an error.
Limitations with Galera Cluster Monitoring (galeramon)
The default master selection is based only on MIN(wsrep_local_index). This
can be influenced with the server priority mechanic described in the Galera Monitor manual.
Router limitations
Refer to individual router documentation for a list of their limitations.
This page is licensed: CC BY-SA / Gnu FDL
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
libatomic
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 Configuration Guide 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 Configuration Guide 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.
This page is licensed: CC BY-SA / Gnu FDL
MariaDB MaxScale 21.06 REST API
Manage MaxScale programmatically using the REST API. This interface allows for the dynamic administration and monitoring of resources like servers, services, listeners, and filters.
MaxScale 21.06 Monitors
Monitors are essential for high availability. They track backend server status, detect failures, promote replicas, and perform automatic failovers, ensuring service continuity.
Pluggable authentication module (PAM) is a general purpose authentication API.
An application using PAM can authenticate a user without knowledge about the
underlying authentication implementation. The actual authentication scheme is
defined in the operating system PAM config (e.g. /etc/pam.d/), and can be
quite elaborate. MaxScale supports a very limited form of the PAM protocol,
which this document details.
Configuration
The MaxScale PAM module requires little configuration. All that is required
is to change the listener authenticator module to "PAMAuth".
MaxScale uses the PAM authenticator plugin to authenticate users with plugin
set to "pam" in the mysql.user-table. The PAM service name of a user is read
from the authetication_string-column. The matching PAM service in the
operating system PAM config is used for authenticating the user. If the_authetication_string_ for a user is empty, the fallback service "mysql" is
used.
PAM service configuration is out of the scope of this document, see The Linux-PAM System Administrators' Guide
for more information. A simple service definition used for testing this module
is below.
If enabled, MaxScale communicates with the client as if using .
This setting has no effect on MaxScale-to-backend communication, which adapts to
either "dialog" or "mysql_clear_password", depeding on which one the backend
suggests. This setting is meant to be used with the similarly named MariaDB
Server setting.
This setting defines the authentication mode used. Two values are supported:
password Normal password-based authentication
password_2FA Password + 2FA-code based authentication
authenticator_options=pam_mode=password_2FA
If set to "password_2FA", any users authenticating via PAM will be asked two
passwords ("Password" and "Verification code") during login. MaxScale uses the
normal password when either the local PAM api or a backend asks for "Password".
MaxScale answers any other password prompt (e.g. "Verification code") with the
second password. See the limitations section
for more details. Two-factor mode is incompatible with_pam_use_cleartext_plugin_.
Defines backend authentication mapping, i.e. switch of authentication method
between client-to-MaxScale and MaxScale-to-backend. Supported values:
none No mapping
mariadb Map users to normal MariaDB accounts
authenticator_options=pam_backend_mapping=mariadb
If set to "mariadb", MaxScale will authenticate clients to backends using
standard MariaDB authentication. Authentication to MaxScale itself still uses
PAM. MaxScale asks the local PAM system if the client username was mapped
to another username during authentication, and use the mapped username when
logging in to backends. Passwords for the mapped users can be given in a file,
see pam_mapped_pw_file below. If passwords are not given, MaxScale will try to
authenticate without a password. Because of this, normal PAM users and mapped
users cannot be used on the same listener.
Because the client still needs to authenticate to MaxScale normally, an
anonymous user may be required. If the backends do not allow such a user, one
can be manually added using the service setting user_accounts_file.
To map usernames, the PAM service needs to use a module such as_pam_user_map.so_. This module is not a standard Linux component and needs to be
installed separately. It is included in recent MariaDB Server packages and can
also be compiled from source. See
for more information on how to configure the module. If the goal is to only map
users from PAM to MariaDB in MaxScale, then configuring user mapping
on just the machine running MaxScale is enough.
Instead of using pam_backend_mapping, consider using the listener setting user_mapping_file,
as it is easier to configure. pam_backend_mapping should only be used when
the user mapping needs to be defined by pam.
pam_mapped_pw_file
Type: path
Mandatory: No
Dynamic: No
Default: None
Path to a json-text file with user passwords. Default value is empty, which
disables the feature.
This feature only works together with pam_backend_mapping=mariadb. The file is
only read during listener creation (typically MaxScale start) or when a listener
is modified during runtime. The file should contain passwords for the mapped
users. When a client is authenticating, MaxScale searches the password data for a
matching username. If one is found, MaxScale uses the supplied password when
logging in to backends. Otherwise, MaxScale tries to authenticate without a
password.
One array, "users_and_passwords", is read from the file. Each array element in the array must define the following fields:
"user": String. Mapped client username.
"password": String. Backend server password. Can be encrypted with maxpasswd.
When backend authenticator mapping is not in use
(authenticator_options=pam_backend_mapping=none), the PAM authenticator
supports a limited version of .
It requires less configuration but is also less accurate than proper mapping.
Anonymous mapping is enabled in MaxScale if the following user exists:
Empty username (e.g. ''@'%' or ''@'myhost.com')
plugin = 'pam'
Proxy grant is on (The query SHOW GRANTS FOR user@host; returns at least one
row with GRANT PROXY ON ...)
When the authenticator detects such users, anonymous account mapping is enabled
for the hosts of the anonymous users. To verify this, enable the info log
(log_info=1 in MaxScale config file). When a client is logging in using the
anonymous user account, MaxScale will log a message starting with "Found
matching anonymous user ...".
When mapping is on, the MaxScale PAM authenticator does not require client
accounts to exist in the mysql.user-table received from the backend. MaxScale
only requires that the hostname of the incoming client matches the host field of
one of the anonymous users (comparison performed using LIKE). If a match is
found, MaxScale attempts to authenticate the client to the local machine with
the username and password supplied. The PAM service used for authentication is
read from the authentication_string-field of the anonymous user. If
authentication was successful, MaxScale then uses the username and password to
log to the backends.
Anonymous mapping is only attempted if the client username is not found in themysql.user-table as explained in Configuration. This means,
that if a user is found and the authentication fails, anonymous authentication
is not attempted even when it could use a different PAM service with a different
outcome.
Setting up PAM group mapping for the MariaDB server is a more involved process
as the server requires details on which Unix user or group is mapped to which
MariaDB user. See
for more details. Performing all the steps in the guide also on the MaxScale
machine is not required, as the MaxScale PAM plugin only checks that the client
host matches an anonymous user and that the client (with the username and
password it provided) can log into the local PAM configuration. If using normal
password authentication, simply generating the Unix user and password should be
enough.
Implementation details and limitations
The general PAM authentication scheme is difficult for a proxy such as MaxScale.
An application using the PAM interface needs to define a conversation function
to allow the OS PAM modules to communicate with the client, possibly exchanging
multiple messages. This works when a client logs in to a normal server, but not
with MaxScale since it needs to autonomously log into multiple backends. For
MaxScale to successfully log into the servers, the messages and answers need to
be predefined. The passwords given to MaxScale need to work as is when MaxScale
logs into the backends. This requirement prevents the use of one-time passwords.
The MaxScale PAM authentication module supports two password modes. In normal
mode, client authentication begins with MaxScale sending an
AuthSwitchRequest packet. In addition to the command, the packet contains the
client plugin name ("dialog" or "mysql_clear_password"), a message type byte (4)
and the message "Password: ". In the next packet, the client should send the
password, which MaxScale will forward to the PAM api running on the local
machine. If the password is correct, an OK packet is sent to the client. If the
local PAM api asks for additional credentials as is typical in two-factor
authentication schemes, authentication fails. Informational messages such as
password expiration notifications are allowed. These are simply printed to the
log.
On the backend side, MaxScale expects the servers to act as MaxScale did towards
the client. The servers should send an AuthSwitchRequest packet as defined
above, MaxScale responds with the password received by the client authenticator
and finally backend replies with OK. Informational messages from backends are
only printed to the info-log.
Two-factor authentication support
MaxScale supports a limited form of two-factor authentication with thepam_mode=password_2FA-option. Since MaxScale uses the 2FA-code given by the
client to log in to the local PAM api as well as all the backends, the code must
be reusable. This prevents the use of any kind of centrally checked one-use
codes. Time-based codes work, assuming the backends are checking the codes
independently of each other. Automatic reconnection features (e.g.
readwritesplit-router) will not work, as the code has likely changed since
original authentication.
Optionally, the PAM configuration on the backend servers can be weakened such
that the servers only asks for the normal password. This way, MaxScale will
check the 2FA-code of the incoming client, while MaxScale logs into the backends
using only the password.
Due to technical reasons, MaxScale does not forward the password prompts from
the PAM api to the client. MaxScale will always ask for "Password" and
"Verification code", even if the PAM api asks for other items. This prevents the
use of authentication schemes where a specific question must be answered (e.g.
"Input code Nr. 5"). This is not a significant limitation, as such schemes would
not work with backend servers anyway.
Test tool
MaxScale binary directory contains the test_pam_login-executable. This simple
program asks for a username, password and PAM service and then uses the given
credentials to login to the given service. test_pam_login uses the same code
as MaxScale itself to communicate with the OS PAM interface and may be useful
for diagnosing PAM login issues.
This page is licensed: CC BY-SA / Gnu FDL
MaxScale 21.06 Database Firewall filter
This filter is deprecated in MariaDB MaxScale 6 and will be removed in MaxScale 22.08.
The Database Firewall filter is used to block queries that match a set of
rules. It can be used to prevent harmful queries from reaching the backend
database instances or to limit access to the database based on a more flexible
set of rules compared to the traditional GRANT-based privilege system. Currently
the filter does not support multi-statements.
Note that the firewall filter should be viewed as a best-effort solution
intended for protecting against accidental misuse rather than malicious
attacks.
Configuration
The Database Firewall filter only requires minimal configuration in the
maxscale.cnf file. The actual rules of the Database Firewall filter are located
in a separate text file. The following is an example of a Database Firewall
filter configuration in maxscale.cnf.
The Database Firewall filter has one mandatory parameter, rules.
rules
A path to a file with the rule definitions in it. The file should be readable by
the user MariaDB MaxScale is run with. If a relative path is given, the path is
interpreted relative to the module configuration directory. The default module
configuration directory is /etc/maxscale.modules.d.
action
This parameter is optional and determines what action is taken when a query
matches a rule. The value can be either allow, which allows all matching
queries to proceed but blocks those that don't match, or block, which blocks
all matching queries, or ignore which allows all queries to proceed.
The following statement types will always be allowed through when action is
set to allow:
COM_CHANGE_USER: The user is changed for an active connection
COM_FIELD_LIST: Alias for the SHOW TABLES; query
COM_INIT_DB: Alias for USE <db>;
COM_PING: Server is pinged
COM_PROCESS_INFO: Alias for SHOW PROCESSLIST;
COM_PROCESS_KILL: Alias for KILL <id>; query
COM_QUIT: Client closes connection
COM_SET_OPTION: Client multi-statements are being configured
You can have both blacklist and whitelist functionality by configuring one
filter with action=allow and another one with action=block. You can then use
different rule files with each filter, one for blacklisting and another one for
whitelisting. After this you only have to add both of these filters to a service
in the following way.
If a query is blocked, the filter will return an error to the client with the
error number 1141 and an SQL state of HY000.
log_match
Log all queries that match a rule. For the any matching mode, the name of the
rule that matched is logged and for other matching modes, the name of the last
matching rule is logged. In addition to the rule name the matched user and the
query itself is logged. The log messages are logged at the notice level.
log_no_match
Log all queries that do not match a rule. The matched user and the query is
logged. The log messages are logged at the notice level.
treat_string_as_field
This optional parameter specifies how the database firewall should treat
strings. If true, they will be handled as fields, which will cause column
blocking rules to match even if ANSI_QUOTES has been enabled and " is
used instead of backtick.
treat_string_as_field=false
The default value is true.
Note that this may cause a false positive, if a "true" string contains the
name of a column to be blocked.
treat_string_arg_as_field
This optional parameter specifies how the database firewall should treat
strings used as arguments to functions. If true, they will be handled
as fields, which will cause function column blocking rules to match even
even if ANSI_QUOTES has been enabled and " is used instead of backtick.
treat_string_arg_as_field=false
The default value is true.
Note that this may cause a false positive, if a "true" string contains the
name of a column to be blocked.
strict
Whether to treat unsupported SQL or multi-statement SQL as an error. This is a
boolean parameter and the default value is true.
When disabled, SQL that cannot be fully parsed is allowed to pass if the rules
do not cause it to be blocked. This can be used to provide a best-effort mode
where uncertainly about the SQL is allowed.
Rule syntax
The rules are defined by using the following syntax:
rule NAME match RULE [at_times VALUE...] [on_queries {select|update|insert|delete|grant|revoke|drop|create|alter|use|load}]
Where NAME is the identifier for this rule and RULE is the mandatory rule definition.
Rules are identified by their name and have mandatory parts and optional parts.
You can add comments to the rule files by adding the # character at
the beginning of the line. Trailing comments are not supported.
The first step of defining a rule is to start with the keyword rule which
identifies this line of text as a rule. The second token is identified as
the name of the rule. After that the mandatory token match is required
to mark the start of the actual rule definition.
The rule definition must contain exactly one mandatory rule parameter. It can
also contain one of each type of optional rule parameter.
Mandatory rule parameters
The Database Firewall filter's rules expect a single mandatory parameter for a
rule. You can define multiple rules to cover situations where you would like to
apply multiple mandatory rules to a query.
wildcard
This rule blocks all queries that use the wildcard character *.
Example
Use of the wildcard is not allowed:
rule examplerule match wildcard
columns
This rule expects a list of values after the columns keyword. These values are
interpreted as column names and if a query targets any of these, it is matched.
Example
Deny name and salary columns:
rule examplerule match columns name salary
function
This rule expects a list of values after the function keyword. These values
are interpreted as function names and if a query uses any of these, it is
matched. The symbolic comparison operators (<, >, >= etc.) are also
considered functions whereas the text versions (NOT, IS, IS NOT etc.) are
not considered functions.
Example
Match queries using the sum and count functions:
rule examplerule match function sum count
not_function
This rule expects a list of values after the not_function keyword. These values
are interpreted as function names and if a query uses any function other than these,
it is matched. The symbolic comparison operators (<, >, >= etc.) are also
considered functions whereas the text versions (NOT, IS, IS NOT etc.) are
not considered functions.
If the rule is given no values, then the rule will match a query using any function.
Example
Match queries using other functions but the length function:
rule examplerule match not_function length
Match queries using functions:
rule examplerule match not_function
uses_function
This rule expects a list of column names after the keyword. If any of the
columns are used with a function, the rule will match. This rule can be
used to prevent the use of a column with a function.
Example
Deny function usage with name and address columns:
rule examplerule match uses_function name address
function and columns
This rule combines the function and columns type rules to match if one
of the listed columns uses one of the listed functions. The rule expects
the function and columns keywords both followed by a list of values.
Example
Deny use of the sum function with name or address columns:
rule examplerule match function sum columns name address
not_function and columns
This rule combines the not_function and columns type rules to match if
one of the listed columns is used in conjunction with functions other than
the listed ones. The rule expects the not_function and columns keywords
both followed by a list of values.
If not_function is not provided with a list of values, then the rule
matches if any of the columns is used with any function.
Example
Match if any other function but length is used with the name or address
columns:
rule examplerule match not_function length columns name address
Match if any function is used with the _ssn_column:
rule examplerule match not_function columns ssn
regex
This rule blocks all queries matching the regular expression. The regex string expects a
PCRE2 syntax regular expression. For more information about PCRE2 syntax, read the PCRE2 documentation. Unlike
typical MaxScale regex parameters, the value should be enclosed in single or double
quotes, not in /.../. Any compilation options must be included in the pattern itself.
Example
Block selects to accounts:
rule examplerule match regex '.*select.*from.*accounts.*'
limit_queries
This rule has been DEPRECATED. Please use the Throttle Filter instead.
The limit_queries rule expects three parameters. The first parameter is the
number of allowed queries during the time period. The second is the time period
in seconds and the third is the amount of time in seconds for which the rule is
considered active and blocking.
WARNING: Using limit_queries in action=allow is not supported.
Example
Over 50 queries within a window of 5 seconds will block for 100 seconds:
rule examplerule match limit_queries 50 5 100
no_where_clause
This rule inspects the query and blocks it if it has no WHERE clause. For
example, this would disallow a DELETE FROM ... query without a WHERE
clause. This does not prevent wrongful usage of the WHERE clause e.g. DELETE FROM ... WHERE 1=1.
Example
Queries must have a where clause:
rule examplerule match no_where_clause
Optional rule parameters
Each mandatory rule accepts one or more optional parameters. These are to be
defined after the mandatory part of the rule.
at_times
This rule expects a list of time ranges that define the times when the rule in
question is active. The time formats are expected to be ISO-8601 compliant and
to be separated by a single dash (the - character). For example, to define the
active period of a rule to be 5pm to 7pm, you would include at times 17:00:00-19:00:00 in the rule definition. The rule uses local time to check if
the rule is active and has a precision of one second.
on_queries
This limits the rule to be active only on certain types of queries. The possible
values are:
Keyword
Matching operations
select
SELECT statements
insert
INSERT statements
update
UPDATE statements
Multiple values can be combined using the pipe character | e.g.on_queries select|insert|update.
Applying rules to users
The users directive defines the users to which the rule should be applied.
users NAME... match { any | all | strict_all } rules RULE...
The first keyword is users, which identifies this line as a user definition
line.
The second component is a list of user names and network addresses in the format_user@0.0.0.0_. The first part is the user name and the second part is
the network address. You can use the % character as the wildcard to enable
user name matching from any address or network matching for all users. After the
list of users and networks the keyword match is expected. This means that the
following user definitions are supported:
user@host
user@%
%@host
Partial wildcards, e.g. user@192.% are not supported.
As MaxScale listens to the IPv6 all address by default, IPv4 addresses will be
mapped into the IPv6 space. This means that the IPv4 address 192.168.0.1 will
show up in MaxScale as ::ffff:192.168.0.1. Take this into account when
defining the users directives.
After this either the keyword any, all or strict_all is expected. This
defined how the rules are matched. If any is used when the first rule is
matched the query is considered as matched and the rest of the rules are
skipped. If instead the all keyword is used all rules must match for the query
to be considered as matched. The strict_all is the same as all but it checks the rules
from left to right in the order they were listed. If one of these does not
match, the rest of the rules are not checked. This could be useful in situations
where you would for example combine limit_queries and regex rules. By usingstrict_all you can have the regex rule first and the limit_queries rule
second. This way the rule only matches if the regex rule matches enough times
for the limit_queries rule to match.
After the matching part comes the rules keyword after which a list of rule names
is expected. This allows reusing of the rules and enables varying levels of
query restriction.
If a particular NAME appears on several users lines, then when an
actual user matches that name, the rules of each line are checked
independently until there is a match for the statement in question. That
is, the rules of each users line are treated in an OR fashion with
respect to each other.
Module commands
Read Module Commands documentation for
details about module commands.
The dbfwfilter supports the following module commands.
rules/reload FILTER [FILE]
Load a new rule file or reload the current rules. New rules are only taken into
use if they are successfully loaded and in cases where loading of the rules
fail, the old rules remain in use. The FILTER parameter is the filter instance
whose rules are reloaded. The FILE argument is an optional path to a rule file
and if it is not defined, the current rule file is used.
rules FILTER
Shows the current statistics of the rules. The FILTER parameter is the filter
instance to inspect.
Use Cases
Use Case 1 - Prevent rapid execution of specific queries
To prevent the excessive use of a database we want to set a limit on the rate of
queries. We only want to apply this limit to certain queries that cause unwanted
behaviour. To achieve this we can use a regular expression.
First we define the limit on the rate of queries. The first parameter for the
rule sets the number of allowed queries to 10 queries and the second parameter
sets the rate of sampling to 5 seconds. If a user executes queries faster than
this, any further queries that match the regular expression are blocked for 60
seconds.
rule limit_rate_of_queries match limit_queries 10 5 60
rule query_regex match regex '.*select.*from.*user_data.*'
To apply these rules we combine them into a single rule by adding a users line
to the rule file.
users %@% match all rules limit_rate_of_queries query_regex
Use Case 2 - Only allow deletes with a where clause
We have a table which contains all the managers of a company. We want to prevent
accidental deletes into this table where the where clause is missing. This poses
a problem, we don't want to require all the delete queries to have a where
clause. We only want to prevent the data in the managers table from being
deleted without a where clause.
To achieve this, we need two rules. The first rule defines that all delete
operations must have a where clause. This rule alone does us no good so we need
a second one. The second rule blocks all queries that match a regular
expression.
rule safe_delete match no_where_clause on_queries delete
rule managers_table match regex '.*from.*managers.*'
When we combine these two rules we get the result we want. To combine these two
rules add the following line to the rule file.
users %@% match all rules safe_delete managers_table
This page is licensed: CC BY-SA / Gnu FDL
MaxScale 21.06 SQL Resource
The SQL resource represents a database connection.
The following endpoints provide a simple REST API interface for executing
SQL queries on servers and services in MaxScale.
This document uses the :id value in the URL to represent a connection ID and
the :query_id to represent a query ID. These values do not need to be manually
added as the relevant links are returned in the request body of each endpoint.
The endpoints use JSON Web Tokens to uniquely identify open SQL connections. A
connection token can be acquired with a POST /v1/sql request and can be used
with the POST /v1/sql/:id/query, GET /v1/sql/:id/results/:query_id andDELETE /v1/sql endpoints. All of these endpoints accept a connection token in
the token parameter of the request:
POST /v1/sql/query?token=eyJhbGciOiJIUzI1NiJ9.eyJhdWQiOiJhZG1pbiIsImV4cCI6MTU4MzI1NDE1MSwiaWF0IjoxNTgzMjI1MzUxLCJpc3MiOiJtYXhzY2FsZSJ9.B1BqhjjKaCWKe3gVXLszpOPfeu8cLiwSb4CMIJAoyqw
In addition to request parameters, the token can be stored in cookies in which
case they are automatically used by the REST API. For more information about
token storage in cookies, see the documentation for POST /v1/sql.
Request Parameters
All of the endpoints that operate on a single connection support the following
request parameters. The GET /v1/sql and GET /v1/sql/:id endpoints are an
exception as they ignore the current connection token.
token
The connection token to use for the request. If provided, the value is
unconditionally used even if a cookie with a valid token exists.
The request body must be a JSON object consisting of the following fields:
target
The object in MaxScale to connect to. This is a mandatory value and the
given value must be the name of a valid server, service or listener in
MaxScale.
user
The username to use when creating the connection. This is a mandatory value.
password
The password for the user. This is a mandatory value.
db
The default database for the connection. By default the connection will have
no default database.
timeout
Connection timeout in seconds. The default connection timeout is 10
seconds. This controls how long the SQL connection creation can take before
an error is returned.
The response will contain the new connection with the token stored atmeta.token. If the request uses the persist=yes request parameter, the token
is stored in cookies instead of the metadata object and the response body will
not contain the token.
The location of the newly created connection will be stored at links.self in
the response body as well as in the Location header.
The token must be given to all subsequent requests that use the connection. It
must be either given in the token parameter of a request or it must be stored
in the cookies. If both a token parameter and a cookie exist at the same time,
the token parameter will be used instead of the cookie.
Request Parameters
This endpoint supports the following request parameters.
persist
Store the connection token in cookies instead of returning it as the response body.
This parameter expects only one value, yes, as its argument. Whenpersist=yes is set, the token is stored in two cookies,conn_id_body_<id> and conn_id_sig_<id> where the <id> part is replaced
by the ID of the connection.
The conn_id_body_<id> cookie contains the JWT header and claims sections
and contains the connection ID in the aud value. This can be used to
retrieve the connection ID from the cookies if the browser session is
closed.
max-age
Sets the connection token maximum age in seconds. The default ismax-age=28800. Only positive values are accepted and if a non-positive or
a non-integer value is found, the parameter is ignored. Once the token age
exceeds the configured maximum value, the token can no longer be used and a
new connection must be created.
Response
Connection was opened:
Status: 201 Created
{
"data": {
"id": "5",
"links": {
// The "related" endpoint is the URL to the query endpoint for this connection.
"related": "http://localhost:8989/v1/sql/5/queries/",
"self": "http://localhost:8989/v1/sql/5/"
},
"type": "sql"
},
"links": {
"self": "http://localhost:8989/v1/sql/5/"
},
"meta": {
"token": "eyJhbGciOiJIUzI1NiJ9.eyJhdWQiOiI1IiwiZXhwIjoxNjIwMjM1Mzc3LCJpYXQiOjE2MjAyMDY1NzcsImlzcyI6Im14cy1xdWVyeSJ9.2CJ8DsEPbGlvs2DrBUC6FJA64VMSU8kbX1U4FSu2-OY"
}
}
Missing or invalid payload:
Status: 403 Forbidden
Close an opened SQL connection
DELETE /v1/sql/:id
Response
Connection was closed:
Status: 204 No Content
Missing or invalid connection token:
Status: 403 Forbidden
Reconnect an opened SQL connection
POST /v1/sql/:id/reconnect
Reconnects an existing connection. This can also be used if the connection to
the backend server was lost due to a network error.
The connection will use the same credentials that were passed to the POST /v1/sql endpoint. The new connection will still have the same ID in the REST
API but will be treated as a new connection by the database. A reconnection
re-initializes the connection and resets the session state. Reconnections cannot
take place while a transaction is open.
Response
Reconnection was successful:
Status: 204 No Content
Reconnection failed or connection is already in use:
Status: 503 Service Unavailable
Missing or invalid connection token:
Status: 403 Forbidden
Execute SQL query
POST /v1/sql/:id/queries
The request body must be a JSON object with the value of the sql field set to
the SQL to be executed:
{
"sql": "SELECT * FROM test.t1",
"max_rows": 1000
}
The request body must be a JSON object consisting of the following fields:
sql
The SQL to be executed. If the SQL contain multiple statements, multiple
results are returned in the response body.
max_rows
The maximum number of rows returned in the response. By default this is 1000
rows. Setting the value to 0 means no limit. Any extra rows in the result
will be discarded.
By default, the complete result is returned in the response body. If the SQL
query returns more than one result, the results array will contain all the
results.
The results array can have three types of objects: resultsets, errors, and OK
responses.
A resultset consists of the data field with the result data stored as a two
dimensional array. The names of the fields are stored in an array in thefields field. These types of results will be returned for any operation that
returns rows (i.e. SELECT statements)
An error consists of an object with the errno field set to the MariaDB error
code, the message field set to the human-readable error message and thesqlstate field set to the current SQLSTATE of the connection.
{
"data": {
"attributes": {
"results": [
{
"errno": 1064,
"message": "You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'table t1' at line 1",
"sqlstate": "42000"
}
],
"sql": "select syntax_error from table t1"
},
"id": "4-1",
"type": "queries"
},
"links": {
"self": "http://localhost:8989/v1/sql/4/queries/4-1/"
}
}
An OK response is returned for any result that completes successfully but not
return rows (e.g. an INSERT or UPDATE statement). The affected_rows
field contains the number of rows affected by the operation, thelast_insert_id contains the auto-generated ID and the warnings field
contains the number of warnings raised by the operation.
It is also possible for the fields of the error response to be present in the
resultset response if the result ended with an error but still generated some
data. Usually this happens when query execution is interrupted but a partial
result was generated by the server.
If the API returns this response, the connection to the database server was
lost. The only valid action to take at this point is to close it with theDELETE /v1/sql/:id endpoint.
This page is licensed: CC BY-SA / Gnu FDL
MaxScale 21.06 Binlogrouter
NOTE: The binlog router delivered with 2.5 is completely new and is not
100% backward compatible with the binlog router delivered with earlier
versions of MaxScale.
The binlogrouter is a router that acts as a replication proxy for MariaDB
master-slave replication. The router connects to a master, retrieves the binary
logs and stores them locally. Slave servers can connect to MaxScale like they
would connect to a normal master server. If the master server goes down,
replication between MaxScale and the slaves can still continue up to the latest
point to which the binlogrouter replicated to. The master can be changed without
disconnecting the slaves and without them noticing that the master server has
changed. This allows for a more highly available replication setup.
In addition to the high availability benefits, the binlogrouter creates only one
connection to the master whereas with normal replication each individual slave
will create a separate connection. This reduces the amount of work the master
database has to do which can be significant if there are a large number of
replicating slaves.
Differences Between Old and New Binlogrouter Implementations
The binlogrouter in MaxScale 2.5.0 is a new and improved version of the original
binlogrouter found in older MaxScale versions. The new implementation contains
most of the features that were in the original binlogrouter but some of them
were removed as they were either redundant or not useful.
The major differences between the new and old binlog router are:
The list of servers where the database users for authentication are loaded
must be explicitly configured with the cluster, servers ortargets parameter. Alternatively, the users can be read from a file. See user_accounts_file
for more information.
The old binlog router had both server_id and master_id, the new onlyserver_id.
No need to configure heartbeat and burst interval anymore as they are
now automatically configured.
Traditional replication that uses the binary log name and file offset to
start the replication process is not supported.
Semi-sync support is not implemented.
Binlog encryption is not implemented.
Secondary masters are not supported, but the functionality provided byselect_master is roughly equivalent.
The new binlogrouter will write its own binlog files to prevent problems that
could happen when the master changes. This causes the binlog names to be
different in the binlogrouter when compared to the ones on the master.
The documentation for the binlogrouter in MaxScale 2.4 is provided for reference here.
Supported SQL Commands
The binlogrouter supports a subset of the SQL constructs that the MariaDB server
supports. The following commands are supported:
CHANGE MASTER TO
The binlogrouter supports the same syntax as the MariaDB server but only the
following values are allowed:
MASTER_HOST
MASTER_PORT
MASTER_USER
MASTER_PASSWORD
MASTER_USE_GTID
MASTER_SSL
MASTER_SSL_CA
MASTER_SSL_CAPATH
MASTER_SSL_CERT
MASTER_SSL_CRL
MASTER_SSL_CRLPATH
MASTER_SSL_KEY
MASTER_SSL_CIPHER
MASTER_SSL_VERIFY_SERVER_CERT
NOTE: MASTER_LOG_FILE and MASTER_LOG_POS are not supported
as binlogrouter only supports GTID based replication.
STOP SLAVE
Stops replication, same as MariaDB.
START SLAVE
Starts replication, same as MariaDB.
RESET SLAVE
Resets replication. Note that the RESET SLAVE ALL form that is supported
by MariaDB isn't supported by the binlogrouter.
SHOW BINARY LOGS
Lists the current files and their sizes. These will be different from the
ones listed by the original master where the binlogrouter is replicating
from.
PURGE { BINARY | MASTER } LOGS TO <filename>
Purges binary logs up to but not including the given file. The file name
must be one of the names shown in SHOW BINARY LOGS. The version of this
command which accepts a timestamp is not currently supported.
Automatic purging is supported using the configuration
parameter .
The files are purged in the order they were created. If a file to be purged
is detected to be in use, the purge stops. This means that the purge will
stop at the oldest file that a slave is still reading.
NOTE: You should still take precaution not to purge files that a potential
slave will need in the future. MaxScale can only detect that a file is
in active use when a slave is connected, and requesting events from it.
SHOW MASTER STATUS
Shows the name and position of the file to which the binlogrouter will write
the next replicated data. The name and position do not correspond to the
name and position in the master.
SHOW SLAVE STATUS
Shows the slave status information similar to what a normal MariaDB slave
server shows. Some of the values are replaced with constants values that
never change. The following values are not constant:
Slave_IO_State: Set to Waiting for master to send event when
replication is ongoing.
Master_Host
SELECT { Field } ...
The binlogrouter implements a small subset of the MariaDB SELECT syntax as
it is mainly used by the replicating slaves to query various parameters. If
a field queried by a client is not known to the binlogrouter, the value
will be returned back as-is. The following list of functions and variables
are understood by the binlogrouter and are replaced with actual values:
@@gtid_slave_pos, @@gtid_current_pos or @@gtid_binlog_pos: All of
these return the latest GTID replicated from the master.
SET
@@global.gtid_slave_pos: Set the position from which binlogrouter should
start replicating. E.g. SET @@global.gtid_slave_pos="0-1000-1234,1-1001-5678"
SHOW VARIABLES LIKE '...'
Shows variables matching a string. The LIKE operator in SHOW VARIABLES
is mandatory for the binlogrouter. This means that a plain SHOW VARIABLES
is not currently supported. In addition, the LIKE operator in
binlogrouter only supports exact matches.
Currently the only variables that are returned are gtid_slave_pos,gtid_current_pos and gtid_binlog_pos which return the current GTID
coordinates of the binlogrouter. In addition to these, the server_id
variable will return the configured server ID of the binlogrouter.
Configuration Parameters
The binlogrouter is configured similarly to how normal routers are configured in
MaxScale. It requires at least one listener where clients can connect to and one
server from which the database user information can be retrieved. An example
configuration can be found in the example section of this document.
datadir
Type: path
Mandatory: No
Dynamic: No
Default: /var/lib/maxscale/binlogs
Directory where binary log files are stored.
server_id
Type: number
Mandatory: No
Dynamic: No
Default: 1234
The server ID that MaxScale uses when connecting to the master and when serving
binary logs to the slaves.
Automatically select the master server to replicate from.
When this feature is enabled, the master which binlogrouter will replicate
from will be selected from the servers defined by a monitor cluster=TheMonitor.
Alternatively servers can be listed in servers. The servers should be monitored
by a monitor. Only servers with the Master status are used. If multiple master
servers are available, the first available master server will be used.
If a CHANGE MASTER TO command is received while select_master is on, the
command will be honored and select_master turned off until the next reboot.
This allows the Monitor to perform failover, and more importantly, switchover.
It also allows the user to manually redirect the Binlogrouter. The current
master is "sticky", meaning that the same master will be chosen on reboot.
NOTE: Do not use the mariadbmon parameter auto_rejoin if the monitor is
monitoring a binlogrouter. The binlogrouter does not support all the SQL
commands that the monitor will send and the rejoin will fail. This restriction
will be lifted in a future version.
The GTID the replication will start from, will be based on the latest replicated
GTID. If no GTID has been replicated, the router will start replication from the
start. Manual configuration of the GTID can be done by first configuring the
replication manually with CHANGE MASTER TO.
Duration after which a binary log file can be automatically removed.
The duration is measured from the last modification of the log file. Files are
purged in the order they were created. The automatic purge works in a similar
manner to PURGE BINARY LOGS TO <filename> in that it will stop the purge if
an eligible file is in active use, i.e. being read by a slave.
The minimum number of log files the automatic purge keeps. At least one file
is always kept.
New installation
Configure and start MaxScale.
If you have not configured select_master=true (automatic
master selection), issue a CHANGE MASTER TO command to binlogrouter.
mysql -u USER -pPASSWORD -h maxscale-IP -P binlog-PORT
CHANGE MASTER TO master_host="master-IP", master_port=master-PORT, master_user=USER, master_password="PASSWORD", master_use_gtid=slave_pos;
START SLAVE;
Redirect each slave to replicate from Binlogrouter
mysql -u USER -pPASSWORD -h slave-IP -P slave-PORT
STOP SLAVE;
CHANGE MASTER TO master_host="maxscale-IP", master_port=binlog-PORT,
master_user="USER", master_password="PASSWORD", master_use_gtid=slave_pos;
START SLAVE;
SHOW SLAVE STATUS \G
Upgrading to version 2.5
Binlogrouter does not read any of the data that a version prior to 2.5
has saved. By default binlogrouter will request the replication stream
from the blank state (from the start of time), which is basically meant
for new systems. If a system is live, the entire replication data probably
does not exist, and if it does, it is not necessary for binlogrouter to read
and store all the data.
Before you start
Note that binlogrouter only supports GTID based replication.
Make sure that the configured data directory for the new binlogrouter
is different from the old one, or move old data away.
See datadir.
If the master contains binlogs from the blank state, and there
is a large amount of data, consider purging old binlogs.
See .
Deployment
The method described here inflicts the least downtime. Assuming you have
configured version 2.5, and it is ready to go:
Redirect each slave that replicates from Binlogrouter to replicate from the
master.
mysql -u USER -pPASSWORD -h slave-IP -P slave-PORT
STOP SLAVE;
CHANGE MASTER TO master_host="master-IP", master_port=master-PORT,
master_user="USER", master_password="PASSWORD", master_use_gtid=slave_pos;
START SLAVE;
SHOW SLAVE STATUS \G
Stop the old version of MaxScale, and start the new one.
Verify routing functionality.
Issue a CHANGE MASTER TO command, or use select_master.
mysql -u USER -pPASSWORD -h maxscale-IP -P binlog-PORT
CHANGE MASTER TO master_host="master-IP", master_port=master-PORT,
master_user=USER,master_password="PASSWORD", master_use_gtid=slave_pos;
Run maxctrl list servers. Make sure all your servers are accounted for.
Pick the lowest gtid state (e.g. 0-1000-1234,1-1001-5678) on display and
issue this command to Binlogrouter:
STOP SLAVE
SET @@global.gtid_slave_pos = "0-1000-1234,1-1001-5678";
START SLAVE
NOTE: Even with select_master=true you have to set @@global.gtid_slave_pos
if any binlog files have been purged on the master. The server will only stream
from the start of time if the first binlog file is present.
See select_master.
Redirect each slave to replicate from Binlogrouter.
mysql -u USER -pPASSWORD -h slave-IP -P slave-PORT
STOP SLAVE;
CHANGE MASTER TO master_host="maxscale-IP", master_port=binlog-PORT,
master_user="USER", master_password="PASSWORD",
master_use_gtid=slave_pos;
START SLAVE;
SHOW SLAVE STATUS \G
Galera cluster
When replicating from a Galera cluster, select_master must be
set to true, and the servers must be monitored by the Galera Monitor.
Configuring binlogrouter is the same as described above.
The Galera cluster must be configured to use .
The MariaDB version must be 10.5.1 or higher.
The required GTID related server settings for MariaDB/Galera to work with
Binlogrouter are listed here:
[mariadb]
log_slave_updates = ON
log_bin = pinloki # binlog file base name. Must be the same on all servers
gtid_domain_id = 1001 # Must be different for each galera server
binlog_format = ROW
[galera]
wsrep_on = ON
wsrep_gtid_mode = ON
wsrep_gtid_domain_id = 42 # Must be the same for all servers
Example
The following is a small configuration file with automatic master selection.
With it, the service will accept connections on port 3306.
The KafkaImporter module reads messages from Kafka and streams them into a
MariaDB server. The messages are inserted into a table designated by either the
topic name or the message key (see table_name_in for
details). The table will be automatically created with the following SQL:
CREATE TABLE IF NOT EXISTS my_table (
data LONGTEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL,
id VARCHAR(1024) AS (JSON_EXTRACT(data, '$._id')) UNIQUE KEY,
CONSTRAINT data_is_json CHECK(JSON_VALID(data))
);
The payload of the message is inserted into the data field from which the id
field is calculated. The payload must be a valid JSON object and it must either
contain a unique _id field or it must not exist or the value must be a JSON
null. This is similar to the MongoDB document format where the _id field is
the primary key of the document collection.
If a message is read from Kafka and the insertion into the table fails due to a
violation of one of the constraints, the message is ignored. Similarly, messages
with duplicate _id value are also ignored: this is done to avoid inserting the
same document multiple times whenever the connection to either Kafka or MariaDB
is lost.
The limitations on the data can be removed by either creating the table before
the KafkaImporter is started, in which case the CREATE TABLE IF NOT EXISTS
does nothing, or by altering the structure of the existing table. The minimum
requirement that must be met is that the table contains the data field to
which string values can be inserted into.
The database server where the data is inserted is chosen from the set of servers
available to the service. The first server labeled as the Master with the best
rank will be chosen. This means that a monitor must be configured for the
MariaDB server where the data is to be inserted.
In MaxScale versions 21.06.18, 22.08.15, 23.02.12, 23.08.8, 24.02.4 and 24.08.2
the _id field is not required to be present. Older versions of MaxScale used
the following SQL where the _id field was mandatory:
CREATE TABLE IF NOT EXISTS my_table (
data LONGTEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL,
id VARCHAR(1024) AS (JSON_EXTRACT(data, '$._id')) UNIQUE KEY,
CONSTRAINT data_is_json CHECK(JSON_VALID(data)),
CONSTRAINT id_is_not_null CHECK(JSON_EXTRACT(data, '$._id') IS NOT NULL)
);
Required Grants
The user defined by the user parameter of the service must have INSERT andCREATE privileges on all tables that are created.
Parameters
bootstrap_servers
Type: string
Mandatory: Yes
Dynamic: Yes
The list of Kafka brokers as a CSV list in host:port format.
topics
Type: stringlist
Mandatory: Yes
Dynamic: Yes
The comma separated list of topics to subscribe to.
batch_size
Type: count
Mandatory: No
Dynamic: Yes
Default: 100
Maximum number of uncommitted records. The KafkaImporter will buffer records
into batches and commit them once either enough records are gathered (controlled
by this parameter) or when the KafkaImporter goes idle. Any uncommitted records
will be read again if a reconnection to either Kafka or MariaDB occurs.
The Kafka message part that is used to locate the table to insert the data into.
Enumeration Values:
topic: The topic named is used as the fully qualified table name.
key: The message key is used as the fully qualified table name. If the Kafka
message does not have a key, the message is ignored.
For example, all messages with a fully qualified table name of my_db.my_table
will be inserted into the table my_table located in the my_db database. If
the table or database names have special characters that must be escaped to make
them valid identifiers, the name must also contain those escape characters. For
example, to insert into a table named my table in the database my database,
the name would be:
The mirror router is designed for data consistency and database behavior
verification during system upgrades. It allows statement duplication to multiple
servers in a manner similar to that of the Tee filter with exporting of collected query metrics.
For each executed query the router exports a JSON object that describes the
query results and has the following fields:
Key
Description
query
The executed SQL if an SQL statement was executed
command
The SQL command
session
The connection ID of the session that executed the query
The objects in the results array describe an individual query result and have
the following fields:
Key
Description
target
The target where the query was executed
checksum
The CRC32 checksum of the result
rows
Number of returned rows
Configuration Parameters
main
Type: target
Mandatory: Yes
Dynamic: Yes
The main target from which results are returned to the client. This is a
mandatory parameter and must define one of the targets configured in thetargets parameter of the service.
If the connection to the main target cannot be created or is lost mid-session,
the client connection will be closed. Connection failures to other targets are
not fatal errors and any open connections to them will be closed. The router
does not create new connections after the initial connections are created.
exporter
Type: enum
Mandatory: Yes
Dynamic: Yes
Values: log, file, kafka
The exporter where the data is exported. This is a mandatory parameter. Possible
values are:
log
Exports metrics to MaxScale log on INFO level. No configuration parameters.
file
Exports metrics to a file. Configured with the parameter.
kafka
Exports metrics to a Kafka broker. Configured with the and
parameters.
file
Type: string
Default: No default value
Mandatory: No
Dynamic: Yes
The output file where the metrics will be written. The file must be writable by
the user that is running MaxScale, usually the maxscale user.
When the file parameter is altered at runtime, the old file is closed before
the new file is opened. This makes it a convenient way of rotating the file
where the metrics are exported. Note that the file name alteration must change
the value for it to take effect.
This is a mandatory parameter when configured with exporter=file.
kafka_broker
Type: string
Default: No default value
Mandatory: No
Dynamic: Yes
The Kafka broker list. Must be given as a comma-separated list of broker hosts
with optional ports in host:port format.
This is a mandatory parameter when configured with exporter=kafka.
kafka_topic
Type: string
Default: No default value
Mandatory: No
Dynamic: Yes
The kafka topic where the metrics are sent.
This is a mandatory parameter when configured with exporter=kafka.
on_error
Type: enum
Default: ignore
Mandatory: No
Dynamic: Yes
Values: ignore, close
What to do when a backend network connection fails. Accepted values are:
ignore
Ignore the failing backend if it's not the backend that the main parameter
points to.
close
Close the client connection when the first backend fails.
This parameter was added in MaxScale 6.0. Older versions always ignored
failing backends.
report
Type: enum
Default: always
Mandatory: No
Dynamic: Yes
Values: always, on_conflict
When to report the result of the queries. Accepted values are:
always
Always report the result for all queries.
on_conflict
Only report when one or more backends returns a conflicting result.
This parameter was added in MaxScale 6.0. Older versions always reported the
result.
Contents of non-SQL statements are not added to the exported metrics.
Data synchronization in dynamic environments (e.g. when replication is in use)
is not guaranteed. This means that result mismatches can be reported when the
data is only eventually consistent.
This page is licensed: CC BY-SA / Gnu FDL
MaxScale 21.06 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.
binlog_format=row
binlog_row_image=full
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 Replication Proxy service
[replication-service]
type=service
router=binlogrouter
server_id=4000
master_id=3000
filestem=binlog
user=maxuser
password=maxpwd
# The Avro conversion service
[avro-service]
type=service
router=avrorouter
source=replication-service
filestem=binlog
start_index=15
# The listener for the replication-service
[replication-listener]
type=listener
service=replication-service
port=3306
# The client listener for the avro-service
[avro-listener]
type=listener
service=avro-service
protocol=CDC
port=4001
The source parameter in the avro-service points to the replication-service
we defined before. This service will be the data source for the avrorouter. 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.
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)
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 Binlogrouter 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.
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.
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.
MaxScale uses systemd for managing the process. This means that normalsystemctl commands can be used to start and stop MaxScale. To start MaxScale,
use systemctl start maxscale. To stop it, use systemctl stop maxscale.
The systemd service file for MaxScale is located in/lib/systemd/system/maxscale.service.
Additional Options for MaxScale
Additional command line options and other systemd configuration options
can be given to MariaDB MaxScale by creating a drop-in file for the
service unit file. You can do this with the systemctl edit maxscale.service
command. For more information about systemd drop-in
files, refer to the systemctl man page
and the 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.
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 logs
As there currently is only the maxscale log, that is the only one that will be
rotated.
This may be integrated into the Linux logrotate mechanism by adding a
configuration file to the /etc/logrotate.d directory. If we assume we want to
rotate the log files once per month and wish to keep 5 log files worth of
history, the configuration file would look as follows.
/var/log/maxscale/maxscale.log {
monthly
rotate 5
missingok
nocompress
sharedscripts
postrotate
\# run if maxscale is running
if test -n "`ps acx|grep maxscale`"; then
/usr/bin/maxctrl rotate logs
fi
endscript
}
MariaDB MaxScale will also rotate all of its log files if it receives the USR1
signal. Using this the logrotate configuration script can be rewritten as
In older versions MaxScale renamed the log file, behavior which is not fully
compliant with the assumptions of logrotate and may lead to issues, depending on
the used logrotate configuration file. From version 2.1 onward, MaxScale will
not itself rename the log file, but when the log is rotated, MaxScale will
simply close and reopen the same log file. That will make the behavior fully
compliant with logrotate.
Taking Objects Temporarily Out of Use
Putting Servers into Maintenance
MariaDB MaxScale supports the concept of maintenance mode for servers within a
cluster. This allows for planned, temporary removal of a database from the
cluster without the need to change the MariaDB MaxScale configuration.
maxctrl set server db-server-3 maintenance
To achieve this, you can use the set server command in maxctrl to set the
maintenance mode flag for the server. This may be done interactively within
maxctrl or by passing the command on the command line.
This will cause MariaDB MaxScale to stop routing any new requests to the server,
however if there are currently requests executing on the server these will not
be interrupted. Connections to servers in maintenance mode are closed as soon as
the next request arrives. To close them immediately, use the --force option
for maxctrl set server.
maxctrl clear server db-server-3 maintenance
Clearing the maintenance mode for a server will bring it back into use. If
multiple MariaDB MaxScale instances are configured to use the node then
maintenance mode must be set within each MariaDB MaxScale instance.
Stopping and Starting Services
maxctrl stop service db-service
Services can be stopped to temporarily halt their use. Stopping a service will
cause it to stop accepting new connections until it is started. New connections
are not refused if the service is stopped and are queued instead. This means
that connecting clients will wait until the service is started again.
maxctrl start service db-service
Starting a service will cause it to accept all queued connections that were
created while it was stopped.
Stopping and Starting Monitors
maxctrl stop monitor db-monitor
Stopping a monitor will cause it to stop monitoring the state of the servers
assigned to it. This is useful when the state of the servers is assigned
manually with maxctrl set server.
maxctrl start monitor db-monitor
Starting a monitor will make it resume monitoring of the servers. Any manually
assigned states will be overwritten by the monitor.
Runtime Configuration Modification
The MaxScale configuration can be changed at runtime by using the create,alter and destroy commands of maxctrl. These commands either create,
modify or destroy objects (servers, services, monitors etc.) inside
MaxScale. The exact syntax for each of the commands and any additional options
that they take can be seen with maxctrl --help <command>.
Not all parameters can be modified at runtime. Refer to the module documentation
for more information on which parameters can be modified at runtime. If a
parameter cannot be modified at runtime, the object can be destroyed and
recreated in order to change it.
All runtime changes are persisted in files stored by default in/var/lib/maxscale/maxscale.cnf.d/. This means that any changes done at runtime
persist through restarts. Any changes done to objects in the main configuration
file are ignored if a persisted entry is found for it.
For example, if the address of a server is modified with maxctrl alter server db-server-1 address 192.168.0.100, the file/var/lib/maxscale/maxscale.cnf.d/db-server-1.cnf is created with the complete
configuration for the object. To remove all runtime changes for all objects,
remove all files found in /var/lib/maxscale/maxscale.cnf.d.
Core Parameter Configuration
Modify global MaxScale parameters:
maxctrl alter maxscale auth_connect_timeout 5s
Some global parameters cannot be modified at runtime. Refer to the Configuration Guide for a full list
of parameters that can be modified at runtime.
Managing Servers
Create a new server
maxctrl create server db-server-1 192.168.0.100 3306
Modify a Server
maxctrl alter server db-server-1 port 3307
Destroy a Server
maxctrl destroy server db-server-1
A server can only be destroyed if it is not used by any services or monitors. To
automatically remove the server from the services and monitors that use it, use
the --force flag.
Drain a Server
maxctrl set server db-server-1 drain
When a server is set into the drain state, no new connections to it are
created. Unlike to the maintenance state which immediately stops all new
requests and closes all connections if used with the --force option, thedrain state allows existing connections to continue routing requests to them
in order to be gracefully closed once the client disconnects.
To remove the drain state, use clear server command:
maxctrl clear server db-server-1 drain
Servers with the Master state cannot be drained. To drain them, first perform
a switchover to another node and then drain the server.
maxctrl alter monitor db-monitor monitor_interval 1000
Add Server to a Monitor
maxctrl link monitor db-monitor db-server-1
Remove a Server from a Monitor
maxctrl unlink monitor db-monitor db-server-1
Destroy a Monitor
maxctrl destroy monitor db-monitor
A monitor can only be destroyed if it is not monitoring any servers. To
automatically remove the servers from the monitor, use the --force flag.
Managing Services
Create a New Service
maxctrl create service db-service readwritesplit user=db-user password=db-password
Modify a Service
maxctrl alter service db-service user new-db-user
Add Servers to a Service
maxctrl link service db-service db-server1
Any servers added to services will only be used by new sessions. Existing
sessions will use the servers that were available when they connected.
Remove Servers from a Service
maxctrl unlink service db-service db-server1
Similarly to adding servers, removing servers from a service will only affect
new sessions. Existing sessions keep using the servers even if they are removed
from a service.
Change the Filters of a Service
maxctrl alter service-filters my-regexfilter my-qlafilter
The order of the filters is significant: the first filter will be the first to
receive the query. The new set of filters will only be used by new
sessions. Existing sessions will keep using the filters that were configured
when they connected.
Destroy a Service
maxctrl destroy service db-service
The service can only be destroyed if it uses no servers or clusters and has no
listeners associated with it. To force destruction of a service even if it does
use servers or has listeners, use the --force flag. This will also destroy any
listeners associated with the service.
Destroying a listener will close the network socket and stop it from accepting
new connections. Existing connections that were created through it will keep
displaying it as the originating listener.
Listeners cannot be moved from one service to another. In order to do this, the
listener must be destroyed and then recreated with the new service.
Managing MaxCtrl and REST API Users
MaxCtrl uses the same credentials as the MaxScale REST API. These users can be
managed via MaxCtrl.
Create a New MaxCtrl User
maxctrl create user basic-user basic-password
By default new users are only allowed to read data. To make the account an
administrative account, add the --type=admin option to the command:
maxctrl create user admin-user admin-password --type=admin
Administrative accounts are allowed to use all MaxCtrl commands and modify any
parts of MaxScale.
Change the Password of an Existing User
maxctrl alter user admin-user new-admin-password
Remove a User
maxctrl destroy user basic-user
This page is licensed: CC BY-SA / Gnu FDL
MaxScale 21.06 Upgrading MariaDB MaxScale
For more information about what has changed, please refer to the ChangeLog and to the release notes.
Before starting the upgrade, any existing configuration files should
be backed up.
NOTE MaxScale 6.4 was renamed to 21.06 in May 2024. Thus, what would have
been released as 6.4.16 in June, was released as 21.06.16. The purpose of this
change is to make the versioning scheme used by all MaxScale series
identical. 21.06 denotes the year and month when the first 6 release was made.
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.
The default value of threads was changed to auto.
The following deprecated core parameters have been removed:
thread_stack_size
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.
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.
The deprecated MaxAdmin interface has been removed in 2.5.0 in favor of the REST
API and the MaxCtrl command line client. The cli and maxscaled modules can
no longer be used.
The credentials used by services now require additional grants. For a full list
of required grants, refer to the .
The settings detect_stale_master, detect_standalone_master anddetect_stale_slave are replaced by master_conditions andslave_conditions. The old settings may still be used, but will be removed in
a later version.
The encrypted passwords feature has been updated to be more secure. Users are
recommended to generate a new encryption key and re-encrypt their passwords
using the maxkeys and maxpasswd utilities. Old passwords still work.
The default state of servers in 2.4 was Running and in 2.5 it is nowDown. This was done to prevent newly added servers from being accidentally
used before they were monitored.
It is now mandatory to specify in the configuration what version the
monitored Columnstore cluster is.
Please see the
for details.
The binlog router delivered with MaxScale 2.5 is completely new and
not 100% backward compatible with the binlog router delivered with
earlier MaxScale versions. If you use the binlog router, carefully
assess whether the functionality provided by the new one fulfills
your requirements, before upgrading MaxScale.
The tee filter parameter service has been deprecated in favor of the target
parameter. All usages of service can be replaced with target.
Section and object names starting with @@ are now reserved for
internal use by MaxScale.
In case such names have been used, they must manually be changed
in all configuration files of MaxScale, before MaxScale 2.4 is started.
Those files are:
The main configuration file; typically /etc/maxscale.cnf.
All nested configuration files; typically /etc/maxscale.cnf.d/*.
All dynamic configuration files; typically /var/lib/maxscale/maxscale.cnd.d/*.
Whitespace in section names that was deprecated in MaxScale 2.2 will now be
rejected, which will cause the startup of MaxScale to fail.
To prevent that, section names like
must be changed, for instance, to
Durations can now be specified using one of the suffixes h, m, s
and ms for specifying durations in hours, minutes, seconds and
milliseconds, respectively.
Not providing an explicit unit has been deprecated in MaxScale 2.4,
so it is adviseable to add suffixes to durations. For instance,
MaxScale 2.4 will use a SHA2-512 hash for new admin user passwords. To upgrade a
user to use the better hashing algorithm, either recreate the user or use themaxctrl alter user command.
The following settings have been removed and cause a startup error
if defined:
mysql51_replication
multimaster
allow_cluster_recovery.
If multiple masters are available for a readwritesplit service, the one with
the lowest connection count is selected.
If a master server is placed into maintenance mode, all open transactions are
allowed to gracefully finish before the session is closed. To forcefully close
the connections, use the --force option for maxctrl set server.
The lazy_connect
Starting with MaxScale 2.3.0 up to 40% of the memory can be used for
caching parsed queries. The most noticeable change is that it improves
performance in almost all cases where queries need to be parsed. Most of
the time this happens when the readwritesplit router or filters are used.
The amount of memory that MaxScale uses can be controlled with thequery_classifier_cache_size parameter. For example, to limit the total
memory to 1GB, add query_classifier_cache_size=1G to your
configuration. To disable it, set the value to 0.
In addition to the aforementioned query classifier caching, the
readwritesplit session command history is enabled by default in 2.3 but is
limited to a maximum of 50 commands after which the history is
disabled. This is unlikely to show in any metrics but it contributes to
the increased memory foorprint of MaxScale.
All unknown parameters are now treated as errors. Check your configuration for
errors if MaxScale fails to start after upgrading to 2.3.1.
In the configuration file, passwords for monitors and services should be
specified using password; the support for the deprecatedpasswd will be removed in the future. That is, the following
should be changed to
Authenticator options are now only used with listeners.
The file format for the administrative users used by MaxScale has been
changed. Old style files are automatically upgraded and a backup of the old file is
stored in /var/lib/maxscale/passwd.backup.
Modules may now use a built-in regular expression string parameter type instead
of a normal string when accepting patterns. The modules that use the new regex
parameter type are qlafilter and tee. When inputting pattern, enclose the
string in slashes, e.g. match=/^select/ defines the pattern ^select.
Binlog server automatically accepts GTID connection from MariaDB 10 slave servers
by saving all incoming GTIDs into a SQLite map database.
In the 2.2.1 beta version MaxCtrl was in its own package whereas in 2.2.2
it is in the main maxscale package. If you have a previous installation
of MaxCtrl, please remove it before upgrading to MaxScale 2.2.2.
MaxScale 2.1.2 added support for IPv6 addresses. The default interface that listeners bind to
was changed from the IPv4 address 0.0.0.0 to the IPv6 address ::. To bind to the old IPv4 address,
add address=0.0.0.0 to the listener definition.
Starting with MaxScale 2.1, any changes made with the newly added
will be persisted in a configuration file. These files are located in /var/lib/maxscale/maxscale.cnf.d/.
The name of the log file was changed from maxscaleN.log to maxscale.log. The
default location for the log file is /var/log/maxscale/maxscale.log.
Rotating the log files will cause MaxScale to reopen the file instead of
renaming them. This makes the MaxScale logging facility logrotate compatible.
The disable_sescmd_history option is now enabled by default. This means that
slaves will not be recovered mid-session even if a replacement slave is
available. To enable the legacy behavior, add the disable_sescmd_history=true
parameter to the service definition.
The MariaDB session state is reset in MaxScale 2.1 for persistent
connections. This means that any modifications to the session state (default
database, user variable etc.) will not survive if the connection is put into the
connection pool. For most users, this is the expected behavior.
The location of the MariaDB user data cache was moved from/var/cache/maxscale/<Service> to /var/cache/maxscale/<Service>/<Listener>.
Galeramon will assign the master status only to the node which has a_wsrep_local_index_ value of 0. This will guarantee consistent writes with
multiple MaxScales but it also causes slower changes of the master node.
To enable the legacy behavior, add root_node_as_master=false to the Galera
monitor configuration.
The default editing mode was changed from vim to emacs mode. To start
maxadmin in the legacy mode, use the -i option.
The default way the communication between MaxAdmin and MariaDB MaxScale is
handled has been changed from an internet socket to a Unix domain socket.
The former alternative is still available but has been deprecated.
If no arguments are given to MaxAdmin, it will attempt to connect to
MariaDB MaxScale using a Unix domain socket. After the upgrade you will
need to provide at least one internet socket related flag - -h, -P,-u or -p - to force MaxAdmin to use the internet socket approach.
E.g.
The MySQL Monitor now assigns the stale state to the master server by default.
In addition to this, the slave servers receive the stale slave state when they
lose the connection to the master. This should not cause changes in behavior
but the output of MaxAdmin will show new states when replication is broken.
The service users now also need SELECT privileges on mysql.tables_priv. This is
required for the resolution of table level grants. To grant SELECT privileges
for the service user, replace the user and hostname in the following example.
MaxScale 1.4 upgrades the used password encryption algorithms to more secure ones.
This requires that the password files are recreated with the maxkeys tool.
For more information about how to do this, please read the installation guide:
The SSL configuration parameters are now a part of the listeners. If a service
used the old style SSL configuration parameters, the values should be moved to
the listener which is associated with that service.
Here is an example of an old style configuration.
And here is the new, 1.4 compatible configuration style.
Please also note that the enabled SSL mode is no longer supported due to
the inherent security issues with allowing SSL and non-SSL connections on
the same port. In addition to this, SSLv3 is no longer supported due to
vulnerabilities found in it.
The master server details are now provided with a master.ini file located in
the binlog directory and it can be changed using a CHANGE MASTER TO command issued
via a MySQL connection to MaxScale.
This file, properly filled, is now mandatory and without it the binlog router
cannot connect to the master database.
Before starting binlog router after MaxScale 1.3 upgrade, please add relevant
information to master.ini, example:
Additionally, the option servers=masterdb in the service definition is no
longer required.
This document describes upgrading MaxScale from version 1.1.1 to 1.2 and
the major differences in the new version compared to the old version. The
major changes can be found in the Changelog.txt file in the installation
directory and the official release notes in the ReleaseNotes.txt file.
Upgrading MaxScale will copy the MaxScale.cnf file in/usr/local/mariadb-maxscale/etc/ to /etc/ and renamed to maxscale.cnf.
Binary log files are not automatically copied and should be manually moved
from /usr/local/mariadb-maxscale to /var/lib/maxscale/.
MaxScale 1.2 follows the and
installs to /usr/ and /var/ subfolders. Here are the major changes and
file locations.
Configuration files are located in /etc/ and use lowercase letters: /etc/maxscale.cnf
Binary files are in /usr/bin/
Libraries and modules are in /usr/lib64/maxscale/
MaxScale can run as a non-root user with the 1.2 version. RPM and DEB
packages install the maxscale user and maxscale group which are used
by the init scripts and systemd configuration files. If you are installing
from a binary tarball, you can run the postinst script included in it to
manually create these groups.
This document describes upgrading MaxScale from version 1.0.5 to 1.1.0 and
the major differences in the new version compared to the old version. The
major changes can be found in the Changelog.txt file in the installation
directory and the official release notes in the ReleaseNotes.txt file.
If you are installing MaxScale from a RPM package, we recommend you back
up your configuration and log files and that you remove the old installation
of MaxScale completely. If you choose to upgrade MaxScale instead of removing
it and re-installing it afterwards, the init scripts in /etc/init.d folder
will be missing. This is due to the RPM packaging system but the script can
be re-installed by running the postinst script found in the/usr/local/mariadb-maxscale folder.
The 1.1.0 version of MaxScale installs into /usr/local/mariadb-maxscale
instead of /usr/local/skysql/maxscale. This will cause external references
to MaxScale's home directory to stop working so remember to update all
paths with the new version.
The MaxAdmin client's default password in MaxScale 1.1.0 is mariadb
instead of skysql.
The Galera Monitor is a monitoring module for MaxScale that monitors a Galera
cluster. It detects whether nodes are a part of the cluster and if they are in
sync with the rest of the cluster. It can also assign master and slave roles
inside MaxScale, allowing Galera clusters to be used with modules designed for
traditional master-slave clusters.
By default, the Galera Monitor will choose the node with the lowestwsrep_local_index value as the master. This will mean that two MaxScales
running on different servers will choose the same server as the master.
WSREP Variables and Their Effects
The following WSREP variables are inspected by galeramon to see whether a node is
usable. If the node is not usable, it loses the Master and Slave labels and
will be in the Running state.
If wsrep_ready=0, the WSREP system is not yet ready and the Galera node
cannot accept queries.
If wsrep_desync=1 is set, the node is desynced and is not participating in
the Galera replication.
If wsrep_reject_queries=[ALL|ALL_KILL] is set, queries are refused and the
node is unusable.
With wsrep_sst_donor_rejects_queries=1, donor nodes reject
queries. Galeramon treats this the same as if wsrep_reject_queries=ALL was
set.
If wsrep_local_state is not 4 (or 2 with available_when_donor=true), the
node is not in the correct state and is not used.
Galera clusters and slaves replicating from it
MaxScale 2.4.0 added support for slaves replicating off of Galera nodes. If a
non-Galera server monitored by galeramon is replicating from a Galera node also
monitored by galeramon, it will be assigned the Slave, Running status as long
as the replication works. This allows read-scaleout with Galera servers without
increasing the size of the Galera cluster.
Required Grants
The Galera Monitor requires the REPLICA MONITOR grant to work:
CREATE USER 'maxscale'@'maxscalehost' IDENTIFIED BY 'maxscale-password';
GRANT REPLICA MONITOR ON *.* TO 'maxscale-user'@'maxscalehost';
With MariaDB Server 10.4 and earlier, REPLICATION CLIENT is required instead.
GRANT REPLICATION CLIENT ON *.* TO 'maxscale-user'@'maxscalehost';
If set_donor_nodes is configured, the SUPER grant is required:
GRANT SUPER ON *.* TO 'maxscale'@'maxscalehost';
Configuration
A minimal configuration for a monitor requires a set of servers for monitoring
and a username and a password to connect to these servers. The user requires the
REPLICATION CLIENT privilege to successfully monitor the state of the servers.
For a list of optional parameters that all monitors support, read the Monitor Common document.
Galera Monitor optional parameters
These are optional parameters specific to the Galera Monitor.
disable_master_failback
Type: boolean
Default: false
Dynamic: Yes
If a node marked as master inside MaxScale happens to fail and the master status
is assigned to another node MaxScale will normally return the master status to
the original node after it comes back up. With this option enabled, if the
master status is assigned to a new node it will not be reassigned to the
original node for as long as the new master node is running. In this case theMaster Stickiness status bit is set which will be visible in themaxctrl list servers output.
available_when_donor
Type: boolean
Default: false
Dynamic: Yes
This option allows Galera nodes to be used normally when they are donors in an
SST operation when the SST method is non-blocking
(e.g. wsrep_sst_method=mariadb-backup).
Normally when an SST is performed, both participating nodes lose their Synced,
Master or Slave statuses. When this option is enabled, the donor is treated as
if it was a normal member of the cluster (i.e. wsrep_local_state = 4). This is
especially useful if the cluster drops down to one node and an SST is required
to increase the cluster size.
The current list of non-blocking SST
methods are xtrabackup, xtrabackup-v2 and mariadb-backup. Read the
documentation for more details.
disable_master_role_setting
Type: boolean
Default: false
Dynamic: Yes
This disables the assignment of master and slave roles to the Galera cluster
nodes. If this option is enabled, Synced is the only status assigned by this
monitor.
use_priority
Type: boolean
Default: false
Dynamic: Yes
Enable interaction with server priorities. This will allow the monitor to
deterministically pick the write node for the monitored Galera cluster and will
allow for controlled node replacement.
root_node_as_master
Type: boolean
Default: false
Dynamic: Yes
This option controls whether the write master Galera node requires a_wsrep_local_index_ value of 0. This option was introduced in MaxScale 2.1.0 and
it is disabled by default in versions 2.1.5 and newer. In versions 2.1.4 and
older, the option was enabled by default.
A Galera cluster will always have a node which has a wsrep_local_index value
of 0. Based on this information, multiple MaxScale instances can always pick the
same node for writes.
If the root_node_as_master option is disabled for galeramon, the node with the
lowest index will always be chosen as the master. If it is enabled, only the
node with a wsrep_local_index value of 0 can be chosen as the master.
This parameter can work with disable_master_failback but using them together
is not advisable: the intention of root_node_as_master is to make sure that
all MaxScale instances that are configured to use the same Galera cluster will
send writes to the same node. If disable_master_failback is enabled, this is
no longer true if the Galera cluster reorganizes itself in a way that a
different node gets the node index 0, writes would still be going to the old
node that previously had the node index 0. A restart of one of the MaxScales or
a new MaxScale joining the cluster will cause writes to be sent to the wrong
node, thus resulting in an increasing the rate of deadlock errors and
sub-optimal performance.
set_donor_nodes
Type: boolean
Default: false
Dynamic: Yes
This option controls whether the global variable wsrep_sst_donor should be set
in each cluster node with slave' status.
The variable contains a list of slave servers, automatically sorted, with
possible master candidates at its end.
The sorting is based either on wsrep_local_index or node server priority
depending on the value of use_priority option.
If no server has priority defined the sorting switches to wsrep_local_index.
Node names are collected by fetching the result of the variable wsrep_node_name.
Example of variable being set in all slave nodes, assuming three nodes:
SET GLOBAL wsrep_sst_donor = "galera001,galera000"
Note:
in order to set the global variable wsrep_sst_donor, proper privileges are
required for the monitor user that connects to cluster nodes.
This option is disabled by default and was introduced in MaxScale 2.1.0.
Interaction with Server Priorities
If the use_priority option is set and a server is configured with thepriority=<int> parameter, galeramon will use that as the basis on which the
master node is chosen. This requires the disable_master_role_setting to be
undefined or disabled. The server with the lowest positive value of priority
will be chosen as the master node when a replacement Galera node is promoted to
a master server inside MaxScale. If all candidate servers have the same
priority, the order of the servers in the servers parameter dictates which is
chosen as the master.
Nodes with a negative value (priority < 0) will never be chosen as the
master. This allows you to mark some servers as permanent slaves by assigning a
non-positive value into priority. Nodes with the default priority of 0 are
only selected if no nodes with higher priority are present and the normal node
selection rules apply to them (i.e. selection is based on wsrep_local_index).
In this example node-1 is always used as the master if available. If node-1
is not available, then the next node with the highest priority rank is used. In
this case it would be node-3. If both node-1 and node-3 were down, thennode-2 would be used. Because node-4 has a value of -1 in priority, it
will never be the master. Nodes without priority parameter are considered as
having a priority of 0 and will be used only if all nodes with a positive_priority_ value are not available.
With priority ranks you can control the order in which MaxScale chooses the
master node. This will allow for a controlled failure and replacement of nodes.
. It also reduces the
overall load on the system when connections are rapidly opened and closed.
Transaction replays now have a limit on how many times a replay is
attempted. The default values is five attempts and is controlled by thetransaction_replay_attempts parameter.
If transaction replay is enabled and a deadlock occurs (SQLSTATE 40XXX), the
transaction is automatically retried.
. If you are using custom modules, please make sure they are in this directory before starting MaxScale.
Log files are in the var/log/maxscale/ folder
MaxScale's PID file is located in /var/run/maxscale/maxscale.pid
Data files and other persistent files are in /var/lib/maxscale/
maxctrl alter service My-Service connection_keepalive 30000
maxctrl alter service My-Service connection_keepalive 30000ms
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.
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.
The CCR filter has no mandatory parameters.
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.
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_.
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.
Type:
Mandatory: No
Dynamic: No
Values: ignorecase
Regular expression options for match and ignore.
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.
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.
This page is licensed: CC BY-SA / Gnu FDL
MaxScale 21.06 Insert Stream Filter
This filter was introduced in MariaDB MaxScale 2.1.
MariaDB MaxScale 21.06 Getting Started
This is your starting point for MariaDB MaxScale 21.06. Find essential guides for installation, learn the basics of configuration, and explore tutorials to get up and running quickly.
MaxScale 21.06 Monitors
MariaDB MaxScale 21.06 Protocols
Protocol modules interpret client-server communication for MaxScale. This section covers the available modules, including the standard MariaDB protocol, NoSQL, and Change Data Capture (CDC).
MariaDB MaxScale 21.06 Reference
Access detailed technical information for MariaDB MaxScale 21.06. This section is your complete reference for configuration settings, command-line tools, hint syntax, and more.
MaxScale 21.06 Hint Syntax
Refer to the Hintfilter documentation for the
MaxScale hint syntax.
This page is licensed: CC BY-SA / Gnu FDL
MariaDB MaxScale 21.06 Routers
Routers are the core of a MaxScale service, intelligently directing database traffic. This version adds the KafkaCDC router and enhances others with features like causal reads.
MariaDB MaxScale 21.06 Tutorials
Get hands-on experience with MariaDB MaxScale 21.06. These tutorials provide step-by-step instructions for common tasks like setting up read-write splitting, failover, and sharding.
MariaDB MaxScale 21.06 Upgrading
Before upgrading to MariaDB MaxScale 21.06, it is critical to review the changes. This guide outlines new features, altered parameters, and deprecated functionality to ensure a smooth transition.
MaxScale MariaDB Monitor Usage
Learn to use the MariaDB Monitor to automate cluster management. This guide covers how to configure server monitoring, automatic failover, switchover, and other HA features.
Aurora Monitor
This monitor is designed specifically for Amazon Aurora clusters. It identifies the writer and reader instances, allowing MaxScale to route queries and manage high availability in an AWS environment.
MariaDB MaxScale 21.06 Filters
Filters are powerful modules that intercept and process database traffic in MaxScale. Use them to log, transform, block, or reroute queries to add control, security, and monitoring.
MariaDB MaxScale 21.06 Design Documents
Explore the internal architecture of MariaDB MaxScale. This section contains the detailed design documents and specifications used by developers for core features and modules.
MariaDB MaxScale 21.06 Connectors
This section details connectors for MariaDB MaxScale 21.06. Learn about the Change Data Capture (CDC) APIs that allow applications to consume a real-time stream of database events.
MariaDB MaxScale 21.06 Authenticators
Secure your MaxScale deployment by configuring authenticators. These modules manage client authentication against backend MariaDB servers, supporting various mechanisms for robust security.
MariaDB MaxScale 21.06 About
This release enhanced data integration and read-after-write consistency. It introduced the Kafka CDC router to stream database changes and a causal reads feature for replica servers.
The insertstream filter converts bulk inserts into CSV data streams that are
consumed by the backend server via the LOAD DATA LOCAL INFILE mechanism. This
leverages the speed advantage of LOAD DATA LOCAL INFILE over regular inserts
while also reducing the overall network traffic by condensing the inserted
values into CSV.
Note: This is an experimental filter module
This filter has no parameters.
The filter translates all INSERT statements done inside an explicit transaction
into LOAD DATA LOCAL INFILE streams. The file name used in the request will
always be maxscale.data.
The following example is translated into a LOAD DATA LOCAL INFILE request
followed by two CSV rows.
Multiple inserts to the same table are combined into a single stream. This
allows for efficient bulk loading with simple insert statements.
The following example will use only one LOAD DATA LOCAL INFILE request followed
by four CSV rows.
Non-INSERT statements executed inside the transaction will close the streaming
of the data. Avoid interleaving SELECT statements with INSERT statements inside
transactions.
The following example has to use two LOAD DATA LOCAL INFILE requests, each
followed by two CSV rows.
Note: Avoid doing this!
The more inserts that are streamed, the more efficient this filter is. The
saving in network bandwidth in bytes can be estimated with the following
formula:
Positive values indicate savings in network bandwidth usage.
The filter has no parameters so it is extremely simple to configure. The
following example shows the required filter configuration.
BEGIN;
INSERT INTO test.t1 VALUES (1, "hello"), (2, "world");
COMMIT;
BEGIN;
INSERT INTO test.t1 VALUES (1, "hello"), (2, "world");
INSERT INTO test.t1 VALUES (3, "foo"), (4, "bar");
COMMIT;
BEGIN;
INSERT INTO test.t1 VALUES (1, "hello"), (2, "world");
SELECT * FROM test.t1;
INSERT INTO test.t1 VALUES (3, "foo"), (4, "bar");
COMMIT;
((20 + t) * n) + (n * (m * 2)) - 108 - t = x
n = Number of INSERT statements
m = Number of values in each insert statement
t = Length of table name
x = Number of bytes saved
[Insert-Stream]
type=filter
module=insertstream
MaxScale 21.06 Binlog Filter
This filter was introduced in MariaDB MaxScale 2.3.0.
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.
Type:
Mandatory: No
Dynamic: Yes
Default: None
Include queries that match the regex. See next entry, exclude, for more information.
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.
Type:
Mandatory: No
Dynamic: Yes
Default: None
See the next entry, rewrite_dest, for more information.
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.
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 .
This page is licensed: CC BY-SA / Gnu FDL
MaxScale 21.06 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:
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>/.
MaxScale 21.06 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.
MaxScale 21.06 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.
All registered module commands can be shown with maxctrl list commands and
they can be executed with maxctrl call command <module> <name> ARGS... whereis the name of the module and is the name of the command.ARGS is a command specific list of arguments.
The module command API is defined in the modulecmd.h header. It consists of
various functions to register and call module commands. Read the function
documentation in the header for more details.
The following example registers the module command my_command for module_my_module_.
The array
MaxScale 21.06 Cat
The cat router is a special router that concatenates result sets.
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.
The router has no special parameters. To use it, define a service withrouter=cat and add the servers you want to use.
The order the servers are defined in is the order in which the servers are
queried. This means that the results are ordered based on the servers
parameter of the service. The result will only be completed once all servers
have executed this.
All commands executed via this router will be executed on all servers. This
means that an INSERT through the
MaxScale 21.06 Configuring the Galera Monitor
This document describes how to configure a Galera cluster 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.
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.
MaxScale 21.06 Configuring the MariaDB Monitor
This document describes how to configure a MariaDB master-slave cluster monitor
to be used with MaxScale.
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.
The monitor user requires the REPLICATION CLIENT privileges to do basic
monitoring. To create a user with the proper grants, execute the following SQL.
Note: If the automatic failover of the MariaDB Monitor will used, the user
will require additional grants. Execute the following SQL to grant them.
This page is licensed: CC BY-SA / Gnu FDL
MaxScale 21.06 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.
MaxScale 21.06 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.
This tutorial is a part of .
Please read it and follow the instructions. Return here once basic setup is complete.
After configuring the servers and the monitor, we create a read-write-splitter service
configuration. Create the following section in your configuration file. The section name
is also the name of the service and should be meaningful. For this tutorial, we use the
name Splitter-Service.
router defines the routing module used. Here we use readwritesplit for
query-level read-write-splitting.
A service needs a list of servers where queries will be routed to. The server names must
match the names of server sections in the configuration file and not the hostnames or
addresses of the servers.
MaxScale 21.06 Aurora Monitor
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.
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
Understanding Monitors in MaxScale
In MariaDB MaxScale, monitors perform the following tasks:
Deciding whether a server is up or down.
Deciding whether a server is the primary server or a replica server.
Performing automatic failover when the primary server fails (for certain kinds of deployments).
MaxScale 21.06 Maxscale CDC Connector
The C++ connector for the .
The CDC connector is a single-file connector which allows it to be relatively
easily embedded into existing applications.
To start using the connector, either download it from the or
and install the maxscale-cdc-connector package.
A CDC connection object is prepared by instantiating the CDC::Connection
class. To create the actual connection, call the CDC::Connection::connect
method of the class.
After the connection has been created, call the CDC::Connection::read
MaxScale 21.06 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.
The following settings may be given in the authenticator_options of the
listener.
Type:
Mandatory: No
MariaDB MaxScale 21.06
This version of MariaDB MaxScale enhanced data integration and consistency. It introduced the Kafka CDC connector for streaming changes and simplified causal reads for replica servers.
Dynamic: No
Default: false
The service setting log_auth_warnings must
also be enabled for this setting to have effect. When both settings are enabled,
password hashes are logged if a client gives a wrong password. This feature may
be useful when diagnosing authentication issues. It should only be enabled on a
secure system as the logging of password hashes may be a security risk.
Users can be deleted by removing the related rows in 'cdcusers' file. For
more details on the format of the cdcusers file, read the CDC Protocol documentation.
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.
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).
router will send it to all servers. In
the case of commands that do not return resultsets, the response of the last
server is sent to the client. This means that if one of the earlier servers
returns a different result, the client will not see it.
As the intended use-case of the router is to mainly reduce multiple result sets
into one, it has no mechanisms to prevent writes from being executed on slave
servers (which would cause data corruption or replication failure). Take great
care when performing administrative operations though this router.
If a connection to one of the servers is lost, the client connection will also
be closed.
Here is a simple example service definition that uses the servers from the Configuring Servers tutorial and the
credentials from the MaxScale Tutorial.
This page is licensed: CC BY-SA / Gnu FDL
Configuration
Behavior
Example
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 datadir parameter must be
set to the directory that contains it.
The user and password parameters define the credentials the service uses to populate
user authentication data. These users were created at the start of the MaxScale Tutorial.
To allow network connections to a service, a network ports must be associated with it.
This is done by creating a separate listener section in the configuration file. A service
may have multiple listeners but for this tutorial one is enough.
The service parameter tells which service the listener connects to. For the_Splitter-Listener_ we set it to Splitter-Service.
A listener must define the network port to listen on.
The optional address-parameter defines the local address the listener should bind to.
This may be required when the host machine has multiple network interfaces. The
default behavior is to listen on all network interfaces (the IPv6 address ::).
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.
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 Amazon RDS User Guide
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 Monitor Common 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.
To build and package the connector as a library, follow MaxScale build
instructions with the exception of adding -DTARGET_COMPONENT=devel to the
CMake call.
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 .
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.
Sending UUID
Specify the output format (AVRO or JSON) for data retrieval.
Send CDC commands to retrieve router statistics or to query for data events
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.
REGISTER
REGISTER UUID=UUID, TYPE={JSON | AVRO}
Register as a client to the service.
Example:
Server returns OK on success and ERR on failure.
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:
MaxScale includes an example CDC client application written in Python 3. You can
find the source code for it .
This page is licensed: CC BY-SA / Gnu FDL
MaxScale 21.06 HintRouter
HintRouter was introduced in 2.2 and is still beta.
Overview
The HintRouter module is a simple router intended to operate in conjunction
with the NamedServerFilter. The router looks at the hints embedded in a packet
buffer and attempts to route the packet according to the hint. The user can also
set a default action to be taken when a query has no hints or when the hints
could not be applied.
If a packet has multiple hints attached, the router will read them in order and
attempt routing. Any successful routing ends the process and any further hints
are ignored for the packet.
Configuration
The HintRouter is a rather simple router and only accepts a few configuration
settings.
default_action=<master|slave|named|all>
This setting defines what happens when a query has no routing hint or applying
the routing hint(s) fails. If also the default action fails, the routing will
end in error and the session closes. The different values are:
Value
Description
Note that setting default action to anything other than all means that session
variable write commands are by default not routed to all backends.
Defines the default backend name if default_action=named. <server-name> must
be a valid backend name.
<limit> should be an integer, -1 by default. Defines how many backend slave
servers a session should attempt to connect to. Having less slaves defined in
the services and/or less successful connections during session creation is not
an error. The router will attempt to distribute slaves evenly between sessions
by assigning them in a round robin fashion. The session will always try to
connect to a master regardless of this setting, although not finding one is not
an error.
Negative values activate default mode, in which case this value is set to the
number of backends in the service - 1, so that the sessions are connected to all
slaves.
If the hints or the default_action point to a named server, this setting is
probably best left to default to ensure that the specific server is connected to
at session creation. The router will not attempt to connect to additional
servers after session creation.
A minimal configuration doesn't require any parameters as all settings have
reasonable defaults.
If packets should be routed to the master server by default and only a few
connections are required, the configuration might be as follows.
This page is licensed: CC BY-SA / Gnu FDL
MaxScale Read/Write Split Router
MariaDB MaxScale is a database proxy that extends the high availability, scalability, and security of MariaDB Server while at the same time simplifying application development by decoupling it from underlying database infrastructure. It includes multiple routers that each support different use cases.
Router
Description
• Performs query-based load balancing. • Each client connected to MaxScale is mapped to multiple connections to different back-end database servers. • Routes write queries to connection to primary server. • Routes read queries to connection to replica server.
MaxScale 21.06 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 names_Write-Service_ and Read-Service.
router defines the routing module used. Here we use readconnroute for
connection-level routing.
A service needs a list of servers to route queries to. The server names must
match the names of server sections in the configuration file and not the hostnames or
addresses of the servers.
The router_options-parameter tells the readconnroute-module which servers it should
route a client connection to. For the write service we use the master-type and for the
read service the slave-type.
The user and password parameters define the credentials the service uses to populate
user authentication data. These users were created at the start of the .
For increased security, see .
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.
The service parameter tells which service the listener connects to. For the_Write-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 ::).
For the last steps, please return to .
This page is licensed: CC BY-SA / Gnu FDL
MaxScale 21.06 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_.
Preparing the GSSAPI system
For Unix systems, the usual GSSAPI implementation is Kerberos. This is a short
guide on how to set up Kerberos for MaxScale.
The first step is to configure MariaDB to use GSSAPI authentication. The MariaDB
documentation for the
is a good example on how to set it up.
The next step is to copy the keytab file from the server where MariaDB is
installed to the server where MaxScale is located. The keytab file must be
placed in the configured default location which almost always is/etc/krb5.keytab. Alternatively, the keytab filepath can be given as an
authenticator option.
The location of the keytab file can be changed with the KRB5_KTNAME environment
variable: keytab_def.html
To take GSSAPI authentication into use, add the following to the listener.
The principal name should be the same as on the MariaDB servers.
Type: string
Mandatory: No
Dynamic: No
Default: mariadb/localhost.localdomain
The service principal name to send to the client. This parameter is a string
parameter which is used by the client to request the token.
This parameter must be the same as the principal name that the backend MariaDB
server uses.
Type: path
Mandatory: No
Dynamic: No
Default: Kerberos Default
Keytab file location. This should be an absolute path to the file containing the
keytab. If not defined, Kerberos will search from a default location, usually/etc/krb5.keytab. This path is set to an environment variable. This means that
multiple listeners with GSSAPIAuth will override each other. If using multiple
GSSAPI authenticators, either do not set this option or use the same value for
all listeners.
Read the document for more
details on how authentication modules work in MaxScale.
The GSSAPI plugin authentication starts when the database server sends the
service principal name in the AuthSwitchRequest packet. The principal name will
usually be in the form service@REALM.COM.
The client searches its local cache for a token for the service or may request
it from the GSSAPI server. If found, the client sends the token to the database
server. The database server verifies the authenticity of the token using its
keytab file and sends the final OK packet to the client.
The GSSAPI authenticator modules require the GSSAPI development libraries
(krb5-devel on CentOS 7).
This page is licensed: CC BY-SA / Gnu FDL
MaxScale 21.06 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.
This document is designed as a simple tutorial on schema-based sharding using MariaDB MaxScale in an environment in which you have two servers. The object of this tutorial is to have a system that, to the client side, acts like a single MariaDB database but actually is sharded between the two servers.
The database users should be configured according to . The contains easy to follow instructions on how to set up MaxScale.
This tutorial will assume the user is using of the binary distributions available and has installed this in the default location. The process of configuring MariaDB MaxScale will be covered within this document. The installation and configuration of the MariaDB servers will not be covered in-depth.
