Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Explore the available routing modules in MariaDB MaxScale. This reference covers routers for read-write splitting, connection balancing, sharding, and specialized traffic management.
Concatenate result sets from multiple backend servers into a single response. This router is useful for querying partitioned data or aggregating results from distributed nodes.
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 with router=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 cat 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 tutorial and the credentials from the .
This page is licensed: CC BY-SA / Gnu FDL
Route analytical queries to an Exasol cluster. This router integrates Exasol with MaxScale often used alongside SmartRouter for hybrid transactional/analytical workloads.
ExasolRouter is a router that in itself is capable of using an Exasol cluster. It is primarily intended to be used together with SmartRouter, with writes being directed to a regular MariaDB cluster and reads to Exasol.
Unlike the other routers of MaxScale, the targets ExasolRouter routes to are not specified using servers, targets, or cluster entries in the configuration file. Instead, Exasol database nodes are specified using the setting.
If ExasolRouter is used standalone, a MariaDB server or a service should be specified using targets. ExasolRouter will not route to it, but it will use it for authenticating clients. However, Exasol will be accessed on behalf of all clients using the credentials specified in the .
A user and password must always be specified, but will only be used if a MariaDB server/service has been specified as a target, and only for authenticating a client.
The user and password to be used when accessing Exasol must be specified using UID and PWD in the .
connection_stringType: string
Mandatory: Yes
Dynamic: No
Specifies the Exasol connection string.
For example:
Here it is assumed there is an odbc.ini ODBC configuration file containing an ExasolDSN entry.
appearanceType:
Mandatory: No
Dynamic: No
Values: read_only, read_write
Specifies how the Exasol router appears to other components of MaxScale.
Note Irrespective of the value, the router does not in any way restrict what kind of queries can be run through the router.
install_preprocessor_scriptType:
Mandatory: No
Dynamic: No
Default true
Specifies whether the MariaDB preprocessor script should be installed. With the script installed, some MariaDB SQL constructs will be transparently translated to equivalent Exasol SQL.
At the time of this writing, the script looks like
See
preprocessor_scriptType: Path
Mandatory: No
Dynamic: No
Default: ""
Specifies the location of a file from which the preprocessor script should be read. With this setting, the built-in default script can be overridden.
If the path is not absolute it will be interpreted relative to the MaxScale data directory.
use_preprocessor_scriptType:
Mandatory: No
Dynamic: No
Default: true
Specifies whether the preprocessor script should be used. If true, the session creation will fail unless the script is present.
The Exasol Router transparently translates some MariaDB constructs to equivalent Exasol constructs.
COM_INIT_DBThe MariaDB COM_INIT_DB packet, using which the default database is changed, is transformed into the statement OPEN SCHEMA <db>.
Currently a transformation will be made only if there is an exact match (apart from case and differences in whitespace) with the MariaDb SQL.
The primary purpose of the Exasol router is to be used together with . A minimal configuration looks as follows:
Here it is assumed there is an odbc.ini ODBC configuration file containing and ExasolDSN entry.
Note that user and password must be specified, even if they in this context are not used.
With this setup, all writes will always be sent to Server1. Reads will initially be sent to both Server1 and ExasolService and once SmartRouter has learnt what kind of reads are best sent to which target, it will exclusively send reads to either Server1 or ExasolService depending on which one is likely to provide the response faster.
Here, a single server was used as master. It could just as well be a service in front of a MariaDB cluster, which would provide HA.
A minimal stand-alone configuration looks as follows.
With this setup, it is possible to connect using the regular mariadb command line utility to the port 4008 and all queries will be sent to Exasol.
[concat-service]
type=service
router=cat
servers=dbserv1,dbserv2,dbserv3
user=maxscale
password=maxscale_pwDefault: read_only
SELECT @@VERSION_COMMENT LIMIT 1
SELECT 'Exasol' AS '@@version_comment' LIMIT 1
SELECT DATABASE()
SELECT TABLE_NAME AS 'Database()' FROM EXA_ALL_TABLES WHERE TABLE_SCHEMA = CURRENT_SCHEMA
SHOW DATABASES
SELECT SCHEMA_NAME AS 'Database' FROM EXA_SCHEMAS ORDER BY SCHEMA_NAME
SHOW TABLES
SELECT TABLE_NAME AS 'Tables' FROM SYS.EXA_ALL_TABLES WHERE TABLE_SCHEMA = CURRENT_SCHEMA ORDER BY TABLE_NAME
connection_string=DSN=ExasolDSN;UID=sys;PWD=exasol;FINGERPRINT=NOCERTCHECKCREATE OR REPLACE PYTHON3 SCALAR SCRIPT UTIL.maria_preprocessor(request VARCHAR(2000000))
EMITS (translated_sql VARCHAR(2000000)) AS
def adapter_call(request):
import sqlglot
try:
result = sqlglot.transpile(
request,
read='mysql',
write='exasol',
identify=True,
unsupported='ignore'
)
return str(result[0])
except Exception:
return request
/[Server1]
type=server
address=127.0.0.1
port=3306
protocol=mariadbbackend
[ExasolService]
type=service
router=exasolrouter
connection_string=DSN=ExasolDSN;UID=sys;PWD=exasol
user=
password=
[SmartService]
type=service
router=smartrouter
user=MyServiceUser
password=MyServicePassword
targets=Server1, ExasolService
master=Server1
[SmartListener]
type=listener
service=SmartService
port=4007
[Server1]
type=server
address=127.0.0.1
port=3306
protocol=mariadbbackend
[ExasolService]
type=service
router=exasolrouter
connection_string=DSN=ExasolDSN;UID=sys;PWD=exasol;FINGERPRINT=NOCERTCHECK
targets=Server1
user=MyServiceUser
password=MyServicePassword
[ExasolListener]
type=listener
service=ExasolService
port=4008Import data from Apache Kafka into MariaDB. This router consumes messages from Kafka topics and applies them as transactions to your database tables.
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). By default the table will be automatically created with the following SQL:
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 25.01.1 the _id field is not required to be present. Older versions of MaxScale used the following SQL where the _id field was mandatory:
The user defined by the user parameter of the service must have INSERT andCREATE privileges on all tables that are created.
bootstrap_serversType: string
Mandatory: Yes
Dynamic: Yes
The list of Kafka brokers as a CSV list in host:port format.
topicsType: stringlist
Mandatory: Yes
Dynamic: Yes
The comma separated list of topics to subscribe to.
batch_sizeType: 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.
kafka_sasl_mechanismType:
Mandatory: No
Dynamic: Yes
Values: PLAIN, SCRAM-SHA-256, SCRAM-SHA-512
SASL mechanism to use. The Kafka broker must be configured with the same authentication scheme.
kafka_sasl_userType: string
Mandatory: No
Dynamic: Yes
Default: ""
SASL username used for authentication. If this parameter is defined,kafka_sasl_password must also be provided.
kafka_sasl_passwordType: string
Mandatory: No
Dynamic: Yes
Default: ""
SASL password for the user. If this parameter is defined, kafka_sasl_user must also be provided.
kafka_sslType:
Mandatory: No
Dynamic: Yes
Default: false
Enable SSL for Kafka connections.
kafka_ssl_caType: path
Mandatory: No
Dynamic: Yes
Default: ""
SSL Certificate Authority file in PEM format. If this parameter is not defined, the system default CA certificate is used.
kafka_ssl_certType: path
Mandatory: No
Dynamic: Yes
Default: ""
SSL public certificate file in PEM format. If this parameter is defined,kafka_ssl_key must also be provided.
kafka_ssl_keyType: path
Mandatory: No
Dynamic: Yes
Default: ""
SSL private key file in PEM format. If this parameter is defined,kafka_ssl_cert must also be provided.
table_name_inType:
Mandatory: No
Dynamic: Yes
Values: topic, key
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:
timeoutType:
Mandatory: No
Dynamic: Yes
Default: 5000ms
Timeout for both Kafka and MariaDB network communication.
engineType: string
Default: InnoDB
Mandatory: No
Dynamic: Yes
The storage engine used for tables that are created by the KafkaImporter.
This defines the ENGINE table option and must be the name of a valid storage engine in MariaDB. When the storage engine is something other than InnoDB, the table is created without the generated column and the check constraints:
This is done to avoid conflicts where the custom engine does not support all the features that InnoDB supports.
The backend servers used by this service must be MariaDB version 10.2 or newer.
This page is licensed: CC BY-SA / Gnu FDL
Stream replication events from MariaDB to Apache Kafka. This router captures data changes (CDC) and publishes them as JSON objects to Kafka topics for downstream processing.
The KafkaCDC module reads data changes in MariaDB via replication and converts them into JSON objects that are then streamed to a Kafka broker.
DDL events (CREATE TABLE, ALTER TABLE) are streamed as JSON objects in the following format (example created by CREATE TABLE test.t1(id INT)):
The domain
CREATE TABLE IF NOT EXISTS my_table (
data JSON NOT NULL,
id VARCHAR(1024) AS (JSON_EXTRACT(data, '$._id')) UNIQUE KEY
);Default: PLAIN
Default: topic
server_idsequenceevent_numbertimestampevent_typeinsert: the event is the data that was added to MariaDB
delete: the event is the data that was removed from MariaDB
update_before: the event contains the data before an update statement modified it
update_after: the event contains the data after an update statement modified it
All remaining fields contains data from the table. In the example event this would be the fields id and data.
The sending of these schema objects is optional and can be disabled usingsend_schema=false.
DML events (INSERT, UPDATE, DELETE) are streamed as JSON objects that follow the format specified in the DDL event. The objects are in the following format (example created by INSERT INTO test.t1 VALUES (1)):
The table_name and table_schema fields were added in MaxScale 2.5.3. These contain the table name and schema the event targets.
The router stores table metadata in the MaxScale data directory. The default value is /var/lib/maxscale/<service name>. If data for a table is replicated before a DDL event for it is replicated, the CREATE TABLE will be queried from the primary server.
During shutdown, the Kafka event queue is flushed. This can take up to 60 seconds if the network is slow or there are network problems.
In order for kafkacdc to work, the binary logging on the source server must be configured to use row-based replication and the row image must be set to full by configuring binlog_format=ROW and binlog_row_image=FULL.
The servers parameter defines the set of servers where the data is replicated from. The replication will be done from the first primary server that is found.
The user and password of the service will be used to connect to the primary. This user requires the REPLICATION SLAVE grant.
The KafkaCDC service must not be configured to use listeners. If a listener is configured, all attempts to start a session will fail.
Type: string
Mandatory: Yes
Dynamic: No
The list of Kafka brokers to use in host:port format. Multiple values can be separated with commas. This is a mandatory parameter.
Type: string
Mandatory: Yes
Dynamic: No
The Kafka topic where the replicated events will be sent. This is a mandatory parameter.
Type: boolean
Mandatory: No
Dynamic: No
Default: false
Enable idempotent producer mode. This feature requires Kafka version 0.11 or newer to work and is disabled by default.
When enabled, the Kafka producer enters a strict mode which avoids event duplication due to broker outages or other network errors. In HA scenarios where there are more than two MaxScale instances, event duplication can still happen as there is no synchronization between the MaxScale instances.
The Kafka C library,librdkafka, describes the parameter as follows:
When set to true, the producer will ensure that messages are successfully produced exactly once and in the original produce order. The following configuration properties are adjusted automatically (if not modified by the user) when idempotence is enabled: max.in.flight.requests.per.connection=5 (must be less than or equal to 5), retries=INT32_MAX (must be greater than 0), acks=all, queuing.strategy=fifo.
Type: duration
Mandatory: No
Dynamic: Yes
Default: 10s
The connection and read timeout for the replication stream.
Type: string
Mandatory: No
Dynamic: No
Default: ""
The initial GTID position from where the replication is started. By default the replication is started from the beginning. The value of this parameter is only used if no previously replicated events with GTID positions can be retrieved from Kafka.
Starting in MaxScale 24.02, the special values newest and oldest can be used:
newest uses the current value of @@gtid_binlog_pos as the GTID where the replication is started from.
oldest uses the oldest binlog that's available in SHOW BINARY LOGS and then extracting the oldest GTID from it with SHOW BINLOG EVENTS.
Once the replication has started and a GTID position has been recorded, this parameter will be ignored. To reset the recorded GTID position, delete thecurrent_gtid.txt file located in /var/lib/maxscale/<SERVICE>/ where<SERVICE> is the name of the KafkaCDC service.
Type: number
Mandatory: No
Dynamic: No
Default: 1234
The server_id used when replicating from the primary in direct replication mode. The default value is 1234. This parameter was added in MaxScale 2.5.7.
Type: regex
Mandatory: No
Dynamic: Yes
Default: ""
Only include data from tables that match this pattern.
For example, if configured with match=accounts[.].*, only data from theaccounts database is sent to Kafka.
The pattern is matched against the combined database and table name separated by a period. This means that the event for the table t1 in the test database would appear as test.t1. The behavior is the same even if the database or the table name contains a period. This means that an event for the test.table table in the my.data database would appear as my.data.test.table.
Here is an example configuration that only sends events for tables from thedb1 database. The accounts and users tables in the db1 database are filtered out using the exclude parameter.
Type: regex
Mandatory: No
Dynamic: Yes
Default: ""
Exclude data from tables that match this pattern.
For example, if configured with exclude=mydb[.].*, all data from the tables in the mydb database is not sent to Kafka.
The pattern matching works the same way for both of the exclude and match parameters. See match for an explanation on how the patterns are matched against the database and table names.
Type: boolean
Mandatory: No
Dynamic: No
Default: false
Controls whether multiple instances cooperatively replicate from the same cluster. This is a boolean parameter and is disabled by default. It was added in MaxScale 6.0.
When this parameter is enabled and the monitor pointed to by the cluster parameter supports cooperative monitoring (currently only mariadbmon), the replication is only active if the monitor owns the cluster it is monitoring.
Whenever an instance that does not own the cluster gains ownership of the cluster, the replication will continue from the latest GTID that was delivered to Kafka.
This means that multiple MaxScale instances can replicate from the same set of servers and the event is only processed once. This feature does not provide exactly-once semantics for the Kafka event delivery. However, it does provide high-availability for the kafkacdc instances which allows automated failover between multiple MaxScale instances.
Type: boolean
Mandatory: No
Dynamic: Yes
Default: true
Send JSON schema object into the stream whenever the table schema changes. These events, as described here, can be used to detect whenever the format of the data being sent changes.
If this information in these schema change events is not needed or the code that processes the Kafka stream can't handle them, they can be disabled with this parameter.
Type: boolean
Mandatory: No
Dynamic: No
Default: true
On startup, the latest GTID is by default read from the Kafka cluster. This makes it possible to recover the replication position stored by another MaxScale. Sometimes this is not desirable and the GTID should only be read from the local file or started anew. Examples of these are when the GTIDs are reset or the replication topology has changed.
Type: boolean
Mandatory: No
Dynamic: No
Default: false
Enable SSL for Kafka connections. This is a boolean parameter and is disabled by default.
Type: path
Mandatory: No
Dynamic: No
Default: ""
Path to the certificate authority file in PEM format. If this is not provided, the default system certificates will be used.
Type: path
Mandatory: No
Dynamic: No
Default: ""
Path to the public certificate in PEM format.
The client must provide a certificate if the Kafka server performs authentication of the client certificates. This feature is enabled by default in Kafka and is controlled by ssl.endpoint.identification.algorithm.
If kafka_ssl_cert is provided, kafka_ssl_key must also be provided.
Type: path
Mandatory: No
Dynamic: No
Default: ""
Path to the private key in PEM format.
If kafka_ssl_key is provided, kafka_ssl_cert must also be provided.
Type: string
Mandatory: No
Dynamic: No
Default: ""
Username for SASL authentication.
If kafka_sasl_user is provided, kafka_sasl_password must also be provided.
Type: string
Mandatory: No
Dynamic: No
Default: ""
Password for SASL authentication.
If kafka_sasl_password is provided, kafka_sasl_user must also be provided.
Type: enum
Mandatory: No
Dynamic: No
Values: PLAIN, SCRAM-SHA-256, SCRAM-SHA-512
Default: PLAIN
The SASL mechanism used. The default value is PLAIN which uses plaintext authentication. It is recommended to enable SSL whenever plaintext authentication is used.
Allowed values are:
PLAIN
SCRAM-SHA-256
SCRAM-SHA-512
The value that should be used depends on the SASL mechanism used by the Kafka broker.
The following configuration defines the minimal setup for streaming replication events from MariaDB into Kafka as JSON:
The KafkaCDC module provides at-least-once semantics for the generated events. This means that each replication event is delivered to kafka at least once but there can be duplicate events in case of failures.
This page is licensed: CC BY-SA / Gnu FDL
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)
);`my database`.`my table`CREATE TABLE IF NOT EXISTS my_table (data JSON NOT NULL);{
"namespace": "MaxScaleChangeDataSchema.avro",
"type": "record",
"name": "ChangeRecord",
"table": "t2", // name of the table
"database": "test", // the database the table is in
"version": 1, // schema version, incremented when the table format changes
"gtid": "0-3000-14", // GTID that created the current version of the table
"fields": [
{
"name": "domain", // First part of the GTID
"type": "int"
},
{
"name": "server_id", // Second part of the GTID
"type": "int"
},
{
"name": "sequence", // Third part of the GTID
"type": "int"
},
{
"name": "event_number", // Sequence number of the event inside the GTID
"type": "int"
},
{
"name": "timestamp", // UNIX timestamp when the event was created
"type": "int"
},
{
"name": "event_type", // Event type
"type": {
"type": "enum",
"name": "EVENT_TYPES",
"symbols": [
"insert", // The row that was inserted
"update_before", // The row before it was updated
"update_after", // The row after it was updated
"delete" // The row that was deleted
]
}
},
{
"name": "id", // Field name
"type": [
"null",
"long"
],
"real_type": "int", // Field type
"length": -1, // Field length, if found
"unsigned": false // Whether the field is unsigned
}
]
}{
"domain": 0,
"server_id": 3000,
"sequence": 20,
"event_number": 1,
"timestamp": 1580485945,
"event_type": "insert",
"id": 1,
"table_name": "t2",
"table_schema": "test"
}[Kafka-CDC]
type=service
router=kafkacdc
servers=server1
user=maxuser
password=maxpwd
bootstrap_servers=127.0.0.1:9092
topic=my-cdc-topic
match=db1[.]
exclude=db1 [.](accounts|users)# The server we're replicating from
[server1]
type=server
address=127.0.0.1
port=3306
# The monitor for the server
[MariaDB-Monitor]
type=monitor
module=mariadbmon
servers=server1
user=maxuser
password=maxpwd
monitor_interval=5s
# The MariaDB-to-Kafka CDC service
[Kafka-CDC]
type=service
router=kafkacdc
servers=server1
user=maxuser
password=maxpwd
bootstrap_servers=127.0.0.1:9092
topic=my-cdc-topicBalance connections across backend servers. This lightweight router distributes new client connections to available nodes without inspecting individual SQL statements.
This document provides an overview of the readconnroute router module and its intended use case scenarios. It also displays all router configuration parameters with their descriptions.
The readconnroute router provides simple and lightweight load balancing across a set of servers. The router can also be configured to balance connections based on a weighting parameter defined in the server's section.
Note that readconnroute balances connections and not statements. When a client connects, the router selects a server based upon the router configuration and current server load, but the single created connection is fixed and will not be changed for the duration of the session. If the connection between MaxScale and the server breaks, the connection cannot be re-established and the session will be closed. The fact that the server is fixed when the client connects also means that routing hints are ignored.
Warning: By default readconnroute will not prevent writes from being done even if you define router_options=slave. To prevent writes, add filters=readonly to the service to load the module that will block all writes. Otherwise, the client application is responsible for making sure that it only performs read-only queries in such cases. readconnroute is simple by design: it selects a server for each client connection and routes all queries there. If something more complex is required, the router is usually the right choice.
For more details about the standard service parameters, refer to the .
router_optionsType:
Mandatory: No
Dynamic: Yes
Values: master, slave, synced
router_options can contain a comma separated list of valid server roles. These roles are used as the valid types of servers the router will form connections to when new sessions are created.
Examples:
Here is a list of all possible values for the router_options.
If no router_options parameter is configured in the service definition, the router will use the default value of running. This means that it will load balance connections across all running servers defined in the servers parameter of the service.
When a connection is being created and the candidate server is being chosen, the list of servers is processed in from first entry to last. This means that if two servers with equal weight and status are found, the one that's listed first in the servers parameter for the service is chosen.
master_accept_readsType:
Mandatory: No
Dynamic: Yes
Default: true
This option can be used to prevent queries from being sent to the current primary. If router_options does not contain master, the readconnroute instance is usually meant for reading. Setting master_accept_reads=false excludes the primary from server selection (and thus from receiving reads).
If router_options contains master, the setting of master_accept_reads has no effect.
By default master_accept_reads=true.
max_replication_lagType:
Mandatory: No
Dynamic: Yes
Default: 0s
The maximum acceptable replication lag. The value is in seconds. The default value is 0s, which means that the lag is ignored.
The replication lag of a server must be less than the configured value in order for it to be used for routing. To configure the router to not allow any lag, use the smallest duration larger than 0, that is, max_replication_lag=1s.
preferred_labelsType: string list
Mandatory: No
Dynamic: Yes
Default: None
Comma-separated list of labels that defines the preference of servers.
If defined, servers with these labels are preferred over those that do not have them. This allows different readconnroute services to prefer different servers based on user-configurable labels in the servers and services. This also makes it possible to have a separate readconnroute service for example reporting or logical backups that prefers a specific set of servers and another readconnroute service for the production client workload, all while still allowing both of them to use the same server in case of an outage.
Multiple label values can be given and the servers are grouped based on that. For example with preferred_labels=banana,mango,kiwi, servers with the banana label are used first after which the mangoones are used and then the kiwi ones. If no servers with these labels are found, any server that is valid based on router_options is used.
The most common use for the readconnroute is to provide either a read or write port for an application. This provides a more lightweight routing solution than the more complex readwritesplit router but requires the application to be able to use distinct write and read ports.
To configure a read-only service that tolerates primary failures, we first need to add a new section into the configuration file.
Here the router_options designates replicas as the only valid server type. With this configuration, the queries are load balanced across the replica servers.
The router_diagnostics output for readconnroute has the following fields.
queries: Number of queries executed through this service.
Sending of binary data with LOAD DATA LOCAL INFILE is not supported.
The router will never reconnect to the server it initially connected to.
This page is licensed: CC BY-SA / Gnu FDL
Intelligently route queries based on workload type. SmartRouter directs transactional queries to MariaDB and analytical queries to column-store engines for hybrid processing.
SmartRouter is the query router of the SmartQuery framework. Based on the type of the query, each query is routed to the server or cluster that can best handle it.
For workloads where both transactional and analytical queries are needed, SmartRouter unites the Transactional (OLTP) and Analytical (OLAP) workloads into a single entry point in MaxScale. This allows a MaxScale client to freely mix transactional and analytical queries using the same connection. This is known as Hybrid Transactional and Analytical Processing, HTAP.
SmartRouter is configured as a service that either routes to other MaxScale routers or plain servers. Although one can configure SmartRouter to use a plain server directly, we refer to the configured "servers" as clusters.
For details about the standard service parameters, refer to the .
masterType: target
Mandatory: Yes
Dynamic: No
One of the clusters must be designated as the master. All writes go to the primary cluster, which for all practical purposes should be a primary-replica ReadWriteSplit. This document does not go into details about setting up primary-replica clusters, but suffice to say, that when setting up the ColumnStore servers they should be configured to be replicas of a MariaDB server running an InnoDB engine. The ReadWriteSplit has more on primary-replica setup.
Suppose we have a Transactional service like
for which we have defined the listener
That is, that service can be accessed using the socket /tmp/rws-row.sock.
The Analytical service could look like this
Then we can define the SmartQuery service as follows
Note that the SmartQuery listener listens on a port, while the Row and Column service listeners listen on Unix domain sockets. The reason is that there is a significant performance benefit when SmartRouter accesses the services over a Unix domain socket compared to accessing them over a TCP/IP socket.
A complete configuration example can be found at the end of this document.
SmartRouter keeps track of the performance, or the execution time, of queries to the clusters. Measurements are stored with the canonical of a query as the key. The canonical of a query is the sql with all user-defined constants replaced with question marks. When SmartRouter sees a read-query whose canonical has not been seen before, it will send the query to all clusters. The first response from a cluster will designate that cluster as the best one for that canonical. Also, when the first response is received, the other queries are cancelled. The response is sent to the client once all clusters have responded to the query or the cancel.
There is obviously overhead when a new canonical is seen. This means that queries after a MaxScale start will be slightly slower than normal. The execution time of a query depends on the database engine, and on the contents of the tables being queried. As a result, MaxScale will periodically re-measure queries.
The performance behavior of queries under dynamic conditions, and their effect on different storage engines is being studied at MariaDB. As we learn more, we will be able to better categorize queries and move that knowledge into SmartRouter.
LOAD DATA LOCAL INFILE is not supported.
The performance data is not persisted. The measurements will be performed anew after each startup.
This page is licensed: CC BY-SA / Gnu FDL
Replicate binary logs from a primary server to local storage. This router serves as an intermediate replication master reducing load on the primary database in large clusters.
The binlogrouter is a router that acts as a replication proxy for MariaDB primary-replica replication. The router connects to a primary, retrieves the binary logs and stores them locally. Replica servers can connect to MaxScale like they would connect to a normal primary server. If the primary server goes down, replication between MaxScale and the replicas can still continue up to the latest point to which the binlogrouter replicated to. The primary can be changed without disconnecting the replicas and without them noticing that the primary 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 primary whereas with normal replication each individual replica will create a separate connection. This reduces the amount of work the primary database has to do which can be significant if there are a large number of replicating replicas.
runningDefault: running
master
A server assigned as a primary by one of MariaDB MaxScale monitors. Depending on the monitor implementation, this could be a primary server of a Primary-Replica replication cluster or a Write-Primary of a Galera cluster.
slave
A server assigned as a replica of a primary. If all replicas are down, but the primary is still available, then the router will use the primary.
synced
A Galera cluster node which is in a synced state with the cluster.
running
A server that is up and running. All servers that MariaDB MaxScale can connect to are labeled as running.
router_options=slave
router_options=master,slave[Read-Service]
type=service
router=readconnroute
servers=replica1,replica2,replica3
router_options=slave[RWS-Row]
type=service
router=readwritesplit
servers = row_server_1, row_server_2, ...[RWS-Row-Listener]
type=listener
service=RWS-Row
socket=/tmp/rws-row.sock[RWS-Column]
type = service
router = readwritesplit
servers = column_server_1, column_server_2, ...
[RWS-Column-Listener]
type = listener
service = RWS-Column
socket = /tmp/rws-col.sock[SmartQuery]
type = service
router = smartrouter
targets = RWS-Row, RWS-Column
master = RWS-Row
[SmartQuery-Listener]
type = listener
service = SmartQuery
port = <port>[maxscale]
[row_server_1]
type = server
address = <ip>
port = <port>
[row_server_2]
type = server
address = <ip>
port = <port>
[Row-Monitor]
type = monitor
module = mariadbmon
servers = row_server_1, row_server_2
user = <user>
password = <password>
monitor_interval = 2000ms
[column_server_1]
type = server
address = <ip>
port = <port>
[Column-Monitor]
type = monitor
module = csmon
servers = column_server_1
user = <user>
password = <password>
monitor_interval = 2000ms
# Row Read write split
[RWS-Row]
type = service
router = readwritesplit
servers = row_server_1, row_server_2
user = <user>
password = <password>
[RWS-Row-Listener]
type = listener
service = RWS-Row
socket = /tmp/rws-row.sock
# Columnstore Read write split
[RWS-Column]
type = service
router = readwritesplit
servers = column_server_1
user = <user>
password = <password>
[RWS-Column-Listener]
type = listener
service = RWS-Column
socket = /tmp/rws-col.sock
# Smart Query router
[SmartQuery]
type = service
router = smartrouter
targets = RWS-Row, RWS-Column
master = RWS-Row
user = <user>
password = <password>
[SmartQuery-Listener]
type = listener
service = SmartQuery
port = <port>File purge and archive are mutually exclusive. Archiving simply means that a binlog is moved to another directory. That directory can be mounted to another file system for backups or, for example, a locally mounted S3 bucket.
If archiving is started from a primary that still has all its history intact, a full copy of the primary can be archived.
File compression preserves disk space and makes archiving faster. All binlogs except the very last one, which is the one being logged to, can be compressed. The overhead of reading from a compressed binlog is small, and is typically only needed when a replica goes down, reconnects and is far enough behind the current GTID that an older file needs to be opened.
There is no automated way as of yet for the binlogrouter to use archived files, but should the need arise files can be copied from the archive to the binlog directory. See 'Modifying binlog files manually'.
The related configuration options, which are explained in more detail in the configuration section are:
expiration_mode Select purge or archive.
datadir Directory where binlog files are stored (the default is usually fine).
archivedir _Directory to which files are archived. This directory must exist when MaxScale is started.
expire_log_minimum_files The minimum number of binlogs to keep before purge or archive is allowed.
Duration from the last file modification until the binlog is eligible for purge or archive.
Select a compression algorithm or none for no compression. Currently only zstandard is supported.
The minimum number of binlogs not to compress.
Following are example settings where it is expected that a replica is down for no more than 24 hours.
There is usually no reason to modify the contents of the binlog directory. Changing the contents can cause failures if not done correctly. Never make any changes if running a version prior to 23.08, except when a bootstrap is needed.
A binlog file has the name .<sequence_number>. The basename is decided by the primary server. The sequence number increases by one for each file and is six digits long. The first file has the name .000001
The file binlog.index contains the view of the current state of the binlogs as a list of file names ordered by the file sequence number. binlog.index is automatically generated any time the contents of datadir changes.
Older files can be manually deleted and should be deleted in the order they were created, lowest to highest sequence number. Prefer to use purge configuration.
Archived files can be copied back to datadir, but care should be taken to copy them back in the reverse order they were created, highest to lowest sequence number. The copied over files will be re-archived once expire_log_duration time has passed.
Never leave a gap in the sequence numbers, and always preserve the name of a binlog file if copied. Do not copy binlog files on top of existing binlog files.
As of version 24.02 any binlog except the latest one can be manually compressed, .e.g:
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 primary 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 replica is still reading. NOTE: You should still take precaution not to purge files that a potential replica will need in the future. MaxScale can only detect that a file is in active use when a replica 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 primary.
SHOW SLAVE STATUS
Shows the replica status information similar to what a normal MariaDB replica 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 primary to send event when replication is ongoing.
Master_Host: Address of the current primary.
SELECT { Field } ...
The binlogrouter implements a small subset of the MariaDB SELECT syntax as it is mainly used by the replicating replicas 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 primary.
version()
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.
If the server from which the binlogrouter replicates from is using semi-sync replication, the binlogrouter will acknowledge the replicated events.
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.
Type: path
Mandatory: No
Dynamic: No
Default: /var/lib/maxscale/binlogs
Directory where binary log files are stored.
Type: string
Mandatory: Yes
Default: No
Dynamic: No
Mandatory if expiration_mode=archive
The directory to where files are archived. This is presumably a directory mounted to a remote file system or an S3 bucket. Ensure that the user running MaxScale (typically "maxscale") has sufficient privileges on the archive directory. S3 buckets mounted with s3fs may require setting permissions manually:
The directory must exist when MaxScale starts.
Type: count
Mandatory: No
Dynamic: No
Default: 1234
The server ID that MaxScale uses when connecting to the primary and when serving binary logs to the replicas.
Type: duration
Mandatory: No
Dynamic: No
Default: 10s
Network connection and read timeout for the connection to the primary.
Type: boolean
Mandatory: No
Dynamic: No
Default: false
Automatically select the primary server to replicate from.
When this feature is enabled, the primary 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 primary servers are available, the first available primary 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 primary is "sticky", meaning that the same primary will be chosen on reboot.
NOTE: Do not use the mariadbmon parameterauto_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.
Type: enum
Dynamic: No
Values: purge, archive
Default: purge
Choose whether expired logs should be purged or archived.
Type: duration
Mandatory: No
Dynamic: No
Default: 0s
Duration after which a binary log file expires, i.e. becomes eligible for purge or archive. This is similar to the server system variable.
A value of 0s turns off purging.
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 replica.
Type: number
Mandatory: No
Dynamic: No
Default: 2
The minimum number of log files the automatic purge keeps. At least one file is always kept.
Type: enum
Mandatory: No
Dynamic: No
Values: none, zstandard
Default: none
Type: count
Mandatory: No
Dynamic: No
Default: 2
The minimum number of log files that are not compressed. At least one file is not compressed.
Type: boolean
Mandatory: No
Dynamic: No
Default: false
When enabled, only DDL events are written to the binary logs. This means thatCREATE, ALTER and DROP events are written but INSERT, UPDATE andDELETE events are not.
This mode can be used to keep a record of all the schema changes that occur on a database. As only the DDL events are stored, it becomes very easy to set up an empty server with no data in it by simply pointing it at a binlogrouter instance that has ddl_only enabled.
Type: string
Mandatory: No
Dynamic: No
Default: ""
Encryption key ID used to encrypt the binary logs. If configured, an Encryption Key Manager must also be configured and it must contain the key with the given ID. If the encryption key manager supports versioning, new binary logs will be encrypted using the latest encryption key. Old binlogs will remain encrypted with older key versions and remain readable as long as the key versions used to encrypt them are available.
Once binary log encryption has been enabled, the encryption key ID cannot be changed and the key must remain available to MaxScale in order for replication to work. If an encryption key is not available or the key manager fails to retrieve it, the replication from the currently selected primary server will stop. If the replication is restarted manually, the encryption key retrieval is attempted again.
Re-encryption of binlogs using another encryption key is not possible. However, this is possible if the data is replicated to a second MaxScale server that uses a different encryption key. The same approach can also be used to decrypt binlogs.
Type: enum
Mandatory: No
Dynamic: No
Values: AES_CBC, AES_CTR, AES_GCM
Default: AES_GCM
The encryption cipher to use. The encryption key size also affects which mode is used: only 128, 192 and 256 bit encryption keys are currently supported.
Possible values are:
AES_GCM (default)
AES_CBC
AES_CTR
.
Type: boolean
Mandatory: No
Default: false
Dynamic: Yes
Enable semi-synchronous replication when replicating from a MariaDB server. If enabled, the binlogrouter will send acknowledgment for each received event. Note that the rpl_semi_sync_master_enabled parameter must be enabled in the MariaDB server where the replication is done from for the semi-synchronous replication to take place.
Configure and start MaxScale.
If you have not configured select_master=true (automatic primary selection), issue a CHANGE MASTER TO command to binlogrouter.
Redirect each replica to replicate from Binlogrouter
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.
Binlogrouter uses inotify which has three kernel limits. While Binlogrouter uses a modest number of inotify instances, the limit max_user_instances applies to the total number of instances for the user and has a low default value on many systems. A double or triple of the default value should suffice. The other two limits, max_queued_events and max_user_watches are usually high enough, but it is sensible to double (triple) them if max_user_instances was doubled (tripled).
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 .
If the primary contains binlogs from the blank state, and there is a large amount of data, consider purging old binlogs. See
The method described here inflicts the least downtime. Assuming you have configured MaxScale version 2.5 or newer, and it is ready to go:
Redirect each replica that replicates from Binlogrouter to replicate from the primary.
Stop the old version of MaxScale, and start the new one. Verify routing functionality.
Issue a CHANGE MASTER TO command, or use select_master.
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:
NOTE: Even with select_master=true you have to set @@global.gtid_slave_pos if any binlog files have been purged on the primary. The server will only stream from the start of time if the first binlog file is present. See select_master.
Redirect each replica to replicate from Binlogrouter.
If for any reason you need to "bootstrap" the binlogrouter you can change the datadir or delete the entire binglog directory (datadir) when MaxScale is NOT running. This could be necessary if files are accidentally deleted or the file system becomes corrupt.
No changes are required to the attached replicas.
if select_master is set to true and the primary contains the entire binlog history, a simple restart of MaxScale sufficies.
In the normal case, the primary does not have the entire history and you will need to set the GTID position to a starting value, usually the earliest gtid state of all replicas. Once MaxScale has been restarted connect to the binlogrouter from the command line.
If select_master is set to true issue:
else
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 Wsrep GTID Mode](../../../galera-cluster/high-availability/using-mariadb-replication-with-mariadb-galera-cluster/using-mariadb-gtids-with-mariadb-galera-cluster.md)
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:
The following is a small configuration file with automatic primary selection. With it, the service will accept connections on port 3306.
Old-style replication with binlog name and file offset is not supported and the replication must be started by setting up the GTID to replicate from.
Only replication from MariaDB servers (including Galera) is supported.
Old encrypted binary logs are not re-encrypted with newer key versions (MXS-4140)
The MariaDB server where the replication is done from must be configured withbinlog_checksum=CRC32.
This page is licensed: CC BY-SA / Gnu FDL
Duplicate client queries to multiple targets. The Mirror router sends traffic to a primary server while asynchronously forwarding the same queries to a secondary server for testing.
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:
The objects in the results array describe an individual query result and have the following fields:
mainType: 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.
exporterType:
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.
fileType: 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_brokerType: 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_topicType: 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_errorType:
Default: ignore
Mandatory: No
Dynamic: Yes
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
This parameter was added in MaxScale 6.0. Older versions always ignored failing backends.
reportType:
Default: always
Mandatory: No
Dynamic: Yes
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.
Broken network connections are not recreated.
Prepared statements are not supported.
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
expiration_mode=archive
archivedir = /mnt/binlog-s3
expire_log_minimum_files=3
expire_log_duration=24h
compression_algorithm=zstandard
number_of_noncompressed_files=2zstd --rm -z binlog.001234s3fs my-bucket /home/joe/S3_bucket_mount/ -o umask=0077mariadb -u USER -pPASSWORD -h maxscale-IP -P binlog-PORT
CHANGE MASTER TO master_host="primary-IP", master_port=PRIMARY-PORT, master_user=USER, master_password="PASSWORD", master_use_gtid=slave_pos;
START SLAVE;mariadb -u USER -pPASSWORD -h replica-IP -P replica-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 \Gmariadb -u USER -pPASSWORD -h replica-IP -P replica-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 \Gmariadb -u USER -pPASSWORD -h maxscale-IP -P binlog-PORT
CHANGE MASTER TO master_host="primary-IP", master_port=primary-PORT,
master_user=USER,master_password="PASSWORD", master_use_gtid=slave_pos;STOP SLAVE
SET @@global.gtid_slave_pos = "0-1000-1234,1-1001-5678";
START SLAVEmariadb -u USER -pPASSWORD -h replica-IP -P replica-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 \Gmariadb -u USER -pPASSWORD -h maxscale-IP -P binlog-PORT
STOP SLAVE;
SET @@global.gtid_slave_pos = "gtid_state";
START SLAVE;mariadb -u USER -pPASSWORD -h maxscale-IP -P binlog-PORT
CHANGE MASTER TO master_host="primary-IP", master_port=PRIMARY-PORT, master_user=USER, master_password="PASSWORD", master_use_gtid=slave_pos;
SET @@global.gtid_slave_pos = "gtid_state";
START SLAVE;[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[server1]
type=server
address=192.168.0.1
port=3306
[server2]
type=server
address=192.168.0.2
port=3306
[MariaDB-Monitor]
type=monitor
module=mariadbmon
servers=server1, server2
user=maxuser
password=maxpwd
monitor_interval=10s
[Replication-Proxy]
type=service
router=binlogrouter
cluster=MariaDB-Monitor
select_master=true
expiration_mode=archive
archivedir=/mnt/somedir
expire_log_minimum_files=3
expire_log_duration=24h
compression_algorithm=zstandard
number_of_noncompressed_files=2
user=maxuser
password=maxpwd
[Replication-Listener]
type=listener
service=Replication-Proxy
port=3306Master_User: The user used to replicate.
Master_Port: The port the primary is listening on.
Master_Log_File: The name of the latest file that the binlogrouter is writing to.
Read_Master_Log_Pos: The current position where the last event was written in the latest binlog.
Slave_IO_Running: Set to Yes if replication running and No if it's not.
Slave_SQL_Running Set to Yes if replication running and No if it's not.
Exec_Master_Log_Pos: Same as Read_Master_Log_Pos.
Gtid_IO_Pos: The latest replicated GTID.
@@versionUNIX_TIMESTAMP(): The current timestamp.
@@version_comment: Always pinloki.
@@global.gtid_domain_id: Always 0.
@master_binlog_checksum: Always CRC32.
@@session.auto_increment_increment: Always 1
@@character_set_client: Always utf8
@@character_set_connection: Always utf8
@@character_set_results: Always utf8
@@character_set_server: Always utf8mb4
@@collation_server: Always utf8mb4_general_ci
@@collation_connection: Always utf8_general_ci
@@init_connect: Always an empty string
@@interactive_timeout: Always 28800
@@license: Always BSL
@@lower_case_table_names: Always 0
@@max_allowed_packet: Always 16777216
@@net_write_timeout: Always 60
@@performance_schema: Always 0
@@query_cache_size: Always 1048576
@@query_cache_type: Always OFF
@@sql_mode: Always an empty string
@@system_time_zone: Always UTC
@@time_zone: Always SYSTEM
@@tx_isolation: Always REPEATABLE-READ
@@wait_timeout: Always 28800
kafka
Exports metrics to a Kafka broker. Configured with the kafka_broker and kafka_topic parameters.
Values: ignore, close
Values: always, on_conflict
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
query_id
Query sequence number, starts from 1
results
Array of query result objects
target
The target where the query was executed
checksum
The CRC32 checksum of the result
rows
Number of returned rows
warnings
Number of returned warnings
duration
Query duration in milliseconds
type
Result type, one of ok, error or resultset
[server1]
type=server
address=127.0.0.1
port=3000
[server2]
type=server
address=127.0.0.1
port=3001
[MariaDB-Monitor]
type=monitor
module=mariadbmon
servers=server1,server2
user=maxuser
password=maxpwd
monitor_interval=2s
[Mirror-Router]
type=service
router=mirror
user=maxuser
password=maxpwd
targets=server1,server2
main=server1
exporter=file
file=/tmp/Mirror-Router.log
[Mirror-Listener]
type=listener
service=Mirror-Router
port=3306Compare the behavior of two MariaDB server versions. The Diff router executes queries against a main and a test server to detect discrepancies in results or performance.
The diff-router, hereafter referred to as Diff, compares the behaviour of one MariaDB server version to that of another.
Diff will send the workload both to the server currently being used - called main - and to another server - called other - whose behaviour needs to be assessed.
The responses from main are returned to the client, without waiting for the responses from other. While running, Diff collects latency histogram data that later can be used for evaluating the behaviour of main and other.
Although Diff is a normal MaxScale router that can be configured manually, typically it is created using commands provided by the router itself. As its only purpose is to compare the behaviour of different servers, it is only meaningful to start it provided certain conditions are fulfilled and those conditions are easily ensured using the router itself.
Diff collects latency information separately for each canonical &#xNAN;statement, which simply means a statement where all literals have been replaced with question marks. For instance, the canonical statement ofSELECT f FROM t WHERE f = 10 and SELECT f FROM t WHERE f = 20 is in both cases SELECT f FROM t WHERE f = ?. The latency information of both of those statements will be collected under the same canonical statement.
Before starting to register histogram data, Diff will collect from main that will be used for defining the edges and the number of bins of the histogram.
The responses from main and other are considered to be different
if the checksum of the response from main and *other differ, or
if the response time of _other* is outside the boundaries of the histogram edges calculated from the samples from main.
A difference in the response time of individual queries is not a meaningful criteria, as there for varying reasons (e.g. network traffic) can be a significant amount of variance in the results. It would only always cause a large number of false positives.
When a discrepancy is detected, an EXPLAIN statement will be executed if the query was a DELETE, SELECT, INSERT or UPDATE. The EXPLAIN will be executed using the same connection that was used for executing the original statement. In the normal case, the EXPLAIN will be executed immediately after the original statement, but if the client is streaming requests, an other statement may have been exceuted in between.
EXPLAINs are not always executed, but the frequency is controlled by and . The EXPLAIN results are included in the of Diff.
While running, Diff will also collect QPS information over a sliding window whose size is defined by .
Diff produces two kinds of output:
Output that is generated when Diff terminates or upon . That output can be visualized as explained .
Diff can continuously report queries whose responses from main and other differ as described .
When Diff starts it will create a directory diff in MaxScale's data directory (typically /var/lib/maxscale). Under that it will create a directory whose name is the same as that of the service specified in . The output files are created in that directory.
The behaviour and usage of Diff is most easily explained using an example.
Consider the following simple configuration that only includes the very essential.
There is a service MyService that uses a single server MyServer1, which, for this example, is assumed to run MariaDB 10.5.
Suppose that the server should be upgraded to 11.2 and we want to find out whether there would be some issues with that.
In order to use Diff for comparing the behaviour of MariaDB 10.5 and MariaDB 11.2, the following steps must be taken.
Install MariaDB 11.2 on a host that performance wise is similar to the host on which MariaDB 10.5 is running.
Configure the MariaDB 11.2 server to replicate from the MariaDB 10.5 server.
Create a server entry for the MariaDB 11.2 server in the MaxScale configuration.
The created entry could be something like:
Note that this server must not be added to the service that uses the original server. That is, in this example, MariaDB_112 must not be added to the service MyService.
The new server can be added to the monitor used for monitoring the servers of the service, but that is not necessary. However, unless it is added, maxctrl list servers will show the server as being down.
With these steps Diff is ready to be used.
Diff is controlled using a number of module commands.
Syntax: create new-service existing-service used-server new-server
Where:
new-service: The name of the service using the Diff router, to be created.
existing-service: The name of an existing service in whose context the new server is to be evaluated.
used-server: A server used by existing-service
With this command, preparations for comparing the server MariaDB_112 against the server MyServer1 of the service MyService will be made. At this point it will be checked in what kind of replication relationshipMariaDB_112 is with respect to MyServer1. If the steps in were followed, it will be detected thatMariaDB_112 replicates from MyServer1.
If everything seems to be in order, the service DiffMyService will be created. Settings such as user and password that are needed by the service DiffMyService will be copied from MyService.
Using maxctrl we can check that the service indeed has been created.
Now the comparison can be started.
Syntax: start diff-service
Where:
diff-service: The name of the service created in thecreate` step.
When Diff is started, it performs the following steps:
All sessions of MyService are suspended.
In the MyService service, the server target MyServer1 is replaced with DiffMyService.
The replication from MyServer1 to MariaDB_112
In the first step, all sessions that are idle will immediately be suspended, which simply means that nothing is read from the client socket. Sessions that are waiting for a response from the server and sessions that have an active transaction continue to run. Immediately when a session becomes idle, it is suspended.
Once all sessions have been suspended, the service is rewired. In the case of MyService above, it means that the targetMyServer1 is replaced with DiffMyService. That is, requests that earlier were sent to MyServer1, will, once the sessions are resumed, be sent to DiffMyService, which sends them forward to both MyServer1 and MariaDB_112.
Restarting the sessions means that the direct connections toMyServer1 will be closed and equivalent ones created via the service DiffMyService, which will also create connections to MariaDB_112.
When the sessions are resumed, client requests will again be processed, but they will now be routed via DiffMyService.
With maxctrl we can check that MyServer has been rewired.
The target of MyService is DiffMyService instead of MyServer1 that it used to be.
The output object returned by create tells the current state.
The sessions object shows how many sessions there are in total and how many that currently are suspended. Since there were no existing sessions in this example, they are both 0.
The state shows what Diff is currently doing. synchronizing means that it is in the process of changing MyService to useDiffMyService. sync_state shows that it is currently in the process of suspending sessions.
Syntax: status diff-service
Where:
diff-service: The name of the service created in thecreate` step.
When Diff has been started, its current status can be checked with the command status. The output is the same as what was returned when Diff was started.
The state is now comparing, which means that everything is ready and clients can connect in normal fashion.
Syntax: summary diff-service
Where:
diff-service: The name of the service created in thecreate` step.
While Diff is running, it is possible at any point to request a summary.
The summary consists of two files, one for the main server and one for the other server. The files are written to a subdirectory with the same name as the Diff service, which is created in the subdirectory diff in the data directory of MaxScale.
Assuming the data directory is the default /var/lib/maxscale, the directory would in this example be/var/lib/maxscale/diff/DiffMyService.
The names of the files will be the server name, concatenated with a timestamp. In this example, the names of the files could be:
The visualization of the results is done using the program.
Syntax: stop diff-service
Where:
diff-service: The name of the service created in thecreate` step.
The comparison can stopped with the command stop.
Stopping Diff reverses the effect of starting it:
All sessions are suspended.
In the service, 'DiffMyService' is replaced with 'MyServer1'.
The sessions are restarted.
The sessions are resumed.
As the sessions have to be suspended, it may take a while before the operation has completed. The status can be checked with the 'status' command.
Syntax: destroy diff-service
Where:
diff-service: The name of the service created in thecreate` step.
As the final step, the command destroy can be called to destroy the service.
The visualization of the data is done with the maxvisualize program, which is part of the Capture functionality. The visualization will open up a browser window to show the visualization.
If no browser opens up, the visualization URL is also printed into the command line which by default should be.
In the case of the example above, the directory where the output files are created would be /var/lib/maxscale/diff/MyService. And the files to be used when visualizing would be called something likeMyServer1_2024-05-07_140323.json andMariaDB_112_2024-05-07_140323.json. The timestamp will be different every time is executed.
The order is significant; the first argument is the baseline and the second argument the results compared to the baseline.
If the value of is something else but never, Diff will continously log results to a file whose name is the concatenation for the main and other server followed by a timestamp. In the example above, the name would be something likeMyServer1_MariaDB_112_2024-02-15_152838.json.
Each line (here expanded for readability) in the file will look like:
The meaning of the fields are as follows:
id: Running number, increases for each query, but will not be in strict increasing order if a statement needed to be EXPLAINed and the following did not.
session: The session id.
command: The protocol packet type.
query: The SQL of the query.
Instead of an explain object, there may be an explained_by array, containing the ids of similar statements (i.e. their canonical statement is the same) that were EXPLAINed.
Diff can run in a read-only or read-write mode and the mode is deduced from the replication relationship between main and_other_.
If other replicates from main, it is assumed that main is the primary. In this case Diff will, when started, stop the replication from main to other. When the comparison ends Diff will, depending on the value of either reset the replication from main to other or leave the situation as it is.
If other and main replicates from a third seriver, it is assumed main is a replica. In this case, Diff will, when started, leave the replication as it is and do nothing when the comparison ends.
If the replication relationship between main and other is anything else, Diff will refuse to start.
mainType: server
Mandatory: Yes
Dynamic: No
The main target from which results are returned to the client. Must be a server and must be one of the servers listed in .
If the connection to the main target cannot be created or is lost mid-session, the client connection will be closed.
serviceType: service
Mandatory: Yes
Dynamic: No
Specifies the service Diff will modify.
explainType:
Mandatory: No
Dynamic: Yes
Values: none, other, `both'
Specifies whether a request should be EXPLAINed on only other, both other and main or neither.
explain_entriesType: non-negative integer
Mandatory: No
Dynamic: Yes
Default: 2
Specifies how many times at most a particular canonical statement is EXPLAINed during the period specified by .
explain_periodType:
Mandatory: No
Dynamic: Yes
Default: 15m
Specifies the length of the period during which at most number of EXPLAINs are executed for a statement.
max_request_lagType: non-negative integer
Mandatory: No
Dynamic: Yes
Default: 10
Specifies the maximum number of requests other may be lagging behind main before the execution of SELECTs against other are skipped to bring it back in line with main.
on_errorType:
Mandatory: No
Dynamic: Yes
Values: close, ignore
Specifies whether an error from other, will cause the session to be closed. By default it will not.
percentileType: count
Mandatory: No
Dynamic: Yes
Min: 1
Specifies the percentile of sampels that will be considered when calculating the width and number of bins of the histogram.
qps_windowType:
Mandatory: No
Dynamic: No
Default: 15m
Specifies the size of the sliding window during which QPS is calculated and stored. When a is requested, the QPS information will also be saved.
reportType:
Mandatory: No
Dynamic: Yes
Values: always, on_discrepancy, never
Specifies when the results of executing a statement on other and main should be logged; always, when there is a significant difference or_never_.
reset_replicationType:
Mandatory: No
Dynamic: Yes
Default: true
If Diff has started in read-write mode and the value ofreset_replication is true, when the comparison ends it will execute the following on other:
If Diff has started in read-only mode, the value of reset_replication will be ignored.
Note that since Diff writes updates directly to both main and_other_ there is no guarantee that it will be possible to simply start the replication. Especially not if gtid_strict_mode is on.
retain_faster_statementsType: non-negative integer
Mandatory: No
Dynamic: Yes
Default: 5
Specifies the number of faster statements that are retained in memory. The statements will be saved in the summary when the comparison ends, or when Diff is explicitly instructed to do so.
retain_slower_statementsType: non-negative integer
Mandatory: No
Dynamic: Yes
Default: 5
samplesType: count
Mandatory: No
Dynamic: Yes
Min: 100
Specifies the number of samples that will be collected in order to define the edges and number of bins of the histograms.
Diff is not capable of adapting to any changes made in the cluster configuration. For instance, if Diff starts up in read-only mode and main is subsequently made primary, Diff will not sever the replication from main to other. The result will be that other receives the same writes twice; once via the replication from the server it is replicating from and once when Diff executes the same writes.
Diff is not compatible with . If configuration synchronization is enabled, an attempt to create a Diff router will fail.
This page is licensed: CC BY-SA / Gnu FDL
Implement database sharding with a single logical entry point. The SchemaRouter routes queries to specific backend servers based on the database or table being accessed.
The SchemaRouter provides an easy and manageable sharding solution by building a single logical database server from multiple separate ones. Each database is shown to the client and queries targeting unique databases are routed to their respective servers. In addition to providing simple database-based sharding, the schemarouter also enables cross-node session variable usage by routing all queries that modify the session to all nodes.
By default the SchemaRouter assumes that each database and table is only located on one server. If it finds the same database or table on multiple servers, it will close the session with the following error:
The exception to this rule are the system tables mysql, information_schema
new-server: The server that should be compared to used-server.
The sessions are restarted, which will cause existing connections to MyServer1 to be closed and new ones to be created, via Diff, to both MyServer1 and MariaDB_112.
The sessions are resumed, which means that the client traffic will continue.
results: Array of results.
target: The server the result relates to.
checksum: The checksum of the result.
rows: How many rows were returned.
warnings: The number of warnings.
duration: The execution duration in nanonseconds.
type: What type of result resultset, ok or error.
explain: The result of EXPLAIN FORMAT=JSON statement.
Default: both
Default: ignore
Default: 99
Default: on_discrepancy
[MyServer1]
type=server
address=192.168.1.2
port=3306
[MyService]
type=service
router=readwritesplit
servers=MyServer1
...[MariaDB_112]
type=server
address=192.168.1.3
port=3306
protocol=mariadbbackendmaxctrl call command diff create DiffMyService MyService MyServer1 MariaDB_112
{
"status": "Diff service 'DiffMyService' created. Server 'MariaDB_112' ready to be evaluated."
}maxctrl list services
┌───────────────┬────────────────┬─────────────┬───────────────────┬────────────────────────┐
│ Service │ Router │ Connections │ Total Connections │ Targets │
├───────────────┼────────────────┼─────────────┼───────────────────┼────────────────────────┤
│ MyService │ readwritesplit │ 0 │ 0 │ MyServer1 │
├───────────────┼────────────────┼─────────────┼───────────────────┼────────────────────────┤
│ DiffMyService │ diff │ 0 │ 0 │ MyServer1, MariaDB_112 │
└───────────────┴────────────────┴─────────────┴───────────────────┴────────────────────────┘maxctrl call command diff start DiffMyService
{
"sessions": {
"suspended": 0,
"total": 0
},
"state": "synchronizing",
"sync_state": "suspending_sessions"
}maxctrl list services
┌───────────────┬────────────────┬─────────────┬───────────────────┬────────────────────────┐
│ Service │ Router │ Connections │ Total Connections │ Targets │
├───────────────┼────────────────┼─────────────┼───────────────────┼────────────────────────┤
│ MyService │ readwritesplit │ 0 │ 0 │ DiffMyService │
├───────────────┼────────────────┼─────────────┼───────────────────┼────────────────────────┤
│ DiffMyService │ diff │ 0 │ 0 │ MyServer1, MariaDB_112 │
└───────────────┴────────────────┴─────────────┴───────────────────┴────────────────────────┘{
"sessions": {
"suspended": 0,
"total": 0
},
"state": "synchronizing",
"sync_state": "suspending_sessions"
}maxctrl call command diff status DiffMyService
{
"sessions": {
"suspended": 0,
"total": 0
},
"state": "comparing",
"sync_state": "not_applicable"
}maxctrl call command diff summary DiffMyService
OKMyServer1_2024-05-07_140323.json
MariaDB_112_2024-05-07_140323.jsonmaxctrl call command diff stop DiffMyService
{
"sessions": {
"suspended": 0,
"total": 0
},
"state": "stopping",
"sync_state": "suspending_sessions"
}maxctrl call command diff destroy DiffMyService
OKmaxvisualize MyServer1_2024-05-07_140323.json MariaDB_112_2024-05-07_140323.json{
"id": 1,
"session": 1,
"command": "COM_QUERY",
"query": "select @@version_comment limit 1",
"results": [
{
"target": "MyServer1",
"checksum": "0f491b37",
"rows": 1,
"warnings": 0,
"duration": 257805,
"type": "resultset",
"explain": { ... }
},
{
"target": "MariaDB_112",
"checksum": "0f491b37",
"rows": 1,
"warnings": 0,
"duration": 170043,
"type": "resultset",
"explain": { ... }
}
]
}RESET SLAVE
START SLAVEperformance_schemasysIf duplicate tables are expected, use the ignore_tables_regex parameter to control which duplicate tables are allowed. To disable the duplicate database detection, useignore_tables_regex=.*. Starting with MaxScale 25.01, the detection of duplicate tables can be turned off with allow_duplicates=true.
Schemarouter compares table and database names case-insensitively. This means that the tables test.t1 and test.T1 are assumed to refer to the same table.
The main limitation of SchemaRouter is that aside from session variable writes and some specific queries, a query can only target one server. This means that queries which depend on results from multiple servers give incorrect results. See Limitations for more information.
From 2.3.0 onwards, SchemaRouter is capable of limited table family sharding.
Older versions of MaxScale required that the auth_all_servers parameter was enabled in order for the schemarouter services to load the authentication data from all servers instead of just one server. Starting with MaxScale 24.02, the schemarouter automatically fetches the authentication data from all servers and joins it together. At the same time, the auth_all_servers parameter has been deprecated and is ignored if present in the configuration.
If a command modifies the session state by modifying any session or user variables, the query is routed to all nodes. These statements include SET statements as well as any other statements that modify the behavior of the client.
If a client changes the default database after connecting, either with a USE <db> query or a COM_INIT_DB command, the query is routed to all servers that contain the database. This same logic applies when a client connects with a default database: the default database is set only on servers that actually contain it.
If a query targets one or more tables that the schemarouter has discovered during the database mapping phase, the query is only routed if a server is found that contains all of the tables that the query uses. If no such server is found, the query is routed to the server that was previously used or to the first available backend if none have been used. If a query uses a table but doesn't define the database it is in, it is assumed to be located on the default database of the connection.
If a query targets a table or a database that is present on all nodes (e.g. information_schema) and the connection is using a default database, the query is routed based on the default database. This makes it possible to control where queries that do match a specific node are routed. If the connection is not using a default database, the query is routed based solely on the tables it contains.
If a query uses a table that is unknown to the schemarouter or executes a command that doesn't target a table, the query is routed to a server contains the current active default database. If the connection does not have a default database, the query is routed to the backend that was last used or to the first available backend if none have been used. If the query contains a routing hint that directs it to a server, the query is routed there.
This means that all administrative commands, replication related command as well as certain transaction control statements (XA transaction) are routed to the first available server in certain cases. To avoid problems, use routing hints to direct where these statements should go.
Starting with MaxScale 6.4.5, transaction control commands (BEGIN, COMMIT and ROLLBACK) are routed to all nodes. Older versions of MaxScale routed the queries to the first available backend. This means that cross-shard transactions are technically possible but, without external synchronization, the transactions are not guaranteed to be globally consistent.
LOAD DATA LOCAL INFILE commands are routed to the first available server that contains the tables listed in the query.
To check how databases and tables map to servers, execute the special querySHOW SHARDS. The query does not support any modifiers such as LIKE.
The schemarouter will also intercept the SHOW DATABASES command and generate it based on its internal data. This means that newly created databases will not show up immediately and will only be visible when the cached data has been updated.
The schemarouter maps each of the servers to know where each database and table is located. As each user has access to a different set of tables and databases, the result is unique to the username and the set of servers that the service uses. These results are cached by the schemarouter. The lifetime of the cached result is controlled by the refresh_interval parameter.
When a server needs to be mapped, the schemarouter will route a query to each of the servers using the client's credentials. While this query is being executed, all other sessions that would otherwise share the cached result will wait for the update to complete. This waiting functionality was added in MaxScale 2.4.19, older versions did not wait for existing updates to finish and would perform parallel database mapping queries.
Here is an example configuration of the schemarouter:
The module generates the list of databases based on the servers parameter using the connecting client's credentials. The user and password parameters define the credentials that are used to fetch the authentication data from the database servers. The credentials used only require the same grants as mentioned in the configuration documentation.
The list of databases is built by sending a SHOW DATABASES query to all the servers. This requires the user to have at least USAGE and SELECT grants on the databases that need be sharded.
If you are connecting directly to a database or have different users on some of the servers, you need to get the authentication data from all the servers. You can control this with the auth_all_servers parameter. With this parameter, MariaDB MaxScale forms a union of all the users and their grants from all the servers. By default, the schemarouter will fetch the authentication data from all servers.
For example, if two servers have the database shard and the following rights are granted only on one server, all queries targeting the databaseshard would be routed to the server where the grants were given.
This would in effect allow the user 'john' to only see the database 'shard' on this server. Take notice that these grants are matched against MariaDB MaxScale's hostname instead of the client's hostname. Only user authentication uses the client's hostname and all other grants use MariaDB MaxScale's hostname.
Type: boolean
Mandatory: No
Dynamic: Yes
Default: false
If enabled, the detection of duplicated tables on shards is not done.
This parameter was added in MaxScale 25.01 and it is a more convenient and efficient way to disable the duplicate table detection that previously was only possible with ignore_tables_regex=.*.
Type: stringlist
Mandatory: No
Dynamic: Yes
Default: ""
List of full table names (e.g. db1.t1) to ignore when checking for duplicate tables. By default no tables are ignored.
This parameter was once called ignore_databases.
Type: regex
Mandatory: No
Dynamic: No
Default: ""
A PCRE2 regular expression that is matched against database names when checking for duplicate databases. By default no tables are ignored.
The following configuration ignores duplicate tables in the databases db1 and db2, and all tables starting with "t" in db3.
This parameter was once called ignore_databases_regex.
This parameter has been moved to the MaxScale core in MaxScale 6.0.
This parameter has been moved to the MaxScale core in MaxScale 6.0.
Type: boolean
Mandatory: No
Dynamic: No
Default: false
Enable database map refreshing mid-session. These are triggered by a failure to change the database i.e. USE ... queries. This feature is disabled by default.
Before MaxScale 6.2.0, this parameter did nothing. Starting with the 6.2.0 release of MaxScale this parameter now works again but it is disabled by default to retain the same behavior as in older releases.
Type: duration
Mandatory: No
Dynamic: Yes
Default: 300s
The minimum interval between database map refreshes in seconds. The default value is 300 seconds.
If no explicit unit is provided, the value is interpreted as seconds in MaxScale 2.4. In subsequent versions a value without a unit may be rejected. Note that since the granularity of the intervaltimeout is seconds, a timeout specified in milliseconds will be rejected, even if the duration is longer than a second.
Type: duration
Mandatory: No
Dynamic: Yes
Default: 150s
The time how long stale database map entries can be used while an update is in progress. When a database map entry goes stale, the next connection to be created will start an update of the database map. While this update is ongoing, other connections can use the stale entry for up to max_staleness seconds. If this limit is exceeded and the update still hasn't completed, new connections will instead block and wait for the update to finish.
This feature was added in MaxScale 23.08.0. Older versions of MaxScale always waited for the update to complete when the database map entry went stale.
This functionality was introduced in 2.3.0.
If the same database exists on multiple servers, but the database contains different tables in each server, SchemaRouter is capable of routing queries to the right server, depending on which table is being addressed.
As an example, suppose the database db exists on servers server1 and server2, but that the database on server1 contains the table tbl1 and on server2 contains the table tbl2. The query SELECT * FROM db.tbl1 will be routed to server1 and the querySELECT * FROM db.tbl2 will be routed to server2. As in the example queries, the table names must be qualified with the database names for table-level sharding to work. Specifically, the query series below is not supported.
The router_diagnostics output for a schemarouter service contains the following fields.
shard_map_hits: Cache hits for the shard map cache.
shard_map_misses: Cache misses for the shard map cache.
In MaxScale 24.02, the queries sescmd_percentage, longest_sescmd_chain,times_sescmd_limit_exceeded, longest_session, shortest_session andaverage_session statistics have been replaced by core service statistics.
Read Module Commands documentation for details about module commands.
The schemarouter supports the following module commands.
Invalidates the database map cache of the given service. This can be used to schedule the updates to the database maps to happen at off-peak hours by configuring a high value for refresh_interval and invalidating the cache externally.
Clears the database map cache of the given service. This forces new connections to use a freshly retrieved entry.
If the set of databases and tables in each shard is very large, the update can take some time. If there are stale cache entries and max_staleness is configured to be higher than the time it takes to update the database map, the invalidation will only slow down one client connection that ends up doing the update. When the cache is cleared completely, all clients will have to wait for the update to complete. In general, cache invalidation should be preferred over cache clearing.
Cross-database queries (e.g. SELECT column FROM database1.table UNION select column FROM database2.table) are not properly supported. Such queries are routed either to the first explicit database in the query, the current database in use or to the first available database, depending on which succeeds.
Without a default database, queries that do not use fully qualified table names and which do not modify the session state (e.g. SELECT * FROM t1) will be routed to the first available server. This includes queries such as explicit transaction commands (BEGIN, COMMIT, ROLLBACK), all non-table CREATE commands (CREATE DATABASE, CREATE SEQUENCE) as well as any SELECT statements that do not directly refer to a table. CREATE commands should be done directly on the node or the router should be equipped with the hint filter and a routing hint should be used. Queries that modify the session state (e.g. SET autocommit=1) will be routed to all servers regardless of the default database. For explicit transactions, the recommended way is to use SET autocommit=0 to start a transaction and SET autocommit=1 to commit it, otherwise routing hints are required to correctly route the transaction control commands. changed the routing of transaction control commands to route them to all servers used by the schemarouter.
SELECT queries that modify session variables are not supported because uniform results cannot be guaranteed. If such a query is executed, the behavior of the router is undefined. To work around this limitation, the query must be executed in separate parts.
If a query targets a database the SchemaRouter has not mapped to a server, the query will be routed to the first available server. This possibly returns an error about database rights instead of a missing database.
Prepared statement support is limited. PREPARE, EXECUTE and DEALLOCATE are routed to the correct backend if the statement is known and only requires one backend server. EXECUTE IMMEDIATE is not supported and is routed to the first available backend and may give wrong results. Similarly, preparing a statement from a variable (e.g. PREPARE stmt FROM @a) is not supported and may be routed wrong.
SHOW DATABASES is handled by the router instead of routed to a server. The router only answers correctly to the basic version of the query. Any modifiers such as LIKE are ignored. Starting with MaxScale 22.08, the database names will always be in lowercase.
SHOW TABLES is routed to the server with the current database. If using table-level sharding, the results will be incomplete. Similarly, SHOW TABLES FROM db1 is routed to the server with database db1, ignoring table sharding. Use SHOW SHARDS to get results from the router itself. Starting with MaxScale 22.08, the database names will always be in lowercase.
USE db1 is routed to the server with db1. If the database is divided to multiple servers, only one server will get the command.
Here is a small tutorial on how to set up a sharded database.
This page is licensed: CC BY-SA / Gnu FDL
ERROR 5000 (DUPDB): Error: duplicate tables found on two different shards.show shards;
Database |Server |
---------|-------------|
db1.t1 |MyServer1 |
db1.t2 |MyServer1 |
db2.t1 |MyServer2 |[Shard-Router]
type=service
router=schemarouter
servers=server1,server2
user=myuser
password=mypwd# Execute this on both servers
CREATE USER 'john'@'%' IDENTIFIED BY 'password';
# Execute this only on the server where you want the queries to go
GRANT SELECT,USAGE ON shard.* TO 'john'@'%';[Shard-Router]
type=service
router=schemarouter
servers=server1,server2
user=myuser
password=mypwd
ignore_tables_regex=^db1|^db2|^db3\.tUSE db;
SELECT * FROM tbl1; // May be routed to an incorrect backend if using table sharding.Optimize query performance with read-write splitting. This router directs write queries to the primary server and load balances read queries across available replica nodes.
This document provides a short overview of the readwritesplit router module and its intended use case scenarios. It also displays all router configuration parameters with their descriptions. A list of current limitations of the module is included and use examples are provided.
The readwritesplit router is designed to increase the read-only processing capability of a cluster while maintaining consistency. This is achieved by splitting the query load into read and write queries. Read queries, which do not modify data, are spread across multiple nodes while all write queries will be sent to a single node.
The router is designed to be used with a traditional Primary-Replica replication cluster. It automatically detects changes in the primary server and will use the current primary server of the cluster. With a Galera cluster, one can achieve a resilient setup and easy primary failover by using one of the Galera nodes as a Write-Primary node, where all write queries are routed, and spreading the read load over all the nodes.
Maintenance and Draining stateWhen a server that readwritesplit uses is put into maintenance mode, any ongoing requests are allowed to finish before the connection is closed. If the server that is put into maintenance mode is a primary, open transaction are allowed to complete before the connection is closed. Note that this means neither idle session nor long-running transactions will be closed by readwritesplit. To forcefully close the connections, use the following command:
If a server is put into the Draining state while a connection is open, the connection will be used normally. Whenever a new connection needs to be created, whether that be due to a network error or when a new session being opened, only servers that are neither Draining nor Drained will be used.
Readwritesplit router-specific settings are specified in the configuration file of MariaDB MaxScale in its specific section. The section can be freely named but the name is used later as a reference in a listener section.
For more details about the standard service parameters, refer to the .
All router parameters can be configured at runtime. Usemaxctrl alter service to modify them. The changed configuration will only be taken into use by new sessions.
max_slave_connectionsType: integer
Mandatory: No
Dynamic: Yes
Min: 0
max_slave_connections sets the maximum number of replicas a router session uses at any moment. The default is to use at most 255 replica connections per client connection. In older versions the default was to use all available replicas with no limit.
For example, if you have configured MaxScale with one primary and three replicas and set max_slave_connections=2, for each client connection a connection to the primary and two replica connections would be opened. The read query load balancing is then done between these two replicas and writes are sent to the primary.
By tuning this parameter, you can control how dynamic the load balancing is at the cost of extra created connections. With a lower value ofmax_slave_connections, less connections per session are created and the set of possible replica servers is smaller. With a higher value in max_slave_connections, more connections are created which requires more resources but load balancing will almost always give the best single query response time and performance. Longer sessions are less affected by a highmax_slave_connections as the relative cost of opening a connection is lower.
max_slave_connections=0When readwritesplit is configured with max_slave_connections=0, readwritesplit will behave slightly differently in that it will route all reads to the current master server. This is a convenient way to force all of the traffic to go to a single node while still being able to leverage the replay and reconnection features of readwritesplit.
In this mode, the behavior of master_failure_mode=fail_on_write also changes slightly. If the current Master server fails and a read is done when there's no other Master server available, the connection will be closed. This is done to prevent an extra slave connection from being opened that would not be closed if a new Master server would arrive.
slave_connectionsType: integer
Mandatory: No
Dynamic: Yes
Default: 255
This parameter controls how many replica connections each new session starts with. The default value is 255 which is the same as the default value ofmax_slave_connections.
In contrast to max_slave_connections, slave_connections serves as a soft limit on how many replica connections are created. The number of replica connections can exceed slave_connections if the load balancing algorithm finds an unconnected replica server better than all other replicas.
Setting this parameter to 1 allows faster connection creation and improved resource usage due to the smaller amount of initial backend connections. It is recommended to use slave_connections=1 when the lifetime of the client connections is short.
max_replication_lagType:
Mandatory: No
Dynamic: Yes
Default: 0s
NOTE Up until 23.02, this parameter was called max_slave_replication_lag, which has been deprecated but still works as an alias for max_replication_lag.
Specify how many seconds a replica is allowed to be behind the primary. The lag of a replica must be less than the configured value in order for it to be used for routing. If set to 0s (the default value), the feature is disabled.
The replica lag must be less than max_replication_lag. This means that it is possible to define, with max_replication_lag=1s, that all replicas must be up to date in order for them to be used for routing.
Note that this feature does not guarantee that writes done on the primary are visible for reads done on the replica. This is mainly due to the method of replication lag measurement. For a feature that guarantees this, refer to .
The lag is specified as documented . Note that since the granularity of the lag is seconds, a lag specified in milliseconds will be rejected, even if the duration is longer than a second.
The Readwritesplit-router does not detect the replication lag itself. A monitor such as the MariaDB-monitor for a Primary-Replica cluster is required. This option only affects Primary-Replica clusters. Galera clusters do not have a concept of replica lag even if the application of write sets might have lag. When a server is disqualified from routing because of replication lag, a warning is logged. Similarly, when the server has caught up enough to be a valid routing target, another warning is logged. These messages are only logged when a query is being routed and the replication state changes.
Starting with MaxScale versions 23.08.7, 24.02.3 and 25.01.1, readwritesplit will discard connections to any servers that have excessive replication lag. The connection will be discarded if a server is lagging behind by more than twice the amount of max_replication_lag and the server is behind by more than 300 seconds (replication lag > MAX(300, 2 * max_replication_lag)).
use_sql_variables_inType:
Mandatory: No
Dynamic: Yes
Values: master, all
This parameter controls how SELECT statements that use SQL user variables are handled. Here is an example of such a query that uses it to return an increasing row number for a resultset:
By default MaxScale will route both the SET and SELECT statements to all nodes. Any future reads of the user variables can also be performed on any node.
The possible values for this parameter are:
all (default)
Modifications to user variables inside SELECT statements as well as reads of user variables are routed to all servers. Versions before MaxScale 22.08 returned an error if a user variable was modified inside of a SELECT statement when use_sql_variables_in=all was used. MaxScale 22.08 will instead route the query to all servers and discard the extra results.
DML statements, such as INSERT, UPDATE or DELETE, that modify SQL user variables are still treated as writes and are only routed to the primary server. For example, after the following query the value of @myid is no longer the same on all servers and the SELECT statement can return different values depending where it ends up being executed:
master_reconnectionType:
Mandatory: No
Dynamic: Yes
Default: true (>= MaxScale 24.02), false(<= MaxScale 23.08)
Allow the primary server to change mid-session. This feature requires that is not used.
Starting with MaxScale 24.02, if is enabled,master_reconnection will be automatically disabled.
When a readwritesplit session starts, it will pick a primary server as the current primary server of that session. When master_reconnection is disabled, when this primary server is lost or changes to another server, the connection will be closed.
When master_reconnection is enabled, readwritesplit can sometimes recover a lost connection to the primary server. This largely depends on the value ofmaster_failure_mode.
With master_failure_mode=fail_instantly, the primary server is only allowed to change to another server. This change must happen without a loss of the primary server.
With master_failure_mode=fail_on_write, the loss of the primary server is no longer a fatal error: if a replacement primary server appears before any write queries are received, readwritesplit will transparently reconnect to the new primary server.
In both cases the change in the primary server can only take place if is enabled or has not yet been exceeded and the session does not have an open transaction.
The recommended configuration is to use master_reconnection=true andmaster_failure_mode=fail_on_write. This provides improved fault tolerance without any risk to the consistency of the database.
slave_selection_criteriaType:
Mandatory: No
Dynamic: Yes
Values: least_current_operations, adaptive_routing, least_behind_master
This option controls how the readwritesplit router chooses the replicas it connects to and how the load balancing is done. The default behavior is to route read queries to the replica server with the lowest amount of ongoing queries i.e. least_current_operations.
The option syntax:
Where <criteria> is one of the following values.
least_current_operations (default), the replica with least active operations
adaptive_routing, based on server average response times.
least_behind_master, the replica with smallest replication lag
least_current_operations uses the current number of active operations (i.e. SQL queries) as the load balancing metric and it optimizes for maximal query throughput. Each query gets routed to the server with the least active operations which results in faster servers processing more traffic.
adaptive_routing uses the server response time and current estimated server load as the load balancing metric. The server that is estimated to finish an additional query first is chosen. A modified average response time for each server is continuously updated to allow slow servers at least some traffic and quickly react to changes in server load conditions. This selection criteria is designed for heterogeneous clusters: servers of differing hardware, differing network distances, or when other loads are running on the servers (including a backup). If the servers are queried by other clients than MaxScale, the load caused by them is indirectly taken into account.
least_behind_master uses the measured replication lag as the load balancing metric. This means that servers that are more up-to-date are favored which increases the likelihood of the data being read being up-to-date. However, this is not as effective as causal_reads would be as there's no guarantee that writes done by the same connection will be routed to a server that has replicated those changes. The recommended approach is to useLEAST_CURRENT_OPERATIONS or ADAPTIVE_ROUTING in combination withcausal_reads.
NOTE: least_global_connections and least_router_connections should not be used, they are legacy options that exist only for backwards compatibility. Using them will result in skewed load balancing as the algorithm uses a metric that's too coarse (number of connections) to load balance something that's finer (individual SQL queries).
The least_global_connections and least_router_connections use the connections from MariaDB MaxScale to the server, not the amount of connections reported by the server itself.
Starting with MaxScale versions 2.5.29, 6.4.11, 22.08.9, 23.02.5 and 23.08.1, lowercase versions of the values are also accepted. For example, slave_selection_criteria=LEAST_CURRENT_OPERATIONS and slave_selection_criteria=least_current_operations are both accepted as valid values.
Starting with MaxScale 23.08.1, the legacy uppercase values have been deprecated. All runtime modifications of the parameter will now be persisted in lowercase. The uppercase values are still accepted but will be removed in a future MaxScale release.
master_accept_readsType:
Mandatory: No
Dynamic: Yes
Default: false
Enables the primary server to be used for reads. This is a useful option to enable if you are using a small number of servers and wish to use the primary for reads as well and the load on it does not reduce the write throughput of the cluster.
By default, no reads are sent to the primary as long as there is a valid replica server available. If no replicas are available, reads are sent to the primary regardless of the value of master_accept_reads.
strict_multi_stmtType:
Mandatory: No
Dynamic: Yes
Default: false
When a client executes a multi-statement query, it will be treated as if it were a DML statement and routed to the primary. If the option is enabled, all queries after a multi-statement query will be routed to the primary to guarantee a consistent session state.
If the feature is disabled, queries are routed normally after a multi-statement query.
Warning: Enable the strict mode only if you know that the clients will send statements that cause inconsistencies in the session state.
strict_sp_callsType:
Mandatory: No
Dynamic: Yes
Default: false
Similar to strict_multi_stmt, this option allows all queries after a CALL operation on a stored procedure to be routed to the primary.
All warnings and restrictions that apply to strict_multi_stmt also apply tostrict_sp_calls.
strict_tmp_tablesType:
Mandatory: No
Dynamic: Yes
Default: true (>= MaxScale 24.02), false (<= MaxScale 23.08)
When strict_tmp_tables is disabled, all temporary tables are lost when a reconnection of the primary node occurs. This means that if a reconnection to the primary takes place, temporary tables might appear to disappear in the middle of a connection.
When strict_tmp_tables is enabled, reconnections are prevented as long as a temporary tables exist. In this case if the primary node is lost and temporary table exist, the session is closed. If a session creates temporary tables but does not drop them, this behavior will effectively disable reconnections until the session is closed.
master_failure_modeType:
Mandatory: No
Dynamic: Yes
Values: fail_instantly, fail_on_write, error_on_write
This option controls how the failure of a primary server is handled.
The following table describes the values for this option and how they treat the loss of a primary server.
These also apply to new sessions created after the primary has failed. This means that in fail_on_write or error_on_write mode, connections are accepted as long as replica servers are available.
When configured with fail_on_write or error_on_write, sessions that are idle will not be closed even if all backend connections for that session have failed. This is done in the hopes that before the next query from the idle session arrives, a reconnection to one of the replicas is made. However, this can leave idle connections around unless the client application actively closes them. To prevent this, use the parameter.
Note: If master_failure_mode is set to error_on_write and the connection to the primary is lost, by default, clients will not be able to execute write queries without reconnecting to MariaDB MaxScale once a new primary is available. If is enabled, the session can recover if one of the replicas is promoted as the primary.
retry_failed_readsType:
Mandatory: No
Dynamic: Yes
Default: true
This option controls whether autocommit selects are retried in case of failure.
When a simple autocommit select is being executed outside of a transaction and the replica server where the query is being executed fails, readwritesplit can retry the read on a replacement server. This makes the failure of a replica transparent to the client.
If a part of the result was already delivered to the client, the query will not be retried. The retrying of queries with partially delivered results is only possible when transaction_replay is enabled.
delayed_retryType:
Mandatory: No
Dynamic: Yes
Default: false
Retry queries over a period of time.
When this feature is enabled, a failure to route a query due to a connection problem will not immediately result in an error. The routing of the query is delayed until either a valid candidate server is available or the retry timeout is reached. If a candidate server becomes available before the timeout is reached, the query is routed normally and no connection error is returned. If no candidates are found and the timeout is exceeded, the router returns to normal behavior and returns an error.
When combined with the master_reconnection parameter, failures of writes done outside of transactions can be hidden from the client connection. This allows a primary to be replaced while writes are being sent.
Starting with MaxScale 21.06.18, 22.08.15, 23.02.12, 23.08.8, 24.02.4 and 25.01.1, delayed_retry will no longer attempt to retry a query if it was already sent to the database. If a query is received while a valid target server is not available, the execution of the query is delayed until a valid target is found or the delayed retry timeout is hit. If a query was already sent, it will not be replayed to prevent duplicate execution of statements.
In older versions of MaxScale, duplicate execution of a statement can occur if the connection to the server is lost or the server crashes but the server comes back up before the timeout for the retrying is exceeded. At this point, if the server managed to read the client's statement, it will be executed. For this reason, it is recommended to only enable delayed_retry for older versions of MaxScale when the possibility of duplicate statement execution is an acceptable risk.
delayed_retry_timeoutType:
Mandatory: No
Dynamic: Yes
Default: 10s
The duration to wait until an error is returned to the client whendelayed_retry is enabled.
If no explicit unit is provided, the value is interpreted as seconds in MaxScale 2.4. In subsequent versions a value without a unit may be rejected. Note that since the granularity of the timeout is seconds, a timeout specified in milliseconds will be rejected, even if the duration is longer than a second.
transaction_replayType:
Mandatory: No
Dynamic: Yes
Default: false
Replay interrupted transactions.
Enabling this parameter enables both delayed_retry and master_reconnection and sets master_failure_mode to fail_on_write, thereby overriding any configured values for these parameters.
When the server where the transaction is in progress fails, readwritesplit can migrate the transaction to a replacement server. This can completely hide the failure of a primary node without any visible effects to the client.
If no replacement node becomes available, the client connection is closed.
To control how long a transaction replay can take, usetransaction_replay_timeout.
Please refer to the section for a more detailed explanation of what should and should not be done with transaction replay.
transaction_replay_max_sizeType:
Mandatory: No
Dynamic: Yes
Default: 1 MiB
The limit on transaction size for transaction replay in bytes. Any transaction that exceeds this limit will not be replayed. The default value is 1 MiB. This limit applies at a session level which means that the total peak memory consumption can be transaction_replay_max_size times the number of client connections.
The amount of memory needed to store a particular transaction will be slightly larger than the length in bytes of the SQL used in the transaction. If the limit is ever exceeded, a message will be logged at the info level.
The number of times that this limit has been exceeded is shown inmaxctrl show service as trx_max_size_exceeded.
transaction_replay_attemptsType: integer
Mandatory: No
Dynamic: Yes
Default: 5
The upper limit on how many times a transaction replay is attempted before giving up.
A transaction replay failure can happen if the server where the transaction is being replayed fails while the replay is in progress. In practice this parameter controls how many server and network failures a single transaction replay tolerates. If a transaction is replayed successfully, the counter for failed attempts is reset.
transaction_replay_timeoutType:
Mandatory: No
Dynamic: Yes
Default: 30s (>= MaxScale 24.02), 0s (<= MaxScale 23.08)
The time how long transactions are attempted for. To explicitly disable this feature, set the value to 0 seconds.
When transaction_replay_timeout is enabled, the time a transaction replay can take is controlled solely by this parameter. This is a more convenient and predictable method of controlling how long a transaction replay can be attempted before the connection is closed.
If delayed_retry_timeout is less than transaction_replay_timeout, it is set to the same value.
Without transaction_replay_timeout the time how long a transaction can be retried is controlled by delayed_retry_timeout andtransaction_replay_attempts. This can result in a maximum replay time limit ofdelayed_retry_timeout multiplied by transaction_replay_attempts, by default this is 50 seconds. The minimum replay time limit can be as low astransaction_replay_attempts seconds (5 seconds by default) in cases where the connection fails after it was created. Usually this happens due to problems like the max_connections limit being hit on the database server.
transaction_replay_timeout is the recommended method of controlling the timeouts for transaction replay and is by default set to 30 seconds in MaxScale 24.02.
transaction_replay_retry_on_deadlockType:
Mandatory: No
Dynamic: Yes
Default: false
Enable automatic retrying of transactions that end up in a deadlock.
If this feature is enabled and a transaction returns a deadlock error (e.g. SQLSTATE 40001: Deadlock found when trying to get lock; try restarting transaction), the transaction is automatically retried. If the retrying of the transaction results in another deadlock error, it is retried until it either succeeds or a transaction checksum error is encountered.
transaction_replay_safe_commitType:
Mandatory: No
Dynamic: Yes
Default: true
If a transaction is ending and the COMMIT statement at the end of it is interrupted, there is a risk of duplicating the transaction if it is replayed. This parameter prevents the retrying of transactions that are about to commit.
This parameter was added in MaxScale 23.08.0 and is enabled by default. The older version of MaxScale always attempted to replay the transaction even if there was a risk of duplicating the transaction.
In MaxScale 25.01.0, this parameter also disabled the replaying of individual DML statements that delayed_retry enabled. The result of this was that only statements done inside of an explicit transactions or with autocommit disabled were replayed and writes done with autocommit enabled were never replayed.
In MaxScale 25.01.1 and newer versions, where delayed_retry no longer attempts to retry a query if it was already sent to the database, write queries outside of transactions are delayed if no valid target is found but they are never retried. Thus transaction_replay_safe_commit again only affects how the COMMIT of a transaction is handled.
If the data that is about to be modified is read before it is modified and it is locked in an appropriate manner (e.g. with SELECT ... FOR UPDATE or with the SERIALIZABLE isolation level), it is safe to replay a transaction that was about to commit. This is because the checksum of the transaction will mismatch if the original transaction ended up committing on the server. Disabling this feature can enable more robust delivery of transactions but it requires that the SQL is correctly formed and compatible with this behavior.
transaction_replay_retry_on_mismatchType:
Mandatory: No
Dynamic: Yes
Default: false
Retry transactions that end in checksum mismatch.
When enabled, any replayed transactions that end with a checksum mismatch are retried until they either succeeds or one of the transaction replay limits is reached (delayed_retry_timeout, transaction_replay_timeout ortransaction_replay_attempts).
transaction_replay_checksumType:
Mandatory: No
Dynamic: Yes
Values: full, result_only, no_insert_id
Selects which transaction checksum method is used to verify the result of the replayed transaction.
Note that only transaction_replay_checksum=full is guaranteed to retain the consistency of the replayed transaction.
Possible values are:
full (default)
All responses from the server are included in the checksum. This retains the full consistency guarantee of the replayed transaction as it must match exactly the one that was already returned to the client.
result_only
optimistic_trxThis feature has been moved into the filter in MaxScale 25.01 and the parameter has been removed from readwritesplit.
causal_readsType:
Mandatory: No
Dynamic: Yes
Values: none, local, global
Enable causal reads. This feature requires MariaDB 10.2.16 or newer to function.
If a client connection modifies the database and causal_reads is enabled, any subsequent reads performed on replica servers will be done in a manner that prevents replication lag from affecting the results.
The following table contains a comparison of the modes. Read the for more information on what a sync consists of and why minimizing the number of them is important.
The fast, fast_global and fast_universal modes should only be used when low latency is more important than proper distribution of reads. These modes should only be used when the workload is mostly read-only with only occasional writes. If used with a mixed or a write-heavy workload, the traffic will end up being routed almost exclusively to the primary server.
Note: This feature also enables multi-statement execution of SQL in the protocol. This is equivalent to using allowMultiQueries=true in or using CLIENT_MULTI_STATEMENTS and CLIENT_MULTI_RESULTS in the Connector/C. The section explains why this is necessary.
The possible values for this parameter are:
none (default)
Read causality is disabled.
local
causal_readsThis feature is based on the MASTER_GTID_WAIT function and the tracking of server-side status variables. By tracking the latest GTID that each statement generates, readwritesplit can then perform a synchronization operation with the help of the MASTER_GTID_WAIT function.
If the replica has not caught up to the primary within the configured time, as specified by , it will be retried on the primary.
The exception to this rule is the fast mode which does not do any synchronization at all. This can be done as any reads that would go to out-of-date servers will be re-routed to the current primary.
Normal SQL
A practical example can be given by the following set of SQL commands executed with autocommit=1.
As the statements are not executed inside a transaction, from the load balancer's point of view, the latter statement can be routed to a replica server. The problem with this is that if the value that was inserted on the primary has not yet replicated to the server where the SELECT statement is being performed, it can appear as if the value we just inserted is not there.
By prefixing these types of SELECT statements with a command that guarantees consistent results for the reads, read scalability can be improved without sacrificing consistency.
The set of example SQL above will be translated by MaxScale into the following statements.
The SET command will synchronize the replica to a certain logical point in the replication stream (see for more details). If the synchronization fails, the query will not run and it will be retried on the server where the transaction was originally done.
Prepared Statements
Binary protocol prepared statements are handled in a different manner. Instead of adding the synchronization SQL into the original SQL query, it is sent as a separate packet before the prepared statement is executed.
We'll use the same example SQL but use a binary protocol prepared statement for the SELECT:
The SQL that MaxScale executes will be the following:
Both the synchronization query and the execution of the prepared statement are sent at the same time. This is done to remove the need to wait for the result of the synchronization query before routing the execution of the prepared statement. This keeps the performance of causal_reads for prepared statements the same as it is for normal SQL queries.
As a result of this, each time the synchronization query times out, the connection will be killed by the KILL statement and readwritesplit will retry the query on the primary. This is done to prevent the execution of the prepared statement that follows the synchronization query from being processed by the MariaDB server.
It is recommend that the session command history is enabled whenever prepared statements are used with causal_reads. This allows new connections to be created whenever a causal read times out.
A failed causal read inside of a read-only transaction started with START TRANSACTION READ ONLY will return the following error:
Older versions of MaxScale attempted to retry the command on the current primary server which would cause the connection to be closed and a warning to be logged.
Starting with MaxScale 24.02.5, the fast modes fast, fast_global andfast_universal work with Galera clusters. In older versions, none of thecausal_reads modes worked with Galera. The non-fast modes that rely on the function still do not work with Galera. This is because Galera does not implement a mechanism that allows a client to wait for a particular GTID.
If the combination of the original SQL statement and the modifications added to it by readwritesplit exceed the maximum packet size (16777213 bytes), the causal read will not be attempted and a non-causal read is done instead. This applies only to text protocol queries as the binary protocol queries use a different synchronization mechanism.
causal_reads_timeoutType:
Mandatory: No
Dynamic: Yes
Default: 10s
The timeout for the replica synchronization done by causal_reads.
If no explicit unit is provided, the value is interpreted as seconds in MaxScale 2.4. In subsequent versions a value without a unit may be rejected. Note that since the granularity of the timeout is seconds, a timeout specified in milliseconds will be rejected, even if the duration is longer than a second.
lazy_connectType:
Mandatory: No
Dynamic: Yes
Default: false
Lazy connection creation causes connections to backend servers to be opened only when they are needed. This reduces the load that is placed on the backend servers when the client connections are short.
Normally readwritesplit opens as many connections as it can when the session is first opened. This makes the execution of the first query faster when all available connections are already created. When lazy_connect is enabled, this initial connection creation is skipped. If the client executes only read queries, no connection to the primary is made. If only write queries are made, only the primary connection is used.
In MaxScale 23.08.2, if a is received as the first command, the default behavior is to execute it on a replica. If is enabled, the query is executed on the primary server, if one is available. In practice this means that workloads which are mostly reads with infrequent writes should disablemaster_accept_reads if they also use lazy_connect.
Older versions of MaxScale always tried to execute all session commands on the primary node if one was available.
reuse_prepared_statementsThis feature has been moved into the filter in MaxScale 25.01 and the parameter has been removed from readwritesplit.
sync_transactionType:
Mandatory: No
Dynamic: Yes
Values: none, soft, hard
Synchronize transactions on one or more replicas.
This feature synchronizes all committed transactions on one or more replicas by waiting for the transaction to be replicated. It has two modes of operation: the soft mode synchronizes the transactions but fails silently if a synchronization timeout occurs whereas the hard mode will close the client session if synchronization times out.
The soft mode can be used to limit the amount of replication lag that the cluster will see. In this mode, the sync_transaction_timeout setting controls the maximum amount of time that a client will wait for a transaction to be synchronized. If the timeout is exceeded, the processing of client requests proceeds normally. This throttles the rate at which transactions arrive while still allowing new transactions to be committed even if there is a network outage or the replication is lagging behind too much.
The hard mode behaves in a similar manner to that of the soft mode except that if a timeout occurs, the client connection is closed. In this mode, if a client receives the OK for the commit of a transaction, it means that it has been replicated and processed by at least one server in the cluster. The hard mode provides a mode of operation that is a synchronous form of replication. However it does come with the downside that if no server manages to replicate a transaction, no new transactions are successfully committed until a server becomes available and replication catches up.
Transaction synchronization in the hard mode can be used as an alternative for the semi-synchronous replication in MariaDB. In this mode, the transaction synchronization can provide a stronger guarantee of durability by requiring more servers to be fully synchronized. This use-case is for those situations where losing a minority of the cluster in one go is a possibility and that the survival of transactions is of utmost importance.
In the soft mode, it is still beneficial to have semi-synchronous replication enabled if automatic failover is used in mariadbmon. In this kind of a configuration, the transaction synchronization acts more as a replication lag avoidance mechanism rather than a replication synchronization mechanism.
This feature does not work with Galera or MySQL.
If a SQL statement produces multiple commits (i.e. generates more than one GTID), only the first transaction will be synchronized.
sync_transaction_countType: integer
Mandatory: No
Dynamic: Yes
Min: 1
The minimum number of backend servers to synchronize with.
The synchronization request is sent to all backends to which there are open connections. Once enough backend servers have been successfully synchronized, the response to the committing of the transaction is routed to the client. By default, this happens once the fastest backend has executed the transaction.
By increasing the value of sync_transaction_count, the synchronization can be done on more servers. In the soft mode, this reduces replication lag on multiple servers while in the hard mode it makes the transactions durable on more servers.
When the hard mode is combined with the automatic failover and cooperative replication of mariadbmon, a disaster tolerant synchronous replication cluster is be formed.
If the value of max_slave_connections is lower than the value of sync_transaction_count, it is raised to match it so that a successful synchronization is possible.
sync_transaction_timeoutType:
Mandatory: No
Dynamic: Yes
Default: 10s
Timeout for the transaction synchronization. The timeout values can be given in milliseconds.
This is the maximum time that a transaction will wait for synchronization. In the soft mode, the result is returned if the synchronization times out and in the hard mode, the connection is closed.
For example, with sync_transaction=soft and sync_transaction_timeout=3s, the synchronization of a COMMIT will take at most 3 seconds after which the result is always returned to the client, regardless of whether it was synchronized or not.
sync_transaction_max_lagType:
Mandatory: No
Dynamic: Yes
Default: 0s
Upper limit of allowed synchronization lag. This setting only affects the soft mode of transaction synchronization. If sync_transaction=hard is used, this setting is ignored.
If this setting is set to zero (the default), all transactions are always synchronized and the replication self-regulates the rate of transaction execution. The downside of this approach is that all transactions fully synchronize with at least one node which causes all commits to suffer the latency penalty. When replication is not the performance bottleneck, this overhead is unnecessary.
When sync_transaction_max_lag is configured, a single transaction is used to probe the synchronization lag while other transactions are allowed to execute in parallel without synchronization. Once the measured synchronization lag exceeds the configured limit, all transactions will be synchronized.
When the value of sync_transaction_max_lag is higher than the value of sync_transaction_timeout, the replication lag as reported by the monitor is used to determine when to start synchronizing all transactions.
Very high values of sync_transaction_max_lag combined with high values of sync_transaction_timeout may cause oscillations in the commit times of transactions and thus it's recommended to keep the maximum lag relatively low.
The router_diagnostics output for a readwritesplit service contains the following fields.
queries: Number of queries executed through this service.
route_master: Number of writes routed to primary.
route_slave: Number of reads routed to replicas.
The general rule with server ranks is that primary servers will be used before secondary servers. Readwritesplit is an exception to this rule. The following rules govern how readwritesplit behaves with servers that have different ranks.
Sessions will use the current primary server as long as possible. This means that sessions with a secondary primary will not use the main primary as long as the secondary primary is available.
All replica connections will use the same rank as the primary connection. Any stale connections with a different rank than the primary will be discarded.
If no primary connection is available and master_reconnection is enabled, a connection to the best primary is created. If the new primary has a different priority than existing connections have, the connections with a different rank will be discarded.
The readwritesplit router supports routing hints. For a detailed guide on hint syntax and functionality, please read document.
The route to master hint can be used to treat a read as if it was a write. This is useful when a read done outside of a transaction depends on a previously committed transaction that may not have replicated to the other servers in the cluster. Alternative automated ways of solving this are and .
All routing hints are ignored if they are done inside of a transaction. This is done to guarantee the consistency of a transaction and to make sure that a transaction through readwritesplit behaves identically to a transaction done directly against MariaDB.
The route to slave hint is always ignored by readwritesplit as it is either redundant or would cause writes to be sent to the wrong server.
The route to last and route to server <name> hints only work on reads. If they are used on a write and the target server cannot be used for writes, it is treated as a retryable error if query retrying of writes is enable
The readwritesplit router implements the following module commands.
reset-gtidThe command resets the global GTID state in the router. It can be used with causal_reads=global to reset the state. This can be useful when the cluster is reverted to an earlier state and the GTIDs recorded in MaxScale are no longer valid.
The first and only argument to the command is the router name. For example, to reset the GTID state of a readwritesplit named My-RW-Router, the following MaxCtrl command should be used:
Examples of the readwritesplit router in use can be found in the folder.
Here is a small explanation which shows what kinds of queries are routed to which type of server.
Routing to primary is important for data consistency and because majority of writes are written to binlog and thus become replicated to replicas.
The following operations are routed to primary:
DML statements (INSERT, UPDATE, DELETE etc.)
DDL statements (DROP, CREATE, ALTER etc.)
In addition to these, if the readwritesplit service is configured with the max_replication_lag parameter, and if all replicas suffer from too much replication lag, then statements will be routed to the primary. (There might be other similar configuration parameters in the future which limit the number of statements that will be routed to replicas.)
If either session_track_transaction_info=CHARACTERISTICS or session_track_system_variables=tx_isolation is configured for the MariaDB server, readwritesplit will track the transaction isolation level and lock the session to the primary when the isolation level is set to serializable. This retains the correctness of the isolation level which can otherwise cause problems.
Starting with MaxScale 23.08, once the transaction isolation level is set to something other than SERIALIZABLE, the session is no longer locked to the primary and returns to its normal state. Older versions of MaxScale remain locked to the primary even if the session goes out of the SERIALIZABLE isolation level.
The ability to route some statements to replicas is important because it also decreases the load targeted to primary. Moreover, it is possible to have multiple replicas to share the load in contrast to single primary.
The following types of queries can be routed to replicas:
Read-only statements (i.e. SELECT and SHOW) outside of transactions with autocommit enabled that only use read-only built-in functions
All statements within an explicit read-only transaction (START TRANSACTION READ ONLY)
A third class of statements includes those which modify session data, such as session system variables, user-defined variables, the default database, etc. We call them session commands, and they must be replicated as they affect the future results of read and write operations. They must be executed on all servers that could execute statements on behalf of this client.
Session commands include for example:
Commands that modify the session state (SET, USE, CHANGE USER)
Text protocol PREPARE statements
Binary protocol prepared statements
NOTE: if variable assignment is embedded in a write statement it is routed to primary only. For example, INSERT INTO t1 values(@myvar:=5, 7) would be routed to primary only.
The router stores all of the executed session commands so that in case of a connection failure, a replacement connection can be opened and the session command history can be replayed on that new connections. The number of stored session commands depends on the router configuration. For more information, refer to the documentation of .
In the following cases, a query is routed to the same server where the previous query was executed. If no previous target is found, the query is routed to the current primary.
If a query uses the FOUND_ROWS() function, it will be routed to the server where the last query was executed. This is done with the assumption that a query with SQL_CALC_FOUND_ROWS was previously executed.
COM_STMT_FETCH_ROWS will always be routed to the same server where the COM_STMT_EXECUTE was routed.
Starting with MaxScale 25.08, readwritesplit has a hard limit of 256 targets. If more than 256 targets are used in a service, only the first 256 are used.
Read queries are routed to the primary server in the following situations:
Query is executed inside an open read-write transaction
Statement includes a stored procedure or an UDF call
If there are multiple statements inside one query e.g.INSERT INTO ... ; SELECT LAST_INSERT_ID();
If a prepared statement targets a temporary table on the primary, the replica servers will fail to execute it. This will cause all replica connections to be closed (MXS-1816).
When transaction replay is enabled, readwritesplit calculates a checksum of the server responses for each transaction. This is used to determine whether a replayed transaction was identical to the original transaction. Starting with MaxScale 23.08, a 128-bit xxHash checksum is stored for each statement that is in the transaction. Older versions of MaxScale used a single 160-bit SHA1 checksum for the whole transaction.
If the results from the replacement server are not identical when the transaction is replayed, the client connection is closed. This means that any transaction with a server specific result (e.g. NOW(), @@server_id) cannot be replayed successfully but it will still be attempted.
If a transaction reads data before updating it, the rows should be locked by using SELECT ... FOR UPDATE. This will prevent overlapping transactions when multiple transactions are being replayed that modify the same set of rows.
If the connection to the server where the transaction is being executed is lost when the final COMMIT is being executed, it is impossible to know whether the transaction was successfully committed. This means that there is a possibility for duplicate transaction execution which can result in data duplication in certain cases.
In MaxScale 23.08, the transaction_replay_safe_commit variable controls whether a replay is attempted or not whenever a COMMIT is interrupted. By default the transaction will not be replayed. Older versions of MaxScale always replayed the transaction.
Data duplication can happen if the transaction consists of the following statement types:
INSERT of rows into a table that does not have an auto-increment primary key
A "blind update" of one or more rows e.g. UPDATE t SET c = c + 1 WHERE id = 123
A "blind delete" e.g. DELETE FROM t LIMIT 100
This is not an exhaustive list and any operations that do not check the row contents before performing the operation on them might face this problem.
In all cases the problem of duplicate transaction execution can be avoided by including a SELECT ... FOR UPDATE in the statement. This will guarantee that in the case that the transaction fails when it is being committed, the row is only modified if it matches the expected contents.
Similarly, a connection loss during COMMIT can also result in transaction replay failure. This happens due to the same reason as duplicate transaction execution but the retried transaction will not be committed. This can be considered a success case as the transaction replay detected that the results of the two transactions are different. In these cases readwritesplit will abort the transaction and close the client connection.
Statements that result in an implicit commit do not reset the transaction when transaction_replay is enabled. This means that if the transaction is replayed, the transaction will be committed twice due to the implicit commit being present. The exception to this are the transaction management statements such asBEGIN and START TRANSACTION: they are detected and will cause the transaction to be correctly reset.
In older versions of MaxScale, if a connection to a server is lost while a statement is being executed and the result was partially delivered to the client, readwritesplit would immediately close the session without attempting to replay the failing statement. Starting with MaxScale 23.08, this limitation no longer applies if the statement was done inside of a transaction andtransaction_replay is enabled ().
If the connection to the server where a transaction is being executed is lost while a ROLLBACK is being executed, readwritesplit will still attempt to replay the transaction in the hopes that the real response can be delivered to the client. However, this does mean that it is possible that a rolled back transaction which gets replayed ends up with a conflict and is reported as a replay failure when in reality a rolled back transaction could be safely ignored.
Any changes to the session state (e.g. autocommit state, SQL mode) done inside a transaction will remain in effect even if the connection to the server where the transaction is being executed fails. When readwritesplit creates a new connection to a server to replay the transaction, it will first restore the session state by executing all session commands that were executed. This means that if the session state is changed mid-transaction in a way that affects the results, transaction replay will fail.
The following partial transaction demonstrates the problem by using inside a transaction.
If this transaction has to be replayed the actual SQL that gets executed is the following.
First the session state is restored by executing all commands that changed the state after which the actual transaction is replayed. Due to the fact that the SQL_MODE was changed mid-transaction, one of the queries will now return an error instead of the result we expected leading to a transaction replay failure.
In a service-to-service configuration (i.e. a service using another service in its targets list ), if the topmost service starts a transaction, all lower-level readwritesplit services will also behave as if a transaction is open. If a connection to a backend database fails during this, it can result in unnecessary transaction replays which in turn can end up with checksum conflicts. The recommended approach is to not use any commands inside a transaction that would be routed to more than one node.
When a multi-statement query is executed through the readwritesplit router, it will always be routed to the primary. See for more details.
If the multi-statement query creates a temporary table, it will not be detected and reads to this table can be routed to replica servers. To prevent this, always execute the temporary table creation as an individual statement.
Whenever a session command is executed, the type of the result that was returned by the primary server is compared to the result of all the other servers. If the command succeeded on the primary, it is expected to also succeed on all other servers and conversely, if it fails it's expected to fail on all other servers as well.
If a command produces a different result than was expected, the connection to that server is permanently discarded and no further connection attempts are made to it within the same session.
The most common case where a session command will produce a different result on a replica is when a database is created on the primary and a USE <db> command is executed right after it but the creation of the database hasn't had time to replicate to the replicas before the USE <db> command arrives.
If a SELECT query modifies a user variable when the use_sql_variables_in parameter is set to all, it will be routed to all backends to keep the session state consistent. For applications where this is a common pattern, the performance overhead of this can be avoided at the cost of the user variables being inconsistent by using use_sql_variables_in=master. This will route all queries that use user variables to the primary.
This page is licensed: CC BY-SA / Gnu FDL
Default: 255
Default: all
masterModifications to user variables inside SELECT statements as well as reads of user variables are routed to the primary server. This forces more of the traffic onto the primary server but it reduces the amount of data that is discarded for any SELECT statement that also modifies a user variable. With this mode, the state of user variables is not deterministic if they are modified inside of a SELECT statement. SET statements that modify user variables are still routed to all servers.
least_router_connectionsleast_global_connectionsDefault: least_current_operations
least_global_connections, the replica with least connections from MariaDB MaxScaleleast_router_connections, the replica with least connections from this service
Default: fail_on_write (MaxScale 23.08: fail_instantly)
Default: full
Only resultsets and errors are included in the checksum. OK packets (i.e. successful queries that do not return results) are ignored. This mode is intended to be used in cases where the extra information (auto-generated ID, warnings etc.) returned in the OK packet is not used by the application. This mode is safe to use only if the auto-generated ID is not actually used by any following queries. An example of such behavior would be a transaction that ends with an INSERT into a table with an AUTO_INCREMENT field.
no_insert_id
The same as result_only but results from queries that useLAST_INSERT_ID() are also ignored. This mode is safe to use only if the result of the query is not used by any subsequent statement in the transaction.
fastfast_globaluniversalfast_universalDefault: none
universal
Cluster
High, one sync per read plus a roundtrip to the primary.
fast_universal
Cluster
Low, one roundtrip to the primary.
global
Writes are globally visible. If one connection writes a value, all connections to the same service will see it. In general this mode is slower than the local mode due to the extra synchronization it has to do. This guarantees global happens-before ordering of reads when all transactions are inside a single GTID domain.This mode gives similar benefits as the local mode in that it improves read scalability at the cost of latency. With MaxScale versions 2.5.14 and older, multi-domain use of causal_reads could cause non-causal reads to occur. Starting with MaxScale 2.5.15, this was fixed and all the GTID coordinates are passed alongside all requests which makes multi-domain GTIDs safe to use. However, this does mean that the GTID coordinates will never be reset: if replication is reset and GTID coordinates go "backwards", readwritesplit will not consider these as being newer than the ones already stored. To reset the stored GTID coordinates in readwritesplit, MaxScale must be restarted. MaxScale 6.4.11 added the new reset-gtid module command to readwritesplit. This allows the global GTID state used bycausal_reads=global to be reset without having to restart MaxScale.
fast
This mode is similar to the local mode where it will only affect the connection that does the write but where the local mode waits for a replica server to catch up, the fast mode will only use servers that are known to have replicated the write. This means that if no replica has replicated the write, the primary where the write was done will be used. The value of causal_reads_timeout is ignored in this mode. Currently the replication state is only updated by the mariadbmon monitor whenever the servers are monitored. This means that a smaller monitor_interval provides faster replication state updates and possibly better overall usage of servers. This mode is the inverse of the local mode in the sense that it improves read latency at the cost of read scalability while still retaining the causality guarantees for reads. This functionality can also be considered an improved version of the functionality that the module provides.
fast_global
This mode is identical to the fast mode except that it uses the global GTID instead of the session local one. This is similar to how local and global modes differ from each other. The value of causal_reads_timeout is ignored in this mode. Currently the replication state is only updated by the mariadbmon monitor whenever the servers are monitored. This means that a smaller monitor_interval provides faster replication state updates and possibly better overall usage of servers.
universal
The universal mode guarantees that all SELECT statements always see the latest observable transaction state on a database cluster. The basis of this is the @@gtid_current_pos variable which is read from the current primary server before each read. This guarantees that if a transaction was visible at the time the read is received by readwritesplit, the transaction is guaranteed to be complete on the replica server where the read is done. This mode is the most consistent of all the modes. It provides consistency regardless of where a write originated from but it comes at the cost of increased latency. For every read, a round trip to the current primary server is done. This means that the latency of any given SELECT statement increases by roughly twice the network latency between MaxScale and the database cluster. In addition, an extra SELECT statement is always executed on the primary which places some load on the server.
fast_universal
A mix of fast and universal. This mode that guarantees that all SELECT statements always see the latest observable transaction state but unlike the universal mode that waits on the server to catch up, this mode behaves like fast and routes the query to the current primary if no replicas are available that have caught up. This mode provides the same consistency guarantees of universal with a constant latency overhead of one extra roundtrip. However, this also puts the most load on the primary node as even a moderate write load can cause the GTIDs of replicas to lag too far behind.
SQL like INSERT ... RETURNING that commits a transaction and returns a resultset will only work with causal reads if the connector supports the DEPRECATE_EOF protocol feature. The following table contains a list of MariaDB connectors and whether they support the protocol feature.
Connector/C++
No
1.1.5
Connector/ODBC
No
3.2.5
Default: none
Default: 1
route_all: Number of session commands routed to all servers.rw_transactions: Number of explicit read-write transactions.
ro_transactions: Number of explicit read-only transactions.
replayed_transactions: Number of replayed transactions.
server_query_statistics: Statistics for each configured and used server consisting of the following fields.
id: Name of the server
total: Total number of queries.
read: Total number of reads.
write: Total number of writes.
avg_sess_duration: Average duration of a client session to this server.
avg_sess_active_pct: Average percentage of time client sessions were active. 0% means connections were opened but never used.
avg_selects_per_session: Average number of selects per session.
If no open connections exist, the servers with the best rank will used.
Stored procedure calls
User-defined function calls
Queries that use sequences (NEXT VALUE FOR seq, NEXTVAL(seq) or seq.nextval)
Statements that use any of the following functions:
LAST_INSERT_ID()
GET_LOCK()
RELEASE_LOCK()
IS_USED_LOCK()
IS_FREE_LOCK()
Statements that use any of the following variables:
@@last_insert_id
@@identity
Reads done with causal_reads enabled that timed out on the replica
Replication primary commands (e.g. SHOW MASTER STATUS)
Other miscellaneous commands (COM_QUIT, COM_PING etc.)
fail_instantly
When the failure of the primary server is detected, the connection will be closed immediately.
fail_on_write
The client connection is closed if a write query is received when no primary is available.
error_on_write
If no primary is available and a write query is received, an error is returned stating that the connection is in read-only mode.
local
Session
Low, one sync per write.
fast
Session
None, no sync at all.
global
Service
Medium, one sync per read.
fast_global
Service
Connector/J
Yes
3.5.2
Connector/Node.js
Yes
3.4.0
Connector/R2DBC
Yes
1.3.0
Connector/C
No
3.4.4
None, no sync at all.
maxctrl set server <server> maintenance --forceSET @rownum := 0;
SELECT @rownum := @rownum + 1 AS rownum, user, host FROM mysql.user;SET @myid := 0;
INSERT INTO test.t1 VALUES (@myid := @myid + 1);
SELECT @myid; -- Might return 1 or 0slave_selection_criteria=<criteria># Use the primary for reads
master_accept_reads=true# Enable strict multi-statement mode
strict_multi_stmt=trueINSERT INTO test.t1 (id) VALUES (1);
SELECT * FROM test.t1 WHERE id = 1;INSERT INTO test.t1 (id) VALUES (1);
-- These are executed as one multi-query
SET @maxscale_secret_variable=(
SELECT CASE
WHEN MASTER_GTID_WAIT('0-3000-8', 10) = 0 THEN 1
ELSE (SELECT 1 FROM INFORMATION_SCHEMA.ENGINES)
END); SELECT * FROM test.t1 WHERE id = 1;COM_QUERY: INSERT INTO test.t1 (id) VALUES (1);
COM_STMT_PREPARE: SELECT * FROM test.t1 WHERE id = ?;
COM_STMT_EXECUTE: ? = 123COM_QUERY: INSERT INTO test.t1 (id) VALUES (1);
COM_STMT_PREPARE: SELECT * FROM test.t1 WHERE id = ?;
COM_QUERY: IF (MASTER_GTID_WAIT('0-3000-8', 10) <> 0) THEN KILL (SELECT CONNECTION_ID()); END IF
COM_STMT_EXECUTE: ? = 123Error: 1792
SQLSTATE: 25006
Message: Causal read timed out while in a read-only transaction, cannot retry command.maxctrl call command readwritesplit reset-gtid My-RW-RouterSET SQL_MODE=''; -- A session command
BEGIN;
SELECT "hello world"; -- Returns the string "hello world"
SET SQL_MODE='ANSI_QUOTES'; -- A session command
SELECT 'hello world'; -- Returns the string "hello world"SET SQL_MODE=''; -- Replayed session command
SET SQL_MODE='ANSI_QUOTES'; -- Replayed session command
BEGIN;
SELECT "hello world"; -- Returns an error
SELECT 'hello world'; -- Returns the string "hello world"