PHP 7.2.8 PDO Fails to Connect to MySQL 8.0.12 (GA) in AWS EC2 LEMP Stack

Solution 1:

Further investigation allowed us to learn that PHP doesn't support caching_sha2_authentication as yet, mysql had to revert to native.

NOTE: One cannot simply create accounts WITH mysql_native_password, but must set the default to mysql_native_password.

EDIT: Cut info that turned out to be unrelated to the issue.

Solution 2:

The text on the PHP documentation page you linked to is:

MySQL 8

When running a 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.

This is because MySQL 8 defaults to caching_sha2_password, a plugin that is not recognized by the older PHP (mysqlnd) releases. Instead, change it by setting default_authentication_plugin=mysql_native_password in my.cnf. The caching_sha2_password plugin will be supported in a future PHP release. In the meantime, the mysql_xdevapi extension does support it.

This doesn't mean that PHP currently supports caching_sha2_password. It does not. It means that PHP no longer throws fatal errors when connecting to a MySQL 8.0 server that advertises support for caching_sha2_password.

You can install the named PECL extension if you wish, but it has a different API to mysqlnd, so it is not a drop-in replacement, and will not work with existing PHP code that uses mysqlnd's API.

For the moment, until a new version of PHP mysqlnd is released that actually supports authenticating with caching_sha2_password, your only option is to not use it, going back to mysql_native_password as documented.