MySQL-Server 8.0 下PHP无法链接到数据库
最近应一名客户需求将站点服务器升级到CENTOS 8 PHP从 5.6 升级到 7.3 MysqlServer 从5.6升级到8,环境全部部署完成后,将站点数据导入时遇到报错无法链接到数据;起初以为是密码写错了,反复检查之后确的。
到这个问题的原因是MySQL升级到8.0后将默认身份验证插件由mysql_native_password
变更为caching_sha2_password
,PHP目前还使用的是旧的认证方式,所以会导致一直报错密码错误等;
官方描述
caching_sha2_password as the Preferred Authentication Plugin
The caching_sha2_password and sha256_password authentication plugins provide more secure password encryption than the mysql_native_password plugin, and caching_sha2_password provides better performance than sha256_password. Due to these superior security and performance characteristics of caching_sha2_password, it is as of MySQL 8.0 the preferred authentication plugin, and is also the default authentication plugin rather than mysql_native_password. This change affects both the server and the libmysqlclient client library:
点击这里去官方查看全文
解决办法
解决办法也简单,有两个办法,一个是将默认验证方式更换回mysql_native_password
,二是不变更默认验证方式,将指定账号的验证方式变更为mysql_native_password
。
1、 默认验证方式更换回mysql_native_password
[mysqld]
default_authentication_plugin=mysql_native_password
重启后数据将采用mysql_native_password
为默认验证方式,此时在新建用户即可;
2、是不变更默认验证方式,将指定账号的验证方式变更为mysql_native_password
ALTER USER 'user'@'localhost'
IDENTIFIED WITH mysql_native_password
BY 'password';
flush privileges;
客户选择的是第二种方式,来解决这个问题,等PHP解决这个问题之后在更改新的验证方式。