MySQL user not connecting

Problem:
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

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

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

<?php
$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();
    exit;
}
echo "Success: A proper connection to MySQL was made!";
echo "Host information: " . mysqli_get_host_info($link);
mysqli_close($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! https://stackoverflow.com/questions/52364415/php-with-mysql-8-0-error-the-server-requested-authentication-method-unknown-to

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:

[mysqld]
default_authentication_plugin=mysql_native_password

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!