Detailed solution to the problem that MySQL database cannot be connected remotely

Preface

When using MySQL database, you encounter this problem:

MySQL can be connected locally, but when the remote machine is connected, an error always occurs.Host ... is not allowed to connect to this MySQL server。

This is usually because MySQL's user rights or configuration restricts remote access.

1. Log in to the MySQL database

First, log in to MySQL using the local command line:

mysql -u root -p

Enter the root user's password to enter the database.

2. Switch to mysql system library

MySQL user permissions are stored inmysqlIn the database, we need to switch to it:

USE mysql;

3. View current user and host permissions

Run the following command to view the host settings of the current MySQL user:

SELECT User, Host FROM ;

You will see a table, for example:

+------------------+-----------+
| User             | Host      |
+------------------+-----------+
| root             | localhost |
|     | localhost |
|         | localhost |
+------------------+-----------+

NotedrootUser'sHostyeslocalhost, this means itOnly connect from the machine, cannot be connected by remote machines.

4. Modify the root user's Host to %

To allow the root user to connect from any host, it needs to beHostChange to%(Indicates any host):

UPDATE  SET host='%' WHERE user='root' AND host='localhost';

5. Refresh permissions take effect

After modifying the permissions, you need to refresh the permission table before it takes effect:

FLUSH PRIVILEGES;

Run the following command to view the host settings of the current MySQL user:

SELECT User, Host FROM ;

You will see a table where the root host is set to % to indicate that remote connections can be accepted

+------------------+-----------+
| User             | Host      |
+------------------+-----------+
| root             | %         |
|     | localhost |
|         | localhost |
+------------------+-----------+

If nothing unexpected happens, you can now connect remotely. If there are any problems, you can troubleshoot 6-7.

6. Check the MySQL configuration file

Sometimes, MySQL configuration files(or) will restrict the bound addresses and only listen to them by default127.0.0.1。

Open the configuration file:
```
vim /etc/
```
turn up[mysqld]Part, modified:
```
bind-address = 0.0.0.0
```
This means listening for all IPs, not only native ones.
After saving, restart MySQL:
```
systemctl restart mysqld
```

7. Confirm whether the firewall is open port 3306

The default MySQL port is3306, Linux as an example ensures that the server firewall allows remote access:

# Check the firewall statusfirewall-cmd --list-all

# Open port 3306 (firewalld example only)firewall-cmd --add-port=3306/tcp --permanent
firewall-cmd --reload

8. Summary

To resolve the problem that MySQL cannot connect remotely, you can follow the steps below to troubleshoot:

ReviseTable, put root'sHostChange to%
Refresh permissions
Check whether the link is possible. If not, check the configuration and port firewall
examineConfigure, ensurebind-addressyes0.0.0.0
Check whether the firewall is open port 3306

This is all about this article about the problem of MySQL database being unable to connect remotely. For more related content related to MySQL database being unable to connect remotely, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!