Detailed explanation of the solution to the error in creating a user and granting permissions in mysql8.0

Question 1:

The writing method that will report an error:

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

The following 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';

How to grant a certain permission separately:

GRANT SELECT 
ON oilsystem.input 
TO 'u5'@'localhost'

Refresh permissions and view how permissions are written:

FLUSH PRIVILEGES; 
select * from user;

Note: You need to add a sentence before creating a user

Use mysql;

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

REVOKE select 
ON . 


FROM 'u1'@'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 from 5.7 to 8.0 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 method.

Method 1: 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 method

Previous versions of MySQL used mysql_native_password for password encryption.

The default caching_sha2_password for newly added user passwords is

If you upgrade based on the previous MySQL, the password encryption used by the user must be mysql_native_password

If you use the previous password encryption method, modify the file /etc/my.cnf. Finally: I also stepped on countless pits before the deployment was successful.

The above is the full content of this article. I hope it will be helpful for everyone’s study. I also hope that everyone will support 123WORDPRESS.COM.