Shulou(Shulou.com)05/31 Report--

This article mainly introduces what are the pits of the new features of mysql8.0. It is very detailed and has a certain reference value. Friends who are interested must read it!

I. create users and authorizations

In mysql8.0, the creation of users and authorization is not the same as before, in fact, strictly speaking, it can not be said to be different, can only be said to be more stringent, mysql8.0 needs to create users and set passwords before authorization.

# first create a user create user 'hong'@'%' identified by' 123123 hong'@'%' with grant option # and then authorize grant all privileges on *. * to 'user

If you still use the original 5.7 method, you will report an error:

Grant all privileges on *. * to 'sroot'@'%' identified by' 123123'

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'identified by' 123123 at line 1

II. Remote links to MySQL8.0

There is no remote link after the MySQL8.0 installation is completed, this is because MySQL8.0 only supports localhost access, so we have to set it up before we can access it remotely.

The specific setting steps are as follows:

Log in to MySQL with ①

Execute the command: mysql-u root-p

Enter the password after entering enter.

② selects mysql database

The execution command is: use mysql

View the user table of user information stored in the mysql database.

③ views information about the current root user in the user table of the mysql database

The execution command is: select host,user,authentication_string,plugin from user

After executing the command, a table is displayed. The host of the root user displays the localhost by default, indicating that only local access is supported, not remote access.

④ changes the default configuration of host

The execution command is: update user set host='%' where user='root'

⑤ refresh

The execution command is: flush privileges

I thought this would be fine, but then, when I made a remote connection to mysql with navicat, there was an error in the pop-up window:

The reason for this is that in the previous version of mysql8, the encryption rule is mysql_native_password, but after mysql8, the encryption rule is caching_sha2_password. There are two ways to solve the problem, one is to upgrade the navicat driver, and the other is to restore the mysql login password encryption rules to mysql_native_password. I use it in the second way:

ALTER USER 'root'@'%' IDENTIFIED BY' password' PASSWORD EXPIRE NEVER; # modify encryption rules ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY' password';# update the user's password FLUSH PRIVILEGES; # refresh permissions

The problem is solved.

Considerations for setting mysql8.0 user password

In MySQL 8.0.11, caching_sha2_password is the default authentication plug-in rather than the previous mysql_native_password. For information about the impact of this change on server operation and server compatibility with clients and connectors, see caching_sha2_password as the preferred authentication plug-in. (translated from https://dev.mysql.com/doc/refman/8.0/en/caching-sha2-pluggable-authentication.html)

Today, when configuring and installing mysql8.0.11 on the new service, set the mysql password as usual. after the setting is successful, enter mysql-u root-p under shell, and then enter the password to enter normally, but the connection on phpmyadmin or directly using http://php.net/manual/zh/mysqli.real-connect.php indicates that you cannot connect. The specific error message is as follows

Mysqli_real_connect (): The server requested authentication method unknown to the client [sha256_password]

After searching around and finding the official documents, I found that from version 8.0.11, unlike mysql5.7 and previous versions, the default authentication method for setting a user's password is caching_sha2_password. If it is found that the original program cannot connect to mysql after upgrading mysql8.0.11, you can quickly set it to mysql5.7 and previous versions of password authentication with the following command on the mysql command line client client. At the same time, the way to change the password under MYSQL8.0.11 is not the same as before, and the original command to change the password cannot be used under mysql8.0.11.

> use mysql > ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY' your password'; > FLUSH PRIVILEGES; is all the contents of this article entitled "what are the pits of mysql8.0 's new features?" Thank you for reading! Hope to share the content to help you, more related knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.