MySQL 8 Compatibility With PHP 5

·

1 min read

I am maintaining a legacy PHP 5.6 Laravel application. It uses a MySQL DB maintained by another department, and I have no ownership over it. That DB was recently upgraded to version 8 and it is not compatible with my application. This is how I resolve the issue.

Errors

These some of the error messages shown when PHP code connects to MySQL 8 server.
(HY000/2054): Server sent charset unknown to the client.

The server requested authentication method unknown to the client [caching_sha2_password]

This is due to MySQL 8 changing the default authentication plugin to caching_sha2_password, and default character set to utf8bm4.

Solution

Set user password using mysql_native_password plugin.

# For new user
CREATE USER 'foobar'@'host' IDENTIFIED WITH mysql_native_password BY 'password';
# For existing user
ALTER USER 'foobar'@'host' IDENTIFIED WITH mysql_native_password BY 'password';

Add this to MySQL config file (my.cnf).

[mysqld]
collation-server=utf8_unicode_ci
character-set-server=utf8
default-authentication-plugin=mysql_native_password

Restart MySQL server for the change to take effect.

I found a similar article specific to CentOS 7: How to Run PHP 5 Applications with MySQL 8.0 on CentOS 7