MariaDB MaxScale like a Pro: Setting up MaxScale 2.3

I created this series of blog posts after being worked with the MariaDB MaxScale for many customers. All the points mentioned here reflect my views; I’ll add links to the online docs so we can have an official reference. I intend to share my experiences working with MaxScale; we need more practical documentation so we improve the MaxScale usage and transfer knowledge.

First of all, MaxScale 2.3 release, notes, take a look.

Something you need to know before starting with the praxis here – all instances are running Debian 9.

root@prod-mariadb01:~# lsb_release -a
No LSB modules are available.
Distributor ID:	Debian
Description:	Debian GNU/Linux 9.9 (stretch)
Release:	9.9
Codename:	stretch

MariaDB MaxScale in a nutshell…

The MariaDB MaxScale is an intelligent database proxy which understands SQL language and has a bunch of bundle modules known as routers or services, monitors, filters, etc. After setting up the MaxScale packages, you have access to all bundle modules, as you need only to add a basic configuration file and start the service. Since version 2.1, you don’t need to elaborate a complete configuration file (/etc/maxscale.cnf) to start the service. Once you get a configuration file with the global [maxscale] section and at least one service defined, you can start the MaxScale service.

What are we going to be doing for setting up the MariaDB MaxScale?

  1. Add the MariaDB Official Repository;
  2. Create the needed users on the database servers (you will see soon that I’m considering you already a Master/Slave running environment);
  3. Create the .secrets file so we can encrypt the passwords for the users on the maxscale.cnf;
  4. Create a basic configuration file for MaxScale and start the process;
  5. Run dynamic commands so we can create a monitor (MariaDBMon), the servers, a listener, and link created servers with the monitor and the service.

Moving on…

By chance, you can be running something like below, which is going to give you the global configuration plus the ReadWtiteSplit Router configured as the service to be configured:

#: This is the basic configuration file we can get in place to start maxscale.
#: Notice that we need yet to come back soon to this file so we can add the service 
#: encrypted user password (attention to security matters, no clear text passwords, please)

threads                     = auto
log_augmentation            = 1
ms_timestamp                = 1

type                        = service
router                      = readwritesplit
user                        = maxusr
password                    = <add your encrypted maxusr password here>
version_string              = 5.5.50-MariaDB #:should be used with all 10.1 servers and older

As we need SOP or Standard Operational Procedures for everything we perform, the documentation I have for setting up MaxScale considers to always have two users:

  • A service user: no matter how many services/routers you’re running on a MaxScale instance, you need to have at least one user set for the service. The defined user for service is the maxusr (yes, without the “e”, I didn’t forget that). Once you defined that user, you also need to create it on backends so the MaxScale Router/Service can connect to backends and forward queries. In this specific scenario, as we’re speaking about the ReadWriteSplit Router, writes will be sent to the master and reads will be sent to the slaves. You would like to check how the ReadWriteSplit Router Routing Decisions work so you can better design your applications;
  • A monitor user: monitors are modules that monitor the backends, and depending on what you’re running, you will use one monitor or another. If you running a replication cluster, regular GTID Master/Slaves replication, you want to use the MariaDBMon which is going to give you the automatic operations such as failover/rejoin and the manual possibility to perform a switchover;
  • A replication user: as we’re considering a replication cluster, or a yet simple master/slave scenario, we need to have a user so MaxScale can configure replication on database servers when needed. It happens when we configure MaxScale to execute a failover in case the master should crash, we run a manual switchover or yet, a rejoin needs to be executed when the old master comes back to the cluster now as a new slave/replica. If you don’t create a replication user when configuring the MariaDBMon, be aware that the user for your replication will be the one you defined to run the monitor per se, I personally don’t like that (don’t be lazy, 😉 ).

Let’s assume you have a simple Master/Slave already running, like below:

#: master/slave topology
MariaDB MaxScale Servers
1. prod_maxscale01 ( - Mode: Active)

MariaDB Servers Backends Cluster
2. prod_mariadb01 ( - master)
3. \__ prod_mariadb02 ( - slave/replica)
4. \__ prod_mariadb03 (  - slave/replica)

As a best practice, always configure the @@global.report_host on all database servers with their names:

prod-mariadb01 [(none)]> show slave hosts;
| Server_id | Host           | Port | Master_id |
|         3 | prod_mariadb03 | 3306 |         1 |
|         2 | prod_mariadb02 | 3306 |         1 |
2 rows in set (0.000 sec

Assuming the above MariaDB Servers Backends Cluster already have replication up and running (most of you have an environment like this one), you can just think about how we can add a MaxScale server in the middle of applications and your database servers. Most of the time I’m going to refer to database servers as backends as per the regular terminology we use after adding a Load Balancer to a database topology.

Doing a quick recap on where we are, we need now to create the users on the master, so, we can see users replicating to slaves and have the same data all around. Also, it’s good to have the @@global.gtid_strict_mode set on all the servers so we can keep the binary log files the same on all the servers (MaxScale also likes that).

Below we are creating the users as mentioned before, considering the backends we’re working with:

#: maxscale service user
CREATE USER 'maxusr'@'10.136.%' IDENTIFIED BY '123';
GRANT SELECT ON mysql.user TO 'maxusr'@'10.136.%';
GRANT SELECT ON mysql.db TO 'maxusr'@'10.136.%';
GRANT SELECT ON mysql.tables_priv TO 'maxusr'@'10.136.%';
GRANT SHOW DATABASES ON *.* TO 'maxusr'@'10.136.%';
GRANT SELECT ON mysql.roles_mapping TO maxusr@'10.136.%';

#: maxscale monitor user
CREATE USER 'maxmon'@'10.136.%' IDENTIFIED BY '321';
GRANT CREATE, SELECT, UPDATE, INSERT, DELETE ON maxscale_schema.* TO 'maxmon'@'10.136.%';

#: replication users  - make sure the below user can connect
#: from all backends to all backends
CREATE USER mariadb@'10.136.%' IDENTIFIED BY '123';

One additional detail here is that if you’re running the MariaDB Server 10.3.4 or you upgraded from an older version to a newer one, like MariaDB Server 10.3.5, as the user maxmon has the SUPER privilege, the DELETE HISTORY privilege will also be added to the list of GRANTS due to the fact that user should also be able to delete data from the System Versioned Tables.

After creating the above users, we need to go the prod_maxscale01 – – as we need to set up the MariaDB Official Repository and setup MaxScale packages:

#: setting up the repository
root@prod-maxscale01:~# curl -sS https://downloads.mariadb.com/MariaDB/mariadb_repo_setup | sudo bash
[warning] Found existing file at /etc/apt/sources.list.d/mariadb.list. Moving to /etc/apt/sources.list.d/mariadb.list.old_1.
[info] Repository file successfully written to /etc/apt/sources.list.d/mariadb.list
[info] Adding trusted package signing keys...
Executing: /tmp/apt-key-gpghome.sDa0MNg3Md/gpg.1.sh --keyserver hkp://keys.gnupg.net:80 --recv-keys 0x8167EE24 0xE3C94F49 0xcbcb082a1bb943db 0xf1656f24c74cd1d8 0x135659e928c12247
gpg: key 135659E928C12247: "MariaDB Maxscale <maxscale@googlegroups.com>" not changed
gpg: key F1656F24C74CD1D8: 4 signatures not checked due to missing keys
gpg: key F1656F24C74CD1D8: "MariaDB Signing Key <signing-key@mariadb.org>" not changed
gpg: key CBCB082A1BB943DB: 32 signatures not checked due to missing keys
gpg: key CBCB082A1BB943DB: "MariaDB Package Signing Key <package-signing-key@mariadb.org>" not changed
gpg: key CE1A3DD5E3C94F49: 3 signatures not checked due to missing keys
gpg: key CE1A3DD5E3C94F49: "MariaDB Enterprise Signing Key <signing-key@mariadb.com>" not changed
gpg: key 70E4618A8167EE24: "MariaDBManager" not changed
gpg: Total number processed: 5
gpg:              unchanged: 5
Hit:1 http://security.debian.org stretch/updates InRelease
Ign:2 http://mirrors.digitalocean.com/debian stretch InRelease
Hit:3 https://repos.insights.digitalocean.com/apt/do-agent main InRelease
Get:4 http://mirrors.digitalocean.com/debian stretch-updates InRelease [91.0 kB]
Hit:5 http://downloads.mariadb.com/MariaDB/mariadb-10.4/repo/debian stretch InRelease
Hit:6 http://mirrors.digitalocean.com/debian stretch Release
Ign:7 http://downloads.mariadb.com/MaxScale/2.3/debian stretch InRelease
Hit:8 http://downloads.mariadb.com/Tools/debian stretch InRelease
Hit:10 http://downloads.mariadb.com/MaxScale/2.3/debian stretch Release
Hit:9 https://packagecloud.io/akopytov/sysbench/debian stretch InRelease
Fetched 91.0 kB in 0s (106 kB/s)
Reading package lists... Done
[info] Successfully added trusted package signing keys.

#: setting up packages
root@prod-maxscale01:~# apt install maxscale maxscale-experimental mariadb-client -y
Reading package lists... Done
Building dependency tree
Reading state information... Done
The following NEW packages will be installed:
  maxscale maxscale-experimental
0 upgraded, 2 newly installed, 0 to remove and 5 not upgraded.
Need to get 167 kB/31.1 MB of archives.
After this operation, 143 MB of additional disk space will be used.
Get:1 http://downloads.mariadb.com/MaxScale/2.3/debian stretch/main amd64 maxscale-experimental amd64 2.3.11 [167 kB]
Fetched 167 kB in 0s (337 kB/s)
Selecting previously unselected package maxscale.
(Reading database ... 35953 files and directories currently installed.)
Preparing to unpack .../maxscale_2.3.11_amd64.deb ...
Unpacking maxscale (2.3.11) ...
Selecting previously unselected package maxscale-experimental.
Preparing to unpack .../maxscale-experimental_2.3.11_amd64.deb ...
Unpacking maxscale-experimental (2.3.11) ...
Setting up maxscale (2.3.11) ...
Setting up maxscale-experimental (2.3.11) ...

Why am I setting up the mariadb-client package also? We need to test access from the MaxScale host to the backends so we can make sure MaxScale configured with the users we created will also be able to access backends. To catch permission or access denied when we execute queries is really bad as we need to recap on everything we did for the set up and it can take some time to review everything. We definitely don’t want that. Test access and move on.

Now, let’s create the .secrets and get an encryption version of users passwords:

#: create the .secrets file
root@prod_maxscale01:~# maxkeys
Generating .secrets file in /var/lib/maxscale.

#: generate the encrypted password for maxusr - this is the service user
#: you are going to need the below-encrypted password for the next question
root@prod-maxscale01:~# maxpasswd 123 #: maxusr

#: generate the encrypted password for maxmon - this is the monitor user
#: you are going to need the below-encrypted password on next labs
root@prod-maxscale01:~# maxpasswd 321 #: maxmon

#: generate the encrypted password for the mariadb replication user
root@prod-maxscale01:~# maxpasswd 123 #: mariadb

#: adjust permissions for the .secrets file
root@prod-maxscale01:~# chown maxscale:maxscale /var/lib/maxscale/.secrets

With encrypted passwords, we can create a basic configuration file. The below is your /etc/maxscale.cnf:

threads                     = auto
log_augmentation            = 1
ms_timestamp                = 1
admin_host                  =
admin_port                  = 8989

type                        = service
router                      = readwritesplit
user                        = maxusr
password                    = A0FE98035CFA5EB978337B739E949878

Check if the maxscale.service is enabled so it can start with the OS boot and start it. Check the log file afterward:

#: starting up the maxscale.service
root@prod-maxscale01:~# systemctl --now enable maxscale.service

root@prod-maxscale01:~# tail -n30 /var/log/maxscale/maxscale.log
MariaDB MaxScale  /var/log/maxscale/maxscale.log  Mon Aug  5 12:25:54 2019
2019-08-05 12:25:54   notice : (mxb_log_set_syslog_enabled): syslog logging is enabled.
2019-08-05 12:25:54   notice : (mxb_log_set_maxlog_enabled): maxlog logging is enabled.
2019-08-05 12:25:54.078   notice : (mxb_log_set_highprecision_enabled): highprecision logging is enabled.
2019-08-05 12:25:54.078   notice : (config_load_global): Using up to 976.56KiB of memory for query classifier cache
2019-08-05 12:25:54.079   notice : (change_cwd): Working directory: /var/log/maxscale
2019-08-05 12:25:54.079   notice : (init_sqlite3): The collection of SQLite memory allocation statistics turned off.
2019-08-05 12:25:54.079   notice : (init_sqlite3): Threading mode of SQLite set to Multi-thread.
2019-08-05 12:25:54.080   notice : (main): MariaDB MaxScale 2.3.11 started (Commit: 36355922281a6820de63b76fb76c9203861e3988)
2019-08-05 12:25:54.080   notice : (main): MaxScale is running in process 13166
2019-08-05 12:25:54.080   notice : (main): Configuration file: /etc/maxscale.cnf
2019-08-05 12:25:54.080   notice : (main): Log directory: /var/log/maxscale
2019-08-05 12:25:54.081   notice : (main): Data directory: /var/lib/maxscale
2019-08-05 12:25:54.081   notice : (main): Module directory: /usr/lib/x86_64-linux-gnu/maxscale
2019-08-05 12:25:54.081   notice : (main): Service cache: /var/cache/maxscale
2019-08-05 12:25:54.082   notice : (load_module): Loaded module qc_sqlite: V1.0.0 from /usr/lib/x86_64-linux-gnu/maxscale/libqc_sqlite.so
2019-08-05 12:25:54.082   notice : (qc_setup): Query classification results are cached and reused. Memory used per thread: 976.56KiB
2019-08-05 12:25:54.083   notice : (init): The systemd watchdog is Enabled. Internal timeout = 30s
2019-08-05 12:25:54.083   notice : (config_load_single_file): Loading /etc/maxscale.cnf.
2019-08-05 12:25:54.084   notice : (is_directory): /etc/maxscale.cnf.d does not exist, not reading.
2019-08-05 12:25:54.084   notice : (mxs_get_module_object): Initializing statement-based read/write split router module.
2019-08-05 12:25:54.085   notice : (load_module): Loaded module readwritesplit: V1.1.0 from /usr/lib/x86_64-linux-gnu/maxscale/libreadwritesplit.so
2019-08-05 12:25:54.085   notice : (qc_sqlite_process_init): Statements that cannot be parsed completely are logged.
2019-08-05 12:25:54.086   notice : (service_launch_all): Starting a total of 1 services...
2019-08-05 12:25:54.086   warning: (serviceStartAllPorts): Service 'rwsplit-service' has no listeners defined.
2019-08-05 12:25:54.086   notice : (service_launch_all): Service 'rwsplit-service' started (1/1)
2019-08-05 12:25:54.086   notice : (main): Started REST API on []:8989
2019-08-05 12:25:54.086   notice : (main): MaxScale started with 1 worker threads, each with a stack size of 8388608 bytes.
2019-08-05 12:25:54.090   notice : (hkthread): Housekeeper thread started.

Now you have MaxScale up and running! It’s time for testing the maxusr and maxmon connectivity with backends:

#: service user access test
root@prod-maxscale01:~# mysqladmin -u maxusr -p123 -h ping
mysqld is alive
root@prod-maxscale01:~# mysqladmin -u maxusr -p123 -h ping
mysqld is alive
root@prod-maxscale01:~# mysqladmin -u maxusr -p123 -h ping
mysqld is alive

#: monitor user access test
root@prod-maxscale01:~# mysqladmin -u maxmon -p321 -h ping
mysqld is alive
root@prod-maxscale01:~# mysqladmin -u maxmon -p321 -h ping
mysqld is alive
root@prod-maxscale01:~# mysqladmin -u maxmon -p321 -h ping
mysqld is alive

A final test to make sure everything is really set is to test is you can access all databases from all databases. I don’t wanna go over the 9! test here, but, it’s good you go over it and make sure the replication user can access all from all, as replication will be set by MaxScale and you don’t want to have access denied reported by the IO_THREAD for a new slave configured by MaxScale.

At this point, we have MaxScale running with a basic configuration file and also, basic settings for the ReadWriteSplit Router, which is the only service we have running on MaxScale currently. You can notice that we have configurations on the global [maxscale] section for making it possible to remote access MaxScale using the MaxCtrl. Here I’m not considering MaxAdmin as it’s deprecated on MaxScale 2.3 and will be removed on MaxScale 2.4; this latter, currently in beta.

Currently, you can use MaxCtrl to retrieve basic information like below:

#: maxscale global configurations
root@prod-maxscale01:~# maxctrl show maxscale
│ Version      │ 2.3.11                                                               │
│ Commit       │ 36355922281a6820de63b76fb76c9203861e3988                             │
│ Started At   │ Mon, 05 Aug 2019 12:25:54 GMT                                        │
│ Activated At │ Mon, 05 Aug 2019 12:25:54 GMT                                        │
│ Uptime       │ 17863                                                                │
│ Parameters   │ {                                                                    │
│              │     "libdir": "/usr/lib/x86_64-linux-gnu/maxscale",                  │
│              │     "datadir": "/var/lib/maxscale",                                  │
│              │     "process_datadir": "/var/lib/maxscale/data13166",                │
│              │     "admin_auth": true,                                              │
│              │     "admin_enabled": true,                                           │
│              │     "admin_log_auth_failures": true,                                 │
│              │     "admin_host": "",                                         │
│              │     "admin_port": 8989,                                              │
│              │     "admin_ssl_key": "",                                             │
│              │     "admin_ssl_cert": "",                                            │
│              │     "admin_ssl_ca_cert": "",                                         │
│              │     "passive": false,                                                │
│              │     "load_persisted_configs": true                                   │
│              │ }                                                                    │

There are lots os commands to retrieve information from MaxScale using the MaxCtrl in communication to the REST API, running now listening on MaxScale host on any interface, as per the `admin_host` variable and on port 8989, as the what is defined at `admin_port`. As we tested the communication with the MaxScale, we can use now MaxCtrl to create the needed objects like a monitor and a listener, add the servers and link them to the monitor and the service. Since MaxScale 2.1 we have a way to do this with Dynamic Commands; the results of the commands will be to create objects and persist them in files created at the –persistdir. Additionally, a journal file will be also kept at the MaxScale –datadir so it can keep track of the current status of the backends in case the maxscale.service is restarted.

Let’s create the cluster, follow the below commands so we can do that:

#: ReadWriteSplit setup Using Dynamic Commands
#: Created by Wagner Bianchi <bianchi@mariadb.com>
#: task: creating the monitor
maxctrl create monitor replication-monitor mariadbmon --monitor-user=maxmon --monitor-password=AFB909850E7181E9906159CE45176FAD replication_user=mariadb replication_password=A0FE98035CFA5EB978337B739E949878

#: task: configuring the monitor for the replication cluster
maxctrl alter monitor replication-monitor monitor_interval          1000 
maxctrl alter monitor replication-monitor failcount                 3 
maxctrl alter monitor replication-monitor auto_failover             true 
maxctrl alter monitor replication-monitor auto_rejoin               true
maxctrl alter monitor replication-monitor enforce_read_only_slaves  true

#: task: create a listener
maxctrl create listener rwsplit-service replication-rwsplit-listener 3306

#: task: create servers
maxctrl create server prod_mariadb01  3306
maxctrl create server prod_mariadb02 3306
maxctrl create server prod_mariadb03  3306

#: task: link servers with the service
maxctrl link service rwsplit-service prod_mariadb01
maxctrl link service rwsplit-service prod_mariadb02
maxctrl link service rwsplit-service prod_mariadb03

#: task: link servers with the monitor
maxctrl link monitor replication-monitor prod_mariadb01
maxctrl link monitor replication-monitor prod_mariadb02
maxctrl link monitor replication-monitor prod_mariadb03

If you executed the above commands while tailing the maxscale.log, you would see many interesting things. At this point, it’s ready to go:

root@prod-maxscale01:~# maxctrl list servers
│ Server         │ Address       │ Port │ Connections │ State           │ GTID         │
│ prod_mariadb03 │  │ 3306 │ 0           │ Slave, Running  │ 0-1-3        │
│ prod_mariadb02 │ │ 3306 │ 0           │ Slave, Running  │ 0-1-3        │
│ prod_mariadb01 │  │ 3306 │ 0           │ Master, Running │ 0-1-3        │

To conclude this blog post, we can do a final test, a sysbench on maxscale server:

root@prod-maxscale01:~# mysql -u bianchi -p123 -h -e "create database maxscaledb" -vvv
create database maxscaledb

Query OK, 1 row affected (0.002 sec)


root@prod-maxscale01:~# sysbench --test=/usr/share/sysbench/oltp_read_write.lua --table_size=10000 --mysql-db=maxscaledb --tables=20 --mysql-user=bianchi --mysql-password=123 --mysql-port=3306 --mysql-host= --db-driver=mysql --threads=32 --events=0 --time=60 --rand-type=uniform --report-interval=1 prepare
sysbench 1.0.17 (using bundled LuaJIT 2.1.0-beta2)

Initializing worker threads...

Creating table 'sbtest15'...
Creating table 'sbtest17'...
Creating table 'sbtest16'...
Creating table 'sbtest14'...
Creating a secondary index on 'sbtest19'...
Creating a secondary index on 'sbtest20'...
Creating a secondary index on 'sbtest16'...
Creating a secondary index on 'sbtest11'...
Creating a secondary index on 'sbtest14'...

root@prod-maxscale01:~# sysbench --test=/usr/share/sysbench/oltp_read_write.lua --table_size=10000 --mysql-db=maxscaledb --tables=20 --mysql-user=bianchi --mysql-password=123 --mysql-port=3306 --mysql-host= --db-driver=mysql --threads=32 --events=0 --time=60 --rand-type=uniform --report-interval=1 run &
[1] 15656

root@prod-maxscale01:~# maxctrl list servers
│ Server         │ Address       │ Port │ Connections │ State           │ GTID     │
│ prod_mariadb03 │  │ 3306 │ 32          │ Slave, Running  │ 0-1-8144 │
│ prod_mariadb01 │  │ 3306 │ 32          │ Master, Running │ 0-1-8144 │
│ prod_mariadb02 │ │ 3306 │ 32          │ Slave, Running  │ 0-1-8144 │

Next blog, I will carry on with this same environment as per what was defined here, I will test failover, switchover, and rejoin.

You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

AddThis Social Bookmark Button

Leave a Reply