MySQL 8.0.17 installation and usage tutorial diagram

Written in front

In the past and in the current projects, the most commonly used relational databases are SqlServer or Oracle. Later, I took on a project that required development using MySQL as the database. So, have to learn to use it. However, there are still many problems encountered in the process of learning and applying it to actual development. The following are the problems and summary I encountered during the installation of MySQL. To prevent similar problems from happening again next time.

MySQL Installation

1. The system environment is Win10 Home Edition

2. Download the Windows version of MySQL installation package from the MySQL official website.

The address is: MySQL official website

After clicking Download, the following interface will pop up. Click No, tanks, just start my download.

3. After downloading, unzip the zip package to the directory you want to install. My path is as follows:

Because the computer has only one disk, it can only be placed in the C drive.

4. Configure environment variables

Go to Computer - Properties - Advanced System Settings - Environment Variables and add the path where MySQL is unzipped.

5. Configure MySQL configuration files

Open the folder C:\mysql-8.0.17-winx64\mysql-8.0.17-winx64 that you just unzipped, create a configuration file called my.ini, edit my.ini and configure the following basic information:

[mysql]
default-character-set=utf8
 
[mysqld]
port = 3306
basedir=C:\mysql-8.0.17-winx64\mysql-8.0.17-winx64
datadir=C:\mysql-8.0.17-winx64\mysql-8.0.17-winx64\data
character-set-server=utf8
default-storage-engine=INNODB 

6. Next, let's start the MySQL database

First, open the cmd command line tool as an administrator.


Then, change directories.

The command line is as follows:

cd C:\mysql-8.0.17-winx64\mysql-8.0.17-winx64\bin


Next, install MySQL. The commands are as follows.

mysqld install


Then, enter the initialization command.

mysqld --initialize-insecure --user=mysql


Then the data directory will be generated in the C:\mysql-8.0.17-winx64\mysql-8.0.17-winx64 directory

Finally, start by entering the following command.
The operation commands are as follows:

net start mysql


This proves that the connection has been successful.

Reset Password

1. Log in to MySQL

Enter the operation command:

mysql -u root -p

Because no password was set before, the password is empty, just press Enter:

2. Query user password

Query user password command:

mysql> select host,user,authentication_string from mysql.user;

Host: The IP address that allows the user to log in;

user: the user name of the current database;

authentication_string： user password;

If there is no password, the root line should be empty.


3. Set the root password

Note: The password field and password() function are deprecated in MySQL 5.7.9 and later.

Do not set passwords in the following ways:

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;

The correct steps to change the root password are:

Step 1. If there is content under the authentication_string field of the current root user, set it to empty first. If not, jump to step 2.

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

Step 2. Use ALTER to modify the root user password:

use mysql;
ALTER user 'root'@'localhost' IDENTIFIED BY 'new password';
FLUSH PRIVILEGES; 

So far, the local MySQL you created has been created! ! !
Just reconnect! ! !

Use the graphical interface software Navicat for SQL

1. Unzip the file and copy the key

2. Open navicat.exe in the folder

3. The user name is arbitrary, enter the key, and then connect to the database

4. Enter the password (if you have reset the password for MySQL before, just fill in the new password here), and change the connection name to your favorite

Problem: The connection fails here, as shown in the figure:

Analysis of the reasons:

The reason for this error is that the encryption rule in versions before mysql8 is mysql_native_password, while the encryption rule after mysql8 is caching_sha2_password.

Solution:

1. Open cmd with administrator privileges, enter mysql -u root -p to enter the password and enter the mysql database;

mysql -u root -p #進入數據庫

2. Modify the encryption rules and password and refresh;

ALTER user 'root'@'localhost' IDENTIFIED WITH mysql_native_password by 'new password';
FLUSH PRIVILEGES; #Refresh data 

Successfully connected! ! !

At this point, the installation of the required software and the environment configuration are all completed, and you can happily explore the world of MySQL~~

Summarize

The above is the MySQL 8.0.17 installation and usage tutorial introduced by the editor. I hope it will be helpful to everyone. If you have any questions, please leave me a message and the editor will reply to you in time. I would also like to thank everyone for their support of the 123WORDPRESS.COM website!
If you find this article helpful, please feel free to reprint it and please indicate the source. Thank you!