Hi,
I’m getting a ERROR: Authentication plugin ‘caching_sha2_password’ when trying to run mysql shell.
My cnf file has
[mysql@dvlb7mysqldb01 ~]$ grep authen /etc/my.cnf
default_authentication_plugin=mysql_native_password
And the in mysql.user the plugin is :
plugin: mysql_native_password
Not sure what is going on
mysqlsh --socket=/var/lib/mysql/mysql.sock – util checkForServerUpgrade root@localhost:3306 --target-version=8.4.1 --config-path=/etc/my.cnf
Please provide the password for ‘root@localhost:3306’: ******************
ERROR: Authentication plugin ‘caching_sha2_password’ reported error: Authentication requires secure connection.
Not sure if its connected to this error but I also cannot make mysqlsh connect unless i specify the correct socket file. Not sure why it is not using the default socket file and keeps on using /tmp/mysql.sock
mysqlsh – util checkForServerUpgrade root@localhost:3306 --target-version=8.4.1 --config-path=/etc/my.cnf
MySQL Error 2002 (HY000): Can’t connect to local MySQL server through socket ‘/tmp/mysql.sock’ (2)
Can you provide the contents of the my.cnf please.
# Generated by Percona Configuration Wizard (http://tools.percona.com/) version REL5-20120208
[mysql]
# CLIENT #
port = 3306
socket = /var/lib/mysql/mysql.sock
default-character-set = utf8
[mysqld]
#audit_log_rotate_on_size = 524288000
#audit_log_rotations = 9
#audit_log_include_commands = 'alter_user,create_user,drop_user'
#plugin-load = audit_log.so
#audit-log = FORCE_PLUS_PERMANENT
# GENERAL #
user = mysql
default-storage-engine = InnoDB
socket = /var/lib/mysql/mysql.sock
pid-file = /var/run/mysqld/mysqld.pid
#ignore-db-dir = lost+found
#ignore-db-dir = tmp
log_timestamps = SYSTEM
# MyISAM #
# value from dvlbmysql01 /usr/local/share/umls/umls_my.cnf
key_buffer_size = 750M
myisam-recover-options = FORCE,BACKUP
# SAFETY #
max_allowed_packet = 256M
max-connect-errors = 1000000
skip-name-resolve
sql-mode = "STRICT_TRANS_TABLES,NO_AUTO_VALUE_ON_ZERO,NO_ENGINE_SUBSTITUTION,STRICT_ALL_TABLES"
sysdate-is-now = 1
# DATA STORAGE #
datadir = /var/lib/mysql/data
#tmpdir = /var/lib/mysql/data/tmp
tmpdir = /var/lib/mysql/tmp
# Replication settings & BINARY LOGGING #
# set specifically to match QA and PROD
server-id = 102
binlog-format = ROW
log-bin = /var/lib/mysql/logs/mysql-bin.log
expire-logs-days = 7
sync-binlog = 1
binlog_row_image = MINIMAL
# CACHES AND LIMITS #
tmp-table-size = 512M
max-heap-table-size = 512M
#query-cache-type = 0
#query-cache-size = 0
max-connections = 500
thread-cache-size = 50
open-files-limit = 65535
table-definition-cache = 4096
table-open-cache = 500
# INNODB #
innodb-flush-method = O_DIRECT
innodb-log-files-in-group = 2
innodb-log-file-size = 128M
innodb-flush-log-at-trx-commit = 1
innodb-file-per-table = 1
innodb-buffer-pool-size = 2G
innodb-strict-mode = 1
# LOGGING #
log-error = /var/lib/mysql/errorlogs/mysql-error.log
general_log_file = /var/lib/mysql/errorlogs/general_log.log
slow_query_log_file = /var/lib/mysql/errorlogs/slow-query.log
slow-query-log = 0
long_query_time = 30
slow_query_log_always_write_time = 30
log_error_verbosity = 3
net_read_timeout = 7200
net_write_timeout = 14400
explicit_defaults_for_timestamp = 1
symbolic-links = 0
#Added to balance firewall idle timeout
interactive_timeout = 1200
wait_timeout = 1200
skip-external-locking
sort_buffer_size = 500M
read_buffer_size = 200M
read_rnd_buffer_size = 10M
bulk_insert_buffer_size = 200M
# increase this beyond default, if needed
###join_buffer_size = 100M
local-infile = 1
skip-grant-tables = FALSE
# set/force server character set to be UTF-8
character-set-server = utf8
collation-server = utf8_bin
# disable_ssl
skip_ssl
default_authentication_plugin=mysql_native_password
Your my.cnf has the correct socket. I am wondering do you happen to have a .my.cnf in the home directory of the dvlb7mysqldb01? If you do make sure the socket matches whats in your /etc/my.cnf
By default 8.4 will not use mysql_native_password. Making caching_sha2_password the default for 8.4. MySQL 8.4 requires secure connections when using caching_sha2_password. Check your root user on the 8.4 server and you should see that its password plugin is caching_sha2_password. So on your 8.4 server you need to allow for SSL connections. In you my.cnf on your 8.4 server try setting require_secure_transport = ON.
Give this a try on your 8.4 server. See if your upgrade checker will run now.
There is no other my.cnf.
I have checked the following locations:
- /etc/mysql/my.cnf
- $MYSQL_HOME/my.cnf
- [datadir]/my.cnf
- ~/.my.cnf
I’m running an 8.0 server but a 8.4 mysqlsh
root password plugin is using mysql_native_password not caching_sha2_password