How to Allow Remote Connections to MySQL Database Server

By default, MySQL only accepts connections from your own computer. But sometimes you need to connect to MySQL from a different computer. This guide shows you how to set it up.

When Do You Need Remote MySQL Access?

You might need remote access if:

• Your application runs on a different server than your database
• You want to manage the database from your local computer
• Multiple servers need to talk to the same database

There are two main ways to connect to a remote MySQL server. You can use an SSH tunnel, or you can configure MySQL to accept remote connections. This guide covers the second method.

Note: These same steps work for MariaDB too.

Step 1: Configure MySQL to Listen for Remote Connections

⚠️ Admin privileges required

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

The MySQL settings file is in different places depending on your 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

Also look for a line called skip-networking. If it exists, add a # at the start to turn it off:

# skip-networking

Save the file and close it.

For Fedora or RHEL users:

Open the settings file:

sudo nano /etc/my.cnf

Find or add the bind-address line under the [mysqld] section and set it to 0.0.0.0.

Step 2: Restart MySQL

⚠️ Admin privileges required

For Ubuntu or Debian:

sudo systemctl restart mysql

For Fedora or RHEL:

sudo systemctl restart mysqld

Step 3: Create a User for Remote Access

Now you need to create a MySQL user that can connect from a remote computer.

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';

What these mean:

• 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 4: Open 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 5: Test 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

Error: “Can’t connect to MySQL server”

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”

This means the user you created doesn’t have permission from that IP address. Check that you created the user with the correct IP in the command.

Summary

To allow remote connections to MySQL:

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

All these steps require admin/root access. If you run into problems, double-check that the user was created with the correct IP address and that your firewall is allowing connections on port 3306.