Deploy an Enterprise HTAP Deployment with MariaDB Enterprise ColumnStore 5.5, MariaDB Enterprise Server 10.5, and MariaDB MaxScale 2.5 on SLES 15
Topics on This Page:
These instructions detail the deployment of MariaDB Enterprise Server 10.5, MariaDB Enterprise ColumnStore 5.5, and MariaDB MaxScale 2.5 on SUSE Linux Enterprise Server 15 in a HTAP Deployment configuration.
These instructions detail how to deploy a Hybrid Transactional/Analytical Processing (HTAP) solution, which is suited for a combined transactional and analytical workload.
MariaDB Platform Components
These instructions detail the deployment of the following MariaDB Platform components:
Component |
Description |
---|---|
|
|
|
|
|
MariaDB Enterprise Server Components
These instructions detail the deployment of the following MariaDB Enterprise Server components:
Component |
Description |
---|---|
|
|
|
|
|
MaxScale Components
These instructions detail the deployment of MaxScale with the following components:
Component |
Description |
---|---|
|
|
|
|
|
Term Definitions
Term |
Definition |
---|---|
MaxScale instance |
|
Columnar Database |
|
Row Database |
|
Topology Definition
This HTAP topology involves two servers. One server runs MariaDB Enterprise Server with MariaDB Enterprise ColumnStore. The second server runs MariaDB MaxScale.
Within these instructions, we use these IP addresses:
IP Address |
Component |
192.0.2.1 |
MariaDB MaxScale |
192.0.2.2 |
MariaDB Enterprise Server with MariaDB Enterprise ColumnStore |
Note
Remember to replace the specified IP addresses with the IP addresses in your environment.
System Preparation
Systems hosting Enterprise ColumnStore deployments require some additional configuration prior to installation:
Optimize Linux Kernel Parameters
MariaDB Enterprise ColumnStore performs best when certain Linux kernel parameters are optimized.
Set the relevant kernel parameters in a sysctl configuration file. For proper change management, we recommend setting them in an Enterprise ColumnStore-specific configuration file.
For example, create a
/etc/sysctl.d/90-mariadb-enterprise-columnstore.conf
file with the following contents:# minimize swapping vm.swappiness = 10 # optimize Linux to cache directories and inodes vm.vfs_cache_pressure = 10 # Increase the TCP max buffer size net.core.rmem_max = 16777216 net.core.wmem_max = 16777216 # Increase the TCP buffer limits # min, default, and max number of bytes to use net.ipv4.tcp_rmem = 4096 87380 16777216 net.ipv4.tcp_wmem = 4096 65536 16777216 # don't cache ssthresh from previous connection net.ipv4.tcp_no_metrics_save = 1 # for 1 GigE, increase this to 2500 # for 10 GigE, increase this to 30000 net.core.netdev_max_backlog = 2500
Set the same kernel parameters at runtime using the
sysctl
command:$ sudo sysctl --load=/etc/sysctl.d/90-mariadb-enterprise-columnstore.conf
Disable the Linux Security Module
To avoid confusion and potential problems, we recommend disabling the system's Linux Security Module (LSM) during installation. The specific steps to disable the security module will depend on the platform.
In the Configure the Linux Security Module section, we will configure the security module and restart it.
Disable SELinux (RHEL/CentOS/SLES)
SELinux must be set to permissive
mode before installing MariaDB Enterprise ColumnStore.
Set SELinux to permissive mode by setting
SELINUX=permissive
in/etc/selinux/config
.For example, the file will usually look like this after the change:
# This file controls the state of SELinux on the system. # SELINUX= can take one of these three values: # enforcing - SELinux security policy is enforced. # permissive - SELinux prints warnings instead of enforcing. # disabled - No SELinux policy is loaded. SELINUX=permissive # SELINUXTYPE= can take one of three values: # targeted - Targeted processes are protected, # minimum - Modification of targeted policy. Only selected processes are protected. # mls - Multi Level Security protection. SELINUXTYPE=targeted
Reboot the system.
Confirm that SELinux is in
permissive
mode usinggetenforce
:$ sudo getenforce Permissive
Note
Remember to configure and re-enable SELinux after the installation is complete.
Disable AppArmor (Debian/Ubuntu/SLES)
AppArmor must be disabled before installing MariaDB Enterprise ColumnStore.
Disable AppArmor:
$ sudo systemctl disable apparmor
Reboot the system.
Confirm that no AppArmor profiles are loaded using
aa-status
:$ sudo aa-status
Example output:
apparmor module is loaded. 0 profiles are loaded. 0 profiles are in enforce mode. 0 profiles are in complain mode. 0 processes have profiles defined. 0 processes are in enforce mode. 0 processes are in complain mode. 0 processes are unconfined but have a profile defined.
Note
Remember to configure and re-enable AppArmor after the installation is complete.
Configure the Character Encoding
When using MariaDB Enterprise ColumnStore, it is recommended to set the system's locale to UTF-8.
Set the system's locale to
en_US.UTF-8
by executinglocaledef
:$ sudo localedef -i en_US -f UTF-8 en_US.UTF-8
S3-Compatible Storage
MariaDB Enterprise ColumnStore can use S3-compatible storage to store its data. However, this functionality is optional.
We would recommend S3-compatible storage for all Enterprise ColumnStore users, because it can provide many benefits:
Secure: Most S3-compatible storage is encrypted-at-rest.
Scalable: Most S3-compatible storage is highly optimized for read and write scaling.
Resilient: Most S3-compatible storage is very low maintenance and highly available, since it relies on resilient cloud infrastructure.
Economical: Most S3-compatible storage is very inexpensive.
When S3-compatible storage is used, Enterprise ColumnStore caches data locally to improve performance.
Create an S3 Bucket
If you want to use S3-compatible storage, it is important to create the S3 bucket before you start ColumnStore.
If you already have an S3 bucket, confirm that the bucket is empty.
We will configure Enterprise ColumnStore to use the S3 bucket later in the Configure the S3 Storage Manager section.
Note
The specific steps to create the S3 bucket will depend on what S3-compatible storage you are using.
Enterprise ColumnStore Installation
MariaDB Corporation provides a ZYpp package repository for SUSE Linux Enterprise Server 15.
MariaDB Enterprise ColumnStore ships as a storage engine plugin for MariaDB Enterprise Server and a platform engine to handle back-end storage processes. MariaDB Enterprise Server 10.5 does not require any additional software to operate as a single-node analytics database.
Install Enterprise ColumnStore via ZYpp (SLES)
Retrieve your Customer Download Token at https://customers.mariadb.com/downloads/token/ and substitute for
customer_download_token
in the following directions.Configure the ZYpp package repository.
MariaDB Enterprise ColumnStore
5.5
is available on MariaDB Enterprise Server10.5
.To configure ZYpp package repositories:
$ sudo zypper install wget $ wget https://dlm.mariadb.com/enterprise-release-helpers/mariadb_es_repo_setup $ echo "c78db828709d94876406a0ea346f13fbc38e73996795903f40e3c21385857dd4 mariadb_es_repo_setup" \ | sha256sum -c - $ chmod +x mariadb_es_repo_setup $ sudo ./mariadb_es_repo_setup --token="customer_download_token" --apply \ --mariadb-server-version="10.5"
Install some additional dependencies for Enterprise ColumnStore:
$ sudo zypper install jemalloc
Install MariaDB Enterprise ColumnStore and package dependencies:
$ sudo zypper install MariaDB-server MariaDB-backup \ MariaDB-shared MariaDB-client \ MariaDB-columnstore-engine
Configure MariaDB Enterprise ColumnStore.
Installation only loads MariaDB Enterprise ColumnStore to the system. MariaDB Enterprise ColumnStore requires configuration and additional post-installation steps before the database server is ready for use.
Enterprise ColumnStore Configuration
MariaDB Enterprise ColumnStore requires configuration after it is installed. The configuration file location depends on your operating system.
Enterprise Server Configuration
MariaDB Enterprise Server can be configured in the following ways:
System variables and options can be set in a configuration file (such as
/etc/my.cnf
). MariaDB Enterprise Server must be restarted to apply changes made to the configuration file.System variables and options can be set on the command-line.
If a system variable supports dynamic changes, then it can be set on-the-fly using the SET statement.
Configuration Files
MariaDB's packages include several bundled configuration files. It is also possible to create custom configuration files.
On RHEL, CentOS, and SLES, MariaDB's packages bundle the following configuration files:
/etc/my.cnf
/etc/my.cnf.d/client.cnf
/etc/my.cnf.d/mariadb-enterprise.cnf
/etc/my.cnf.d/mysql-clients.cnf
/etc/my.cnf.d/server.cnf
And on RHEL, CentOS, and SLES, custom configuration files from the following directories are read by default:
/etc/my.cnf.d/
Configuring MariaDB for Enterprise ColumnStore
Determine which system variables and options you need to configure.
Mandatory system variables and options for HTAP include:
System Variable/Option
Description
Set this system variable to
utf8
Set this system variable to
utf8_general_ci
Set this system variable to
ALWAYS
to always use cpimport for LOAD DATA INFILE and INSERT...SELECT statements.Sets the numeric Server ID for this MariaDB Enterprise Server. Must be unique in the deployment.
Set this option to enable the Binary Log.
Set this to
STATEMENT
for HTAP.Set this to
OFF
for HTAP.Set this to
ON
for HTAP.Useful system variables and options for MariaDB Enterprise Server include:
System Variable/Option
Description
Defines the amount of memory InnoDB reserves for the Buffer Pool. Set to no more than 16% of total memory on Servers that also run MariaDB ColumnStore.
Useful system variables and options for HTAP include:
System Variable/Option
Description
Sets the location for the Relay Log
Choose a configuration file in which to configure your system variables and options.
We recommend not making custom changes to one of the bundled configuration files. Instead, create a custom configuration file in one of the included directories. Configuration files in included directories are read in alphabetical order. If you want your custom configuration file to override the bundled configuration files, it is a good idea to prefix the custom configuration file's name with a string that will be sorted last, such as
z-
.On RHEL, CentOS, and SLES, a good custom configuration file would be:
/etc/my.cnf.d/z-custom-my.cnf
Set your system variables and options in the configuration file.
They need to be set in a group that will be read by mariadbd, such as
[mariadb]
or[server]
.For example:
[mariadb] log_error = mariadbd.err character_set_server = utf8 collation_server = utf8_general_ci columnstore_use_import_for_batchinsert = ALWAYS # Replication Configuration (HTAP Server) server_id = 1 log_bin=mariadb-bin binlog_format = STATEMENT log_slave_updates = OFF columnstore_replication_slave = ON # HTAP filtering rules # 1. The transactions are being # replicated from itself replicate_same_server_id = ON
Configure Cross Engine Joins
When a cross engine join is executed, the ExeMgr process connects to the server using the root
user with no password by default. MariaDB Enterprise Server 10.5 will reject this login attempt by default. If you plan to use Cross Engine Joins, you need to configure Enterprise ColumnStore to use a different user account and password. These directions are for configuring the cross engine join user. Directions for creating the cross engine join user are in the Create the Cross Engine Join User section.
To configure cross engine joins, perform the following steps, use the mcsSetConfig command.
For example, to configure Enterprise ColumnStore to use the cross_engine
user account to connect to the server at 127.0.0.1
:
$ sudo mcsSetConfig CrossEngineSupport Host 127.0.0.1
$ sudo mcsSetConfig CrossEngineSupport Port 3306
$ sudo mcsSetConfig CrossEngineSupport User cross_engine
$ sudo mcsSetConfig CrossEngineSupport Password cross_engine_passwd
Note
Please choose a password that meets your organization's password policies. If your MariaDB Enterprise Server instance has a password validation plugin installed, then the password should also meet the configured requirements.
By default, MariaDB Enterprise Server installs the simple_password_check plugin. Its requirements are configured by the simple_password_check_digits, simple_password_check_letters_same_case, simple_password_check_minimal_length, and simple_password_check_other_characters system variables.
Configure the S3 Storage Manager
MariaDB Enterprise ColumnStore can use S3-compatible storage, but it is not required. S3-compatible storage must be configured before it can be used.
To configure Enterprise ColumnStore to use S3-compatible storage, edit /etc/columnstore/storagemanager.cnf
:
[ObjectStorage]
…
service = S3
…
[S3]
bucket = your_columnstore_bucket_name
endpoint = your_s3_endpoint
aws_access_key_id = your_s3_access_key_id
aws_secret_access_key = your_s3_secret_key
# iam_role_name = your_iam_role
# sts_region = your_sts_region
# sts_endpoint = your_sts_endpoint
[Cache]
cache_size = your_local_cache_size
path = your_local_cache_path
The default local cache size is 2 GB.
The default local cache path is
/var/lib/columnstore/storagemanager/cache
.Ensure that the local cache path has sufficient store space to store the local cache.
The
bucket
option must be set to the name of the bucket that you created in the Create an S3 Bucket step.To use an IAM role, you must also uncomment and set
iam_role_name
,sts_region
, andsts_endpoint
.
Start the Enterprise ColumnStore Processes
The Enterprise Server and Enterprise ColumnStore processes can be started using the systemctl
command. In case the processes were started during the installation process, use the restart command to ensure that the processes pick up the new configuration. Perform the following procedure.
Start the MariaDB Enterprise Server process and configure it to start automatically:
$ sudo systemctl restart mariadb $ sudo systemctl enable mariadb
Start the MariaDB Enterprise ColumnStore processes and configure them to start automatically:
$ sudo systemctl restart mariadb-columnstore $ sudo systemctl enable mariadb-columnstore
Create User Accounts
For HTAP deployments, a few user accounts need to be created.
Create the Cross Engine Join User
The credentials for cross engine joins were previously configured in the Cross Engine Joins section. The user account must also be created and granted the necessary privileges to access data.
Connect to the server using MariaDB Client using the
root@localhost
user account:$ sudo mariadb
Create the user account with the CREATE USER statement:
CREATE USER 'cross_engine'@'127.0.0.1' IDENTIFIED BY "cross_engine_passwd"; CREATE USER 'cross_engine'@'localhost' IDENTIFIED BY "cross_engine_passwd";
Note
Please choose the same user name and password that was configured in the Cross Engine Joins section.
Grant the user account
SELECT
privileges on all databases with the GRANT statement:GRANT SELECT ON *.* TO 'cross_engine'@'127.0.0.1'; GRANT SELECT ON *.* TO 'cross_engine'@'localhost';
Create the Client Router User
Connect to each back-end MariaDB server using MariaDB Client using the
root@localhost
user account:$ sudo mariadb
Create the user account used by the monitor and the router using the CREATE USER statement. Make sure that the user account can connect from the MaxScale instance's IP address:
CREATE USER 'maxscale'@'192.0.2.1' IDENTIFIED BY 'max_passwd';
Note
Please choose a password that meets your organization's password policies. If your MariaDB Enterprise Server instance has a password validation plugin installed, then the password should also meet the configured requirements.
By default, MariaDB Enterprise Server installs the simple_password_check plugin. Its requirements are configured by the simple_password_check_digits, simple_password_check_letters_same_case, simple_password_check_minimal_length, and simple_password_check_other_characters system variables.
Grant the required privileges to the user account used by the monitor and the router using the GRANT statement.
For the Read Connection Router (readconnroute), the global
SHOW DATABASES
privilege and theSELECT
privilege on several of the system's privilege tables are required:GRANT SHOW DATABASES ON *.* TO 'maxscale'@'192.0.2.1'; GRANT SELECT ON mysql.columns_priv TO 'maxscale'@'192.0.2.1'; GRANT SELECT ON mysql.db TO 'maxscale'@'192.0.2.1'; GRANT SELECT ON mysql.proxies_priv TO 'maxscale'@'192.0.2.1'; GRANT SELECT ON mysql.roles_mapping TO 'maxscale'@'192.0.2.1'; GRANT SELECT ON mysql.tables_priv TO 'maxscale'@'192.0.2.1'; GRANT SELECT ON mysql.user TO 'maxscale'@'192.0.2.1';
For MariaDB Monitor (mariadbmon), the required permissions depend on the version of MariaDB Enterprise Server that is being used.
In ES10.5.8-5 and later, the following privileges are required:
GRANT BINLOG ADMIN, READ_ONLY ADMIN, RELOAD, REPLICA MONITOR, REPLICATION MASTER ADMIN, REPLICATION REPLICA ADMIN, REPLICATION REPLICA, SHOW DATABASES ON *.* TO 'maxscale'@'192.0.2.1';
In ES10.5.6-4 and before, the following privileges are required:
GRANT BINLOG ADMIN, READ_ONLY ADMIN, RELOAD, REPLICATION MASTER ADMIN, REPLICATION REPLICA ADMIN, REPLICATION REPLICA, SHOW DATABASES ON *.* TO 'maxscale'@'192.0.2.1';
In ES10.4 and before, the following privileges are required:
GRANT SUPER, REPLICATION CLIENT, RELOAD, PROCESS, SHOW DATABASES, EVENT ON *.* TO 'maxscale'@'192.0.2.1';
Create the Replication Router User
Connect to the server using MariaDB Client using the
root@localhost
user account:$ sudo mariadb
Create the user account used by the monitor and the router using the CREATE USER statement. Make sure that the user account can connect from the MaxScale instance's IP address:
CREATE USER 'repl'@'%' IDENTIFIED BY 'repl_passwd';
Note
Please choose a password that meets your organization's password policies. If your MariaDB Enterprise Server instance has a password validation plugin installed, then the password should also meet the configured requirements.
By default, MariaDB Enterprise Server installs the simple_password_check plugin. Its requirements are configured by the simple_password_check_digits, simple_password_check_letters_same_case, simple_password_check_minimal_length, and simple_password_check_other_characters system variables.
Grant the required privileges to the user account used by the monitor and the router using the GRANT statement.
For the Read Connection Router (readconnroute), the global
SHOW DATABASES
privilege and theSELECT
privilege on several of the system's privilege tables are required:GRANT SHOW DATABASES ON *.* TO 'repl'@'%'; GRANT SELECT ON mysql.columns_priv TO 'repl'@'%'; GRANT SELECT ON mysql.db TO 'repl'@'%'; GRANT SELECT ON mysql.proxies_priv TO 'repl'@'%'; GRANT SELECT ON mysql.roles_mapping TO 'repl'@'%'; GRANT SELECT ON mysql.tables_priv TO 'repl'@'%'; GRANT SELECT ON mysql.user TO 'repl'@'%';
For MariaDB Monitor (mariadbmon), the required permissions depend on the version of MariaDB Enterprise Server that is being used.
In ES10.5.8-5 and later, the following privileges are required:
GRANT REPLICATION REPLICA ADMIN, REPLICATION REPLICA, REPLICA MONITOR, REPLICATION MASTER ADMIN ON *.* TO 'repl'@'%';
In ES10.5.6-4 and before, the following privileges are required:
GRANT REPLICATION REPLICA ADMIN, REPLICATION REPLICA, BINLOG MONITOR, REPLICATION MASTER ADMIN ON *.* TO 'repl'@'%';
In ES10.4 and before, the following privileges are required:
GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'repl'@'%';
MaxScale Installation
You also need to install MaxScale on the MaxScale instance.
MariaDB Corporation provides a ZYpp package repository for SUSE Linux Enterprise Server 15.
Install MaxScale via ZYpp (SLES)
On the MaxScale instance, perform the following steps to install MaxScale.
Retrieve your Customer Download Token at https://customers.mariadb.com/downloads/token/ and substitute for
customer_download_token
in the following directions.Configure the ZYpp package repository.
Enterprise ColumnStore
5.5
requires MaxScale2.5
. Pass the version to install using the--mariadb-maxscale-version
flag to the mariadb_es_repo_setup script.To configure ZYpp package repositories:
$ sudo zypper install wget $ wget https://dlm.mariadb.com/enterprise-release-helpers/mariadb_es_repo_setup $ echo "c78db828709d94876406a0ea346f13fbc38e73996795903f40e3c21385857dd4 mariadb_es_repo_setup" \ | sha256sum -c - $ chmod +x mariadb_es_repo_setup $ sudo ./mariadb_es_repo_setup --token="customer_download_token" --apply \ --mariadb-maxscale-version="2.5"
Install MariaDB MaxScale and package dependencies:
$ sudo zypper install maxscale
Configure MaxScale.
Installation only loads MariaDB MaxScale to the system. MariaDB MaxScale requires configuration before MaxScale is ready for use.
Configure MaxScale
MariaDB MaxScale's parameters can be configured in two ways:
All parameters can be set in a configuration file (such as the
/etc/maxscale.cnf
file). When the configuration file is updated, the MaxScale instance must be restarted to apply the changes.If a parameter supports dynamic changes, then it can be set on-the-fly using the MaxCtrl utility.
Configure Global Parameters
Determine which global parameters you need to configure.
Useful global parameters for MariaDB MaxScale:
Parameter
Description
Sets the local IP address or network interface to use when connecting to MariaDB Enterprise Servers.
Sets whether the MaxScale Instance supports the REST API. Disable to block access.
Sets the network interface used by the REST API.
Sets the port used by the REST API.
Sets whether the REST API uses HTTP Basic Access authentication. Users can be created wih maxctrl.
Set your global parameters in
maxscale.cnf
.MariaDB MaxScale's global parameters, which apply to the whole instance, are set in the
[maxscale]
configuration group:For example:
[maxscale] admin_auth = true
Configure Servers
Determine which server parameters you need to configure.
Mandatory parameters for Server objects:
Parameter
Description
type
Set the module type to
server
.address
Set the IP address for the MariaDB Enterprise Server.
port
Set the port the MariaDB Enterprise Server listens on.
protocol
Set the protocol to
MariaDBBackend
to connect the Server.Set your server parameters in
maxscale.cnf
.For each Server in your deployment, add a new uniquely-named configuration group to the MaxScale Instance. The name of the configuration group is arbitrary, but it cannot contain any white space. Use whatever terms you find most convenient, but it is most common to use names like
serverN
or the server's host name.For example:
[server1] type = server address = 192.0.2.2 port = 3306 protocol = MariaDBBackend
Configuring MariaDB Monitor
Determine which parameters for MariaDB Monitor (mariadbmon) you need to configure.
Mandatory parameters for MariaDB Monitor (
mariadbmon
):Parameter
Description
type
Set the type to
monitor
.module
Set to
mariadbmon
for MariaDB Replication.servers
Set to a comma-separated list of the Server object names.
user
Set to the user MariaDB MaxScale uses to connect to the Servers.
password
Set to the password MariaDB MaxScale uses to authenticate on the Servers.
Set your MariaDB Monitor parameters in
maxscale.cnf
.Add a new uniquely-named configuration group for the monitor to the MaxScale Instance. The name of the configuration group is arbitrary, but it cannot contain any white space. Use whatever terms you find most convenient, but it is most common to use names like
repl-monitor
.For example:
[repl-monitor] type = monitor module = mariadbmon servers = server1 monitor_interval = 1000ms user = repl password = repl_passwd
Configure the Binlog Filter
Determine which parameters for Binlog Filter (binlogfilter) you need to configure.
Commonly used parameters for Binlog Filter (binlogfilter):
Parameter
Description
type
Set the module type to
filter
to indicate filter configuration.module
Use the
binlogfilter
module.match
Regular Expression indicating the tables you want to replicate. In the example, the
orders
table replicates.rewrite_src
Regular Expression indicating the database you want to replicate data from.
rewrite_dest
Replacement string for the database the filter replicates into.
Set your Binlog Filter parameters in
maxscale.cnf
.Add a new uniquely-named configuration group for the filter to the MaxScale Instance. The name of the configuration group is arbitrary, but it cannot contain any white space. Use whatever terms you find most convenient, but it is most common to use names like
repl-filter
.For example:
[repl-filter] type = filter module = binlogfilter match = /[.]orders/ rewrite_src = innodb rewrite_dest = columnstore
In the example configuration, the Binary Log Filter would replicate writes to the
innodb_schema.orders
table into thecolumnstore_schema.orders
table.
Configure Read Connection Router for Replication
Determine which parameters for Read Connection Router (readconnroute) you need to configure for the router handling replication.
Mandatory parameters for Read Connection Router (
readconnroute
):Parameter
Description
type
Set the module type as
service
.router
Use to set the type of router you want to use for the service. For the Read Connection Router, set to
readconnroute
.servers
Set as a comma-separated list of the Server object names.
user
Set the user you want the MaxScale Instance to use when connecting to Servers.
password
Set the password you want the MaxScale Instance to use in authentication when conecting to Servers.
filters
For the replication routing service, configure it to use the
repl-filter
as configured above.Set your Read Connection Router parameters in
maxscale.cnf
.Add a new uniquely-named configuration group for the router to the MaxScale Instance. The name of the configuration group is arbitrary, but it cannot contain any white space. Use whatever terms you find most convenient, but it is most common to use names like
repl-router
.For example:
[repl-router] type = service router = readconnroute servers = server1 user = repl password = repl_passwd filters = repl-filter
In the example configuration, this router service is associated with the filter that we created in the previous section.
Configure the Replication Router's Listener
Determine which parameters for the listener you need to configure.
Mandatory parameters for the listener:
Parameter
Description
type
Set the module type as a
listener
.service
Use this parameter to connect the listener to a configured routing service.
protocol
Set this parameter to
MariaDBClient
to handle incoming client connections.port
Set this parameter to specify the port you want the MaxScale Instance to listen on.
Set your listener parameters in
maxscale.cnf
.Add a new uniquely-named configuration group for the listener to the MaxScale Instance. The name of the configuration group is arbitrary, but it cannot contain any white space. Use whatever terms you find most convenient, but it is most common to use names like
repl-router-listener
.For example:
[repl-router-listener] type = listener service = repl-router protocol = MariaDBClient port = 3309
Configure Read Connection Router for Clients
Determine which parameters for Read Connection Router (readconnroute) you need to configure for the router handling client connections.
Mandatory parameters for Read Connection Router (
readconnroute
):Parameter
Description
type
Set the module type as
service
.router
Use to set the type of router you want to use for the service. For the Read Connection Router, set to
readconnroute
.servers
Set as a comma-separated list of the Server object names.
user
Set the user you want the MaxScale Instance to use when connecting to Servers.
password
Set the password you want the MaxScale Instance to use in authentication when conecting to Servers.
Set your Read Connection Router parameters in
maxscale.cnf
.Add a new uniquely-named configuration group for the router to the MaxScale Instance. The name of the configuration group is arbitrary, but it cannot contain any white space. Use whatever terms you find most convenient, but it is most common to use names like
connection-router
.For example:
[connection-router] type = service router = readconnroute servers = server1 user = maxscale password = max_passwd
Configure the Client Router's Listener
Determine which parameters for the listener you need to configure.
Mandatory parameters for the listener:
Parameter
Description
type
Set the module type as a
listener
.service
Use this parameter to connect the listener to a configured routing service.
protocol
Set this parameter to
MariaDBClient
to handle incoming client connections.port
Set this parameter to specify the port you want the MaxScale Instance to listen on.
Set your listener parameters in
maxscale.cnf
.Add a new uniquely-named configuration group for the listener to the MaxScale Instance. The name of the configuration group is arbitrary, but it cannot contain any white space. Use whatever terms you find most convenient, but it is most common to use names like
connection-router-listener
.For example:
[connection-router-listener] type = listener service = connection-router protocol = MariaDBClient port = 3306
Start MaxScale
The MaxScale processes can be started using the systemctl
command.
Start the service:
$ sudo systemctl start maxscale
Configure the service to start automatically:
$ sudo systemctl enable maxscale
Configure MariaDB Replication
The deployment requires MariaDB Replication, which must be configured.
In the HTAP deployment, the MariaDB Enterprise Server treats MariaDB MaxScale as a Primary Server. The Replica Server process connects to the MaxScale Instance where it's routed back to the Server to retrieve binary log events. Those writes are then filtered, changing the database name before they replicate back to the same Server on a different database.
Connect to the back-end MariaDB server using MariaDB Client using the
root@localhost
user account:$ sudo mariadb
Set the GTID position by setting the gtid_slave_pos system variable.
If this is a new deployment, then it would be set to the empty string:
SET GLOBAL gtid_slave_pos='';
Configure the Server to replicate from MariaDB MaxScale by using the CHANGE MASTER TO statement:
CHANGE MASTER TO MASTER_USER = "repl", MASTER_HOST = "192.0.2.1", MASTER_PORT = 3309, MASTER_PASSWORD = "repl_passwd", MASTER_USE_GTID = slave_pos;
Note the
MASTER_HOST
option is set to the IP address of the MaxScale Instance. TheMASTER_PORT
option is set to thereplication-listener
port.Start replication using the START SLAVE statement:
START SLAVE;
The Server connects to the Binary Log Router on the MaxScale Instance. Writes in InnoDB are then routed through this service back to the Server and written in the MariaDB ColumnStore table.
Confirm that replication is working properly using the SHOW SLAVE STATUS statement:
SHOW SLAVE STATUS;
Configure the Linux Security Module
If you stopped the Linux Security Module (LSM) during installation, you can restart the module and configure.
The specific steps to configure the security module depend on the platform.
Configure SELinux (RHEL/CentOS/SLES)
We set SELinux to permissive mode in the Disable SELinux section, but we have to create an SELinux policy for Enterprise ColumnStore before re-enabling it. This will ensure that SELinux does not interfere with Enterprise ColumnStore's functionality. A policy can be generated while SELinux is still in permissive mode using the audit2allow
command.
To configure SELinux, you have to install the packages required for
audit2allow
.On SLES, install the following:
$ sudo zypper install policycoreutils python3-policycoreutils
Allow the system to run under load for a while to generate SELinux audit events.
After the system has taken some load, generate an SELinux policy from the audit events using
audit2allow
:$ sudo grep mysqld /var/log/audit/audit.log | audit2allow -M mariadb_local
If no audit events were found, this will print the following:
$ sudo grep mysqld /var/log/audit/audit.log | audit2allow -M mariadb_local Nothing to do
If audit events were found, the new SELinux policy can be loaded using
semodule
:$ sudo semodule -i mariadb_local.pp
Set SELinux to enforcing mode by setting
SELINUX=enforcing
in/etc/selinux/config
:# This file controls the state of SELinux on the system. # SELINUX= can take one of these three values: # enforcing - SELinux security policy is enforced. # permissive - SELinux prints warnings instead of enforcing. # disabled - No SELinux policy is loaded. SELINUX=enforcing # SELINUXTYPE= can take one of three values: # targeted - Targeted processes are protected, # minimum - Modification of targeted policy. Only selected processes are protected. # mls - Multi Level Security protection. SELINUXTYPE=targeted
Reboot the system.
Confirm that SELinux is in enforcing mode using
getenforce
:$ sudo getenforce
Configure AppArmor (Debian/Ubuntu/SLES)
We disabled AppArmor in the Disable AppArmor section, but we have to create an AppArmor profile for Enterprise ColumnStore before re-enabling it. This will ensure that AppArmor does not interfere with Enterprise ColumnStore's functionality.
For information on how to create a profile, see How to create an AppArmor Profile on ubuntu.com.
Administration
Multi-node Enterprise ColumnStore has several components. Each of those components needs to be administered.
Enterprise Server Administration
MariaDB Enterprise Server uses systemctl
to start and stop the server processes:
Operation |
Command |
Start |
|
Stop |
|
Restart |
|
Enable during startup |
|
Disable during startup |
|
Status |
|
Enterprise ColumnStore Administration
MariaDB Enterprise ColumnStore uses systemctl
to start and stop the ColumnStore processes:
Operation |
Command |
Start |
|
Stop |
|
Restart |
|
Enable during startup |
|
Disable during startup |
|
Status |
|
MaxScale Administration
MariaDB MaxScale uses systemctl
to start and stop the MaxScale process:
Operation |
Command |
Start |
|
Stop |
|
Restart |
|
Enable during startup |
|
Disable during startup |
|
Status |
|
Testing
When you have the ColumnStore and MaxScale instances running with replication ready, you should test each feature to ensure that it is running as expected and that there weren't any issues during startup.
Checking MaxScale Status
Check that MaxScale is running properly by using the MaxCtrl utility:
$ sudo maxctrl show maxscale ┌──────────────┬──────────────────────────────────────────────────────────────────────┐ │ Version │ 2.5.8 │ ├──────────────┼──────────────────────────────────────────────────────────────────────┤ │ Commit │ 61b8bbf7f63c38ca9c408674e66f3627a0b2192e │ ├──────────────┼──────────────────────────────────────────────────────────────────────┤ │ Started At │ Fri, 03 Jan 2020 18:05:18 GMT │ ├──────────────┼──────────────────────────────────────────────────────────────────────┤ │ Activated At │ Fri, 03 Jan 2020 18:05:18 GMT │ ├──────────────┼──────────────────────────────────────────────────────────────────────┤ │ Uptime │ 109 │ ├──────────────┼──────────────────────────────────────────────────────────────────────┤ │ Parameters │ { │ │ │ "libdir": "/usr/lib/x86_64-linux-gnu/maxscale", │ │ │ "datadir": "/var/lib/maxscale", │ │ │ "process_datadir": "/var/lib/maxscale/data3850", │ │ │ "cachedir": "/var/cache/maxscale", │ │ │ "configdir": "/etc", │ │ │ "config_persistdir": "/var/lib/maxscale/maxscale.cnf.d", │ │ │ "module_configdir": "/etc/maxscale.modules.d", │ │ │ "piddir": "/var/run/maxscale", │ │ │ "logdir": "/var/log/maxscale", │ │ │ "langdir": "/var/lib/maxscale", │ │ │ "execdir": "/usr/bin", │ │ │ "connector_plugindir": "/usr/lib/x86_64-linux-gnu/mysql/plugin", │ │ │ "threads": 1, │ │ │ "thread_stack_size": 8388608, │ │ │ "writeq_high_water": 0, │ │ │ "writeq_low_water": 0, │ │ │ "auth_connect_timeout": 3, │ │ │ "auth_read_timeout": 1, │ │ │ "auth_write_timeout": 2, │ │ │ "skip_permission_checks": false, │ │ │ "admin_auth": true, │ │ │ "admin_enabled": true, │ │ │ "admin_log_auth_failures": true, │ │ │ "admin_host": "127.0.0.1", │ │ │ "admin_port": 8989, │ │ │ "admin_ssl_key": "", │ │ │ "admin_ssl_cert": "", │ │ │ "admin_ssl_ca_cert": "", │ │ │ "admin_pam_readwrite_service": "", │ │ │ "admin_pam_readonly_service": "", │ │ │ "passive": false, │ │ │ "query_classifier": "", │ │ │ "query_classifier_cache_size": 155008819, │ │ │ "retain_last_statements": 0, │ │ │ "dump_last_statements": "never", │ │ │ "session_trace": 0, │ │ │ "load_persisted_configs": true, │ │ │ "max_auth_errors_until_block": 10 │ │ │ } │ └──────────────┴──────────────────────────────────────────────────────────────────────┘
Listing MaxScale Services
List MaxScale's services by using the MaxCtrl utility to execute the list services command:
$ sudo maxctrl list services ┌────────────────────┬───────────────┬─────────────┬───────────────────┬─────────┐ │ Service │ Router │ Connections │ Total Connections │ Servers │ ├────────────────────┼───────────────┼─────────────┼───────────────────┼─────────┤ │ connection-router │ readconnroute │ 0 │ 5 │ server1 │ ├────────────────────┼───────────────┼─────────────┼───────────────────┼─────────┤ │ repl-router │ readconnroute │ 1 │ 4 │ server1 │ └────────────────────┴───────────────┴─────────────┴───────────────────┴─────────┘
Connecting to the Server
Connections to the HTAP deployment should route through port 3306 on the MaxScale Instance.
Connect to the server using MariaDB Client:
$ mariadb -udb_user -p -h 192.0.2.1 Password> Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 38 Server version: 10.5.8-5-MariaDB-Enterprise MariaDB Enterprise Server Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]>
Remember in managing users and privileges you need to duplicate CREATE USER and GRANT statements to include the MaxScale Instance host.
Checking Replication Status
In this HTAP deployment, MariaDB Enterprise Server uses MariaDB Replication through the Binary Log Router on the MaxScale Instance to replicate writes from an InnoDB table to a MariaDB ColumnStore table.
Connect to the server using MariaDB Client using the
root@localhost
user account:$ mariadb -udb_user -p -h 192.0.2.1 Password> Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 38 Server version: 10.5.8-5-MariaDB-Enterprise MariaDB Enterprise Server Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]>
Execute the SHOW SLAVE STATUS statement.
This statement returns a lot of columns, but the important ones are
Slave_IO_Running
andSlave_SQL_Running
. If both of those sayYes
, then replication is running correctly.SHOW SLAVE STATUS;
Testing Replication
The HTAP deployment as configured replicates data from an innodb_schema.orders
table to the columnstore_schema.orders
table. To test replication, first create these tables on the Server:
Create the databases with the CREATE DATABASE statement:
CREATE DATABASE IF NOT EXISTS columnstore_schema; CREATE DATABASE IF NOT EXISTS innodb_schema;
Create the tables with the CREATE TABLE statement:
CREATE TABLE IF NOT EXISTS columnstore_schema.orders ( id INT, first_name VARCHAR(50), last_name VARCHAR(50), email VARCHAR(50), gender VARCHAR(50), ip_address VARCHAR(20) ) ENGINE=ColumnStore; CREATE TABLE IF NOT EXISTS innodb_schema.orders ( id INT PRIMARY KEY AUTO_INCREMENT, first_name VARCHAR(50), last_name VARCHAR(50), email VARCHAR(50), gender VARCHAR(50), ip_address VARCHAR(20) ) ENGINE=InnoDB;
Insert some data into the InnoDB table with the INSERT statement:
INSERT INTO innodb_schema.orders (first_name, last_name, email, gender, ip_address) VALUES ("Walker", "Percy", "w.percy@example.edu", "Male", "192.0.2.100"), ("Howard", "Lovecraft", "h.lovecraft@example.com", "Male", "192.0.2.29"), ("William", "Faulkner", "faulkner@example.com", "Male", "192.0.2.59"), ("Ernest", "Hemingway", "ernest.hemingway@example.edu", "Male", "192.0.2.15"), ("Scott", "Fitzgerald", "fsfitzgerald@example.edu", "Male", "192.0.2.9");
Confirm that the data was replicated to the ColumnStore table properly with the SELECT statement:
SELECT * FROM columnstore_schema.orders;
+----+------------+------------+------------------------------+----------+---------------+ | id | first_name | last_name | email | gender | ip_address | +----+------------+------------+------------------------------+----------+---------------+ | 1 | Walker | Percy | w.percy@example.edu | Male | 192.0.2.100 | | 2 | Howard | Lovecraft | h.lovecraft@example.com | Male | 192.0.2.29 | | 3 | William | Faulkner | faulkner@example.com | Male | 192.0.2.59 | | 4 | Ernest | Hemingway | ernest.hemingway@example.edu | Male | 192.0.2.15 | | 5 | Scott | Fitzgerald | fsfitzgerald@example.edu | Male | 192.0.2.9 | +----+------------+------------+------------------------------+----------+---------------+
The replica I/O thread running on the Server read changes in its own Binary Log through the replication router on MariaDB MaxScale, it then filtered the changes to the new schema name and applied them on the MariaDB ColumnStore table.
By issuing queries to update the table or queries requiring real-time data to the InnoDB table and issuing analytical processing queries to the MariaDB ColumnStore table, you can now perform hybrid transactional-analytical processing.