How to Allow Remote Connections to MySQL Database Server

Step 1Configure MySQL to Listen for Remote Connections

The bind-address setting tells MySQL which network interface to listen on. While it points at localhost, the server ignores traffic from other machines. Changing it to 0.0.0.0 makes MySQL listen on all IPv4 interfaces of the server, so you can then control who gets in with user accounts and firewall rules.

This section covers MySQL on Linux. The location of the configuration file depends on the distribution:

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

Make a copy of the file before you change it. If something goes wrong, you can put the original back.

For Ubuntu or Debian users:

1. Open the settings file in a text editor, using the command below. You should see the contents of the configuration file.

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

2. Find the bind-address parameter. By default it points at the local machine.

bind-address           = 127.0.0.1

3. Change the value so MySQL accepts connections on all interfaces, as shown below.

bind-address           = 0.0.0.0

4. Look for a skip-networking line further down. If it exists, put a # character at the start of the line. This comments the line out and disables that restriction.

# skip-networking

5. Save the file and exit the editor.

The change does nothing until MySQL is restarted. The restart commands are further down this section.

For Fedora or RHEL users:

1. Open the settings file with a text editor, as shown below.

sudo nano /etc/my.cnf

2. Find the bind-address line beneath the [mysqld] section. If it is not there, add it under that section and set it to 0.0.0.0. The line must sit inside that section, or MySQL will ignore it.

Step 2Restart MySQL

3. Save the file and exit the editor.

⚠️ Admin privileges required

Restart the MySQL service so it reads the new setting. Use the command for your distribution.

For Ubuntu or Debian:

sudo systemctl restart mysql

For Fedora or RHEL:

sudo systemctl restart mysqld

If the service fails to start, you most likely have a typo in the configuration file. Re-open the file and check the lines you edited. To undo the change, restore your backup copy or set the bind address back to its old value, then restart the service again.

Step 3Create a User for Remote Access

MySQL accounts are tied to a host as well as a name. An account created for localhost cannot be used from another computer, even with the right password. You need a separate account that names the remote computer's IP address, or one that allows any address. You create it from the MySQL shell as the root database user.

1. Log into MySQL as the root user, using the command below. You should land at the MySQL prompt.

sudo mysql

If your root account uses a password instead, authenticate with it using this form:

mysql -uroot -p

2. Create the new user account. Replace the placeholder values with your own details.

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

3. Grant privileges on the database to the new account.

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

Each placeholder in these commands has a specific purpose:

• 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

Allowing any IP means anyone who can reach the server can try to log in as that user. Give the account access only to the database it needs, and nothing more.

Example:

To create an account named "john" that connects from IP 10.8.0.5 with the password "secure123" and works with the "sales_db" database, use this syntax. Choose a stronger password on a real server.

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

MySQL communicates on port 3306. Even when MySQL is listening on the network and the user exists, the server's firewall will drop remote traffic until you allow this port. The commands differ by firewall tool, so use the group that matches the one on your server. If your server runs in the cloud, check the provider's security group or network firewall as well. It can block port 3306 even when the server's own firewall is open.

For UFW (Ubuntu):

Permit access from one designated IP address. This is the safer choice.

sudo ufw allow from 192.168.0.1 to any port 3306

Allowing connections from any IP works, but it is insecure. Use it only for short tests, and remove the rule afterwards.

sudo ufw allow 3306/tcp

For iptables:

Permit access from one designated IP address:

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

Allowing connections from any IP is insecure, so avoid it on a server that is reachable from the internet:

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

For FirewallD (Fedora/RHEL):

With this firewall tool, first create a dedicated network zone for database traffic:

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 is insecure, so avoid it on a server that is reachable from the internet:

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

Rules in some of these tools need to be reloaded, or saved, before they take effect or survive a reboot. Check the output of the commands above for any message about this.

Step 5Test Your Connection

Test the connection from the remote workstation, not from the server itself. A test from the server only proves the local connection works. The remote computer needs a MySQL client installed.

1. Run the command below on the remote computer.

mysql -u john -h 192.168.0.1 -p

2. Replace john with the username you created and 192.168.0.1 with the address of the database server.
3. Enter the password when prompted. You should arrive at the MySQL prompt on the remote server.

Troubleshooting

Remote connection failures usually have one of three causes: the firewall is blocking port 3306, the bind-address is wrong in the configuration file, or the database user has no permission for that client's IP address. Work through them in that order, checking the firewall rules, the network listener and the user account.

The likely root causes are:

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

If the connection times out, the firewall is the usual cause. Check the server's firewall and any cloud security group first. If the connection is refused immediately, MySQL is probably not listening on the network address. Confirm the bind-address change was saved and that you restarted the service.

Error: "Host is not allowed to connect"

This error means the server was reached, but the user account has no permission for the client's IP address. An account created for one host does not work from another. Check that the host part of the account matches the IP address the client actually connects from. Then create the account again with the correct address, or add a second account for the new address.

Summary

To allow remote connections to a MySQL server, you edit the configuration file to change the bind address, restart the service, create a user with remote access, and open port 3306 in the firewall. Once all four are done, other computers and applications can connect to the database over the network.

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

You need administrator rights for all of these steps. If connections drop or fail unexpectedly, check the IP binding and the firewall rules first. Where you can, limit access to specific IP addresses rather than opening the server to everyone.