Remote Connection MYSQL8.0 Server issues

Title: Remote Connection MYSQL8.0 Server issue
date:2018-07-07 11:02:26
Updated
Tags: [MYSQL, Pit]
Description
Keywords
Comments
Image
---
Problem Description: previous use of MYSQL5.1, remote access to the MySQL server is not a problem, the problem is the firewall problem, modify the entry and exit rules after the easy solution. But MYSQL8.0 is very pit, according to the online solution to toss for two hours, finally resolved, still do not understand where there are problems, You can only reproduce the operation, leaving a record for later use.

Machine Environment

Operating system: WIN10
GUI Tools: navcat12
mysql:v8.011
Firewall Open Inbound port

# methods to modify the root user directly #
Run cmd in the bin directory before operation, or set environment variables for bin

1. Login to MySQL mysql-u root-p
2. Enter password after carriage return
3. Select the service to operate use MySQL; I am using MySQL as Name, enter the command according to your own server name
4. to view information about the current root user in the MySQL database's users table (host plugin) Select Host,user,authentication_ String,plugin from user;
   
   Displays a table after executing the command, and the root user hosts the default display of localhost, stating that only local access is supported and remote access is not allowed.
5. Authorize the root account first, here I give all permissions directly grant all privileges on * * to ' root ' @ '% ' with grant option;
6. Change the default configuration of root user host update user set host= '% ' where user= ' root ';
7. Execute the previous query command to see if the root host changes to localhost (if it fails, continue looking back)
8. then change the encryption rule alter USER ' root ' @ '% ' identified by ' password ' password EXPIRE never;
9. Update user password ALTER username ' root ' @ '% ' identified with Mysql_native_password by ' password ';
10. Refresh permissions flush privileges; After the
    
    succeeds, you can see that the root host has changed and plugin has become Mysql_native_password (formerly Mysql_na ...). But to modify it before you can really connect successfully, very metaphysical)

If the above steps are unsuccessful, please look down.

Ways to add users directly

Many people may be the same as me, the previous direct modification of the root user failed, and then I tried to directly add the user's method succeeded, first you want to make sure that there is no low-level error, the above command line is mostly by semicolons, please check carefully.

1. Start the cmd, until the third step is the same, first create a user Create user ‘name‘@‘%‘ identified by ‘password‘; name of their own password. You can see one more user after executing the query user command, where I created the else. Its host should be localhost, I forgot before. His plugin is caching_.
   

2. Authorization to usersgrant all privileges on *.* to ‘name‘@‘%‘ with grant option;
3. Modify Hostupdate user set host=‘%‘ where user=‘else‘;
4. Change the encryption methodALTER USER ‘else‘@‘%‘ IDENTIFIED BY ‘password‘ PASSWORD EXPIRE NEVER;
5. Update passwordALTER USER ‘else‘@‘%‘ IDENTIFIED WITH mysql_native_password BY ‘password‘;
6. Refresh PermissionsFLUSH PRIVILEGES;
7. The query user can see that the "%" of the else user's host is "%" plugin to "Mysql_native_password", which basically means success.

8. The problem is solved!

PostScript
My root modification was unsuccessful, but after creating the user and modifying the root again, I did not know what was going on.
In short, to run a remote connection, you must ensure that the server-side user's host is not "localhost" and plugin is "Mysql_native_password."
All the methods of operation are from the network, referring to a number of posts, did not record the source of the bad, sorry.
Finally, I wish you success!

This is your own problem-solving notes, if there are errors please comment or contact me thank you

Remote Connection MYSQL8.0 Server issues