got "ERROR 2003 (HY000): Authentication with backend failed. Session will be closed." with MySQL 8.0 - Source

Revision: 89305
User: 學豪方
Date: 2019-09-17 11:51

//** I have 2 MySQL servers named esDB1 esDB2 and an isolative MaxScale server. **//
//** All installed in CentOS 7 with installed information as: **//

{{{
[root@esDB1 ~]# cat /etc/centos-release
CentOS Linux release 7.4.1708 (Core) 
[root@esDB1 ~]#

[root@esDB1 ~]# yum list installed | grep -i mysql
mysql-commercial-client.x86_64        8.0.15-1.1.el7                   installed
mysql-commercial-common.x86_64        8.0.15-1.1.el7                   installed
mysql-commercial-libs.x86_64          8.0.15-1.1.el7                   installed
mysql-commercial-libs-compat.x86_64   8.0.15-1.1.el7                   installed
mysql-commercial-server.x86_64        8.0.15-1.1.el7                   installed
[root@esDB1 ~]#

[root@esMax1 ~]# yum list installed | grep -i maxscale
Repodata is over 2 weeks old. Install yum-cron? Or run: yum makecache fast
maxscale.x86_64                      2.4.2-1                           installed
[root@esMax1 ~]#
}}}

//** There are 2 users for MaxScale in DB: **//

{{{
mysql> SHOW GRANTS for "MaxMonitor"@"168.6.42.%";
+--------------------------------------------------------------+
| Grants for MaxMonitor@168.6.42.%                             |
+--------------------------------------------------------------+
| GRANT REPLICATION CLIENT ON *.* TO `MaxMonitor`@`168.6.42.%` |
+--------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SHOW GRANTS for "MaxService"@"168.6.42.%";
+----------------------------------------------------------+
| Grants for MaxService@168.6.42.%                         |
+----------------------------------------------------------+
| GRANT SHOW DATABASES ON *.* TO `MaxService`@`168.6.42.%` |
| GRANT SELECT ON `mysql`.* TO `MaxService`@`168.6.42.%`   |
+----------------------------------------------------------+
2 rows in set (0.00 sec)

mysql>
}}}

//** The content of maxscale.cnf is: **//

{{{
[maxscale]
threads=auto
retain_last_statements=5
dump_last_statements=on_error

[esDB1]
type=server
address=168.6.42.111
port=3026
protocol=MariaDBBackend

[esDB2]
type=server
address=168.6.42.112
port=3026
protocol=MariaDBBackend

[MariaDB-Monitor]
type=monitor
module=mariadbmon
servers=esDB1,esDB2
user=MaxMonitor
password=MaxMonitor's password
monitor_interval=2000

[Read-Write-Service]
type=service
router=readwritesplit
servers=esDB1,esDB2
user=MaxService
password=MaxService's password

[MaxAdmin-Service]
type=service
router=cli

[Read-Write-Listener]
type=listener
service=Read-Write-Service
protocol=MariaDBClient
port=3306

[MaxAdmin-Listener]
type=listener
service=MaxAdmin-Service
protocol=maxscaled
socket=default
}}}

//** It can normally use the user "MaxService" directly login DB and execute SQL command. **//

{{{
[root@esDB1 ~]# mysql -uMaxService -p -h168.6.42.111 -P3026
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 29833
Server version: 8.0.15-commercial MySQL Enterprise Server - Commercial

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>
}}}

//** Then I start the MaxScale without any error: **//

{{{
[root@esMax1 ~]# systemctl start maxscale
[root@esMax1 ~]# systemctl status maxscale -l
● maxscale.service - MariaDB MaxScale Database Proxy
   Loaded: loaded (/usr/lib/systemd/system/maxscale.service; disabled; vendor preset: disabled)
   Active: active (running) since Mon 2019-09-09 18:14:45 CST; 19s ago
  Process: 8378 ExecStart=/usr/bin/maxscale (code=exited, status=0/SUCCESS)
  Process: 8375 ExecStartPre=/usr/bin/install -d /var/lib/maxscale -o maxscale -g maxscale (code=exi
ted, status=0/SUCCESS)
  Process: 8372 ExecStartPre=/usr/bin/install -d /var/run/maxscale -o maxscale -g maxscale (code=exi
ted, status=0/SUCCESS)
 Main PID: 8379 (maxscale)
   CGroup: /system.slice/maxscale.service
           └─8379 /usr/bin/maxscale

Sep 09 18:14:45 esMax1 maxscale[8379]: Listening for connections at [/var/run/maxscale/maxadmin.sock]:0
Sep 09 18:14:45 esMax1 maxscale[8379]: Service 'MaxAdmin-Service' started (1/2)
Sep 09 18:14:45 esMax1 maxscale[8379]: Server 'esDB1' version: 8.0.15-commercial
Sep 09 18:14:45 esMax1 maxscale[8379]: Server 'esDB2' version: 8.0.15-commercial
Sep 09 18:14:45 esMax1 maxscale[8379]: [MariaDBAuth] [Read-Write-Service] Loaded 17 MySQL users for listener Read-Write-Listener from server esDB1.
Sep 09 18:14:45 esMax1 maxscale[8379]: Listening for connections at [::]:3306
Sep 09 18:14:45 esMax1 maxscale[8379]: Service 'Read-Write-Service' started (2/2)
Sep 09 18:14:45 esMax1 maxscale[8379]: Loaded server states from journal file: /var/lib/maxscale/MariaDB-Monitor/monitor.dat
Sep 09 18:14:45 esMax1 systemd[1]: Started MariaDB MaxScale Database Proxy.
Sep 09 18:14:45 esMax1 maxscale[8379]: [mariadbmon] Attempted to find a replacement for the current 
master server 'esDB1' because it has started replicating from another server in the cluster, but 'esDB1' is still the best master server.
[root@esMax1 ~]#
}}}

//** The content of maxscale.log is: **//

{{{
MariaDB MaxScale  /var/log/maxscale/maxscale.log  Mon Sep  9 18:14:45 2019
----------------------------------------------------------------------------
2019-09-09 18:14:45   notice : syslog logging is enabled.
2019-09-09 18:14:45   notice : maxlog logging is enabled.
2019-09-09 18:14:45   notice : Using up to 548.00MiB of memory for query classifier cache
2019-09-09 18:14:45   notice : Working directory: /var/log/maxscale
2019-09-09 18:14:45   notice : The collection of SQLite memory allocation statistics turned off.
2019-09-09 18:14:45   notice : Threading mode of SQLite set to Multi-thread.
2019-09-09 18:14:45   notice : MariaDB MaxScale 2.4.2 started (Commit: aad4148d77bf2dfbaa0042bc45abda30c101cad2)
2019-09-09 18:14:45   notice : MaxScale is running in process 8379
2019-09-09 18:14:45   notice : Configuration file: /etc/maxscale.cnf
2019-09-09 18:14:45   notice : Log directory: /var/log/maxscale
2019-09-09 18:14:45   notice : Data directory: /var/lib/maxscale
2019-09-09 18:14:45   notice : Module directory: /usr/lib64/maxscale
2019-09-09 18:14:45   notice : Service cache: /var/cache/maxscale
2019-09-09 18:14:45   notice : No query classifier specified, using default 'qc_sqlite'.
2019-09-09 18:14:45   notice : Loaded module qc_sqlite: V1.0.0 from /usr/lib64/maxscale/libqc_sqlite.so
2019-09-09 18:14:45   notice : Query classification results are cached and reused. Memory used per thread: 137.00MiB
2019-09-09 18:14:45   notice : The systemd watchdog is Enabled. Internal timeout = 30s
2019-09-09 18:14:45   notice : Loading /etc/maxscale.cnf.
2019-09-09 18:14:45   notice : /etc/maxscale.cnf.d does not exist, not reading.
2019-09-09 18:14:45   notice : Loaded module maxscaled: V2.0.0 from /usr/lib64/maxscale/libmaxscaled.so
2019-09-09 18:14:45   notice : Loaded module MariaDBClient: V1.1.0 from /usr/lib64/maxscale/libmariadbclient.so
2019-09-09 18:14:45   warning: [cli] THE 'cli' MODULE AND 'maxadmin' ARE DEPRECATED: Use 'maxctrl' instead
2019-09-09 18:14:45   notice : Loaded module cli: V1.0.0 from /usr/lib64/maxscale/libcli.so
2019-09-09 18:14:45   notice : [readwritesplit] Initializing statement-based read/write split router module.
2019-09-09 18:14:45   notice : Loaded module readwritesplit: V1.1.0 from /usr/lib64/maxscale/libreadwritesplit.so
2019-09-09 18:14:45   notice : [mariadbmon] Initialise the MariaDB Monitor module.
2019-09-09 18:14:45   notice : Loaded module mariadbmon: V1.5.0 from /usr/lib64/maxscale/libmariadbmon.so
2019-09-09 18:14:45   notice : Loaded module MariaDBBackend: V2.0.0 from /usr/lib64/maxscale/libmariadbbackend.so
2019-09-09 18:14:45   notice : Loaded module mariadbbackendauth: V1.0.0 from /usr/lib64/maxscale/libmariadbbackendauth.so
2019-09-09 18:14:45   notice : Using encrypted passwords. Encryption key: '/var/lib/maxscale/.secrets'.
2019-09-09 18:14:45   notice : Loaded module MaxAdminAuth: V2.1.0 from /usr/lib64/maxscale/libmaxadminauth.so
2019-09-09 18:14:45   notice : Loaded module mariadbauth: V1.1.0 from /usr/lib64/maxscale/libmariadbauth.so
2019-09-09 18:14:45   notice : Started REST API on [127.0.0.1]:8989
2019-09-09 18:14:45   notice : MaxScale started with 4 worker threads, each with a stack size of 8388608 bytes.
2019-09-09 18:14:45   notice : Starting a total of 2 services...
2019-09-09 18:14:45   notice : Listening for connections at [/var/run/maxscale/maxadmin.sock]:0
2019-09-09 18:14:45   notice : Service 'MaxAdmin-Service' started (1/2)
2019-09-09 18:14:45   notice : Server 'esDB1' version: 8.0.15-commercial
2019-09-09 18:14:45   notice : Server 'esDB2' version: 8.0.15-commercial
2019-09-09 18:14:45   notice : [MariaDBAuth] [Read-Write-Service] Loaded 17 MySQL users for listener Read-Write-Listener from server esDB1.
2019-09-09 18:14:45   notice : Listening for connections at [::]:3306
2019-09-09 18:14:45   notice : Service 'Read-Write-Service' started (2/2)
2019-09-09 18:14:45   notice : Loaded server states from journal file: /var/lib/maxscale/MariaDB-Monitor/monitor.dat
2019-09-09 18:14:45   warning: [mariadbmon] Attempted to find a replacement for the current master server 'esDB1' because it has started replicating from another server in the cluster, but 'esDB1' is still the best master server.
}}}

//** And then I can use all user's ID to login through MaxScale, but can not execute any SQL command: **//

{{{
[root@esMax1 ~]# mysql -uMaxService -p -h168.6.42.118
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.5-8.0.15-commercial

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>
mysql> show databases;
ERROR 2003 (HY000): Authentication with backend failed. Session will be closed.
mysql> show databases;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    2
Current database: *** NONE ***

ERROR 2003 (HY000): Authentication with backend failed. Session will be closed.
mysql>
}}}

//** The content of maxscale.cnf added 6 records as: **//

{{{
2019-09-09 18:19:35   error  : [mariadbbackend] Unable to write to backend 'esDB1' due to authentication failure. Server in state Master, Running.
2019-09-09 18:19:35   error  : [mariadbbackend] Unable to write to backend 'esDB2' due to authentication failure. Server in state Relay Master, Slave, Running.
2019-09-09 18:19:35   notice : (1) Stmt 1(1970-01-01 08:00:00): select @@version_comment limit 1
2019-09-09 18:20:04   error  : [mariadbbackend] Unable to write to backend 'esDB2' due to authentication failure. Server in state Relay Master, Slave, Running.
2019-09-09 18:20:04   notice : (2) Stmt 1(1970-01-01 08:00:00): show databases
2019-09-09 18:20:04   error  : [mariadbbackend] Unable to write to backend 'esDB1' due to authentication failure. Server in state Master, Running.
}}}

//** Can anyone help me to correct it? **//

Answer

Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.

got "ERROR 2003 (HY000): Authentication with backend failed. Session will be closed." with MySQL 8.0 - Source

Answer

Products

Services

Pricing

Resources

About Us

Download MariaDB