php连接MySQL8.0版本数据库密码验证失败解决方法 因为MySQL8以后版本的密码加密方式与MySQL以前版本不一样!MySQL8是caching_sha2_password加密,MySQL以前版本是mysql_native_password加密,所以本人使用phpmysqli_connect连接数据库失败!(使用cmd命令mysql -u root -p连接成功,使用可视化工具Navicat Premium连接成功!)
失败效果及解决方法如下。cmd连接成功 E:\StudyFile\mysql\mysql-8.0.16-winx64\bin>mysql -u root -p Enter password: ****** Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 24 Server version: 8.0.16 MySQL Community Server - GPL Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> Navicat Premium连接成功
php连接失败
php代码 <?php $connection = mysqli_connect('127.0.0.1', 'root', '123456', 'demo2'); if (!$connection) { // 连接数据库失败 exit('
连接数据库失败'); } $query = mysqli_query($connection, 'select * from users;'); while ($row = mysqli_fetch_assoc($query)) { var_dump($row); }
连接失败警告 Warning: mysqli_connect(): The server requested authentication method unknown to the client [caching_sha2_password] in F:\code\01-mysql.php on line 13 Warning: mysqli_connect(): (HY000/2054): The server requested authentication method unknown to the client in F:\code\01-mysql.php on line 13
解决方法 修改加密方式
使用新版本客户端(不说明)使用cmd 修改加密方式
在进入mysql编辑后使用命令:mysql> ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';
把新版本加密方式caching_sha2_password改为旧加密方式mysql_native_password mysql> ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '1 23456'; Query OK, 0 rows affected (0.12 sec)
更新 命令:flush privileges; mysql> flush privileges; Query OK, 0 rows affected (0.01 sec)
修改完成!可以读取数据库信息了 array(5) { ["id"]=> string(1) "1" ["title"]=> string(6) "实例" ["name"]=> string(7) " 小明" ["age"]=> string(2) "15" ["gender"]=> string(1) "0" }