1 of 8

Installing Primary/Replica

This page explains how to set up a standard Primary/Replica replication topology for MariaDB Enterprise Server.

Overview

Software Version

Diagram

Features

This procedure describes the deployment of the Primary/Replica topology with MariaDB Enterprise Server and MariaDB MaxScale.

Primary/Replica topology provides read scalability and fault tolerance through asynchronous or semi-synchronous single-primary replication.

This procedure has 7 steps, which are executed in sequence.

MariaDB products can be deployed in many different topologies to suit specific use cases. The Primary/Replica topology can be deployed on its own or integrated with MariaDB Enterprise Cluster.

This procedure represents basic product capability with 3 Enterprise Server nodes and 1 MaxScale node.

This page provides an overview of the topology, requirements, and deployment procedure.

Please read and understand this procedure before executing.

Procedure Steps

Step

Description

Components

The following components are deployed during this procedure:

Component

Function

MariaDB Enterprise Server Components

Component

Description

MariaDB MaxScale Components

Component

Description

Topology

Primary/Replica topology provides read scalability and fault tolerance through asynchronous or semi-synchronous single-primary replication of MariaDB Enterprise Server 11.4

The Primary/Replica topology consists of:

1 or more MaxScale nodes
1 Enterprise Server node operating as the primary server
2 or more Enterprise Server nodes operating as replica servers.

The MaxScale nodes:

Monitor the health and availability of the Enterprise Server nodes
Route queries to Enterprise Server nodes using Read/Write Split (readwritesplit) and Read Connection (readconnroute) routers.
Promote replica servers in the event that the primary server fails.

The Enterprise Server node is operating as the primary server:

Receives write queries from MaxScale, logging them to the Binary Log
Provides Binary Logs to replica servers for replication

The Enterprise Server nodes are operating as replica servers:

Receive read queries from MaxScale
Replicate writes asynchronously or semi-synchronously from the primary server

Requirements

These requirements are for the Primary/Replica topology when deployed with MariaDB Enterprise Server 11.4 and MariaDB MaxScale 25.01.

Operating System

In alignment to the , the Primary/Replica topology with MariaDB Enterprise Server 11.4 and MariaDB MaxScale 25.01 is provided for:

AlmaLinux 8 (x86_64, ARM64)
AlmaLinux 9 (x86_64, ARM64)
Debian 11 (x86_64, ARM64)
Debian 12 (x86_64, ARM64)

System User Accounts

User Account

Purpose

Quick Reference

MariaDB Enterprise Server Configuration Management

Method

Description

MariaDB Enterprise Server packages are configured to read configuration files from different paths, depending on the operating system. Making custom changes to Enterprise Server default configuration files is not recommended because custom changes may be overwritten by other default configuration files that are loaded later.

To ensure that your custom changes will be read last, create a custom configuration file with the z- prefix in one of the include directories.

Distribution

Example Configuration File Path

MariaDB Enterprise Server Service Management

The systemctl command is used to start and stop the MariaDB Enterprise Server service.

Operation

Command

For additional information, see "".

MariaDB Enterprise Server Logs

MariaDB Enterprise Server produces log data that can be helpful in problem diagnosis.

Log filenames and locations may be overridden in the server configuration. The default location of logs is the data directory. The data directory is specified by the system variable.

Log

System Variable/Option

Default Filename

MaxScale Configuration Management

MaxScale can be configured using several methods. These methods make use of MaxScale's .

Method

Benefits

The procedure on these pages configures MaxScale using MaxCtrl.

MaxScale Service Management

The systemctl command is used to start and stop the MaxScale service.

Operation

Command

For additional information, see "".

Next Step

Navigation in the procedure :

Step 1: Install MariaDB Enterprise Server

Overview

This page details step 1 of the 7-step procedure "Deploy Primary/Replica Topology".

This step installs MariaDB Enterprise Server

The Primary/Replica topology requires 3 or more MariaDB Enterprise Server nodes for High Availability (HA). Nodes must meet requirements.

Interactive commands are detailed. Alternatively, the described operations can be performed using automation.

Retrieve Download Token

MariaDB Corporation provides package repositories for CentOS / RHEL (YUM) and Debian / Ubuntu (APT). A download token is required to access the MariaDB Enterprise Repository.

Customer Download Tokens are customer-specific and are available through the MariaDB Customer Portal.

To retrieve the token for your account:

Navigate to
Log in.
Copy the Customer Download Token.

Substitute your token for CUSTOMER_DOWNLOAD_TOKEN when configuring the package repositories.

Set Up Repository

On each Enterprise ColumnStore node, install the prerequisites for downloading the software from the Web. Install on CentOS / RHEL (YUM):

Install on Debian / Ubuntu (APT):

On each Enterprise ColumnStore node, configure package repositories and specify Enterprise Server:

Checksums of the various releases of the mariadb_es_repo_setup script can be found in the section at the bottom of the page. Substitute ${checksum} in the example above with the latest checksum.

Install Enterprise Server

On each Enterprise Cluster node, install MariaDB Enterprise Server and MariaDB Enterprise Backup.

Install via CentOS / RHEL (YUM):

Install via Debian / Ubuntu (APT):

Install via SLES (ZYpp):

Next Step

Navigation in the procedure "Deploy Primary/Replica Topology":

This page was step 1 of 7.

Next: Step 2: Start and Configure MariaDB Enterprise Server on Primary Server

Step 2: Start and Configure MariaDB Enterprise Server on Primary Server

Overview

This page details step 2 of the 7-step procedure "".

This step starts and configures a MariaDB Enterprise Server to operate as a primary server in MariaDB Replication.

Interactive commands are detailed. Alternatively, the described operations can be performed using automation.

Step 3: Start and Configure MariaDB Enterprise Server on Replica Servers

Overview

This page details step 3 of the 7-step procedure "Deploy Primary/Replica Topology".

This page starts and configures a MariaDB Enterprise Server 11.4 to operate as a replica server in MariaDB Replication.

Interactive commands are detailed. Alternatively, the described operations can be performed using automation.

Stop the Enterprise Server Service

The installation process might have started the Enterprise Server service. The service should be stopped prior to making configuration changes.

On each Enterprise Server node, stop the MariaDB Enterprise Server service:

Configure Enterprise Server

Enterprise Server nodes require that you set the following system variables and options:

System Variable/Option

Description

MariaDB Enterprise Server also supports group commit.

Parallel Replication

Writes to the primary server that are group committed or logged with a Global Transaction ID in different replication domains can be applied on the replica server using parallel threads to improve performance.

System Variable/Option

Description

Example Configuration

On each Enterprise Server node, edit a configuration file and set these system variables and options:

Set the server_id option to a value that is unique for each Enterprise Server node.

Initialize Replica Database

When deploying a new replica server to an existing system, back up the primary server and restore it on the replica server to initialize the database.

Back up the Primary Server

Use to back up the primary server.

On the primary server, take a full backup:

Confirm successful completion of the backup operation.

On the primary server, prepare the backup:

Confirm successful completion of the prepare operation.

Restore the Backup to the Replica Server

On the primary server, copy the backup directory to each replica server:

On the replica server, move the default to another location:

On the replica server, use to restore the backup to the :

On the replica server, set the file permissions for the :

Start Replica Server

Start MariaDB Enterprise Server. If the Enterprise Server process is already running, restart it to apply the changes from the configuration file.

For additional information, see "".

Set the Global Transaction ID Position

If the replica server was restored from a backup of the primary, set the GTID position.

Get the GTID position that corresponds to the restored backup. This can be found in the xtrabackup_binlong_info file.

The GTID position from the above output is 0-1-2001,1-2-5139.

Connect to the replica server:

Set the system variable to the GTID position:

Start Replication

Execute the CHANGE MASTER TO statement to configure the replica server to connect to the primary server at this position:

The above statement configures the replica server to connect to a primary server located at 192.0.2.10 using the repl user account. This account must first be configured on the primary server.

Use the statement to start replication:

Use statement to confirm replication is running:

Next Step

Navigation in the procedure "Deploy Primary/Replica Topology":

This page was step 3 of 7.

Next: Step 4: Test MariaDB Enterprise Server

Step 5: Install MariaDB MaxScale

Overview

This page details step 5 of the 7-step procedure "Deploy Primary/Replica Topology".

This step install MariaDB MaxScale 25.01.

The MaxScale node must meet requirements.

Interactive commands are detailed. Alternatively, the described operations can be performed using automation.

Retrieve Customer Download Token

MariaDB Corporation provides package repositories for YUM (RHEL, CentOS), APT (Debian, Ubuntu), and ZYpp (SLES). A download token is required to access the MariaDB Enterprise Repository.

Customer Download Tokens are customer-specific and are available through the MariaDB Customer Portal.

To retrieve the token for your account:

Navigate to
Log in.
Copy the Customer Download Token.

Substitute your token for CUSTOMER_DOWNLOAD_TOKEN when configuring the package repositories.

Set Up Repository

On the MaxScale node, install the prerequisites for downloading the software from the Web.

Install on CentOS / RHEL (YUM):

Install on Debian / Ubuntu (APT):

Install on SLES (ZYpp):

On the MaxScale node, configure package repositories and specify MariaDB MaxScale 25.01:

Install MaxScale

On the MaxScale node, install MariaDB MaxScale.

Install on CentOS / RHEL (YUM):

Install on Debian / Ubuntu (APT):

Install on SLES (ZYpp):

Next Step

Navigation in the procedure "Deploy Primary/Replica Topology":

This page was step 5 of 7.

Next: Step 6: Start and Configure MariaDB MaxScale

Step 3: Start and Configure MariaDB Enterprise Server on Replica Servers

Overview

This page details step 3 of the 7-step procedure "Deploy Primary/Replica Topology".

This page starts and configures a MariaDB Enterprise Server 11.4 to operate as a replica server in MariaDB Replication.

Interactive commands are detailed. Alternatively, the described operations can be performed using automation.

Stop the Enterprise Server Service

The installation process might have started the Enterprise Server service. The service should be stopped prior to making configuration changes.

On each Enterprise Server node, stop the MariaDB Enterprise Server service:

Configure Enterprise Server

Enterprise Server nodes require that you set the following system variables and options:

System Variable/Option

Description

MariaDB Enterprise Server also supports group commit.

Parallel Replication

System Variable/Option

Description

Example Configuration

On each Enterprise Server node, edit a configuration file and set these system variables and options:

Set the server_id option to a value that is unique for each Enterprise Server node.

Initialize Replica Database

When deploying a new replica server to an existing system, back up the primary server and restore it on the replica server to initialize the database.

Back up the Primary Server

Use to back up the primary server.

On the primary server, take a full backup:

Confirm successful completion of the backup operation.

On the primary server, prepare the backup:

Confirm successful completion of the prepare operation.

Restore the Backup to the Replica Server

On the primary server, copy the backup directory to each replica server:

On the replica server, move the default to another location:

On the replica server, use to restore the backup to the :

On the replica server, set the file permissions for the :

Start Replica Server

Start MariaDB Enterprise Server. If the Enterprise Server process is already running, restart it to apply the changes from the configuration file.

For additional information, see "".

Set the Global Transaction ID Position

If the replica server was restored from a backup of the primary, set the GTID position.

Get the GTID position that corresponds to the restored backup. This can be found in the xtrabackup_binlong_info file.

The GTID position from the above output is 0-1-2001,1-2-5139.

Connect to the replica server:

Set the system variable to the GTID position:

Start Replication

Execute the CHANGE MASTER TO statement to configure the replica server to connect to the primary server at this position:

The above statement configures the replica server to connect to a primary server located at 192.0.2.10 using the repl user account. This account must first be configured on the primary server.

Use the statement to start replication:

Use statement to confirm replication is running:

Next Step

Navigation in the procedure "Deploy Primary/Replica Topology":

This page was step 3 of 7.

Next: Step 4: Test MariaDB Enterprise Server

Installing Primary/Replica

This page explains how to set up a standard Primary/Replica replication topology for MariaDB Enterprise Server.

Overview

Software Version

Diagram

Features

This procedure describes the deployment of the Primary/Replica topology with MariaDB Enterprise Server and MariaDB MaxScale.

Primary/Replica topology provides read scalability and fault tolerance through asynchronous or semi-synchronous single-primary replication.

This procedure has 7 steps, which are executed in sequence.

MariaDB products can be deployed in many different topologies to suit specific use cases. The Primary/Replica topology can be deployed on its own or integrated with MariaDB Enterprise Cluster.

This procedure represents basic product capability with 3 Enterprise Server nodes and 1 MaxScale node.

This page provides an overview of the topology, requirements, and deployment procedure.

Please read and understand this procedure before executing.

Procedure Steps

Step

Description

Components

The following components are deployed during this procedure:

Component

Function

MariaDB Enterprise Server Components

Component

Description

MariaDB MaxScale Components

Component

Description

Topology

Primary/Replica topology provides read scalability and fault tolerance through asynchronous or semi-synchronous single-primary replication of MariaDB Enterprise Server 11.4

The Primary/Replica topology consists of:

1 or more MaxScale nodes
1 Enterprise Server node operating as the primary server
2 or more Enterprise Server nodes operating as replica servers.

The MaxScale nodes:

Monitor the health and availability of the Enterprise Server nodes
Route queries to Enterprise Server nodes using Read/Write Split (readwritesplit) and Read Connection (readconnroute) routers.
Promote replica servers in the event that the primary server fails.

The Enterprise Server node is operating as the primary server:

Receives write queries from MaxScale, logging them to the Binary Log
Provides Binary Logs to replica servers for replication

The Enterprise Server nodes are operating as replica servers:

Receive read queries from MaxScale
Replicate writes asynchronously or semi-synchronously from the primary server

Requirements

These requirements are for the Primary/Replica topology when deployed with MariaDB Enterprise Server 11.4 and MariaDB MaxScale 25.01.

Operating System

In alignment to the , the Primary/Replica topology with MariaDB Enterprise Server 11.4 and MariaDB MaxScale 25.01 is provided for:

AlmaLinux 8 (x86_64, ARM64)
AlmaLinux 9 (x86_64, ARM64)
Debian 11 (x86_64, ARM64)
Debian 12 (x86_64, ARM64)

System User Accounts

User Account

Purpose

Quick Reference

MariaDB Enterprise Server Configuration Management

Method

Description

To ensure that your custom changes will be read last, create a custom configuration file with the z- prefix in one of the include directories.

Distribution

Example Configuration File Path

MariaDB Enterprise Server Service Management

The systemctl command is used to start and stop the MariaDB Enterprise Server service.

Operation

Command

For additional information, see "".

MariaDB Enterprise Server Logs

MariaDB Enterprise Server produces log data that can be helpful in problem diagnosis.

Log filenames and locations may be overridden in the server configuration. The default location of logs is the data directory. The data directory is specified by the system variable.

Log

System Variable/Option

Default Filename

MaxScale Configuration Management

MaxScale can be configured using several methods. These methods make use of MaxScale's .

Method

Benefits

The procedure on these pages configures MaxScale using MaxCtrl.

MaxScale Service Management

The systemctl command is used to start and stop the MaxScale service.

Operation

Command

For additional information, see "".

Next Step

Navigation in the procedure :

Step 4: Test MariaDB Enterprise Server

Overview

This page details step 4 of the 7-step procedure "Deploy Primary/Replica Topology".

This step tests MariaDB Enterprise Server

Several actions require connection to MariaDB Enterprise Server. A command-line client (mariadb) was included with your ES installation. These instructions describe connection via Unix domain socket. Alternatively, a different client and connection method could be used.

Interactive commands are detailed. Alternatively, the described operations can be performed using automation.

Test Enterprise Server Service

Use Systemd to test whether the MariaDB Enterprise Server service is running.

This action is performed on each Enterprise Server node.

Check if the MariaDB Enterprise Server service is running by executing the following:

If the service is not running on any node, start the service by executing the following on that node:

Test Local Client Connections

Use to test the local connection to the Enterprise Server node.

This action is performed on each Enterprise Server node:

The sudo command is used here to connect to the Enterprise Server node using the root@localhost user account, which authenticates using the authentication plugin. Other user accounts can be used by specifying the --user and --password command-line options.

Test Replica Status

Use to check that replication is running properly on the replica servers.

This action is performed on each replica server.

Execute the following:

If Slave_IO_Running column is not Yes on any replica server, then check:

The network connectivity between the replica server and the primary server
The Last_IO_Error column for details on any errors

If Slave_SQL_Running column is not Yes on any replica server, then check:

The GTID position in
The Last_SQL_Error column for details on any errors

If both columns are not Yes on any replica server, then check:

The replication configuration on the replica server.

If you need to make any corrections, the slave threads can be restarted with .

Test DDL

Use to test DDL.

On the primary server, use the to connect to the node:

Create a test database and table:

On each replica server, use the to connect to the node:

Confirm that the database and table exist:

If the database or table do not exist on any node, then check the replication status on the node.

Test DML

Use to test DML.

On the primary server, use the MariaDB Client to connect to the node:

Insert sample data into the table created in the DDL test:

On each replica server, use the to connect to the node:

Execute a query to retrieve the data:

If the data is not returned on any node, then check the replication status on the node.

Next Step

Navigation in the procedure "Deploy Primary/Replica Topology":

This page was step 4 of 7.

Next: Step 5: Install MariaDB MaxScale

Step 7: Test MariaDB MaxScale

Overview

This page details step 7 of the 7-step procedure "Deploy Primary/Replica Topology".

This step tests MariaDB MaxScale.

Interactive commands are detailed. Alternatively, the described operations can be performed using automation.

Check Global Configuration

Use command to view the global MaxScale configuration.

This action is performed on the MaxScale node:

Output should align to the global MaxScale configuration in the new configuration file you created.

Check Server Configuration

Use the and commands to view the configured server objects.

This action is performed on the MaxScale node:

Obtain the full list of servers objects:

For each server object, view the configuration:

Output should align to the Server Object configuration you performed.

Check Monitor Configuration

Use the and commands to view the configured monitors.

This action is performed on the MaxScale node:

Obtain the full list of monitors:

For each monitor, view the monitor configuration:

Output should align to the Galera Monitor (galeramon) configuration you performed.

Check Service Configuration

Use the and commands to view the configured routing services.

This action is performed on the MaxScale node:

Obtain the full list of routing services:

For each service, view the service configuration:

Output should align to the Read Connection Router (readconnroute) or Read/Write Split Router (readwritesplit) configuration you performed.

Test Application User

Applications should use a dedicated user account. The user account must be created on the primary server.

When users connect to MaxScale, MaxScale authenticates the user connection before routing it to an Enterprise Server node. Enterprise Server authenticates the connection as originating from the IP address of the MaxScale node.

The application users must have one user account with the host IP address of the application server and a second user account with the host IP address of the MaxScale node.

The requirement of a duplicate user account can be avoided by enabling the proxy_protocol parameter for MaxScale and the proxy_protocol_networks for Enterprise Server.

Create a User to Connect from MaxScale

This action is performed on any Enterprise Cluster node:

Connect to the node:

Create the database user account for your MaxScale node:

Replace 192.0.2.104 with the relevant IP address specification for your MaxScale node.

Passwords should meet your organization's password policies.

Grant the privileges required by your application to the database user account for your MaxScale node:

The privileges shown are designed to allow the tests in the subsequent sections to work. The user account for your production application may require different privileges.

Create a User to Connect from the Application Server

This action is performed on any Enterprise Cluster node:

Create the database user account for your application server:

Replace 192.0.2.11 with the relevant IP address specification for your application server.

Passwords should meet your organization's password policies.

Grant the privileges required by your application to the database user account for your application server:

The privileges shown are designed to allow the tests in the subsequent sections to work. The user account for your production application may require different privileges.

Test Connection with Application User

To test the connection, use the MariaDB Client from your application server to connect to an Enterprise Cluster node through MaxScale.

This action is performed on the application server:

Test Connection with Read Connection Router

If you configured the Read Connection Router, confirm that MaxScale routes connections to the replica servers.

On the MaxScale node, use the command to view the available listeners and ports:

Open multiple terminals connected to your application server, in each use MariaDB Client to connect to the listener port for the Read Connection Router (in the example 3308):

Use the application user credentials you created for the --user and --password options.

In each terminal, query the hostname system variable to identify to which you're connected:

Different terminals should return different values since MaxScale routes the connections to different nodes.

Since the router was configured the slave router option, the Read Connection Router only routes connections to replica servers.

Test Write Queries with the Read/Write Split Router

If you configured the Read/Write Split Router, confirm that readwritesplit correctly routes write queries.

This action is performed with multiple client connections to the MaxScale node.

On the MaxScale node, use the command to identify the Enterprise Cluster node currently operating as the primary server:

The server listed as Master is currently operating as the primary server.

On the MaxScale node, use the command to identify the correct listener port:

In the example, the listener port for the Read/Write Split router is 3307.

Use the MariaDB Client to establish multiple connections to the listener configured for the Read/Write Split routing service, query_router_listener, on the MaxScale node:

The database user account for your application server should be specified by the --user option.

Using any client connection, create a test table:

Using each client connection, insert the values of the hostname system variable into the table using the INSERT statement to identify the node that executes the statement:

Using any client connection, query the table using the SELECT statement:

The output shows the hostname from the Enterprise Cluster node operating as the primary server. (Enterprise Cluster offsets auto-increment values by node to avoid write conflicts.)

Confirm that MaxScale is routing write queries to the Enterprise Cluster node operating as the primary server by checking that the test table only contains the hostname of the correct Enterprise Cluster node.

Test Read Queries with the Read/Write Split Router

If you configured the Read/Write Split Router, confirm that readwritesplit properly routes read queries to multiple replica servers.

This action is performed with multiple clients connected to the MaxScale node.

On the MaxScale node, use to identify the Enterprise Cluster nodes that are currently operating as replica servers:

The servers listed as Slave are currently operating as replica servers.

On the MaxScale node, use the command to identify the correct listener port:

In the example, the listener port for the Read/Write Split router is 3307.

Use the MariaDB Client to establish multiple connections to query_router_listener which is the listener configured for the Read/Write Split routing service on the MaxScale node:

The database user account for your application server should be specified by the --user option.

Using each client connection, query the hostname system variable to identify the node that executes the statement:

Confirm that MaxScale routes the SELECT statements to different replica servers.

For more information on different routing criteria, see slave_selection_criteria

Next Step

Navigation in the procedure "Deploy Primary/Replica Topology":

This page was step 7 of 7.

This procedure is complete.

┌──────────────┬───────────────────────────────────────────────────────┐
│ Version      │ 25.01.2                                               │
├──────────────┼───────────────────────────────────────────────────────┤
│ Commit       │ 3761fa7a52046bc58faad8b5a139116f9e33364c              │
├──────────────┼───────────────────────────────────────────────────────┤
│ Started At   │ Thu, 05 Aug 2021 20:21:20 GMT                         │
├──────────────┼───────────────────────────────────────────────────────┤
│ Activated At │ Thu, 05 Aug 2021 20:21:20 GMT                         │
├──────────────┼───────────────────────────────────────────────────────┤
│ Uptime       │ 868                                                   │
├──────────────┼───────────────────────────────────────────────────────┤
│ Config Sync  │ null                                                  │
├──────────────┼───────────────────────────────────────────────────────┤
│ Parameters   │ {                                                     │
│              │     "admin_auth": true,                               │
│              │     "admin_enabled": true,                            │
│              │     "admin_gui": true,                                │
│              │     "admin_host": "0.0.0.0",                          │
│              │     "admin_log_auth_failures": true,                  │
│              │     "admin_pam_readonly_service": null,               │
│              │     "admin_pam_readwrite_service": null,              │
│              │     "admin_port": 8989,                               │
│              │     "admin_secure_gui": false,                        │
│              │     "admin_ssl_ca_cert": null,                        │
│              │     "admin_ssl_cert": null,                           │
│              │     "admin_ssl_key": null,                            │
│              │     "admin_ssl_version": "MAX",                       │
│              │     "auth_connect_timeout": "10000ms",                │
│              │     "auth_read_timeout": "10000ms",                   │
│              │     "auth_write_timeout": "10000ms",                  │
│              │     "cachedir": "/var/cache/maxscale",                │
│              │     "config_sync_cluster": null,                      │
│              │     "config_sync_interval": "5000ms",                 │
│              │     "config_sync_password": "*****",                  │
│              │     "config_sync_timeout": "10000ms",                 │
│              │     "config_sync_user": null,                         │
│              │     "connector_plugindir": "/usr/lib64/mysql/plugin", │
│              │     "datadir": "/var/lib/maxscale",                   │
│              │     "debug": null,                                    │
│              │     "dump_last_statements": "never",                  │
│              │     "execdir": "/usr/bin",                            │
│              │     "language": "/var/lib/maxscale",                  │
│              │     "libdir": "/usr/lib64/maxscale",                  │
│              │     "load_persisted_configs": true,                   │
│              │     "local_address": null,                            │
│              │     "log_debug": false,                               │
│              │     "log_info": false,                                │
│              │     "log_notice": true,                               │
│              │     "log_throttling": {                               │
│              │         "count": 10,                                  │
│              │         "suppress": 10000,                            │
│              │         "window": 1000                                │
│              │     },                                                │
│              │     "log_warn_super_user": false,                     │
│              │     "log_warning": true,                              │
│              │     "logdir": "/var/log/maxscale",                    │
│              │     "max_auth_errors_until_block": 10,                │
│              │     "maxlog": true,                                   │
│              │     "module_configdir": "/etc/maxscale.modules.d",    │
│              │     "ms_timestamp": false,                            │
│              │     "passive": false,                                 │
│              │     "persistdir": "/var/lib/maxscale/maxscale.cnf.d", │
│              │     "piddir": "/var/run/maxscale",                    │
│              │     "query_classifier": "qc_sqlite",                  │
│              │     "query_classifier_args": null,                    │
│              │     "query_classifier_cache_size": 289073971,         │
│              │     "query_retries": 1,                               │
│              │     "query_retry_timeout": "5000ms",                  │
│              │     "rebalance_period": "0ms",                        │
│              │     "rebalance_threshold": 20,                        │
│              │     "rebalance_window": 10,                           │
│              │     "retain_last_statements": 0,                      │
│              │     "session_trace": 0,                               │
│              │     "skip_permission_checks": false,                  │
│              │     "sql_mode": "default",                            │
│              │     "syslog": true,                                   │
│              │     "threads": 1,                                     │
│              │     "users_refresh_interval": "0ms",                  │
│              │     "users_refresh_time": "30000ms",                  │
│              │     "writeq_high_water": 16777216,                    │
│              │     "writeq_low_water": 8192                          │
│              │ }                                                     │
└──────────────┴───────────────────────────────────────────────────────┘

┌─────────────────────┬───────────────────────────────────────────┐
│ Server              │ node3                                     │
├─────────────────────┼───────────────────────────────────────────┤
│ Address             │ 192.0.2.103                               │
├─────────────────────┼───────────────────────────────────────────┤
│ Port                │ 3306                                      │
├─────────────────────┼───────────────────────────────────────────┤
│ State               │ Master, Synced, Running                   │
├─────────────────────┼───────────────────────────────────────────┤
│ Version             │ 11.4.5-3-MariaDB-enterprise-log           │
├─────────────────────┼───────────────────────────────────────────┤
│ Last Event          │ master_up                                 │
├─────────────────────┼───────────────────────────────────────────┤
│ Triggered At        │ Thu, 05 Aug 2021 20:22:26 GMT             │
├─────────────────────┼───────────────────────────────────────────┤
│ Services            │ connection_router_service                 │
│                     │ query_router_service                      │
├─────────────────────┼───────────────────────────────────────────┤
│ Monitors            │ cluster_monitor                           │
├─────────────────────┼───────────────────────────────────────────┤
│ Master ID           │ -1                                        │
├─────────────────────┼───────────────────────────────────────────┤
│ Node ID             │ 1                                         │
├─────────────────────┼───────────────────────────────────────────┤
│ Slave Server IDs    │                                           │
├─────────────────────┼───────────────────────────────────────────┤
│ Current Connections │ 1                                         │
├─────────────────────┼───────────────────────────────────────────┤
│ Total Connections   │ 1                                         │
├─────────────────────┼───────────────────────────────────────────┤
│ Max Connections     │ 1                                         │
├─────────────────────┼───────────────────────────────────────────┤
│ Statistics          │ {                                         │
│                     │     "active_operations": 0,               │
│                     │     "adaptive_avg_select_time": "0ns",    │
│                     │     "connection_pool_empty": 0,           │
│                     │     "connections": 1,                     │
│                     │     "max_connections": 1,                 │
│                     │     "max_pool_size": 0,                   │
│                     │     "persistent_connections": 0,          │
│                     │     "reused_connections": 0,              │
│                     │     "routed_packets": 0,                  │
│                     │     "total_connections": 1                │
│                     │ }                                         │
├─────────────────────┼───────────────────────────────────────────┤
│ Parameters          │ {                                         │
│                     │     "address": "192.0.2.103",               │
│                     │     "disk_space_threshold": null,         │
│                     │     "extra_port": 0,                      │
│                     │     "monitorpw": null,                    │
│                     │     "monitoruser": null,                  │
│                     │     "persistmaxtime": "0ms",              │
│                     │     "persistpoolmax": 0,                  │
│                     │     "port": 3306,                         │
│                     │     "priority": 0,                        │
│                     │     "proxy_protocol": false,              │
│                     │     "rank": "primary",                    │
│                     │     "socket": null,                       │
│                     │     "ssl": false,                         │
│                     │     "ssl_ca_cert": null,                  │
│                     │     "ssl_cert": null,                     │
│                     │     "ssl_cert_verify_depth": 9,           │
│                     │     "ssl_cipher": null,                   │
│                     │     "ssl_key": null,                      │
│                     │     "ssl_verify_peer_certificate": false, │
│                     │     "ssl_verify_peer_host": false,        │
│                     │     "ssl_version": "MAX"                  │
│                     │ }                                         │
└─────────────────────┴───────────────────────────────────────────┘

┌─────────────────────┬─────────────────────────────────────────────────────────────┐
│ Service             │ query_router_service                                        │
├─────────────────────┼─────────────────────────────────────────────────────────────┤
│ Router              │ readwritesplit                                              │
├─────────────────────┼─────────────────────────────────────────────────────────────┤
│ State               │ Started                                                     │
├─────────────────────┼─────────────────────────────────────────────────────────────┤
│ Started At          │ Thu Aug  5 20:23:38 2021                                    │
├─────────────────────┼─────────────────────────────────────────────────────────────┤
│ Current Connections │ 1                                                           │
├─────────────────────┼─────────────────────────────────────────────────────────────┤
│ Total Connections   │ 1                                                           │
├─────────────────────┼─────────────────────────────────────────────────────────────┤
│ Max Connections     │ 1                                                           │
├─────────────────────┼─────────────────────────────────────────────────────────────┤
│ Cluster             │                                                             │
├─────────────────────┼─────────────────────────────────────────────────────────────┤
│ Servers             │ node1                                                       │
│                     │ node2                                                       │
│                     │ node3                                                       │
├─────────────────────┼─────────────────────────────────────────────────────────────┤
│ Services            │                                                             │
├─────────────────────┼─────────────────────────────────────────────────────────────┤
│ Filters             │                                                             │
├─────────────────────┼─────────────────────────────────────────────────────────────┤
│ Parameters          │ {                                                           │
│                     │     "auth_all_servers": false,                              │
│                     │     "causal_reads": "false",                                │
│                     │     "causal_reads_timeout": "10000ms",                      │
│                     │     "connection_keepalive": "300000ms",                     │
│                     │     "connection_timeout": "0ms",                            │
│                     │     "delayed_retry": false,                                 │
│                     │     "delayed_retry_timeout": "10000ms",                     │
│                     │     "disable_sescmd_history": false,                        │
│                     │     "enable_root_user": false,                              │
│                     │     "idle_session_pool_time": "-1000ms",                    │
│                     │     "lazy_connect": false,                                  │
│                     │     "localhost_match_wildcard_host": true,                  │
│                     │     "log_auth_warnings": true,                              │
│                     │     "master_accept_reads": false,                           │
│                     │     "master_failure_mode": "fail_instantly",                │
│                     │     "master_reconnection": false,                           │
│                     │     "max_connections": 0,                                   │
│                     │     "max_sescmd_history": 50,                               │
│                     │     "max_slave_connections": 255,                           │
│                     │     "max_slave_replication_lag": "0ms",                     │
│                     │     "net_write_timeout": "0ms",                             │
│                     │     "optimistic_trx": false,                                │
│                     │     "password": "*****",                                    │
│                     │     "prune_sescmd_history": true,                           │
│                     │     "rank": "primary",                                      │
│                     │     "retain_last_statements": -1,                           │
│                     │     "retry_failed_reads": true,                             │
│                     │     "reuse_prepared_statements": false,                     │
│                     │     "router": "readwritesplit",                             │
│                     │     "session_trace": false,                                 │
│                     │     "session_track_trx_state": false,                       │
│                     │     "slave_connections": 255,                               │
│                     │     "slave_selection_criteria": "LEAST_CURRENT_OPERATIONS", │
│                     │     "strict_multi_stmt": false,                             │
│                     │     "strict_sp_calls": false,                               │
│                     │     "strip_db_esc": true,                                   │
│                     │     "transaction_replay": false,                            │
│                     │     "transaction_replay_attempts": 5,                       │
│                     │     "transaction_replay_max_size": 1073741824,              │
│                     │     "transaction_replay_retry_on_deadlock": false,          │
│                     │     "type": "service",                                      │
│                     │     "use_sql_variables_in": "all",                          │
│                     │     "user": "mxs",                                          │
│                     │     "version_string": null                                  │
│                     │ }                                                           │
├─────────────────────┼─────────────────────────────────────────────────────────────┤
│ Router Diagnostics  │ {                                                           │
│                     │     "avg_sescmd_history_length": 0,                         │
│                     │     "max_sescmd_history_length": 0,                         │
│                     │     "queries": 1,                                           │
│                     │     "replayed_transactions": 0,                             │
│                     │     "ro_transactions": 0,                                   │
│                     │     "route_all": 0,                                         │
│                     │     "route_master": 0,                                      │
│                     │     "route_slave": 1,                                       │
│                     │     "rw_transactions": 0,                                   │
│                     │     "server_query_statistics": [                            │
│                     │         {                                                   │
│                     │             "avg_selects_per_session": 0,                   │
│                     │             "avg_sess_duration": "0ns",                     │
│                     │             "id": "node2",                                  │
│                     │             "read": 1,                                      │
│                     │             "total": 1,                                     │
│                     │             "write": 0                                      │
│                     │         }                                                   │
│                     │     ]                                                       │
│                     │ }                                                           │
└─────────────────────┴─────────────────────────────────────────────────────────────┘

Step 6: Start and Configure MariaDB MaxScale

Overview

This page details step 6 of the 7-step procedure "Deploy Primary/Replica Topology".

This step starts and configures MariaDB MaxScale.

Interactive commands are detailed. Alternatively, the described operations can be performed using automation.

Replace the Default Configuration File

MariaDB MaxScale installations include a configuration file with some example objects. This configuration file should be replaced.

On the MaxScale node, replace the default /etc/maxscale.cnf with the following configuration:

For additional information, see "".

Restart MaxScale

On the MaxScale node, restart the MaxScale service to ensure that MaxScale picks up the new configuration:

For additional information, see "".

Configure Server Objects

On the MaxScale node, use server to create a server object for each MariaDB Enterprise Server:

Configure MariaDB Monitor

MaxScale uses monitors to retrieve additional information from the servers. This information is used by other services in filtering and routing connections based on the current state of the node. For MariaDB Replication, use the MariaDB Monitor (mariadbmon).

On the MaxScale node, use to create a MariaDB Monitor:

In this example:

mdb_monitor is an arbitrary name that is used to identify the new monitor.
mariadbmon is the name of the module that implements the MariaDB Monitor.
user=MAXSCALE_USER sets the user parameter to the database user account that MaxScale uses to monitor the ES nodes.

Choose a MaxScale Router

Routers control how MaxScale balances the load between Enterprise Server nodes. Each router uses a different approach to routing queries. Consider the specific use case of your application and database load and select the router that best suits

Router

Configuration Procedure

Description

Configure Read Connection Router

Use MaxScale Read Connection Router (readconnroute) to route connections to replica servers for a read-only pool.

On the MaxScale node, use maxctrl create service to create a router:

In this example:

connection_router_service is an arbitrary name that is used to identify the new service.
readconnroute is the name of the module that implements the Read Connection Router.
user=MAXSCALE_USER sets the user

Configure Listener for the Read Connection Router

These instructions reference TCP port 3308. You can use a different TCP port. The TCP port used must not be bound by any other listener.

On the MaxScale node, use the maxctrl create listener command to configure MaxScale to use a listener for the Read Connection Router (readconnroute):

In this example:

connection_router_service is the name of the readconnroute service that was previously created.
connection_router_listener is an arbitrary name that is used to identify the new listener.
3308 is the TCP port.

Configure Read/Write Split Router for Queries

MaxScale Read/Write Split Router (readwritesplit) performs query-based load balancing. The router routes write queries to the primary and read queries to the replicas.

On the MaxScale node, use the maxctrl create service command to configure MaxScale to use the Read/Write Split Router (readwritesplit):

In this example:

query_router_service is an arbitrary name that is used to identify the new service.
readwritesplit is the name of the module that implements the Read/Write Split Router.
user=MAXSCALE_USER sets the user

Configure a Listener for the Read/Write Split Router

These instructions reference TCP port 3307. You can use a different TCP port. The TCP port used must not be bound by any other listener.

On the MaxScale node, use the maxctrl create listener command to configure MaxScale to use a listener for the Read/Write Split Router (readwritesplit):

In this example:

query_router_service is the name of the readwritesplit service that was previously created.
query_router_listener is an arbitrary name that is used to identify the new listener.
3307 is the TCP port.

Start Services

To start the services and monitors, on the MaxScale node use maxctrl start services:

Next Step

Navigation in the procedure "Deploy Primary/Replica Topology":

This page was step 6 of 7.

Next: Step 7: Test MariaDB MaxScale

Installing Primary/Replica

hashtagOverview

hashtagProcedure Steps

hashtagComponents

hashtagMariaDB Enterprise Server Components

hashtagMariaDB MaxScale Components

hashtagTopology

hashtagRequirements

hashtagOperating System

hashtagSystem User Accounts

hashtagQuick Reference

hashtagMariaDB Enterprise Server Configuration Management

hashtagMariaDB Enterprise Server Service Management

hashtagMariaDB Enterprise Server Logs

hashtagMaxScale Configuration Management

hashtagMaxScale Service Management

hashtagNext Step

Step 1: Install MariaDB Enterprise Server

hashtagOverview

hashtagRetrieve Download Token

hashtagSet Up Repository

hashtagInstall Enterprise Server

hashtagNext Step

Step 2: Start and Configure MariaDB Enterprise Server on Primary Server

hashtagOverview

Step 3: Start and Configure MariaDB Enterprise Server on Replica Servers

hashtagOverview

hashtagStop the Enterprise Server Service

hashtagConfigure Enterprise Server

hashtagParallel Replication

hashtagExample Configuration

hashtagInitialize Replica Database

hashtagBack up the Primary Server

hashtagRestore the Backup to the Replica Server

hashtagStart Replica Server

hashtagSet the Global Transaction ID Position

hashtagStart Replication

hashtagNext Step

Step 5: Install MariaDB MaxScale

hashtagOverview

hashtagRetrieve Customer Download Token

hashtagSet Up Repository

hashtagInstall MaxScale

hashtagNext Step

Step 3: Start and Configure MariaDB Enterprise Server on Replica Servers

hashtagOverview

hashtagStop the Enterprise Server Service

hashtagConfigure Enterprise Server

hashtagParallel Replication

hashtagExample Configuration

hashtagInitialize Replica Database

hashtagBack up the Primary Server

hashtagRestore the Backup to the Replica Server

hashtagStart Replica Server

hashtagSet the Global Transaction ID Position

hashtagStart Replication

hashtagNext Step

Installing Primary/Replica

hashtagOverview

hashtagProcedure Steps

hashtagComponents

hashtagMariaDB Enterprise Server Components

hashtagMariaDB MaxScale Components

hashtagTopology

hashtagRequirements

hashtagOperating System

hashtagSystem User Accounts

hashtagQuick Reference

hashtagMariaDB Enterprise Server Configuration Management

hashtagMariaDB Enterprise Server Service Management

hashtagMariaDB Enterprise Server Logs

hashtagMaxScale Configuration Management

hashtagMaxScale Service Management

hashtagNext Step

Step 1: Install MariaDB Enterprise Server

hashtagOverview

hashtagRetrieve Download Token

hashtagSet Up Repository

hashtagInstall Enterprise Server

hashtagNext Step

Overview

Procedure Steps

Components

MariaDB Enterprise Server Components

MariaDB MaxScale Components

Topology

Requirements

Operating System

System User Accounts

Quick Reference

MariaDB Enterprise Server Configuration Management

MariaDB Enterprise Server Service Management

MariaDB Enterprise Server Logs

MaxScale Configuration Management

MaxScale Service Management

Next Step

Overview

Retrieve Download Token

Set Up Repository

Install Enterprise Server

Next Step

Overview

Overview

Stop the Enterprise Server Service

Configure Enterprise Server

Parallel Replication

Example Configuration

Initialize Replica Database

Back up the Primary Server

Restore the Backup to the Replica Server

Start Replica Server

Set the Global Transaction ID Position

Start Replication

Next Step

Overview

Retrieve Customer Download Token

Set Up Repository

Install MaxScale

Next Step

Overview

Stop the Enterprise Server Service

Configure Enterprise Server

Parallel Replication

Example Configuration

Initialize Replica Database

Back up the Primary Server

Restore the Backup to the Replica Server

Start Replica Server

Set the Global Transaction ID Position

Start Replication

Next Step

Overview

Procedure Steps

Components

MariaDB Enterprise Server Components

MariaDB MaxScale Components

Topology

Requirements

Operating System

System User Accounts

Quick Reference

MariaDB Enterprise Server Configuration Management

MariaDB Enterprise Server Service Management

MariaDB Enterprise Server Logs

MaxScale Configuration Management

MaxScale Service Management

Next Step

Overview

Retrieve Download Token

Set Up Repository

Install Enterprise Server

Next Step

Overview

Retrieve Customer Download Token

Set Up Repository

Install MaxScale

Next Step

Overview

Configure Enterprise Server

Group Commit