How to Allow Remote Connections to MySQL Database Server

Step 1Configure MySQL to Listen for Remote Connections

To accept remote connections, you need to change how MySQL listens for incoming traffic. Operating systems limit default listening scope to localhost, meaning the software only answers queries originating on the local machine.

MySQL only listens to requests from the local machine by default. Location of the configuration file depends on your operating system.

Configuration file paths vary by operating system distribution:

• Ubuntu/Debian: /etc/mysql/mysql.conf.d/mysqld.cnf
• Fedora/RHEL: /etc/my.cnf

For Ubuntu or Debian users:

Open the settings file with a text editor:

sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

Locate the bind-address parameter within the text:

bind-address           = 127.0.0.1

Modify the value to accept all incoming connections:

bind-address           = 0.0.0.0

Check for a skip-networking line next. Inserting a # character at the beginning disables that restriction:

# skip-networking

Save the changes and exit the editor.

For Fedora or RHEL users:

Open the settings file:

sudo nano /etc/my.cnf

Locate or add the bind-address line beneath the [mysqld] section, assigning it a value of 0.0.0.0.

Step 2Restart MySQL

⚠️ Admin privileges required

For Ubuntu or Debian:

sudo systemctl restart mysql

For Fedora or RHEL:

sudo systemctl restart mysqld

Step 3Create a User for Remote Access

Once external network listening is enabled, create a dedicated database user account for remote authentication. Logging into the database shell allows executing queries that establish the username, allowed origin IP, and authentication credentials.

Log into MySQL as the root user:

sudo mysql

Authenticate with an existing password instead:

mysql -uroot -p

Create a new user account while substituting placeholder values with actual details:

CREATE USER 'user_name'@'ip_address' IDENTIFIED BY 'user_password';

Grant database privileges to the newly created account:

GRANT ALL ON database_name.* TO 'user_name'@'ip_address';

Each placeholder serves a specific purpose in the command structure.

• user_name = the name of the new user
• ip_address = the IP address of the remote computer (use % to allow any IP)
• user_password = the password for this user
• database_name = the database the user can access

Example:

Establishing an account named "john" connecting from IP 10.8.0.5 with password "secure123" targeting the "sales_db" database follows this syntax:

CREATE USER 'john'@'192.168.0.1' IDENTIFIED BY 'secure123';
GRANT ALL ON sales_db.* TO 'john'@'192.168.0.1';

Step 4Open the Firewall

⚠️ Admin privileges required

Port 3306 handles database communication. Operating system firewalls require explicit rules allowing traffic on this port, though specific commands vary by utility.

For UFW (Ubuntu):

Permit access from a designated IP address:

sudo ufw allow from 192.168.0.1 to any port 3306

Allowing connections from any IP remains insecure:

sudo ufw allow 3306/tcp

For iptables:

Permit access from a designated IP address:

sudo iptables -A INPUT -s 192.168.0.1 -p tcp --destination-port 3306 -j ACCEPT

Allowing connections from any IP remains insecure:

sudo iptables -A INPUT -p tcp --destination-port 3306 -j ACCEPT

For FirewallD (Fedora/RHEL):

Establish a dedicated network zone for database communication:

sudo firewall-cmd --new-zone=mysqlzone --permanent
sudo firewall-cmd --reload
sudo firewall-cmd --permanent --zone=mysqlzone --add-source=192.168.0.1/32
sudo firewall-cmd --permanent --zone=mysqlzone --add-port=3306/tcp
sudo firewall-cmd --reload

Allowing connections from any IP remains insecure:

sudo firewall-cmd --permanent --zone=public --add-port=3306/tcp
sudo firewall-cmd --reload

Step 5Test Your Connection

Test connectivity from the remote workstation:

mysql -u john -h 192.168.0.1 -p

Substitute john with the target username and 192.168.0.1 with the server address, providing the password when prompted.

Troubleshooting

Troubleshooting remote connections uncovers common hurdles like 'Can't connect to MySQL server' and 'Host is not allowed to connect.' Firewall blocks or misconfigured network listeners trigger connection failures, while permission mismatches cause host rejection errors.

Potential root causes include:

• Port 3306 is blocked by the firewall
• MySQL is not listening on the right IP address

Error: "Host is not allowed to connect"

User permissions lack authorization for the originating client IP address. Confirming account creation parameters resolves the mismatch.

Summary

Enabling remote MySQL access involves editing configuration files to set bind-address to 0.0.0.0, restarting the MySQL service, creating scoped user accounts, and opening firewall port 3306.

1. Edit the MySQL config file and change bind-address to 0.0.0.0
2. Restart the MySQL service
3. Create a new MySQL user with a specific IP address
4. Give that user permission to access your database
5. Open port 3306 in your firewall
6. Test the connection from the remote computer

Administrative rights govern all configuration procedures. Verifying IP bindings and firewall rule sets resolves unexpected connection drops.