How to Allow Remote Connections to MySQL Database Server

When Do You Need Remote MySQL Access?

You need remote MySQL access when your application and database aren’t on the same computer, or if you want to manage your database from a different machine. It’s also useful when multiple servers need to connect to the same database. This lets you work with your database from anywhere.

• 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 1Configure MySQL to Listen for Remote Connections

To let MySQL accept connections from other computers, you first need to change its settings so it listens on more than just your local machine. You’ll edit the MySQL configuration file, which is usually found in different places depending on your operating system, like ‘/etc/mysql/mysql.conf.d/mysqld.cnf’ on Ubuntu.

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

Next, you’ll create a special MySQL user account that’s allowed to connect from a different computer. You do this by logging into MySQL and using a command like ‘CREATE USER ‘your_username’@’your_ip_address’ IDENTIFIED BY ‘your_password’;’ to set up the new user and their 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';

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

If you get a ‘Can’t connect to MySQL server’ error, it usually means your computer’s firewall is blocking the connection on port 3306, or MySQL isn’t set up to listen for connections from your IP address. Another common issue, ‘Host is not allowed to connect,’ means the user you created doesn’t have permission from the specific IP you’re trying to connect from.

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

Allowing remote MySQL connections involves changing the MySQL configuration file to listen on all IP addresses, restarting the MySQL service, and creating a new user with specific remote access permissions. You also need to ensure your firewall allows connections on port 3306 and then test from your remote computer.

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.