MySQL fix for authentication plugin 'caching_sha2_password' cannot be loaded

mysql.md

my_print_defaults to check which file to edit.

check for a message like this:

Default options are read from the following files in the given order:

/etc/my.cnf /etc/mysql/my.cnf /usr/local/etc/my.cnf ~/.my.cnf

something more ergonomic, try:

/usr/sbin/mysqld --verbose --help | grep -A 1 "Default options"

I prefer /usr/local/etc/my.cnf that is the one that already exists after homebrew install

then my config file is:

# Default Homebrew MySQL server config
[mysqld]
# Only allow connections from localhost
bind-address = 127.0.0.1
default_authentication_plugin=mysql_native_password
validate_password.policy=LOW
validate_password.special_char_count=0
validate_password.length=0
validate_password.mixed_case_count=0
validate_password.number_count=0

After that restart the server mysql.server restart and change user password to null again (now using mysql_native_password):

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '';

And you are good to go! :)