NOTE: This article was written October 2, 2018 when MySQL 8 was very new. This article may quickly become stale as PHP and MySQL progress.
MySQL 8 is cutting edge. And developers who want to try out the new features can expect a smooth ride in ExpressionEngine—with minor tweaks to their MySQL settings.
The biggest incompatible change to MySQL 8 is in how it authenticates your password. The default is a new plugin called
caching_sha2_password. This results in an error in all sorts of applications, including desktop MySQL browsers:
The server requested authentication method unknown to the client [caching_sha2_password] …
Since most versions of PHP do not recognize this new plugin†, you’ll need to change MySQL to authenticate the old way. Use a my.cnf configuration file to restore the old way. In your
my.conf in the [mysqld] section add:
Restart the server after editing the option file. Run the query
SHOW VARIABLES; and look for the
default_authentication_plugin value to verify that your change took.
If the value is correct and you still get that pesky error, then the MySQL user probably needs to be modified as well. To change the user’s authentication plugin, use the ALTER USER command:
ALTER USER 'jeffrey'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';
(replacing password with your actual password, of course). And that should take care of it!
† From the PHP manual:
PHP version before 7.1.16, or PHP 7.2 before 7.2.4, set MySQL 8 Server’s default password plugin to mysql_native_password or else you will see errors similar to The server requested authentication method unknown to the client [caching_sha2_password] even when caching_sha2_password is not used.