How to set remote access permissions in MySQL 8.0

The previous article explained how to reset the MySQL password. Some students reported that they could not connect to the database. This is because MySQL only supports localhost access after installation. We must set it up before we can access it remotely. In addition, there are some problems when connecting to MySQL 8.0. This article will also explain how to solve them.

1. Log in to MySQL

mysql -u root -p

Enter your password

2. Select MySQL database

use mysql;

Because the user table in the mysql database stores user information.

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

select host, user, authentication_string, plugin from user; 

After executing the above command, a table will be displayed

Check the host of the root user in the table. By default, it should show localhost, which only supports local access and does not allow remote access.

4. Authorize all permissions of the root user and set up remote access

GRANT ALL ON *.* TO 'root'@'%';

GRANT ALL ON means all permissions, and % means all hosts are wildcarded, allowing remote access.

5. Refresh permissions

After all operations, you should execute

flush privileges;

6. Check the host of the root user

Execute step 2 again, and you will find that the host of the root user has become %, which means that our modification has been successful and can be accessed remotely.

7. Access the database

There are many visualization tools for remote access to databases, such as Navicat, SQLyog, MySQL workbench, etc. I use Navicat here.

Enter the access host and password, and error 2059 will be reported. This is because the encryption rules of MySql 8.0 and 5.0 are different, and the current visualization tool only supports the old encryption method.

There are two ways to solve this problem. One is to update the Navicat driver to solve this problem, and the other is to change the encryption rule of MySQL user login to mysql_native_password. I tried the first method and it didn't work, so I'll use the second method here.

8. Modify encryption rules

ALTER USER 'root'@'localhost' IDENTIFIED BY 'password' PASSWORD EXPIRE NEVER; 

password is your current password.

9. Update the root user password

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

password is the new password you set.

10. Refresh permissions

FLUSH PRIVILEGES;

OK, the settings are complete, use Navicat to connect to the database again

This is the end of this article about how to set remote access permissions for MySQL 8.0. For more information about remote access permissions for MySQL 8.0, please search for previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope you will support 123WORDPRESS.COM in the future!