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:

default_authentication_plugin=mysql_native_password

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.

Robin Sowell

Robin got involved with EllisLab in 2002, using pMachine Pro to build a personal site. Since then, her casual interest has grown into an obsession and she enjoys nothing more than seeing what new…