As a developer, you’ve probably encountered various MySQL errors while working with databases. One common issue that can be quite frustrating is the ERROR 1130 (HY000): Host '192.168.7.7' is not allowed to connect to this MySQL server. This error typically occurs when trying to connect to a MySQL server from a remote machine using tools like TablePlus or MySQL Workbench.
In this post, I’ll walk you through the steps to troubleshoot and resolve this connection error, so you can get back to developing without any interruptions. Let’s dive in!
Understanding the ERROR 1130
The error ERROR 1130 (HY000): Host '192.168.7.7' is not allowed to connect to this MySQL server occurs when your MySQL server rejects connections from a remote host (in this case, 192.168.7.7). This happens because the MySQL server's settings don't allow connections from that specific host or IP address.
This issue usually stems from a combination of MySQL’s host-based access control and network settings. But don’t worry! Fixing it involves a few simple steps.
Step 1: Check the MySQL User Privileges
The first thing we need to do is ensure that the MySQL user you're trying to connect with has the correct privileges for remote connections.
1.1 Log into MySQL Server
On the machine where MySQL is running, log in as a root user or any other user with the necessary privileges:
mysql -u root -p
Enter your password when prompted.
1.2 Check Existing Privileges
Once logged in, check the privileges for the user trying to connect remotely:
SELECT user, host FROM mysql.user;
This will show a list of all MySQL users and their associated hosts. If the user you're trying to use is restricted to localhost, it will not be able to connect from a remote IP address.
1.3 Grant Remote Access
If the user does not have permissions for remote access, you can grant the required privileges. For example, to allow the user username to connect from any host, you would run:
GRANT ALL PRIVILEGES ON database_name.* TO 'username'@'%' IDENTIFIED BY 'your_password';
This will allow connections from any host (% is a wildcard for any IP address).
If you want to restrict it to a specific IP (e.g., 192.168.7.7), replace % with that IP:
GRANT ALL PRIVILEGES ON database_name.* TO 'username'@'192.168.7.7' IDENTIFIED BY 'your_password';
1.4 Flush Privileges
After granting the privileges, don’t forget to reload the privileges to ensure they take effect:
FLUSH PRIVILEGES;
Step 2: Configure MySQL to Listen on All IP Addresses
By default, MySQL may be configured to listen only on localhost (127.0.0.1), which means it won't accept connections from remote machines.
2.1 Edit the MySQL Configuration File
On the MySQL server, open the configuration file (my.cnf or my.ini, depending on your operating system). The location of the file varies, but it’s typically found at /etc/mysql/my.cnf or /etc/my.cnf.
Use your preferred text editor (like nano or vim):
sudo nano /etc/mysql/my.cnf
2.2 Modify the bind-address
Look for the bind-address directive. If it is set to 127.0.0.1, change it to 0.0.0.0 to allow MySQL to listen on all available IP addresses:
bind-address = 0.0.0.0
Save and close the file.
2.3 Restart MySQL
For the changes to take effect, restart MySQL:
sudo systemctl restart mysql
Step 3: Check Firewall and Security Group Settings
Even after granting the necessary privileges and configuring MySQL to listen on all IPs, your server’s firewall might still block the connection.
3.1 Open the MySQL Port (3306)
MySQL uses port 3306 for client connections. Ensure that this port is open on the server’s firewall. On Ubuntu, for example, you can allow traffic on port 3306 with the following command:
sudo ufw allow 3306
If you're using a cloud provider like AWS, make sure the Security Group attached to your MySQL instance allows inbound traffic on port 3306 from the remote host's IP address.
3.2 Verify the Connection
Finally, test the connection from the client machine (in this case, TablePlus). Try connecting again, and you should no longer see the ERROR 1130 (HY000) message.
Step 4: Double-Check TablePlus Settings
Sometimes, the issue may not be on the MySQL server side, but rather with how TablePlus is configured.
4.1 Verify Host and Port
Make sure you’re connecting to the correct IP address and port. Double-check the following in TablePlus:
-
Host: Should be the IP address of your MySQL server (e.g.,
192.168.7.7or the external IP if connecting remotely). -
Port: The default MySQL port is
3306. - User: The MySQL username.
- Password: The correct password for the MySQL user.
4.2 Test Connection
Click on the Test button in TablePlus to verify the connection. If everything is set up correctly, you should be able to connect without issues.
Top comments (0)