"MySQL8" after the installation of the simple configuration (mainly to solve the navicat and other client landing error problems)

First, Navicat and other client landing error reasons

Using MySQL, most of us still like to use visual client login management, personal preferred to use navicat. Generally installed server, used to build a remote login account, on the MYSQL8 server, the old method to create an account, you can use the MySQL command line normal login, but with navicat and other software login will prompt error

Because of the new features of MySQL8, MySQL8 uses the Caching_sha2_password authentication mechanism by default. The old client does not support this verification, and of course it cannot be logged in.

So the solution is also very simple, we only need to use the old authentication mechanism (Mysql_native_password) to set the password on the server. The old mode is not MySQL8 default mode, so you need to explicitly specify Mysql_native_password mode when creating a user or modifying a password.

First we tried to create a user with the default mode,

Execute ALTER USER ' root ' @ '% ' identified by ' aaabbb '; This is the record created.

In this case, client login will not succeed

If the password mode is indicated with Mysql_native_password in the Create/or modify command

For example, alter USER ' root ' @ '% ' identified with Mysql_native_password by ' aaabbb ';

After executing the user record as follows, the visible plugin is set to Mysql_native_password

At this point, the client can log in again after testing again.

Second, example: complete create a remote administrator account

1. Create a root user who can log in remotely

' Root '@'%' New password ';

Note that the "%" after @ allows remote login, with the Mysql_native_password modifier allows the old mode client to log in

2. Open Permissions for Account

Grant all privileges on * * to [email protected]'%';

Flush privileges;

Iii. Example: Create a regular user and assign a database to it

This is a common requirement in development, which is to specify all permissions within the database for a particular user

1. Add a general Account

' User name '@'%' password ';

2. Create a new database

Create DATABASE IF not EXISTS name; or simply create database name;

We used to specify the default UTF8 character set, and now the default character set for MySQL 8.0 is utf8mb4, so by default.

The default generated database character set is this

3. New Account Authorization New database

Grant all privileges the on database name. * To User name @'%';

For convenience the user name and database name are often set to the same for administration.

Attached: other commonly used commands

Usually less configuration database, a lot of commands are not familiar, so list up, with the time of copy and paste plus modification is the most convenient ...

Create user (with Mysql_native_password is optional, in order to accommodate client compatibility) create users'Root'@'%'Identified with Mysql_native_password by'Password'; set/Modify password alter USER'Root'@'localhost'Identified with Mysql_native_password by'Password'; ALTER USER'Root'@'localhost'Identified by'Password'; Alter user'Yzd'@'%'Identified by'AAA'; clear password alter USER'Root'@'localhost'Identified by"'; UPDATE User SET authentication_string="'WHERE user='Root'Delete User drop username @ localhost;delete from mysql.user Where user="User name"and host="localhost"; Assign permissions grant all privileges on*. * to [email protected]'%'Refresh permissions flush privileges; Create DATABASE IF not EXISTS yzd DEFAULT CHARSET UTF8 COLLATE utf8_general_ci; query Database list Show databases; Querying the data table list show tables; Querying the user listSelectHost,user,plugin,authentication_string fromMysql.user; Querying the current user nameSelectUser (); Query current users permissions show grants;

Mysql8 has a lot of new features, the password is lost after the operation is also different from the old version. These days for the restoration of a Python project background library password, tossing a lot of time, online mysql5 means anti-second-interview is not go through.

Later found, MYSQL8 has another password reset method, continue to explore the

"MySQL8" after the installation of the simple configuration (mainly to solve the navicat and other client landing error problems)