MySQL 8 has dropped support for PHP 7.2 and lower versions of PHP by changing its default password plugin to caching_sha2_password. As a result, when you try to run MySQL 8 with PHP 7.2 or lower versions, you get the database error the server requested authentication method unknown. Fortunately, this is easy to fix. We’ll discuss couple of ways you can enable PHP 7.2 support for MySQL 8.
The Problem: MySQL 8 with PHP 7.2
I was working on a server where the customer had a WHMCS with a module encoded with ionCube. My task was to upgrade the server to latest software. So, I went for PHP 8.0 and MySQL 8. But the WHMCS site started giving me problems. I soon tracked down the problem to a module that required ionCube loaders for PHP 7.2 or lower to run. This wasn’t usually a problem since my Virtualmin – Nginx server setup allows me to run multiple PHP versions at the same time. So, I could use PHP 7.2 for this site and have PHP 8.0 for other WordPress websites the client had.
But the problem was MySQL 8. It only supported PHP 7.4 or higher. So, to have this WHMCS site working properly, I had to find a way to make MySQL 8 compatible with PHP 7.2. A quick search landed me on a php support article which explains the problem. They have given a workaround, But I wasn’t comfortable with it. I needed something that doesn’t affect the whole MySQL 8 installation. Which brings me to the easy solution.
The Easy Solution: Alter MySQL user to use MySQL Native Password
The problem is caused by MySQL 8 making caching_sha2_password its default authentication plugin. PHP 7.2 doesn’t recognize this. So, it’s not able to communicate with MySQL server thus giving a database connection error on the frontend. The old default authentication plugin MySQL used, the one that PHP 7.2 support is mysql_native_password plugin, which is still there. But isn’t being used by default.
We can just alter the MySQL use we’re going to use for PHP 7.2 and change the authentication plugin to mysql_native_password. It’s a setting that can be set globally or per user basis.
Let’s assume the MySQL user we’re going to use with the site running PHP 7.2 or lower is vpsfix. Yon can execute following command to set mysql_native_password as authentication plugin for user vpsfix.
ALTER USER vpsfix IDENTIFIED WITH mysql_native_password BY 'mypassword';
Note that you need to be logged in to MySQL server as the root user before you execute above command. This will solve the MySQL 8 and PHP 7.2 compatibility problem for user vpsfix.
That’s altering an existing MySQL user. If you want, you can manually create a new MySQL user that uses native password authentication plugin. Following is the command for that.
CREATE USER 'vpsfix'@'localhost' IDENTIFIED WITH mysql_native_password BY 'mypassword';
Both these methods are easy to implement and doesn’t do changes to any MySQL 8 global configuration.
The Solution: Change MySQL Default Authentication Plugin
Well, I wrote the previous subtitle not thinking the whole process through. This method is also easy to implement. So, there you go. It’s lunch time and I’m too lazy to come up with new titles.
In this method we’ll implement the workaround I found on the PHP support document I linked to earlier. Since PHP 7.2 works with mysql_native_password, we’ll edit MySQL configuration file and change the default authentication plugin to mysql_native_password.
Open your MySQL configuration file with text editor,
nano /etc/mysql/mysql.conf.d/mysqld.cnf
Your MySQL config file may be on a different location. You may read How to find which MariaDB configuration file is being used on Ubuntu, I wrote it awhile back for MariaDB, but should work for MySQL as well.
Paste the following argument just after the [mysqld] line in the configuration file.
default-authentication-plugin=mysql_native_password
Save the configuration file and restart MySQL.
systemclt restart mysql.service
Now your MySQL installation is fully supported for PHP 7.2. You don’t need to change the settings for each user you add.
