What should I do if php connects to mysql8 and reports an error?

##0x01 Question

In the evening, a student in the group said that

phpexploded and didn’t come out for two hours.

No matter how you look at it, there is no problem. Anyway, the connection keeps failing.

I asked him to add

mysqli_error()and there was no output. I checked the PHP related components and themysqllog but there was no response. Since it is a cloud host, I tried Changedlocalhostto127.0.0.1, still to no avail.

Then I checked the

mysqlversion,mysql8, which is too difficult for people who are still usingmysql5, so I checked it out After getting an update onmysql8, I picked up the child, haha.

Simply put, in

mysql8, a new verification methodcaching_sha2_passwordappeared, and this method became the new The verification mechanism is also processed in this way by default when configuring the password. The originalmysql_native_passwordis replaced. This is whyphpcannot be connected.

For detailed information, please see this link:

https://dev.mysql.com/doc/refman/8.0/en/upgrading-from-previous-series.html#upgrade-caching -sha2-password

0x02 Solution

1. In

/etc/my.cnf(centos for students) Turn off the comment in front ofmysql_native_password.. That is, change back to the old verification method:

[mysqld] default_authentication_plugin=mysql_native_password

2. Enter

mysqland update the password usingmysql_native_password.

mysql>ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password'; mysql>flush privileges;

Or you can add a new user.

mysql>CREATE USER 'test'@'%' IDENTIFIED WITH mysql_native_password BY 'password'; mysql>GRANT ALL PRIVILEGES ON *.* TO 'test'@'%'; mysql>flush privileges;

The problem is successfully solved.

0x03 Follow-up

After thinking about it, I can’t use

phpand I can’t use the new verification method. Then this is not useless, so It can certainly be solved usingphp, so I looked at it again and found something.

There are indeed new components

mysql_xdevapithat can be supported, but beforephp7.2.4it was eitherphp PDOorphp mysqlicannot support the new verification method.

Since the problem has been solved, I will not try this method again. Please try it again when it is used in the future and record it!

Recommended learning: "

PHP Video Tutorial"