MySQL user not connecting

Windows Server 2016, IIS Website, Setup a MySQL server/database, setup a WordPress website, quickly connect with root and root-password. Everything works fine!

Now you want things more secured, so you add a MySQL user with a password and rights to only the wordpress database. Change wp-config.php with the new login and you get a “Error Establishing a Database Connection” when accessing the WordPress website or admin.

Change back to root and the problem is solved. How to fix this?

Well, you google and create a test script

$link = mysql_connect('localhost', 'myuser', 'mypassword');
if (!$link) {
    die('Could not connect: ' . mysql_error());
echo 'Connected successfully';

But this one does not show the actual problem.
A better script is:

$link = mysqli_connect("localhost:3306", "myuser", "mypassword");
if (!$link) {
    echo "Error: Unable to connect to MySQL.";
    echo "Debugging errno: " . mysqli_connect_errno();
    echo "Debugging error: " . mysqli_connect_error();
echo "Success: A proper connection to MySQL was made!";
echo "Host information: " . mysqli_get_host_info($link);

You get the error: “The server requested authentication method unknown to the client“.
Of course it would have been quicker to check the PHP error-log, where I could have read: “[06-Nov-2019 16:11:41 Europe/Belgrade] PHP Warning: mysqli_connect(): The server requested authentication method unknown to the client [caching_sha2_password] in _testdb.php on line 2”

Anyway, that is another issue, so what do you do? Stackoverflow!

You have to change MySQL settings.
Edit my.cnf file, or in my case my.ini in C:\ProgramData\MySQL\MySQL Server 8.0\ , and put this setting in the mysqld section:


Then restart your MySQL server.
Still doesn’t work? Then just recreate the user and try again.
Everything will be nice, shiny and secure!

Any other questions?
Just ask My Brain!