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

Solution to PHP error when connecting to mysql8: 1. Turn off the comment in front of "mysql_native_password" in "/etc/my.cnf"; 2. Enter mysql and update the password with "mysql_native_password" That’s it.

The operating environment of this tutorial: CentOS 7 system, mysql8 version, DELL G3 computer

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

Solution to PHP connection failure to MYSQL8

##0x01 Question

In the evening, a student in the group said that

php exploded 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 the mysql log but there was no response. Since it is a cloud host, I tried Changed localhost to 127.0.0.1, still to no avail.

Then I checked the

mysql version, mysql8, which is too difficult for people who are still using mysql5, so I checked it out After getting an update on mysql8, I picked up the child, haha.

Simply put, in

mysql8, a new verification method caching_sha2_password appeared, and this method became the new The verification mechanism is also processed in this way by default when configuring the password. The original mysql_native_password is replaced. This is why php cannot 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 of mysql_native_password.. That is, change back to the old verification method:

[mysqld]
default_authentication_plugin=mysql_native_password

2. Enter

mysql and update the password using mysql_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

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

There are indeed new components

mysql_xdevapi that can be supported, but before php7.2.4 it was either php PDO or php mysqli cannot 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"

The above is the detailed content of What should I do if php connects to mysql8 and reports an error?. For more information, please follow other related articles on the PHP Chinese website!