| Server IP : 134.29.175.74 / Your IP : 216.73.216.160 Web Server : nginx/1.10.2 System : Windows NT CST-WEBSERVER 10.0 build 19045 (Windows 10) i586 User : Administrator ( 0) PHP Version : 7.1.0 Disable Function : NONE MySQL : OFF | cURL : ON | WGET : OFF | Perl : OFF | Python : OFF | Sudo : OFF | Pkexec : OFF Directory : /nginx/html/JimMartinson/Setup/PHP_MySQL/ |
Upload File : |
<?
// Setup/PHP_MySQL/index.php
$TRACK = "<b>".basename(__FILE__).'</b> <span class="pv_fl">('.__FILE__.")</span>\n<ol>\n";
ini_set('include_path',ini_get('include_path').PATH_SEPARATOR.$_SERVER['DOCUMENT_ROOT']."/"); // Add the DOCUMENT_ROOT to the include_path.
include('application.phpinc');
$title = "PHP MySQL Authentication";
$headTitle = '_CST_ - '.$title;
$pageTitle = '_CST_<br>'.$title;
include('common/pageHeader.phpinc');
$sectionTitle = $title;
include('common/sectionHeader.phpinc');
?>
<h2>Older versions of PHP</h2>
When running a PHP version before 7.1.16, PHP 7.2 before 7.2.4, or PHP 8.0, set MySQL 8 Server's default password plugin to mysql_native_password or else you will see errors similar to The server requested authentication method unknown to the client [caching_sha2_password] even when caching_sha2_password is not used.
<h2>PHP versions 7.1.16, 7.2.4, or 8.0 or newer.</h2>
If you get authentication errors such as:
<ul>
<li>The server requested authentication method unknown to the client</li>
<li>Access denied for user 'root'@'localhost' (using password: YES)</li>
</ul>
If this happens, then your MySQL server is using caching_sha2_password for authentication and mysqlnd is probably using mysql_native_password.
<p>
Here are three methods to fix the problem: the right way, the native way, and the simple way.
<ol>
<li style=" background-color: lightgoldenrodyellow;">
<h2 id="right">The Right Way</h2>
Ensure that your my.ini or my.cnf file is coading and has the correct default_authentication_plugin setting.
<ol>
<li>
Ensure mysqlnd supports caching_sha2_password.
<br>Run phpinfo() and search the page for <b class="copytext">caching_sha2_password</b>.
If it does, continue.
</li>
<li>
Ensure Mysql is loading the my.ini or my.cnf file.
<br>Open MySQL Workbench and go to the Administration tab and select Server Status.
<br>See what Configuration File is being used. <span class="note">Mine was C:\ProgramData\MySQL\MySQL Server 5.5\my.ini.</span>
</li>
<li>
Ensure the caching_sha2_password setting is correct.
<br>Open the configuration file and search for the <b class="copyText">caching_sha2_password</b> line.
<br>It should read: <b class="copyText">caching_sha2_password=caching_sha2_password</b>.
<br>
<span class="note">
For me, it turned out the C:\ProgramData\MySQL\MySQL Server 5.5 directory did not even exist.
<br>There was a C:\ProgramData\MySQL\MySQL Server 8.0 directory with a my.ini file in it..
<br>So I made the "C:\ProgramData\MySQL\MySQL Server 5.5 directory" and copied the my.ini file into it.
<br>The my.ini file did have caching_sha2_password=caching_sha2_password.
<br>After restarting Apache and MySQL it now worked.
</span>
</li>
</ol>
</li>
<li style=" background-color: lightgoldenrodyellow; filter: brightness(95%)";>
<h2 id="native">The Native Way</h2>
Create a new superuser with a native password.
<br>
Open MySQL Workbench.
<br>
Create the user with the sql statement:<br> <b class="code copyText">CREATE USER 'rootnative'@'localhost' IDENTIFIED WITH mysql_native_password BY 'rootnative_password_here';</b>
<br>
Make it a superuser with the sql statement:<br><b class="code copyText">GRANT ALL PRIVILEGES ON *.* TO 'rootnative'@'localhost' WITH GRANT OPTION;</b>
<br>
<span class="note">Replace <b>rootnative_password_here</b> with the password you want to set.</span>
</li>
<li style=" background-color: lightgoldenrodyellow; filter: brightness(90%)";>
<h2 id="simple">The Simple Way</h2>
Just give root a native password.
<br>
Open MySQL Workbench.
<br>
Give root a native password with the sql statement:<br> <b class="code copyText">ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'root_password_here';</b>
<br>
<span class="note">Replace <b>root_password_here</b> with the password you want to set.</span>
</li>
</ol>
</p>
select @@default_authentication_plugin;
<?
include('common/sectionFooter.phpinc');
#setDebugOn();
include('common/pageFooter.phpinc');
?>