php无法连接mysql解决办法


前段时间把我的阿里云机器上面跑的服务修改成了docker部署,但是在升级了mysql容器之后发现另一个wordpress的站一直无法打开,提示无法连接数据库,这个很奇怪,只是升级了下mysql容器,用户名密码等都没有修改,为什么会无法连接呢? 打开了下wordpressdebug模式,发现如下错误信息:

Warning: mysqli_real_connect(): Unexpected server respose while doing caching_sha2 auth: 109 in /wordpress/wp-includes/wp-db.php on line 1531
Warning: mysqli_real_connect(): MySQL server has gone away in /wp-includes/wp-db.php on line 1531
Warning: mysqli_real_connect(): (HY000/2006): MySQL server has gone away in /wordpress/wp-includes/wp-db.php on line 1531

网上查找了下资料才发现在mysql 8以后会出现这样的情况:

MySQL 8

When running a PHP version before 7.1.16, or PHP 7.2 before 7.2.4, 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.

This is because MySQL 8 defaults to caching_sha2_password, a plugin that is not recognized by the older PHP (mysqlnd) releases. Instead, change it by setting default_authentication_plugin=mysql_native_password in my.cnf. The caching_sha2_password plugin will be supported in a future PHP release. In the meantime, the mysql_xdevapi extension does support it.

mysql8的默认加密方式修改成了caching_sha2_password,旧版本的php不支持,解决办法也很简单:
修改my.conf中在mysqld节点中增加:
default_authentication_plugin=mysql_native_password
配置,重启mysql,如果还是不能连接的话需要重新创建用户:

CREATE USER 'root'@'localhost' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON * . * TO 'root'@'localhost';
FLUSH PRIVILEGES;

这样就可以了。