How to Allow Remote Connections to MySQL Database Server

Step 1Configure MySQL to Listen for Remote Connections

To let MySQL accept connections from other computers, we need to adjust its settings so it listens more broadly. By default, MySQL only listens to ‘localhost,’ meaning it only accepts connections from the same machine. You must tell it which IP addresses to listen on.

First, you need to tell MySQL which IP addresses it should listen on. By default, it only listens to localhost (your own computer).

Where you find the MySQL settings file depends on your operating system:

• 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

Look for the line that says bind-address. It usually looks like this:

bind-address           = 127.0.0.1

Change it to allow all connections:

bind-address           = 0.0.0.0

# skip-networking

Save the file and close it.

For Fedora or RHEL users:

Open ⚙️ the settings file:

sudo nano /etc/my.cnf

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

After telling MySQL to listen for outside connections, you must create a specific MySQL user account that can connect from another computer. This involves logging into MySQL and using a command to set up the username, the IP address it can connect from, and its password.

Log into MySQL as the root user:

sudo mysql

Or if you have a password set:

mysql -uroot -p

Then create a new user. Replace the values with your own:

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

Then give that user permission to access your database:

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

Let’s break down what each part means:

• 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:

To create a user named “john” who can connect from IP 10.8.0.5 with password “secure123” and access the “sales_db” database:

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 uses port 3306. Your firewall needs to allow traffic on this port. The steps differ based on your firewall tool.

For UFW (Ubuntu):

Allow access from a specific IP:

sudo ufw allow from 192.168.0.1 to any port 3306

Or allow from any IP (not recommended for security):

sudo ufw allow 3306/tcp

For iptables:

Allow access from a specific IP:

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

Or allow from any IP (not secure):

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

For FirewallD (Fedora/RHEL):

Create a new zone for MySQL access:

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

Or allow from any IP (not recommended):

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

Step 5Test Your Connection

From the remote computer, try to connect:

mysql -u john -h 192.168.0.1 -p

Replace john with your username and 192.168.0.1 with your MySQL server’s IP address. Enter the password when asked.

Troubleshooting

Common remote MySQL connection errors include ‘Can’t connect to MySQL server’ and ‘Host is not allowed to connect.’ The ‘Can’t connect to MySQL server’ error often points to your firewall blocking port 3306, or MySQL not listening on the expected IP address. The ‘Host is not allowed to connect’ error usually means the user account you’re using doesn’t have permission to connect from your specific IP address.

This usually means:

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

Error: “Host is not allowed to connect”

The MySQL user lacks permission from the specified IP address. Verify that the user was created with the correct IP address in the MySQL command.

Summary

To allow remote MySQL connections, you must edit the MySQL configuration file to change ‘bind-address’ to ‘0.0.0.0’, making it listen on all IP addresses, and then 🔄 restart the MySQL service. You also need to create a new MySQL user for remote access with specific IP permissions and ensure your firewall isn’t blocking 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

Admin/root access is required for all setup steps. If problems occur, confirm the user account has the correct IP address and that the firewall allows connections on port 3306.