Explain in detail the solution to the error report granted by the user in creating mysql8.0.

Question 1:

The wrong way to write:

GRANT ALL PRIVILEGES ON *. * 'root'@'%' identified by' 123123 'WITH GRANT OPTION

Here is the correct way to write it:

Grant all privileges on *. * to 'root'@'%'

It can be seen that the authorization statement needs to be removed.

IDENTIFIED BY 'password'

The writing of granting a permission separately:

GRANT SELECT ON oilsystem.input TO 'u5pm localhost'

Refresh permissions and view the writing of permissions:

FLUSH PRIVILEGES; select * from user

Note: you need to add a sentence before creating a user.

Use mysql

In addition, the way to withdraw a certain permission is:

REVOKE select ON. FROM 'U1century localhost'

Question 2:

Mysql remote connection error: authentication plugin caching_sha2

Mysql 8.0 uses the caching_sha2_password authentication mechanism by default-changing from mysql_native_password to caching_sha2_password.

Upgrading version 8.0 from 5.7 will not change the authentication method for existing users, but new users will use the new caching_sha2_password by default.

The client does not support the new encryption.

One of the methods is to change the user's password and encryption method.

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY' password

New feature of mysql8.* caching_sha2_password password encryption

Previous versions of mysql password encryption used mysql_native_password

Caching_sha2_password used by default for newly added user passwords

If you upgrade on the basis of the previous mysql, you have to use mysql_native_password to encrypt the password.

If you use the previous password encryption method, modify the file / etc/my.cnf finally: I also stepped on countless holes to deploy successfully.

The above is the whole content of this article, I hope it will be helpful to your study, and I also hope that you will support it.