A brief discussion on the pitfalls and solutions of the new features of MySQL 8.0 (summary)

1. Create users and authorize

Creating users and authorization in MySQL 8.0 are different from before. Strictly speaking, it cannot be said to be different, but it can only be said to be stricter. MySQL 8.0 requires creating users and setting passwords before authorization.

#Create a user first create user 'hong'@'%' identified by '123123';

#Authorize grant all privileges on *.* to 'hong'@'%' with grant option;

If you still use the original 5.7 method, an error will be reported:

grant all privileges on *.* to 'sroot'@'%' identified by '123123';

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'identified by '123123'' at line 1

2. Remote connection of MySQL 8.0

After MySQL 8.0 is installed, it cannot be connected remotely. This is because MySQL 8.0 only supports localhost access. We must set it up before we can access it remotely.

The specific setting steps are as follows:

① Log in to MySQL

The execution command is: mysql -u root -p

Enter the password after pressing Enter

② Select MySQL database

The execution command is: use mysql;

View the user table that stores user information in the MySQL database.

③ View the relevant information of the current root user in the user table of the mysql database

The execution command is: select host,user,authentication_string,plugin from user;

After the command is executed, a table is displayed. The host of the root user is localhost by default, indicating that only local access is supported and remote access is not allowed.

④ Change the default configuration of the host

The execution command is: update user set host='%' where user='root';

⑤ Refresh

The execution command is: flush privileges;

I thought that was all, but when I used Navicat to connect to MySQL remotely, a pop-up window popped up with an error:

The reason for this is that the encryption rule in versions before mysql8 is mysql_native_password, and after mysql8, the encryption rule is caching_sha2_password. There are two ways to solve the problem. One is to upgrade the navicat driver, and the other is to restore the encryption rule of the mysql user login password to mysql_native_password. I use the second method:

ALTER USER 'root'@'%' IDENTIFIED BY 'password' PASSWORD EXPIRE NEVER; #Modify encryption rules
 ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY 'password'; #Update the user's password FLUSH PRIVILEGES; #Refresh permissions

The problem is solved.

Notes on setting mysql8.0 user password

In MySQL 8.0.11, caching_sha2_password is the default authentication plugin instead of mysql_native_password. For information about how this change affects server operation and server compatibility with clients and connectors, see caching_sha2_password as the Preferred Authentication Plugin. (Translated from https://dev.mysql.com/doc/refman/8.0/en/caching-sha2-pluggable-authentication.html)

Today, when configuring and installing mysql8.0.11 on a new service, I set the mysql password as usual. After the setting is successful, I enter mysql -u root -p in the shell, and then enter the password to enter normally. However, in phpmyadmin or directly using the connection on http://php.net/manual/zh/mysqli.real-connect.php, it prompts that the connection cannot be made. The specific error message is

mysqli_real_connect(): The server requested authentication method unknown to the client [sha256_password]

After searching around and finding the official documentation, I discovered that starting from version 8.0.11, the default verification method for setting user passwords is no longer caching_sha2_password, as in MySQL 5.7 and previous versions. If you find that the original program cannot connect to MySQL after upgrading to MySQL 8.0.11, you can quickly use the following command in the MySQL command line client to set it to the password verification method of MySQL 5.7 and previous versions. At the same time, the way to change passwords under MYSQL 8.0.11 is also slightly different from the original one, and some of the original password change commands cannot be used under MySQL 8.0.11.

> use mysql 

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

> FLUSH PRIVILEGES; 

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.