got "ERROR 2003 (HY000): Authentication with backend failed. Session will be closed." with MySQL 8.0
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 [email protected].% | +--------------------------------------------------------------+ | 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 [email protected].% | +----------------------------------------------------------+ | 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 Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved. 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> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | +--------------------+ 4 rows in set (0.00 sec) 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 Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved. 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 Answered by Markus Mäkelä in this comment.
You have to configure the backends with default_authentication_plugin=mysql_native_password
, MySQL 8.0 uses the caching_sha2_password
plugin by default.