Detailed explanation of the idea of installing mysql8.0. 11 modifying root password and connecting navicat for mysql

1.1. Download:

Official website downloaded zip package, and I downloaded 64-bit:

Download address: https://dev.mysql.com/downloads/mysql/

Download the package for zip:

Decompress after downloading: (Decompress on any disk)

I put it here E:\ web\ mysql-8. 0.11-winx64, shortening the filename by the way, so it's E:\ web\ mysql-8. 0.11.

1.3. Generate an data file:

Run cmd as an administrator

Program-Enter cmd to find cmd. exe and right click to run as administrator

Enter E:\ web\ mysql-8. 0.11\ bin

Execute a command: mysqld --initialize-insecure --user=mysql  Generate the data directory under the E:\ web\ mysql-8. 0.11 directory

1.4. Start the service:

Execute a command: net start mysql  Start the mysql service, if prompted: the service name is invalid... (see step: 1.5);

1.5. Resolve service startup failure (error report):

Tip: Invalid service name

Solution:

Execute a command: mysqld -install  You can do it (you don't need my. ini configuration file Note: Many of them written on the Internet need my. ini configuration file, but you don't need my. ini configuration file. I put my. ini file before, but it prompts that the service can't start, and it started successfully after deleting my. ini)

If the prompt "The service is starting or stopping, please try again later", you need to go to the resource manager to finish the mysql process and restart it.

1.6. Login to mysql:

Login mysql: (Because the password was not set before, the password is empty, so you don't need to enter the password, just enter)

E:\mysql-5.7.20-winx64\bin > mysql -u root -p

Enter password:

1.7. Query user password:

Query user password command: mysql > select host,user,authentication_string from mysql.user;

host: ip'location 'that allows users to log in% indicates that it can be remote;

user: The user name of the current database;

authentication_string: User password (this field is mentioned later);

1.8. Set (or modify) the root user password:

If the default root password is empty, you can't connect with navicat below, so we need to modify the password of root.

This is a crucial step. I stepped on many pits of N here, and later consulted a lot to know that the password field and password () function were discarded after mysql 5.7. 9; authentication_string: The field represents the user password.

The following directly demonstrates the steps to correctly modify the root password:

1. If there is content in the field of authentication_string of the current root user, set it to null first, otherwise, proceed directly to 2 steps.

use mysql; 
update user set authentication_string='' where user='root'

3. The following steps directly demonstrate the correct modification of the root password:

2. Modify the root user password using ALTER as ALTER user 'root' @ 'localhost' IDENTIFIED BY 'New Password'. As follows:

ALTER user 'root'@'localhost' IDENTIFIED BY 'Cliu123#'

There are two points to note here:

1. flush privileges is not required to refresh permissions.

2. Passwords should contain uppercase letters, lowercase letters, numbers and special symbols.

Successful modification; Re-use the username and password to log in;

Note: 1 Do not take the following form of the password:

use mysql; 
update user set authentication_string="newpassword" where user="root"; 

This will set the newpassword value under the authentication_string field of the root user in the user table;

When reused ALTER USER 'root'@'localhost' IDENTIFITED BY 'newpassword' Errors will be reported when;

Because the authentication_string field can only be a 41-bit string password encrypted by mysql; Other reports are in wrong format;

*THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE

At this point, the installation of mysql and the modification of root password are in paragraph 1.

Start navicat for mysql.

The account password is correct, and the connection error is 1251. OK Let's look at this change first:

Before MySQL 8.04, execute: SET PASSWORD=PASSWORD ('[new password]'); But MySQL 8.0. 4 starts, so the default is not possible. Because before, the password authentication plug-in of MySQL was "mysql_native_password", but now it is "caching_sha2_password".

so, we need to change the root password once again.

Log in to the mysql environment first: Execute the following three commands. (Remember to bring a semicolon.)

use mysql ; 
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY ' New password ';
FLUSH PRIVILEGES;

OK. Now reconnect. perfect!

After tossing for so long, I can finally start my SQL road, sprinkle flowers! Sprinkle flowers! Sprinkle flowers!

There are screenshots that were prepared originally, and it was found that the insertion of pictures failed, so let's do it first.

Summarize