Detailed explanation of MySQL remote connection permission

1. Log in to MySQL database

mysql -u root -p

View the user table

mysql> use mysql;
Database changed
mysql> select host,user,password from user;
+--------------+------+-------------------------------------------+
| host | user | password |
+--------------+------+-------------------------------------------+
| localhost | root | *A731AEBFB621E354CD41BAF207D884A609E81F5E |
| 192.168.1.1 | root | *A731AEBFB621E354CD41BAF207D884A609E81F5E |
+--------------+------+-------------------------------------------+
2 rows in set (0.00 sec)

You can see the root user that has been created in the user table. The host field indicates the host being logged in. Its value can be either IP or host name.

(1) If you want to log in using your local IP address, you can change the Host value to your own IP address.

2. Realize remote connection (authorization method)

Changing the value of the host field to % means that you can log in to the MySQL server as the root user on any client machine. It is recommended to set it to % during development.
update user set host = '%' where user = 'root';

Change the permissions to ALL PRIVILEGES

mysql> use mysql;
Database changed
mysql> grant all privileges on *.* to root@'%' identified by "password";
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> select host,user,password from user;
+--------------+------+-------------------------------------------+
| host | user | password |
+--------------+------+-------------------------------------------+
| localhost | root | *A731AEBFB621E354CD41BAF207D884A609E81F5E |
| 192.168.1.1 | root | *A731AEBFB621E354CD41BAF207D884A609E81F5E |
| % | root | *A731AEBFB621E354CD41BAF207D884A609E81F5E |
+--------------+------+-------------------------------------------+
3 rows in set (0.00 sec)

This way the machine can remotely access MySql on the machine with the username root and password root.

3. Realize remote connection (table modification method)

use mysql;

update user set host = '%' where user = 'root';

In this way, you can access Mysql remotely through the root user.

4. If the above method does not work

This may be caused by the access to port 3306 restricted by the corresponding server. The following is an example of Tencent Cloud:

Only by opening port 3306 can the connection be successful!

The above is the detailed explanation and integration of MySQL remote connection permissions 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!