Detailed explanation of MySQL installation and new password authentication method in MySQL 8.0

1. Introduction

Oracle has released MySQL 8.0GA. GA in foreign countries means release version, which means the official version, not the test version.

When installing MySQL, you will always see that in addition to the database service, a lot of services that are basically not used are also installed, so here is the installation process of MySQL. When I was trying to install MySQL 8.0, I found that my navicat could not connect to MySQL 8.0. After searching, I found that MySQL 8.0 provided two password encryption methods. I chose the new one when I installed it.

2. Install MySQL 8.0

2. 1. Add service in mysql installer

Click the add button.

Select custom, then next, so you can choose the services you want to install.

First, add MySQL server to be installed, and then click Next.

Just click next. If you need to change it, change it according to your needs.

Simply click execute to perform the installation.

Installation completed, next.

2.2. Configure MySQL

Next, start configuring MySQL settings

The default is next.

There is no need to do this, just use the default configuration, next. If you have other requirements, you can usually just change the port.

沒什么必要還是選擇以前的加密方式把，剛出來8.0，第三方客戶端基本都不支持這種加密方式，但自帶的命令行支持， Of course, I recommend this for the development environment. You can consider the server side yourself. The old encryption method in the development environment will not have any pitfalls. Select Use Legacy Authentication Method, then next.

Enter the root user’s password and click Next.

The default is next.

Click execute, write the configuration and add the windows service.

Click finish to complete the installation.

Then click next and finish to complete.

3. Start and stop the MySQL service

No detailed explanation is given here. Anyway, in the service tab of the task manager, you can turn the mysql service on and off. It is automatically started by default. I let it start automatically because it doesn't take up many resources.

4. About the new password authentication method of MySQL 8.0

The caching_sha2_password and sha256_password authentication plugins provide more secure password encryption than the mysql_native_password plugin, and caching_sha2_password provides better performance than sha256_password. Due to these superior security and performance characteristics of caching_sha2_password, it is as of MySQL 8.0 the preferred authentication plugin, and is also the default authentication plugin rather than mysql_native_password.

Translation: The caching_sha2_password and sha256_password authentication plug-ins are more secure than the password encryption provided by the mysql_native_password plug-in, and the caching_sha2_password encryption performs better than the sha256_password encryption. Due to its excellent security and performance features, caching_sha2_password is the preferred authentication plugin for MySQL 8.0. It is also the default authentication plugin instead of mysql_native_password.

You can visit this caching_sha2_password Compatibility Issues and Solutions to learn more about the solution to the problem of inaccessibility due to已經使用了新的加密方式. To summarize,

1. Change the encryption method to the old one and add the following to the configuration file my.conf:

[mysqld]
default_authentication_plugin=mysql_native_password

2. Use a client that supports the new encryption method, such as libmysqlclient equal to or higher than version 8.0.4

3. Use a connection driver (Connector) that supports the new encryption method:

MySQL Connector/C++ 1.1.11 or higher or 8.0.7 or higher.

MySQL Connector/J 8.0.9 or higher.

MySQL Connector/NET 8.0.10 or higher (through the classic MySQL protocol).

MySQL Connector/Node.js 8.0.9 or higher.

PHP: the X DevAPI PHP extension (mysql_xdevapi) supports caching_sha2_password.

4. Use the new encryption method and change to the old encryption method, and the root user must also make corresponding changes. Because the root user is still the new encryption method, use the alter statement to reset the password to cover the password of the new encryption method:

ALTER USER 'root'@'localhost'
 IDENTIFIED WITH mysql_native_password
 BY 'password';

password is the password you will set for the root user.

Reference article: Changes Affecting Upgrades to MySQL 8.0

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.