Unable to log into an alternate database via phpMyAdmin

Symptoms

When attempting to log in to a MySQL database version 8.0 or higher, phpMyAdmin returns the error "mysqli::real_connect(): The server requested authentication method unknown to the client [caching_sha2_password]".

Causes

By default, MySQL versions 8.0 and higher use the caching_sha2_password authentication plugin, which is not supported by older versions of PHP below version 7.4.

Solution

Upgrade the PHP version running phpMyAdmin to at least the 7.4 alternative.

Make sure the 7.4 alternative PHP version is installed in the control panel, along with PHP-FPM, in the Software configuration section. Install it if it's not installed yet.

Run the following commands to modify the PHP-FPM configuration files:

cp -ar /etc/php-fpm.d/apache.conf /opt/php74/etc/php-fpm.d/apachealt.conf
sed -i  "s/php-fpm.apache.sock/php-fpm.apachealt.sock/g" /etc/nginx/vhosts-includes/phpmyadmin-nginx.conf
sed -i  "s/php-fpm.apache.sock/php-fpm.apachealt.sock/g" /opt/php74/etc/php-fpm.d/apachealt.conf

Check the Nginx web server and PHP-FPM syntax for version 7.4:

nginx -t
/opt/php74/sbin/php-fpm -t

If there are no errors, restart the services:

systemctl reload nginx php-fpm74 || systemctl restart nginx php-fpm74

 If they are disabled, enable the following extensions in the PHP section for PHP 7.4 to ensure phpMyAdmin works correctly:

• json
• session
• pcre
• hash
• spl
• ctype
• zip
• gd2
• openssl
• libxml
• curl
• sodium