Red Hat Enterprise Linux Server release 7.7
MariaDB 10.4.25
MaxScale 22.08.2

Below is the 'show grants' result for 'maxscale'@'%'

> show grants for 'maxscale'@'%';
------------------------------------------------------------------------------------------------------------------

------------------------------------------------------------------------------------------------------------------

------------------------------------------------------------------------------------------------------------------
8 rows in set (0.000 sec)

The MaxScale log in /var/log/maxscale/maxscale.log will explain why the servers weren't reachable. Are there any errors there that relate to this problem?

Here below is the content of maxscale.log. It showed the last maxscale startup log.

MariaDB MaxScale /var/log/maxscale/maxscale.log Mon Nov 21 16:30:46 2022
----------------------------------------------------------------------------
2022-11-21 16:30:46 notice : The systemd watchdog is Enabled. Internal timeout = 30s
2022-11-21 16:30:46 notice : Using up to 426.88MiB of memory for query classifier cache
2022-11-21 16:30:46 notice : syslog logging is disabled.
2022-11-21 16:30:46 notice : maxlog logging is enabled.
2022-11-21 16:30:46 notice : Host: 'ekycdb1' OS: Linux@3.10.0-1062.el7.x86_64, #1 SMP Thu Jul 18 20:25:13 UTC 2019, x86_64 with 2 processor cores (2.00 available).
2022-11-21 16:30:46 notice : Total main memory: 2.78GiB (2.78GiB usable).
2022-11-21 16:30:46 notice : MariaDB MaxScale 22.08.2 started (Commit: d6662b78821027046876bedd0d2b687908fef091)
2022-11-21 16:30:46 notice : MaxScale is running in process 7737
2022-11-21 16:30:46 notice : Configuration file: /etc/maxscale.cnf
2022-11-21 16:30:46 notice : Log directory: /var/log/maxscale
2022-11-21 16:30:46 notice : Data directory: /var/lib/maxscale
2022-11-21 16:30:46 notice : Module directory: /usr/lib64/maxscale
2022-11-21 16:30:46 notice : Service cache: /var/cache/maxscale
2022-11-21 16:30:46 notice : Working directory: /var/log/maxscale
2022-11-21 16:30:46 notice : Module 'qc_sqlite' loaded from '/usr/lib64/maxscale/libqc_sqlite.so'.
2022-11-21 16:30:46 notice : Query classification results are cached and reused. Memory used per thread: 213.44MiB
2022-11-21 16:30:46 notice : Password encryption key file '/var/lib/maxscale/.secrets' not found, using configured passwords as plaintext.
2022-11-21 16:30:46 notice : MaxScale started with 2 worker threads.
2022-11-21 16:30:46 notice : Module 'galeramon' loaded from '/usr/lib64/maxscale/libgaleramon.so'.
2022-11-21 16:30:46 notice : Module 'readwritesplit' loaded from '/usr/lib64/maxscale/libreadwritesplit.so'.
2022-11-21 16:30:46 notice : Using HS256 for JWT signatures
2022-11-21 16:30:46 warning: The MaxScale GUI is enabled but encryption for the REST API is not enabled, the GUI will not be enabled. Configure `admin_ssl_key` and `admin_ssl_cert` to enable HTTPS or add `admin_secure_gui=false` to allow use of the GUI without encryption.
2022-11-21 16:30:46 notice : Started REST API on [127.0.0.1]:8989
2022-11-21 16:30:46 error : [MariaDB-Monitor] Failed to connect to server 'server1' ([10.0.2.14]:33063) when checking monitor user credentials and permissions.
2022-11-21 16:30:46 error : [MariaDB-Monitor] Failed to connect to server 'server2' ([10.0.2.15]:33063) when checking monitor user credentials and permissions.
2022-11-21 16:30:46 error : [MariaDB-Monitor] Failed to connect to server 'server3' ([10.0.2.16]:33063) when checking monitor user credentials and permissions.
2022-11-21 16:30:46 error : Failed to start monitor. See earlier errors for more information.
2022-11-21 16:30:46 error : Failed to start monitor 'MariaDB-Monitor'.
2022-11-21 16:30:46 notice : Starting a total of 1 services...
2022-11-21 16:30:46 notice : (Read-Write-Listener); Listening for connections at [0.0.0.0]:4006
2022-11-21 16:30:46 notice : Service 'Read-Write-Service' started (1/1)

Here below are the connection testing results. The network interconnections among the nodes are fine.

Performed on node1 (10.0.2.14)
---------------------------------

1. nc -z -v 10.0.2.14 33063
   Ncat: Version 7.50 ( https://nmap.org/ncat )
   Ncat: Connected to 10.0.2.14:33063.
   Ncat: 0 bytes sent, 0 bytes received in 0.02 seconds.

1. nc -z -v 10.0.2.15 33063
   Ncat: Version 7.50 ( https://nmap.org/ncat )
   Ncat: Connected to 10.0.2.15:33063.
   Ncat: 0 bytes sent, 0 bytes received in 0.02 seconds.

1. nc -z -v 10.0.2.16 33063
   Ncat: Version 7.50 ( https://nmap.org/ncat )
   Ncat: Connected to 10.0.2.16:33063.
   Ncat: 0 bytes sent, 0 bytes received in 0.02 seconds.

Performed on node2 (10.0.2.15)
---------------------------------

1. nc -z -v 10.0.2.14 33063
   Ncat: Version 7.50 ( https://nmap.org/ncat )
   Ncat: Connected to 10.0.2.14:33063.
   Ncat: 0 bytes sent, 0 bytes received in 0.02 seconds.

1. nc -z -v 10.0.2.15 33063
   Ncat: Version 7.50 ( https://nmap.org/ncat )
   Ncat: Connected to 10.0.2.15:33063.
   Ncat: 0 bytes sent, 0 bytes received in 0.02 seconds.

1. nc -z -v 10.0.2.16 33063
   Ncat: Version 7.50 ( https://nmap.org/ncat )
   Ncat: Connected to 10.0.2.16:33063.
   Ncat: 0 bytes sent, 0 bytes received in 0.02 seconds.

Performed on node3 (10.0.2.16)
---------------------------------

1. nc -z -v 10.0.2.14 33063
   Ncat: Version 7.50 ( https://nmap.org/ncat )
   Ncat: Connected to 10.0.2.14:33063.
   Ncat: 0 bytes sent, 0 bytes received in 0.02 seconds.

1. nc -z -v 10.0.2.15 33063
   Ncat: Version 7.50 ( https://nmap.org/ncat )
   Ncat: Connected to 10.0.2.15:33063.
   Ncat: 0 bytes sent, 0 bytes received in 0.02 seconds.

1. nc -z -v 10.0.2.16 33063
   Ncat: Version 7.50 ( https://nmap.org/ncat )
   Ncat: Connected to 10.0.2.16:33063.
   Ncat: 0 bytes sent, 0 bytes received in 0.02 seconds.

The log complained 'monitor user credentials and permissions' having problem. I have already executed the following statement on all the nodes.

CREATE USER 'monitor'@'%' IDENTIFIED BY 'abc123';

And now I executed the following statement once again on all the nodes.

ALTER USER 'monitor'@'%' IDENTIFIED BY 'abc123';

Then I stopped maxscale and started it again. But the problem still persists on node 1 alone.

Below are maxscale.log on all the nodes:

node1
-------
2022-11-22 09:30:43 notice : Started REST API on [127.0.0.1]:8989
2022-11-22 09:30:43 error : [MariaDB-Monitor] Failed to connect to server 'server1' ([10.0.2.14]:33063) when checking monitor user credentials and permissions.
2022-11-22 09:30:43 error : [MariaDB-Monitor] Failed to connect to server 'server2' ([10.0.2.15]:33063) when checking monitor user credentials and permissions.
2022-11-22 09:30:43 error : [MariaDB-Monitor] Failed to connect to server 'server3' ([10.0.2.16]:33063) when checking monitor user credentials and permissions.
2022-11-22 09:30:43 error : Failed to start monitor. See earlier errors for more information.
2022-11-22 09:30:43 error : Failed to start monitor 'MariaDB-Monitor'.

node 2
--------
2022-11-22 09:30:49 notice : Started REST API on [127.0.0.1]:8989
2022-11-22 09:30:49 notice : 'server1' sent version string '10.4.25-MariaDB'. Detected type: 'MariaDB', version: 10.4.25.
2022-11-22 09:30:49 notice : Server 'server1' charset: utf8
2022-11-22 09:30:49 notice : 'server2' sent version string '10.4.25-MariaDB'. Detected type: 'MariaDB', version: 10.4.25.
2022-11-22 09:30:49 notice : Server 'server2' charset: utf8
2022-11-22 09:30:49 notice : 'server3' sent version string '10.4.25-MariaDB'. Detected type: 'MariaDB', version: 10.4.25.
2022-11-22 09:30:49 notice : Server 'server3' charset: utf8
2022-11-22 09:30:49 notice : [galeramon] Found cluster members

node 3
--------
2022-11-22 09:30:54 notice : Started REST API on [127.0.0.1]:8989
2022-11-22 09:30:54 notice : 'server1' sent version string '10.4.25-MariaDB'. Detected type: 'MariaDB', version: 10.4.25.
2022-11-22 09:30:54 notice : Server 'server1' charset: utf8
2022-11-22 09:30:54 notice : 'server2' sent version string '10.4.25-MariaDB'. Detected type: 'MariaDB', version: 10.4.25.
2022-11-22 09:30:54 notice : Server 'server2' charset: utf8
2022-11-22 09:30:54 notice : 'server3' sent version string '10.4.25-MariaDB'. Detected type: 'MariaDB', version: 10.4.25.
2022-11-22 09:30:54 notice : Server 'server3' charset: utf8
2022-11-22 09:30:54 notice : [galeramon] Found cluster members

Inside /etc/maxscale.cnf, the following section is the same among all nodes:

[MariaDB-Monitor]
type=monitor
#module=mariadbmon
module=galeramon
servers=server1, server2, server3
user=monitor
password=abc123
monitor_interval=2s

Here below is the user 'monitor' record on mysql.user table:

node1
-------
MariaDB [mysql]> select user,host,password from user where user = 'monitor';
------------------------------------------------------

------------------------------------------------------

------------------------------------------------------
1 row in set (0.001 sec)

node2
-------
MariaDB [mysql]> select user,host,password from user where user = 'monitor';
------------------------------------------------------

------------------------------------------------------

------------------------------------------------------
1 row in set (0.001 sec)

node3
-------
MariaDB [mysql]> select user,host,password from user where user = 'monitor';
------------------------------------------------------

------------------------------------------------------

------------------------------------------------------
1 row in set (0.002 sec)

It seems the passwords are the same among all the nodes for user 'monitor'.

Here attached is an archive (maxscale logs.zip) containing all maxscale.log from all nodes.
maxscale logs.zip

Is the output of SHOW GRANTS the same for all of those users? Looking at the code shows that it might be related to the permissions of that user.

Here are the SHOW GRANTS results on user 'monitor':

node1
-------
> show grants for monitor;
--------------------------------------------------------------------------------------------------------

--------------------------------------------------------------------------------------------------------

--------------------------------------------------------------------------------------------------------
1 row in set (0.000 sec)

node2
-------
> show grants for monitor;
--------------------------------------------------------------------------------------------------------

--------------------------------------------------------------------------------------------------------

--------------------------------------------------------------------------------------------------------
1 row in set (0.000 sec)

node3
-------
> show grants for monitor;
--------------------------------------------------------------------------------------------------------

--------------------------------------------------------------------------------------------------------

--------------------------------------------------------------------------------------------------------
1 row in set (0.000 sec)

The result of SHOW GRANTS:

GRANT USAGE ON . TO ......

It should be "one asterisk + a dot + one asterisk" after 'ON' keyword. I don't know why after copy and paste, "one asterisk + a dot + one asterisk" becomes "space + one dot + space".

Are the configurations on all the MaxScale servers identical? Also check if there are any files in /var/lib/maxscale/maxscale.cnf.d/ that might have different values than the ones on the other nodes.

What I could see under /var/lib/maxscale are those following files on all the nodes:

drwxr-xr-x 2 maxscale maxscale 6 Nov 22 09:30 data17278
rw-rr- 1 maxscale maxscale 353 Nov 21 15:54 MariaDB-Monitor_journal.json
drwxr-xr-x 2 maxscale maxscale 6 Nov 18 16:17 maxscale.cnf.d
-rwxr-xr-x 1 maxscale maxscale 5 Nov 22 09:30 maxscale.lock
rw------ 1 maxscale maxscale 143 Nov 18 16:17 passwd

Inside the path, /var/lib/maxscale/maxscale.cnf.d, there is no file on all the nodes.

Content of passwd on node 1
------------------------------
[

]

Content of passwd on node 2
------------------------------
[

]

Content of passwd on node 3
------------------------------
[

]

For the file, MariaDB-Monitor_journal.json, they have different values:

node 1
-------
{
"module": "galeramon",
"maxscale_version": 220802,
"timestamp": 1669017245,
"servers": [

,

,

]
}

node 2
--------
{
"module": "galeramon",
"maxscale_version": 220802,
"timestamp": 1669103269,
"servers": [

,

,

]
}

node 3
-------
{
"module": "galeramon",
"maxscale_version": 220802,
"timestamp": 1669103271,
"servers": [

,

,

]
}

The file, MariaDB-Monitor_journal.json, was updated by system only, not by us?

Yes, that file is automatically generated by MaxScale.

I have copied /etc/maxscale.cnf from node 2 to node 1, overwritten the original maxscale.cnf on node 1.
Then I restarted maxscale process on all the nodes.
Still the same result on node 1....

2022-11-22 16:00:46 notice : Started REST API on [127.0.0.1]:8989
2022-11-22 16:00:46 error : [MariaDB-Monitor] Failed to connect to server 'server1' ([10.0.2.14]:33063) when checking monitor user credentials and permissions.
2022-11-22 16:00:46 error : [MariaDB-Monitor] Failed to connect to server 'server2' ([10.0.2.15]:33063) when checking monitor user credentials and permissions.
2022-11-22 16:00:46 error : [MariaDB-Monitor] Failed to connect to server 'server3' ([10.0.2.16]:33063) when checking monitor user credentials and permissions.
2022-11-22 16:00:46 error : Failed to start monitor. See earlier errors for more information.
2022-11-22 16:00:46 error : Failed to start monitor 'MariaDB-Monitor'.
2022-11-22 16:00:46 notice : Starting a total of 1 services...

And you can connect with the mysql command line client from that MaxScale node to the database using the monitor credentials?

Current status....

node 1
--------

1. maxctrl list servers
   ┌─────────┬───────────┬───────┬─────────────┬───────┬──────┬─────────────────┐
   │ Server │ Address │ Port │ Connections │ State │ GTID │ Monitor │
   ├─────────┼───────────┼───────┼─────────────┼───────┼──────┼─────────────────┤
   │ server1 │ 10.0.2.14 │ 33063 │ 0 │ Down │ │ MariaDB-Monitor │
   ├─────────┼───────────┼───────┼─────────────┼───────┼──────┼─────────────────┤
   │ server2 │ 10.0.2.15 │ 33063 │ 0 │ Down │ │ MariaDB-Monitor │
   ├─────────┼───────────┼───────┼─────────────┼───────┼──────┼─────────────────┤
   │ server3 │ 10.0.2.16 │ 33063 │ 0 │ Down │ │ MariaDB-Monitor │
   └─────────┴───────────┴───────┴─────────────┴───────┴──────┴─────────────────┘

node 2
--------
maxctrl list servers
┌─────────┬───────────┬───────┬─────────────┬─────────────────────────┬──────┬─────────────────┐
│ Server │ Address │ Port │ Connections │ State │ GTID │ Monitor │
├─────────┼───────────┼───────┼─────────────┼─────────────────────────┼──────┼─────────────────┤
│ server1 │ 10.0.2.14 │ 33063 │ 0 │ Slave, Synced, Running │ │ MariaDB-Monitor │
├─────────┼───────────┼───────┼─────────────┼─────────────────────────┼──────┼─────────────────┤
│ server2 │ 10.0.2.15 │ 33063 │ 0 │ Slave, Synced, Running │ │ MariaDB-Monitor │
├─────────┼───────────┼───────┼─────────────┼─────────────────────────┼──────┼─────────────────┤
│ server3 │ 10.0.2.16 │ 33063 │ 0 │ Master, Synced, Running │ │ MariaDB-Monitor │
└─────────┴───────────┴───────┴─────────────┴─────────────────────────┴──────┴─────────────────┘

node 3
--------
maxctrl list servers
┌─────────┬───────────┬───────┬─────────────┬─────────────────────────┬──────┬─────────────────┐
│ Server │ Address │ Port │ Connections │ State │ GTID │ Monitor │
├─────────┼───────────┼───────┼─────────────┼─────────────────────────┼──────┼─────────────────┤
│ server1 │ 10.0.2.14 │ 33063 │ 0 │ Slave, Synced, Running │ │ MariaDB-Monitor │
├─────────┼───────────┼───────┼─────────────┼─────────────────────────┼──────┼─────────────────┤
│ server2 │ 10.0.2.15 │ 33063 │ 0 │ Slave, Synced, Running │ │ MariaDB-Monitor │
├─────────┼───────────┼───────┼─────────────┼─────────────────────────┼──────┼─────────────────┤
│ server3 │ 10.0.2.16 │ 33063 │ 0 │ Master, Synced, Running │ │ MariaDB-Monitor │
└─────────┴───────────┴───────┴─────────────┴─────────────────────────┴──────┴─────────────────┘

Database clustering status:

node 1
--------
> show status like '%cluster%';
--------------------------------------------------------------------------------------------+

--------------------------------------------------------------------------------------------+

--------------------------------------------------------------------------------------------+
8 rows in set (0.001 sec)

> show status like '%comment%';
---------------------------------+

---------------------------------+

---------------------------------+
1 row in set (0.001 sec)

node 2
--------
> show status like '%cluster%';
--------------------------------------------------------------------------------------------+

--------------------------------------------------------------------------------------------+

--------------------------------------------------------------------------------------------+
8 rows in set (0.001 sec)

> show status like '%comment%';
---------------------------------+

---------------------------------+

---------------------------------+
1 row in set (0.001 sec)

node 3
--------
> show status like '%cluster%';
--------------------------------------------------------------------------------------------+

--------------------------------------------------------------------------------------------+

--------------------------------------------------------------------------------------------+
8 rows in set (0.001 sec)

> show status like '%comment%';
---------------------------------+

---------------------------------+

---------------------------------+
1 row in set (0.001 sec)

I could not login mysql as 'monitor' on all the nodes.

node 1
-------
mysql -umonitor -p
Enter password: abc123
ERROR 1045 (28000): Access denied for user 'monitor'@'localhost' (using password: YES)

node 2
-------
mysql -umonitor -p
Enter password: abc123
ERROR 1045 (28000): Access denied for user 'monitor'@'localhost' (using password: YES)

node 3
-------
mysql -umonitor -p
Enter password: abc123
ERROR 1045 (28000): Access denied for user 'monitor'@'localhost' (using password: YES)

BUT if I did not request password login (i.e. without '-p' parameter), I COULD successfully logon all the nodes!

mysql -umonitor
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 37
Server version: 10.4.25-MariaDB MariaDB 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)]>

This is the user table content:

node 1
-------
> select user,host,password from user;
---------------------------------------------------------------

---------------------------------------------------------------

---------------------------------------------------------------
7 rows in set (0.001 sec)

node 2
-------
> select user,host,password from user;
---------------------------------------------------------------

---------------------------------------------------------------

---------------------------------------------------------------
7 rows in set (0.001 sec)

node 3
-------
> select user,host,password from user;
---------------------------------------------------------------

---------------------------------------------------------------

---------------------------------------------------------------
7 rows in set (0.001 sec)

I created a new user, monitor@localhost, with the same password, 'abc123'. Then I could logon mysql client as 'monitor' on all the nodes.

mysql -umonitor -p
Enter password: abc123
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 43
Server version: 10.4.25-MariaDB MariaDB 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)]>

> select user,host,password from user;
---------------------------------------------------------------

---------------------------------------------------------------

---------------------------------------------------------------
8 rows in set (0.001 sec)

After creating the user, monitor@localhost, I restarted maxscale on all the nodes. But still failed on node 1:

2022-11-22 16:43:18 notice : Started REST API on [127.0.0.1]:8989
2022-11-22 16:43:18 error : [MariaDB-Monitor] Failed to connect to server 'server1' ([10.0.2.14]:33063) when checking monitor user credentials and permissions.
2022-11-22 16:43:18 error : [MariaDB-Monitor] Failed to connect to server 'server2' ([10.0.2.15]:33063) when checking monitor user credentials and permissions.
2022-11-22 16:43:18 error : [MariaDB-Monitor] Failed to connect to server 'server3' ([10.0.2.16]:33063) when checking monitor user credentials and permissions.
2022-11-22 16:43:18 error : Failed to start monitor. See earlier errors for more information.
2022-11-22 16:43:18 error : Failed to start monitor 'MariaDB-Monitor'.
2022-11-22 16:43:18 notice : Starting a total of 1 services...
2022-11-22 16:43:18 notice : (Read-Write-Listener); Listening for connections at [0.0.0.0]:4006
2022-11-22 16:43:18 notice : Service 'Read-Write-Service' started (1/1)

Maxscale has no issue on node 2 and 3.

I also found there was the directory, /etc/maxscale.cnf.d, on all the nodes. But no files exist.

Since the same configuration works on the other MaxScales, it must be something on the one MaxScale node where the problems occur.

I planned to reinstall maxscale software on all the nodes.

I have stopped maxscale process and deleted the following paths and files:

/var/lib/maxscale
/etc/maxscale.*
/var/log/maxscale

I dropped the db users including 'maxscale' and 'monitor'. Here below is the user table query result:

> select user,host from user;
----------------------+

----------------------+

----------------------+
5 rows in set (0.001 sec)

I uninstalled maxscale software on all the nodes.
I will install maxscale again. Inform you the result later.

Install MaxScale on all nodes:

> rpm -Uvh maxscale-22.08.2-1.rhel.7.x86_64.rpm
Preparing... ################################# [100%]
Updating / installing...
1:maxscale-22.08.2-1.rhel.7 ################################# [100%]
Created symlink from /etc/systemd/system/multi-user.target.wants/maxscale.service to /usr/lib/systemd/system/maxscale.service.

> rpm -qa|grep max
maxscale-22.08.2-1.rhel.7.x86_64

After installation, I executed the following command...

systemctl disable maxscale

and query the maxscale software status:

systemctl status maxscale
● maxscale.service - MariaDB MaxScale Database Proxy
Loaded: loaded (/usr/lib/systemd/system/maxscale.service; disabled; vendor preset: disabled)
Active: inactive (dead)

Nov 22 16:00:46 ekycdb1 maxscale[21230]: Using up to 426.88MiB of memory for query classifier cache
Nov 22 16:00:46 ekycdb1 systemd[1]: Started MariaDB MaxScale Database Proxy.
Nov 22 16:42:53 ekycdb1 systemd[1]: Stopping MariaDB MaxScale Database Proxy...
Nov 22 16:42:53 ekycdb1 systemd[1]: Stopped MariaDB MaxScale Database Proxy.
Nov 22 16:43:18 ekycdb1 systemd[1]: Starting MariaDB MaxScale Database Proxy...
Nov 22 16:43:18 ekycdb1 maxscale[21839]: The systemd watchdog is Enabled. Internal timeout = 30s
Nov 22 16:43:18 ekycdb1 maxscale[21839]: Using up to 426.88MiB of memory for query classifier cache
Nov 22 16:43:18 ekycdb1 systemd[1]: Started MariaDB MaxScale Database Proxy.
Nov 23 09:44:24 ekycdb1 systemd[1]: Stopping MariaDB MaxScale Database Proxy...
Nov 23 09:44:24 ekycdb1 systemd[1]: Stopped MariaDB MaxScale Database Proxy.

Question:
(1) Why could it show maxscale status from yesterday even though I uninstalled it this morning?
(2) I just reinstalled maxscale. After installation, why the maxscale status showed 'Stopping MariaDB MaxScale Database Proxy...' and 'Stopped MariaDB MaxScale Database Proxy' messages? Were these two statements triggered by 'systemctl disable maxscale'?

Now...
there is no file under /var/lib/maxscale
there is no file under /etc/maxscale.cnf.d
there is no file under /etc/maxscale.modules.d
there is no file under /var/log/maxscale

I will start configuring maxscale on all the nodes by manipulating /etc/maxscale.cnf and adding db users.

Create db user, maxscale, on node 1

> CREATE USER 'maxscale'@'%' IDENTIFIED BY 'abc123';
Query OK, 0 rows affected (0.019 sec)

> GRANT SELECT ON mysql.user TO 'maxscale'@'%';
Query OK, 0 rows affected (0.026 sec)

> GRANT SELECT ON mysql.db TO 'maxscale'@'%';
Query OK, 0 rows affected (0.020 sec)

> GRANT SELECT ON mysql.tables_priv TO 'maxscale'@'%';
Query OK, 0 rows affected (0.020 sec)

> GRANT SELECT ON mysql.columns_priv TO 'maxscale'@'%';
Query OK, 0 rows affected (0.019 sec)

> GRANT SELECT ON mysql.procs_priv TO 'maxscale'@'%';
Query OK, 0 rows affected (0.019 sec)

> GRANT SELECT ON mysql.proxies_priv TO 'maxscale'@'%';
Query OK, 0 rows affected (0.020 sec)

> GRANT SELECT ON mysql.roles_mapping TO 'maxscale'@'%';
Query OK, 0 rows affected (0.017 sec)

> GRANT SHOW DATABASES ON . TO 'maxscale'@'%';
Query OK, 0 rows affected (0.019 sec)

> SHOW GRANTS for 'maxscale'@'%';
------------------------------------------------------------------------------------------------------------------

------------------------------------------------------------------------------------------------------------------

------------------------------------------------------------------------------------------------------------------
8 rows in set (0.000 sec)

Create db user, monitor, on node 1

> CREATE USER 'monitor'@'%' IDENTIFIED BY 'abc123';
Query OK, 0 rows affected (0.018 sec)

> SHOW GRANTS for 'monitor'@'%';
--------------------------------------------------------------------------------------------------------

--------------------------------------------------------------------------------------------------------

--------------------------------------------------------------------------------------------------------
1 row in set (0.000 sec)

The same query results were shown on node 2 and 3 as follows:

> SHOW GRANTS for 'maxscale'@'%';
------------------------------------------------------------------------------------------------------------------

------------------------------------------------------------------------------------------------------------------

------------------------------------------------------------------------------------------------------------------
8 rows in set (0.000 sec)

> SHOW GRANTS for 'monitor'@'%';
--------------------------------------------------------------------------------------------------------

--------------------------------------------------------------------------------------------------------

--------------------------------------------------------------------------------------------------------
1 row in set (0.000 sec)

Update /etc/maxscale.cnf on node 1 as follows:

1. MaxScale documentation:
2. https://mariadb.com/kb/en/mariadb-maxscale-6/

1. Global parameters
   #
2. Complete list of configuration options:
3. https://mariadb.com/kb/en/mariadb-maxscale-6-mariadb-maxscale-configuration-guide/

[maxscale]
threads=auto

1. Server definitions
   #
2. Set the address of the server to the network
3. address of a MariaDB server.
   #

[server1]
type=server
address=10.0.2.14
port=33063
protocol=MariaDBBackend

[server2]
type=server
address=10.0.2.15
port=33063
protocol=MariaDBBackend

[server3]
type=server
address=10.0.2.16
port=33063
protocol=MariaDBBackend

1. Monitor for the servers
   #
2. This will keep MaxScale aware of the state of the servers.
3. MariaDB Monitor documentation:
4. https://mariadb.com/kb/en/maxscale-6-monitors/
   #
5. Create the monitor user with:
   #
6. CREATE USER 'monitor_user'@'%' IDENTIFIED BY 'monitor_pw';
7. GRANT REPLICATION CLIENT, FILE, SUPER, RELOAD, PROCESS, SHOW DATABASES, EVENT ON . TO 'monitor_user'@'%';
   #

#[MariaDB-Monitor]
[Galera-Monitor]
type=monitor
#module=mariadbmon
module=galeramon
servers=server1,server2,server3
#user=monitor_user
user=monitor
#password=monitor_pw
password=abc123
monitor_interval=2s

1. Service definitions
   #
2. Service Definition for a read-only service and
3. a read/write splitting service.
   #
4. Create the service user with:
   #
5. CREATE USER 'service_user'@'%' IDENTIFIED BY 'service_pw';
6. GRANT SELECT ON mysql.user TO 'service_user'@'%';
7. GRANT SELECT ON mysql.db TO 'service_user'@'%';
8. GRANT SELECT ON mysql.tables_priv TO 'service_user'@'%';
9. GRANT SELECT ON mysql.columns_priv TO 'service_user'@'%';
10. GRANT SELECT ON mysql.procs_priv TO 'service_user'@'%';
11. GRANT SELECT ON mysql.proxies_priv TO 'service_user'@'%';
12. GRANT SELECT ON mysql.roles_mapping TO 'service_user'@'%';
13. GRANT SHOW DATABASES ON . TO 'service_user'@'%';
    #

1. ReadConnRoute documentation:
2. https://mariadb.com/kb/en/mariadb-maxscale-6-readconnroute/

#[Read-Only-Service]
#type=service
#router=readconnroute
#servers=server1
#user=service_user
#password=service_pw
#router_options=slave

1. ReadWriteSplit documentation:
2. https://mariadb.com/kb/en/mariadb-maxscale-6-readwritesplit/

[Read-Write-Service]
type=service
router=readwritesplit
servers=server1,server2,server3
#user=service_user
user=maxscale
#password=service_pw
password=abc123

1. Listener definitions for the services
   #
2. These listeners represent the ports the
3. services will listen on.
   #

#[Read-Only-Listener]
#type=listener
#service=Read-Only-Service
#protocol=MariaDBClient
#port=4008

[Read-Write-Listener]
type=listener
service=Read-Write-Service
protocol=MariaDBClient
address=0.0.0.0
port=4006

I copied /etc/maxscale.cnf from node 1 to node 2 and 3. Then I started maxscale by 'systemctl start maxscale'.
There is one difference on maxscale.cnf. I changed '[MariaDB-Monitor]' to '[Galera-Monitor]'. But on node 1, the 'maxctrl list servers' command still showed server 'Down' status:

node 1
-------
maxctrl list servers
┌─────────┬───────────┬───────┬─────────────┬───────┬──────┬────────────────┐
│ Server │ Address │ Port │ Connections │ State │ GTID │ Monitor │
├─────────┼───────────┼───────┼─────────────┼───────┼──────┼────────────────┤
│ server1 │ 10.0.2.14 │ 33063 │ 0 │ Down │ │ Galera-Monitor │
├─────────┼───────────┼───────┼─────────────┼───────┼──────┼────────────────┤
│ server2 │ 10.0.2.15 │ 33063 │ 0 │ Down │ │ Galera-Monitor │
├─────────┼───────────┼───────┼─────────────┼───────┼──────┼────────────────┤
│ server3 │ 10.0.2.16 │ 33063 │ 0 │ Down │ │ Galera-Monitor │
└─────────┴───────────┴───────┴─────────────┴───────┴──────┴────────────────┘

node 2
-------
maxctrl list servers
┌─────────┬───────────┬───────┬─────────────┬─────────────────────────┬──────┬────────────────┐
│ Server │ Address │ Port │ Connections │ State │ GTID │ Monitor │
├─────────┼───────────┼───────┼─────────────┼─────────────────────────┼──────┼────────────────┤
│ server1 │ 10.0.2.14 │ 33063 │ 0 │ Slave, Synced, Running │ │ Galera-Monitor │
├─────────┼───────────┼───────┼─────────────┼─────────────────────────┼──────┼────────────────┤
│ server2 │ 10.0.2.15 │ 33063 │ 0 │ Slave, Synced, Running │ │ Galera-Monitor │
├─────────┼───────────┼───────┼─────────────┼─────────────────────────┼──────┼────────────────┤
│ server3 │ 10.0.2.16 │ 33063 │ 0 │ Master, Synced, Running │ │ Galera-Monitor │
└─────────┴───────────┴───────┴─────────────┴─────────────────────────┴──────┴────────────────┘

node 3
-------
maxctrl list servers
┌─────────┬───────────┬───────┬─────────────┬─────────────────────────┬──────┬────────────────┐
│ Server │ Address │ Port │ Connections │ State │ GTID │ Monitor │
├─────────┼───────────┼───────┼─────────────┼─────────────────────────┼──────┼────────────────┤
│ server1 │ 10.0.2.14 │ 33063 │ 0 │ Slave, Synced, Running │ │ Galera-Monitor │
├─────────┼───────────┼───────┼─────────────┼─────────────────────────┼──────┼────────────────┤
│ server2 │ 10.0.2.15 │ 33063 │ 0 │ Slave, Synced, Running │ │ Galera-Monitor │
├─────────┼───────────┼───────┼─────────────┼─────────────────────────┼──────┼────────────────┤
│ server3 │ 10.0.2.16 │ 33063 │ 0 │ Master, Synced, Running │ │ Galera-Monitor │
└─────────┴───────────┴───────┴─────────────┴─────────────────────────┴──────┴────────────────┘

Then I executed the following sql statement on node 1:

GRANT REPLICATION CLIENT, FILE, SUPER, RELOAD, PROCESS, SHOW DATABASES, EVENT ON . TO 'monitor'@'%';

On all the nodes, I got the following SHOW GRANTS results:

> SHOW GRANTS for 'monitor'@'%';
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 row in set (0.000 sec)

I also commented the following statements on /etc/maxscale.cnf

[maxscale]
threads=auto

Then I restarted maxscale. But still no luck.

node 1
-------
maxctrl list servers
┌─────────┬───────────┬───────┬─────────────┬───────┬──────┬────────────────┐
│ Server │ Address │ Port │ Connections │ State │ GTID │ Monitor │
├─────────┼───────────┼───────┼─────────────┼───────┼──────┼────────────────┤
│ server1 │ 10.0.2.14 │ 33063 │ 0 │ Down │ │ Galera-Monitor │
├─────────┼───────────┼───────┼─────────────┼───────┼──────┼────────────────┤
│ server2 │ 10.0.2.15 │ 33063 │ 0 │ Down │ │ Galera-Monitor │
├─────────┼───────────┼───────┼─────────────┼───────┼──────┼────────────────┤
│ server3 │ 10.0.2.16 │ 33063 │ 0 │ Down │ │ Galera-Monitor │
└─────────┴───────────┴───────┴─────────────┴───────┴──────┴────────────────┘

node 2
-------
maxctrl list servers
┌─────────┬───────────┬───────┬─────────────┬─────────────────────────┬──────┬────────────────┐
│ Server │ Address │ Port │ Connections │ State │ GTID │ Monitor │
├─────────┼───────────┼───────┼─────────────┼─────────────────────────┼──────┼────────────────┤
│ server1 │ 10.0.2.14 │ 33063 │ 0 │ Slave, Synced, Running │ │ Galera-Monitor │
├─────────┼───────────┼───────┼─────────────┼─────────────────────────┼──────┼────────────────┤
│ server2 │ 10.0.2.15 │ 33063 │ 0 │ Slave, Synced, Running │ │ Galera-Monitor │
├─────────┼───────────┼───────┼─────────────┼─────────────────────────┼──────┼────────────────┤
│ server3 │ 10.0.2.16 │ 33063 │ 0 │ Master, Synced, Running │ │ Galera-Monitor │
└─────────┴───────────┴───────┴─────────────┴─────────────────────────┴──────┴────────────────┘

node 3
-------
maxctrl list servers
┌─────────┬───────────┬───────┬─────────────┬─────────────────────────┬──────┬────────────────┐
│ Server │ Address │ Port │ Connections │ State │ GTID │ Monitor │
├─────────┼───────────┼───────┼─────────────┼─────────────────────────┼──────┼────────────────┤
│ server1 │ 10.0.2.14 │ 33063 │ 0 │ Slave, Synced, Running │ │ Galera-Monitor │
├─────────┼───────────┼───────┼─────────────┼─────────────────────────┼──────┼────────────────┤
│ server2 │ 10.0.2.15 │ 33063 │ 0 │ Slave, Synced, Running │ │ Galera-Monitor │
├─────────┼───────────┼───────┼─────────────┼─────────────────────────┼──────┼────────────────┤
│ server3 │ 10.0.2.16 │ 33063 │ 0 │ Master, Synced, Running │ │ Galera-Monitor │
└─────────┴───────────┴───────┴─────────────┴─────────────────────────┴──────┴────────────────┘

Network connectivity test again on node 1:

> nc -z -v 10.0.2.14 4006
Ncat: Version 7.50 ( https://nmap.org/ncat )
Ncat: Connected to 10.0.2.14:4006.
Ncat: 0 bytes sent, 0 bytes received in 0.02 seconds.

> nc -z -v 10.0.2.15 4006
Ncat: Version 7.50 ( https://nmap.org/ncat )
Ncat: Connected to 10.0.2.15:4006.
Ncat: 0 bytes sent, 0 bytes received in 0.02 seconds.

> nc -z -v 10.0.2.16 4006
Ncat: Version 7.50 ( https://nmap.org/ncat )
Ncat: Connected to 10.0.2.16:4006.
Ncat: 0 bytes sent, 0 bytes received in 0.02 seconds.

> nc -z -v 10.0.2.14 33063
Ncat: Version 7.50 ( https://nmap.org/ncat )
Ncat: Connected to 10.0.2.14:33063.
Ncat: 0 bytes sent, 0 bytes received in 0.02 seconds.

> nc -z -v 10.0.2.15 33063
Ncat: Version 7.50 ( https://nmap.org/ncat )
Ncat: Connected to 10.0.2.15:33063.
Ncat: 0 bytes sent, 0 bytes received in 0.02 seconds.

> nc -z -v 10.0.2.16 33063
Ncat: Version 7.50 ( https://nmap.org/ncat )
Ncat: Connected to 10.0.2.16:33063.
Ncat: 0 bytes sent, 0 bytes received in 0.02 seconds.

Here attached are the maxscale config files and log files.
node1-maxscale-conf.tar.gz node2-maxscale-conf.tar.gz node3-maxscale-conf.tar.gz

Does anything change if you add skip_permission_checks=true under the [maxscale] section? The code where the problem appears to occur can be disabled with this option.

After updating /etc/maxscale.cnf on all the nodes, I restarted maxscale process.

[maxscale]
threads=auto
skip_permission_checks=true

Here below are the results:

node 1
-------
maxctrl list servers
┌─────────┬───────────┬───────┬─────────────┬──────────────────┬──────┬────────────────┐
│ Server │ Address │ Port │ Connections │ State │ GTID │ Monitor │
├─────────┼───────────┼───────┼─────────────┼──────────────────┼──────┼────────────────┤
│ server1 │ 10.0.2.14 │ 33063 │ 0 │ Auth Error, Down │ │ Galera-Monitor │
├─────────┼───────────┼───────┼─────────────┼──────────────────┼──────┼────────────────┤
│ server2 │ 10.0.2.15 │ 33063 │ 0 │ Auth Error, Down │ │ Galera-Monitor │
├─────────┼───────────┼───────┼─────────────┼──────────────────┼──────┼────────────────┤
│ server3 │ 10.0.2.16 │ 33063 │ 0 │ Auth Error, Down │ │ Galera-Monitor │
└─────────┴───────────┴───────┴─────────────┴──────────────────┴──────┴────────────────┘

node 2
-------
maxctrl list servers
┌─────────┬───────────┬───────┬─────────────┬─────────────────────────┬──────┬────────────────┐
│ Server │ Address │ Port │ Connections │ State │ GTID │ Monitor │
├─────────┼───────────┼───────┼─────────────┼─────────────────────────┼──────┼────────────────┤
│ server1 │ 10.0.2.14 │ 33063 │ 0 │ Slave, Synced, Running │ │ Galera-Monitor │
├─────────┼───────────┼───────┼─────────────┼─────────────────────────┼──────┼────────────────┤
│ server2 │ 10.0.2.15 │ 33063 │ 0 │ Slave, Synced, Running │ │ Galera-Monitor │
├─────────┼───────────┼───────┼─────────────┼─────────────────────────┼──────┼────────────────┤
│ server3 │ 10.0.2.16 │ 33063 │ 0 │ Master, Synced, Running │ │ Galera-Monitor │
└─────────┴───────────┴───────┴─────────────┴─────────────────────────┴──────┴────────────────┘

node 3
-------
maxctrl list servers
┌─────────┬───────────┬───────┬─────────────┬─────────────────────────┬──────┬────────────────┐
│ Server │ Address │ Port │ Connections │ State │ GTID │ Monitor │
├─────────┼───────────┼───────┼─────────────┼─────────────────────────┼──────┼────────────────┤
│ server1 │ 10.0.2.14 │ 33063 │ 0 │ Slave, Synced, Running │ │ Galera-Monitor │
├─────────┼───────────┼───────┼─────────────┼─────────────────────────┼──────┼────────────────┤
│ server2 │ 10.0.2.15 │ 33063 │ 0 │ Slave, Synced, Running │ │ Galera-Monitor │
├─────────┼───────────┼───────┼─────────────┼─────────────────────────┼──────┼────────────────┤
│ server3 │ 10.0.2.16 │ 33063 │ 0 │ Master, Synced, Running │ │ Galera-Monitor │
└─────────┴───────────┴───────┴─────────────┴─────────────────────────┴──────┴────────────────┘

The State shows 'Auth Error, Down' on node 1 for all servers.

Here attached is the maxscale.log from node 1.
maxscale.log

Here is cluster status.

node 1
-------
> show status like '%cluster%';
--------------------------------------------------------------------------------------------+

--------------------------------------------------------------------------------------------+

--------------------------------------------------------------------------------------------+
8 rows in set (0.001 sec)

> show status like '%comment%';
---------------------------------+

---------------------------------+

---------------------------------+
1 row in set (0.001 sec)

node 2
-------
> show status like '%cluster%';
--------------------------------------------------------------------------------------------+

--------------------------------------------------------------------------------------------+

--------------------------------------------------------------------------------------------+
8 rows in set (0.001 sec)

> show status like '%comment%';
---------------------------------+

---------------------------------+

---------------------------------+
1 row in set (0.001 sec)

node 3
-------
> show status like '%cluster%';
--------------------------------------------------------------------------------------------+

--------------------------------------------------------------------------------------------+

--------------------------------------------------------------------------------------------+
8 rows in set (0.001 sec)

> show status like '%comment%';
---------------------------------+

---------------------------------+

---------------------------------+
1 row in set (0.001 sec)

OK, there's definitely a bug in the galeramon error reporting for authentication failures. It should've logged a message explaining why the authentication failed. This does however still point towards authentication as the source of this problem. It cannot be a fundamental problem in MaxScale as the other two nodes work while the one node doesn't.

You already have my whole procedure of reinstalling the maxscale and detailed steps on configuring maxscale. What should I do next?

A network trace during the startup on the MaxScale node that's having problems should show what's going on. A Wireshark capture would be enough to see what the server responds with. Optionally, you can add debug=enable-statement-logging under the [maxscale] section to log some of the SQL statements that are executed by MaxScale.

I changed the content of /etc/maxscale.cnf as follows:

[maxscale]
threads=auto
skip_permission_checks=true
debug=enable-statement-logging
log_info=1
log_debug=1

#[MariaDB-Monitor]
[Galera-Monitor]
type=monitor
#module=mariadbmon
module=galeramon
servers=server1,server2,server3
#user=monitor_user
user=monitor
#password=monitor_pw
password=abc123
monitor_interval=2s

I tailed the file 'mariadb.err'. After starting maxscale, I got the following logs:

2022-11-28 15:06:32 700 [Warning] Access denied for user 'maxscale'@'ekycdb1' (using password: YES)
2022-11-28 15:06:33 701 [Warning] Access denied for user 'maxscale'@'ekycdb1' (using password: YES)
2022-11-28 15:06:34 702 [Warning] Access denied for user 'maxscale'@'ekycdb1' (using password: YES)
2022-11-28 15:06:35 703 [Warning] Access denied for user 'maxscale'@'ekycdb1' (using password: YES)
2022-11-28 15:06:36 704 [Warning] Access denied for user 'maxscale'@'ekycdb1' (using password: YES)

Here is the user table content:

> select user,host,password from user;
---------------------------------------------------------------

---------------------------------------------------------------

---------------------------------------------------------------
8 rows in set (0.001 sec)

I executed the following statements to add user, 'monitor'@'ekycdb1':

CREATE USER 'monitor'@'ekycdb1' IDENTIFIED BY 'abc123';
GRANT REPLICATION CLIENT, FILE, SUPER, RELOAD, PROCESS, SHOW DATABASES, EVENT ON . TO 'monitor'@'ekycdb1';

> select user,host,password from user;
---------------------------------------------------------------

---------------------------------------------------------------

---------------------------------------------------------------
8 rows in set (0.001 sec)

Then I stopped and started maxscale again. Still the same result when browsing the mariadb.err file:

2022-11-28 15:11:14 712 [Warning] Access denied for user 'maxscale'@'ekycdb1' (using password: YES)
2022-11-28 15:11:15 713 [Warning] Access denied for user 'maxscale'@'ekycdb1' (using password: YES)
2022-11-28 15:11:16 714 [Warning] Access denied for user 'maxscale'@'ekycdb1' (using password: YES)
2022-11-28 15:11:17 715 [Warning] Access denied for user 'maxscale'@'ekycdb1' (using password: YES)
2022-11-28 15:11:18 716 [Warning] Access denied for user 'maxscale'@'ekycdb1' (using password: YES)
2022-11-28 15:11:19 717 [Warning] Access denied for user 'maxscale'@'ekycdb1' (using password: YES)

I only started the 1st node for testing. Node 2 and 3 did not join the cluster.

MariaDB [mysql]> drop user monitor@ekycdb1;
Query OK, 0 rows affected (0.038 sec)

MariaDB [mysql]> create user 'maxscale'@'ekycdb1' identified by 'abc123';
Query OK, 0 rows affected (0.012 sec)

MariaDB [mysql]> GRANT SELECT ON mysql.user TO 'maxscale'@'ekycdb1';
Query OK, 0 rows affected (0.013 sec)

MariaDB [mysql]> GRANT SELECT ON mysql.db TO 'maxscale'@'ekycdb1';
Query OK, 0 rows affected (0.012 sec)

MariaDB [mysql]> GRANT SELECT ON mysql.tables_priv TO 'maxscale'@'ekycdb1';
Query OK, 0 rows affected (0.013 sec)

MariaDB [mysql]> GRANT SELECT ON mysql.columns_priv TO 'maxscale'@'ekycdb1';
Query OK, 0 rows affected (0.013 sec)

MariaDB [mysql]> GRANT SELECT ON mysql.procs_priv TO 'maxscale'@'ekycdb1';
Query OK, 0 rows affected (0.014 sec)

MariaDB [mysql]> GRANT SELECT ON mysql.proxies_priv TO 'maxscale'@'ekycdb1';
Query OK, 0 rows affected (0.018 sec)

MariaDB [mysql]> GRANT SELECT ON mysql.roles_mapping TO 'maxscale'@'ekycdb1';
Query OK, 0 rows affected (0.013 sec)

MariaDB [mysql]> GRANT SHOW DATABASES ON . TO 'maxscale'@'ekycdb1';
Query OK, 0 rows affected (0.013 sec)

MariaDB [mysql]> SHOW GRANTS for 'maxscale'@'ekycdb1';
------------------------------------------------------------------------------------------------------------------------

------------------------------------------------------------------------------------------------------------------------

------------------------------------------------------------------------------------------------------------------------
8 rows in set (0.000 sec)

After that I started maxscale process.

> systemctl start maxscale

Then the following messages kept showing on mariadb.err:

2022-11-29 9:42:54 13 [Warning] Access denied for user 'monitor'@'ekycdb1' (using password: YES)
2022-11-29 9:43:00 14 [Warning] Access denied for user 'monitor'@'ekycdb1' (using password: YES)
2022-11-29 9:43:06 15 [Warning] Access denied for user 'monitor'@'ekycdb1' (using password: YES)
2022-11-29 9:43:13 16 [Warning] Access denied for user 'monitor'@'ekycdb1' (using password: YES)
2022-11-29 9:43:19 17 [Warning] Access denied for user 'monitor'@'ekycdb1' (using password: YES)

This time, I added back the user account, 'monitor'@'ekycdb1':

MariaDB [mysql]> CREATE USER 'monitor'@'ekycdb1' IDENTIFIED BY 'abc123';
Query OK, 0 rows affected (0.020 sec)

MariaDB [mysql]> select user,host,password from user;
---------------------------------------------------------------

---------------------------------------------------------------

---------------------------------------------------------------
9 rows in set (0.002 sec)

MariaDB [(none)]> show grants for 'monitor'@'ekycdb1';
--------------------------------------------------------------------------------------------------------------

--------------------------------------------------------------------------------------------------------------

--------------------------------------------------------------------------------------------------------------
1 row in set (0.000 sec)

I stopped maxscale and started it again. There is no more 'Access denied for user....' messages displayed on mariadb.err.

I verified the maxscale status by the following commands:

> maxctrl list servers
┌─────────┬───────────┬───────┬─────────────┬─────────────────────────┬──────┬────────────────┐
│ Server │ Address │ Port │ Connections │ State │ GTID │ Monitor │
├─────────┼───────────┼───────┼─────────────┼─────────────────────────┼──────┼────────────────┤
│ server1 │ 10.0.2.14 │ 33063 │ 0 │ Master, Synced, Running │ │ Galera-Monitor │
├─────────┼───────────┼───────┼─────────────┼─────────────────────────┼──────┼────────────────┤
│ server2 │ 10.0.2.15 │ 33063 │ 0 │ Down │ │ Galera-Monitor │
├─────────┼───────────┼───────┼─────────────┼─────────────────────────┼──────┼────────────────┤
│ server3 │ 10.0.2.16 │ 33063 │ 0 │ Down │ │ Galera-Monitor │
└─────────┴───────────┴───────┴─────────────┴─────────────────────────┴──────┴────────────────┘

> maxctrl list services
┌────────────────────┬────────────────┬─────────────┬───────────────────┬───────────────────────────┐
│ Service │ Router │ Connections │ Total Connections │ Targets │
├────────────────────┼────────────────┼─────────────┼───────────────────┼───────────────────────────┤
│ Read-Write-Service │ readwritesplit │ 0 │ 0 │ server1, server2, server3 │
└────────────────────┴────────────────┴─────────────┴───────────────────┴───────────────────────────┘

> maxctrl list listeners Read-Write-Service
┌─────────────────────┬──────┬─────────┬─────────┬────────────────────┐
│ Name │ Port │ Host │ State │ Service │
├─────────────────────┼──────┼─────────┼─────────┼────────────────────┤
│ Read-Write-Listener │ 4006 │ 0.0.0.0 │ Running │ Read-Write-Service │
└─────────────────────┴──────┴─────────┴─────────┴────────────────────┘

I started mariadb and maxscale on both node 2 and 3. Then I queried again on node 1.

> maxctrl list servers;
┌─────────┬───────────┬───────┬─────────────┬─────────────────────────┬──────┬────────────────┐
│ Server │ Address │ Port │ Connections │ State │ GTID │ Monitor │
├─────────┼───────────┼───────┼─────────────┼─────────────────────────┼──────┼────────────────┤
│ server1 │ 10.0.2.14 │ 33063 │ 0 │ Master, Synced, Running │ │ Galera-Monitor │
├─────────┼───────────┼───────┼─────────────┼─────────────────────────┼──────┼────────────────┤
│ server2 │ 10.0.2.15 │ 33063 │ 0 │ Slave, Synced, Running │ │ Galera-Monitor │
├─────────┼───────────┼───────┼─────────────┼─────────────────────────┼──────┼────────────────┤
│ server3 │ 10.0.2.16 │ 33063 │ 0 │ Slave, Synced, Running │ │ Galera-Monitor │
└─────────┴───────────┴───────┴─────────────┴─────────────────────────┴──────┴────────────────┘

It seems the problem was caused by the combination of user and host created. '%' does not work.

I tested adding users 'monitor'@'localhost' and 'maxscale'@'localhost'. 'Access denied' issue happened again. It must be hostname after '@' in order to make maxscale work.

Here are the comment section inside /etc/maxscale.cnf

># Monitor for the servers
>#
># This will keep MaxScale aware of the state of the servers.
># MariaDB Monitor documentation:
># https://mariadb.com/kb/en/maxscale-6-monitors/
>#
># Create the monitor user with:
>#
># CREATE USER 'monitor_user'@'%' IDENTIFIED BY 'monitor_pw';
># GRANT REPLICATION CLIENT, FILE, SUPER, RELOAD, PROCESS, SHOW DATABASES, EVENT ON . TO 'monitor_user'@'%';
>#

># Service definitions
>#
># Service Definition for a read-only service and
># a read/write splitting service.
>#
># Create the service user with:
>#
># CREATE USER 'service_user'@'%' IDENTIFIED BY 'service_pw';
># GRANT SELECT ON mysql.user TO 'service_user'@'%';
># GRANT SELECT ON mysql.db TO 'service_user'@'%';
># GRANT SELECT ON mysql.tables_priv TO 'service_user'@'%';
># GRANT SELECT ON mysql.columns_priv TO 'service_user'@'%';
># GRANT SELECT ON mysql.procs_priv TO 'service_user'@'%';
># GRANT SELECT ON mysql.proxies_priv TO 'service_user'@'%';
># GRANT SELECT ON mysql.roles_mapping TO 'service_user'@'%';
># GRANT SHOW DATABASES ON . TO 'service_user'@'%';
>#

It said creating users as 'monitor_user'@'%' and 'service_user'@'%'. Is it a workable definition? If it is intended to define in this manner, it is a bug. If not, please update your comment sections and documentation.

There is another problem aroused if I defined 'user'@'hostname' alone.

The current user table is as follows:

MariaDB [mysql]> select user,host,password from user;
---------------------------------------------------------------

---------------------------------------------------------------

---------------------------------------------------------------
7 rows in set (0.001 sec)

On node 2 when I started maxscale, there was the following error message shown on mariadb.err:

2022-11-29 10:54:17 95 [Warning] Access denied for user 'monitor'@'ekycdb2' (using password: YES)

On node 3 when I started maxscale, there was the following error message shown on mariadb.err:

2022-11-29 10:54:50 95 [Warning] Access denied for user 'monitor'@'ekycdb3' (using password: YES)

As all the nodes formed a cluster, if I updated the host db field as hostname instead of '%' on any one of the nodes, all host field will have the same hostname (i.e. ekycdb1) which does not correct for other two nodes (ekycdb2, ekycdb3).

Now I added two more users, 'maxscale'@'%' and 'monitor'@'%' with appropriate rights granted.

MariaDB [mysql]> select user,host,password from user;
---------------------------------------------------------------

---------------------------------------------------------------

---------------------------------------------------------------
9 rows in set (0.001 sec)

Then the maxscale query results were all correct on all nodes:

node 1
-------
> maxctrl list servers
┌─────────┬───────────┬───────┬─────────────┬─────────────────────────┬──────┬─────────────────┐
│ Server │ Address │ Port │ Connections │ State │ GTID │ Monitor │
├─────────┼───────────┼───────┼─────────────┼─────────────────────────┼──────┼─────────────────┤
│ server1 │ 10.0.2.14 │ 33063 │ 0 │ Master, Synced, Running │ │ MariaDB-Monitor │
├─────────┼───────────┼───────┼─────────────┼─────────────────────────┼──────┼─────────────────┤
│ server2 │ 10.0.2.15 │ 33063 │ 0 │ Slave, Synced, Running │ │ MariaDB-Monitor │
├─────────┼───────────┼───────┼─────────────┼─────────────────────────┼──────┼─────────────────┤
│ server3 │ 10.0.2.16 │ 33063 │ 0 │ Slave, Synced, Running │ │ MariaDB-Monitor │
└─────────┴───────────┴───────┴─────────────┴─────────────────────────┴──────┴─────────────────┘

node 2
-------
> maxctrl list servers
┌─────────┬───────────┬───────┬─────────────┬─────────────────────────┬──────┬─────────────────┐
│ Server │ Address │ Port │ Connections │ State │ GTID │ Monitor │
├─────────┼───────────┼───────┼─────────────┼─────────────────────────┼──────┼─────────────────┤
│ server1 │ 10.0.2.14 │ 33063 │ 0 │ Master, Synced, Running │ │ MariaDB-Monitor │
├─────────┼───────────┼───────┼─────────────┼─────────────────────────┼──────┼─────────────────┤
│ server2 │ 10.0.2.15 │ 33063 │ 0 │ Slave, Synced, Running │ │ MariaDB-Monitor │
├─────────┼───────────┼───────┼─────────────┼─────────────────────────┼──────┼─────────────────┤
│ server3 │ 10.0.2.16 │ 33063 │ 0 │ Slave, Synced, Running │ │ MariaDB-Monitor │
└─────────┴───────────┴───────┴─────────────┴─────────────────────────┴──────┴─────────────────┘

node 3
-------
> maxctrl list servers
┌─────────┬───────────┬───────┬─────────────┬─────────────────────────┬──────┬─────────────────┐
│ Server │ Address │ Port │ Connections │ State │ GTID │ Monitor │
├─────────┼───────────┼───────┼─────────────┼─────────────────────────┼──────┼─────────────────┤
│ server1 │ 10.0.2.14 │ 33063 │ 0 │ Master, Synced, Running │ │ MariaDB-Monitor │
├─────────┼───────────┼───────┼─────────────┼─────────────────────────┼──────┼─────────────────┤
│ server2 │ 10.0.2.15 │ 33063 │ 0 │ Slave, Synced, Running │ │ MariaDB-Monitor │
├─────────┼───────────┼───────┼─────────────┼─────────────────────────┼──────┼─────────────────┤
│ server3 │ 10.0.2.16 │ 33063 │ 0 │ Slave, Synced, Running │ │ MariaDB-Monitor │
└─────────┴───────────┴───────┴─────────────┴─────────────────────────┴──────┴─────────────────┘

The conclusion is ... I must defined two sets of users for different hosts ('%' and hostname) to make maxscale work:

'maxscale'@'%' and 'maxscale'@'hostname'
'monitor'@'%' and 'monitor'@'hostname'

Is it normal and intended to behave like that, or is it a bug?

The error sent by the server is now included in the log message.

https://mariadb.com/kb/en/mariadb-maxscale-25-galera-monitor/#required-grants

Required Grants
==========
The Galera Monitor requires the REPLICATION CLIENT grant to work:

CREATE USER 'maxscale'@'maxscalehost' IDENTIFIED BY 'maxscale-password';
GRANT REPLICATION CLIENT ON . TO 'maxscale-user'@'maxscalehost';

if set_donor_nodes is configured, the SUPER grant is required:

GRANT SUPER ON . TO 'maxscale'@'maxscalehost';

==================================
Here 'maxscalehost' was used instead of '%'.

https://jira.mariadb.org/browse/MXS-4406?focusedCommentId=243853&page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#comment-243853

Do you mean in the next maxscale version, the error message will not only display on /var/lib/mysql/mariadb.err but also /var/log/maxscale/maxscale.log?

Yes, the next version correctly reports what the MariaDB server sent as the reason of the authentication failure. This should show you why it works on other MaxScales but not on that one instance.

I must defined two sets of users for different hosts ('%' and hostname) to make maxscale work:

'maxscale'@'%' and 'maxscale'@'hostname'
'monitor'@'%' and 'monitor'@'hostname'

The symbol '%' means 'all hosts'. Is the above setup a requirement on galera clustering, or a bug?

https://jira.mariadb.org/browse/MXS-4406?focusedCommentId=243977&page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#comment-243977

This documentation on maxscale monitoring for galera clustering is a bit misleading. As I have said, when you update the host field on any node, the host value will be replicated to all other nodes, which hostnames should be different from one another. In this case, all host values are the same!

If you have three different MaxScales and you don't want to use the wildcard host (i.e. user@%), you'll need to define three different users:

CREATE USER 'user'@'host1' IDENTIFIED BY 'pw';

CREATE USER 'user'@'host2' IDENTIFIED BY 'pw';

CREATE USER 'user'@'host3' IDENTIFIED BY 'pw';

GRANT REPLICATION CLIENT ON . TO 'user'@'host1';

GRANT REPLICATION CLIENT ON . TO 'user'@'host2';

GRANT REPLICATION CLIENT ON . TO 'user'@'host3';

I have tried your suggestion.
I created the following users on each node respectively and deleted the user entry ('monitor'@'%')

node 1
-------
monitor@ekycdb1

node 2
-------
monitor@ekycdb2

node 3
-------
monitor@ekycdb3

========
node 1/2/3
========
MariaDB [mysql]> select user,host,password from user;
---------------------------------------------------------------

---------------------------------------------------------------

---------------------------------------------------------------
10 rows in set (0.001 sec)

But there was strange behavior on maxscale user if I deleted maxscale@'hostname'. Here below is the current user table after deleting 'maxscale'@'ekycdb1':
MariaDB [mysql]> select user,host,password from user;
---------------------------------------------------------------

---------------------------------------------------------------

---------------------------------------------------------------
9 rows in set (0.001 sec)

When I started maxscale on node 1, the following messages were displayed on mariadb.err on ALL nodes:

2022-11-30 16:19:31 23 [Warning] Access denied for user 'maxscale'@'ekycdb1' (using password: YES)
2022-11-30 16:19:32 24 [Warning] Access denied for user 'maxscale'@'ekycdb1' (using password: YES)
2022-11-30 16:19:33 25 [Warning] Access denied for user 'maxscale'@'ekycdb1' (using password: YES)
2022-11-30 16:19:34 26 [Warning] Access denied for user 'maxscale'@'ekycdb1' (using password: YES)
2022-11-30 16:19:35 27 [Warning] Access denied for user 'maxscale'@'ekycdb1' (using password: YES)
2022-11-30 16:19:36 28 [Warning] Access denied for user 'maxscale'@'ekycdb1' (using password: YES)
2022-11-30 16:19:37 29 [Warning] Access denied for user 'maxscale'@'ekycdb1' (using password: YES)
2022-11-30 16:19:38 30 [Warning] Access denied for user 'maxscale'@'ekycdb1' (using password: YES)
2022-11-30 16:19:39 31 [Warning] Access denied for user 'maxscale'@'ekycdb1' (using password: YES)
2022-11-30 16:19:40 32 [Warning] Access denied for user 'maxscale'@'ekycdb1' (using password: YES)

There were only 10 error messages displayed. No more, no less on mariadb.err of ALL the nodes.

When I started maxscale on node 2 and 3, no 'access denied' messages displayed anymore.

The maxscale queries were all successful on ALL nodes even though there were 'access denied' messages for maxscale user on the initial maxscale startup.

=========
Node 1/2/3
=========
> maxctrl list servers
┌─────────┬───────────┬───────┬─────────────┬─────────────────────────┬──────┬────────────────┐
│ Server │ Address │ Port │ Connections │ State │ GTID │ Monitor │
├─────────┼───────────┼───────┼─────────────┼─────────────────────────┼──────┼────────────────┤
│ server1 │ 10.0.2.14 │ 33063 │ 0 │ Master, Synced, Running │ │ Galera-Monitor │
├─────────┼───────────┼───────┼─────────────┼─────────────────────────┼──────┼────────────────┤
│ server2 │ 10.0.2.15 │ 33063 │ 0 │ Slave, Synced, Running │ │ Galera-Monitor │
├─────────┼───────────┼───────┼─────────────┼─────────────────────────┼──────┼────────────────┤
│ server3 │ 10.0.2.16 │ 33063 │ 0 │ Slave, Synced, Running │ │ Galera-Monitor │
└─────────┴───────────┴───────┴─────────────┴─────────────────────────┴──────┴────────────────┘
> maxctrl list services
┌────────────────────┬────────────────┬─────────────┬───────────────────┬───────────────────────────┐
│ Service │ Router │ Connections │ Total Connections │ Targets │
├────────────────────┼────────────────┼─────────────┼───────────────────┼───────────────────────────┤
│ Read-Write-Service │ readwritesplit │ 0 │ 0 │ server1, server2, server3 │
└────────────────────┴────────────────┴─────────────┴───────────────────┴───────────────────────────┘
> maxctrl list listeners Read-Write-Service
┌─────────────────────┬──────┬─────────┬─────────┬────────────────────┐
│ Name │ Port │ Host │ State │ Service │
├─────────────────────┼──────┼─────────┼─────────┼────────────────────┤
│ Read-Write-Listener │ 4006 │ 0.0.0.0 │ Running │ Read-Write-Service │
└─────────────────────┴──────┴─────────┴─────────┴────────────────────┘

I dropped the db user, 'maxscale'@'%' and created the following db users with appropriate rights granted respectively:

node 1
-------
'maxscale'@'ekycdb1'

node 2
-------
'maxscale'@'ekycdb2'

node 3
-------
'maxscale'@'ekycdb3'

Final user table was as follows:
MariaDB [mysql]> select user,host,password from user;
---------------------------------------------------------------

---------------------------------------------------------------

---------------------------------------------------------------
11 rows in set (0.001 sec)

I stopped/started maxscale on all the nodes again. No more 'access denial' messages on mariadb.err. Maxscale status was correct on all the nodes.

=========
Node 1/2/3
=========
> maxctrl list servers;
┌─────────┬───────────┬───────┬─────────────┬─────────────────────────┬──────┬────────────────┐
│ Server │ Address │ Port │ Connections │ State │ GTID │ Monitor │
├─────────┼───────────┼───────┼─────────────┼─────────────────────────┼──────┼────────────────┤
│ server1 │ 10.0.2.14 │ 33063 │ 0 │ Master, Synced, Running │ │ Galera-Monitor │
├─────────┼───────────┼───────┼─────────────┼─────────────────────────┼──────┼────────────────┤
│ server2 │ 10.0.2.15 │ 33063 │ 0 │ Slave, Synced, Running │ │ Galera-Monitor │
├─────────┼───────────┼───────┼─────────────┼─────────────────────────┼──────┼────────────────┤
│ server3 │ 10.0.2.16 │ 33063 │ 0 │ Slave, Synced, Running │ │ Galera-Monitor │
└─────────┴───────────┴───────┴─────────────┴─────────────────────────┴──────┴────────────────┘
> maxctrl list services;
┌────────────────────┬────────────────┬─────────────┬───────────────────┬───────────────────────────┐
│ Service │ Router │ Connections │ Total Connections │ Targets │
├────────────────────┼────────────────┼─────────────┼───────────────────┼───────────────────────────┤
│ Read-Write-Service │ readwritesplit │ 0 │ 0 │ server1, server2, server3 │
└────────────────────┴────────────────┴─────────────┴───────────────────┴───────────────────────────┘
> maxctrl list listeners Read-Write-Service
┌─────────────────────┬──────┬─────────┬─────────┬────────────────────┐
│ Name │ Port │ Host │ State │ Service │
├─────────────────────┼──────┼─────────┼─────────┼────────────────────┤
│ Read-Write-Listener │ 4006 │ 0.0.0.0 │ Running │ Read-Write-Service │
└─────────────────────┴──────┴─────────┴─────────┴────────────────────┘

If 'user'@'%' (either user=maxscale or user=monitor) is no longer correct, may you update the documentation on web and maxscale.cnf ? Or if there is bug, may you help to fix so 'user'@'%' alone will work again?

Ah, I think I figured out why it fails. I didn't see that you had an anonymous user for the ekycdb1 host:

Since connections from this host will match the anonymous user first before matching the wildcard user, it ended up causing authentication failures from that host. For whatever reason, all hosts had an anonymous user for that host instead of their local hosts. Once you drop that ''@'ekycdb1' user from all nodes, you should be able to use a wildcard user again. Normally you don't have anonymous users in production environments as they are dropped by the mysql_secure_installation command.

I have dropped all the invalid users and added back 'mascale'@'%' and 'monitor'@'%' with appropriate rights granted.

Here below is the final status:

MariaDB [mysql]> select user,host,password from user;
---------------------------------------------------------------

---------------------------------------------------------------

---------------------------------------------------------------
6 rows in set (0.002 sec)

I tried restarting maxscale again. All things went smoothly.

node 1/2/3
------------
> maxctrl list servers;
┌─────────┬───────────┬───────┬─────────────┬─────────────────────────┬──────┬────────────────┐
│ Server │ Address │ Port │ Connections │ State │ GTID │ Monitor │
├─────────┼───────────┼───────┼─────────────┼─────────────────────────┼──────┼────────────────┤
│ server1 │ 10.0.2.14 │ 33063 │ 0 │ Master, Synced, Running │ │ Galera-Monitor │
├─────────┼───────────┼───────┼─────────────┼─────────────────────────┼──────┼────────────────┤
│ server2 │ 10.0.2.15 │ 33063 │ 0 │ Slave, Synced, Running │ │ Galera-Monitor │
├─────────┼───────────┼───────┼─────────────┼─────────────────────────┼──────┼────────────────┤
│ server3 │ 10.0.2.16 │ 33063 │ 0 │ Slave, Synced, Running │ │ Galera-Monitor │
└─────────┴───────────┴───────┴─────────────┴─────────────────────────┴──────┴────────────────┘
> maxctrl list services;
┌────────────────────┬────────────────┬─────────────┬───────────────────┬───────────────────────────┐
│ Service │ Router │ Connections │ Total Connections │ Targets │
├────────────────────┼────────────────┼─────────────┼───────────────────┼───────────────────────────┤
│ Read-Write-Service │ readwritesplit │ 0 │ 0 │ server1, server2, server3 │
└────────────────────┴────────────────┴─────────────┴───────────────────┴───────────────────────────┘
> maxctrl list listeners Read-Write-Service
┌─────────────────────┬──────┬─────────┬─────────┬────────────────────┐
│ Name │ Port │ Host │ State │ Service │
├─────────────────────┼──────┼─────────┼─────────┼────────────────────┤
│ Read-Write-Listener │ 4006 │ 0.0.0.0 │ Running │ Read-Write-Service │
└─────────────────────┴──────┴─────────┴─────────┴────────────────────┘

No error messages displayed on mariadb.err.

It depends on you whether it is better to document this on maxscale.cnf or other documentation relating to maxscale. Please close this case. Thank you.