Resolving MySQL "Connection Refused" Error by Adjusting `bind-address` and Hostname Print

When clients face the "Connection refused" error while trying to connect to a MySQL database, it is often due to misconfigurations related to the bind-address in MySQL and the hostname used in their connection scripts. This guide will walk you through the steps to resolve this issue.

Understanding the bind-address Setting

The bind-address configuration in MySQL specifies the network interfaces that MySQL will listen to for incoming connections. It can be set to:

• 127.0.0.1: MySQL listens only for connections from the local machine.
• 0.0.0.0: MySQL listens for connections from any network interface, allowing remote connections.

Common Scenario

A common scenario where this issue arises is when a client uses a domain name or server name as the hostname in their connection script, but MySQL is configured to listen only on 127.0.0.1. This causes remote connection attempts to fail with a "Connection refused" error.

Steps to Resolve the Issue

1. Check and Update the bind-address Setting

First, ensure that your MySQL server is configured to accept remote connections. You can do this by updating the bind-address in the MySQL configuration file.

1. Edit the MySQL Configuration File:

   Locate and edit the MySQL configuration file, typically found at /etc/my.cnf or /etc/mysql/my.cnf.

2. Change the `bind-address`:

 

Find the `bind-address` line and update it to `0.0.0.0` to allow MySQL to listen on all network interfaces.

ini

[mysqld]

bind-address = 0.0.0.0

3. Restart the MySQL Service:

 

Apply the changes by restarting the MySQL service.

sudo systemctl restart mysqld

2. Verify MySQL User Permissions

Ensure that the MySQL user has the correct permissions to connect from the remote host.

1. Log in to MySQL:

mysql -u root -p

2. Grant Permissions to the User:

 

Allow the user to connect from any host (`%`) or specify the client's IP address.

sql

GRANT ALL PRIVILEGES ON database_name.* TO 'username'@'%' IDENTIFIED BY 'password';

FLUSH PRIVILEGES;

3. Adjust the Hostname in Client Connection Script

If the client is using a domain name or server name as the hostname, ensure that it resolves correctly and is reachable. Modify the connection script as follows:php

function OpenCon()

{

$servername = "your_server_domain_or_ip"; // Use the server's domain name or IP address

$username = "your_username";

$password = "your_password";

$dbname = "your_database_name";

// Create connection

$conn = new mysqli($servername, $username, $password, $dbname);

// Check connection

if ($conn->connect_error) {

die("Connection failed: " . $conn->connect_error);

}

return $conn;

1. Firewall Configuration:

 

Ensure that the firewall allows MySQL connections on the default port (3306).

sudo ufw allow 3306/tcp

2. SELinux:

 

If SELinux is enabled, you might need to adjust its policies to allow MySQL connections.

sudo setenforce 0

3. Network Encryption:

 

For secure connections, consider using SSL/TLS to encrypt the data between the client and the server.

Conclusion

By updating the bind-address to 0.0.0.0 and ensuring proper hostname usage in connection scripts, clients can resolve the "Connection refused" error and successfully connect to the MySQL server. Always remember to implement additional security measures when allowing remote connections to safeguard your database.