MySQL Authentication Method "caching_sha2

MySQL Tutorials - Herong's Tutorial Examples

∟MySQL Authentication Method "caching_sha2_password"

A tutorial example is provided on how to create a new user account with 'mysql_native_password' authentication method to avoid the 'caching_sha2_password' error with mysqli_connect() method.

If you are running MySQL Server 8.0 with the default configuration and try to access it with mysqli_connect() method in a PHP script, you will get the error:

PHP Warning:  mysqli_connect(): The server requested authentication
method unknown to the client [caching_sha2_password]
in C:\herong\MySqlTest.php on line 5

Here is why. By default, all user accounts on MySQL Server 8.0 are created with the new authentication method called: caching_sha2_password. Unfortunately, caching_sha2_password is not supported by the "mysqli" module yet.

One solution is to change the user's authentication method back to "mysql_native_password", or create a new user with "mysql_native_password".

Here is how to create a new user "herong" with the old authentication method, "mysql_native_password".

herong> %mysql%\bin\mysql.exe --user=root --password
Enter password: TopSecret

mysql> CREATE USER herong IDENTIFIED
mysql> WITH mysql_native_password BY 'TopSecret';
Query OK, 0 rows affected (0.01 sec)

I updated the MySqlTest.php with the new user account and test it again:

herong> type MySqlTest.php
<?php
#- MySqlTest.php
#  Copyright (c) 2005 HerongYang.com. All Rights Reserved.
#
#   $con = mysqli_connect('localhost','root','TopSecret');
   $con = mysqli_connect('localhost','herong','TopSecret');
   print "MySQL server info = ".mysqli_get_server_info($con)."\n";
   print "MySQL status = ".mysqli_stat($con)."\n";
   mysqli_close($con);
?>

herong> \local\php\php MySqlTest.php
MySQL server info = 8.0.16
MySQL status = Uptime: 19560  Threads: 3  Questions: 16
Slow queries: 0  Opens: 161  Flush tables: 3
Open tables: 65  Queries per second avg: 0.000