Detailed explanation of the idea of ​​installing mysql8.0.11 and changing the root password and connecting navicat for mysql

1.1. Download:

Download the zip package from the official website. I downloaded the 64-bit version:

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

Download the zip package:

Unzip after downloading: (You can unzip it on any disk)

I put it here E:\web\mysql-8.0.11-winx64, and shortened the file name, so it is E:\web\mysql-8.0.11.

1.3. Generate data file:

Run cmd as administrator

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

Enter E:\web\mysql-8.0.11\bin

Execute the command: mysqld --initialize-insecure --user=mysql to create a data directory in the E:\web\mysql-8.0.11 directory

1.4. Start the service:

Execute the command: net start mysql to start the MySQL service. If it prompts: The service name is invalid... (please see step: 1.5);

1.5. Solve the problem of failure to start the service (error):

Tip: The service name is invalid

Solution:

Execute the command: mysqld -install (no my.ini configuration file is required. Note: Many articles on the Internet require a my.ini configuration file. In fact, it is also possible without a my.ini configuration file. I placed a my.ini file before, but it prompted that the service could not be started. After deleting my.ini, it started successfully)

If the prompt "The service is starting or stopping, please try again after a while" appears, you need to end all MySQL processes in the resource manager and restart them.

1.6. Log in to mysql:

Log in to mysql: (Because no password was set before, the password is blank, no need to enter a password, just press 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: The IP address 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 will not be able to connect using navicat below, so we need to change the root password.

This is a critical step. I have fallen into many traps here. Later, I found out after a lot of research that the password field and password() function were abandoned after MySQL 5.7.9; the authentication_string: field represents the user password.

The following is a direct demonstration of the steps to correctly modify the root password:

1. If there is content under the authentication_string field of the current root user, set it to empty first, otherwise proceed directly to step 2.

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

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

2. Use ALTER to modify the root user password. The method is 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. No need to flush privileges to refresh permissions.

2. The password must contain uppercase letters, lowercase letters, numbers, and special symbols.

The modification was successful; Log in again with your username and password;

Note: Do not use the following passwords:

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 you use ALTER USER 'root'@'localhost' IDENTIFITED BY 'newpassword' again, an error will be reported;

Because the authentication_string field can only contain a 41-digit string password encrypted by MySQL; other values ​​will be reported as format errors;

*THISIS NOT AVALIDPASSWORDTHATCANBEUSEDHERE

At this point, the installation of mysql and modification of the root password have come to an end.

Start with navicat for mysql.

The account and password are correct, but the connection reports error 1251. OK, let's take a look at this change first:

Before MySQL 8.04, execute: SET PASSWORD=PASSWORD('[new password]'); However, starting from MySQL 8.0.4, this is not allowed by default. Because before, the password authentication plugin for MySQL was "mysql_native_password", and now it uses "caching_sha2_password".

So, we need to change the root password again.

First log in to the MySQL environment: execute the following three commands. (Remember to include the semicolon)

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

OK. Now reconnect. perfect!

After so much trouble, I can finally start my SQL journey. Congratulations! Sprinkle flowers! Sprinkle flowers!

I had prepared some screenshot instructions, but I found that inserting the picture failed, so I'll just leave it like this for now.

Summarize

The above is a detailed introduction of how to install MySQL 8.0.11, modify the root password, and connect to Navicat for MySQL. I hope it will be helpful to you. If you have any questions, please leave me a message and I will reply to you in time. I would also like to thank everyone for their support of the 123WORDPRESS.COM website!