How to Configure MySQL Remote Access

By default, MySQL only allows connections from the same machine it’s installed on. That’s good for safety, but what if you want to manage your database from your home computer, or let your web app on another server talk to it?

That’s when remote access comes in. And don’t worry, it’s not as scary as it sounds.

In this post, I’ll show you how to make your MySQL database listen for connections from other machines. I’ll also help you avoid the common mistakes I made the first time I did this.

We’ll walk through it step by step. I’ll explain the terms, keep the paragraphs short, and even throw in a few bad puns. So, let’s “connect” and make MySQL less mysterious.


What Is Remote Access?

Remote access just means being able to connect to a computer or service (like MySQL) from another place. Instead of being on the same machine, you’re somewhere else—maybe across the room or even across the world.

In the case of MySQL, remote access lets another computer talk to the database. This is useful when:

  • You’re developing a website on one server and the database is on another
  • You want to manage the database from your laptop using tools like DBeaver or HeidiSQL
  • You have multiple apps sharing one central database

But remote access can also be risky. That’s why MySQL blocks it by default. We’ll open the door—but just a crack.


Before You Start

Here’s what you’ll need:

  • A Linux server with MySQL or MariaDB installed
  • A user with sudo access
  • Another computer that will connect remotely
  • The IP address of that other computer

I’ll be using Ubuntu in this guide, but the steps are similar on most Linux servers.


Step 1: Find Your Server’s IP Address

First, you need to know the IP address of your MySQL server.

Run this:

ip a

Look for something like inet 192.168.x.x under your main network interface. That’s your local IP.

If your server is on the internet, you can also run:

curl ifconfig.me

That gives you the public IP.

Write that down. We’ll use it in a bit.


Step 2: Check MySQL’s Bind Address

Now we need to tell MySQL to listen for outside connections.

By default, it listens only on 127.0.0.1 — that’s the local machine (aka “localhost”). We need to change that.

Open the MySQL config file. It might be here:

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

Or if you use MariaDB:

sudo nano /etc/mysql/mariadb.conf.d/50-server.cnf

Look for this line:

bind-address = 127.0.0.1

Change it to:

bind-address = 0.0.0.0

This makes MySQL listen on all network interfaces.

Save and close the file. In nano, that’s Ctrl+O, Enter, then Ctrl+X.


Step 3: Restart MySQL

To apply the change, restart the service:

sudo systemctl restart mysql

Or if you use MariaDB:

sudo systemctl restart mariadb

Now MySQL is listening for connections—not just from inside the server, but from the outside world too. It’s like opening the front door after keeping it locked.


Step 4: Create a User for Remote Access

You don’t want to let just any user connect. It’s better to make a special MySQL user for remote use.

Log in to MySQL:

sudo mysql -u root -p

Now create a new user. Replace remote_user with whatever name you want, your_password with a strong password, and 192.168.1.100 with the IP of the remote machine:

CREATE USER 'remote_user'@'192.168.1.100' IDENTIFIED BY 'your_password';

Want to allow any IP? You can use '%' instead:

CREATE USER 'remote_user'@'%' IDENTIFIED BY 'your_password';

But be careful—'%' is like yelling “Come on in!” to the whole internet. It’s safer to use a specific IP if you can.

Now give that user permission:

GRANT ALL PRIVILEGES ON *.* TO 'remote_user'@'192.168.1.100' WITH GRANT OPTION;
FLUSH PRIVILEGES;

Then type exit; to leave MySQL.


Step 5: Open the Firewall

You also need to allow traffic to port 3306, which is the default port for MySQL.

On Ubuntu with UFW:

sudo ufw allow from 192.168.1.100 to any port 3306

On CentOS with firewalld:

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

If you don’t do this step, your changes won’t matter. It’s like unlocking the door but forgetting to open it.


Step 6: Test the Connection

Now, go to your other machine (the client). Install MySQL client if needed:

sudo apt install mysql-client

Then try to connect:

mysql -u remote_user -p -h your.server.ip.address

If you get a MySQL prompt, it worked! You’re in.

Try running a query, like:

SHOW DATABASES;

Feels nice, right?


Funny But Helpful: Why “Bind” and “Remote” Sound Like Superhero Names

When I first saw the word bind-address, I thought MySQL was learning magic. Turns out, binding just means “listening on a specific address.” No capes involved.

And remote access? That’s like giving a spare key to a friend. Handy, but be sure you trust them—or they might eat all your snacks (or worse, your data).


Three Benefits of Remote Access

  • You can manage databases from anywhere
    No need to SSH into the server every time.
  • Easier integration between apps
    Connect apps running on different servers to one central database.
  • Use GUI tools
    Tools like DBeaver or MySQL Workbench make it easier to work with your data.

Three Safety Tips to Avoid Problems

  • Don’t allow @'%' unless needed
    It opens your database to everyone.
  • Use strong passwords
    A weak password is like using a shoelace as a lock.
  • Use a VPN or SSH tunnel
    Encrypt the connection to keep snoopers out.

Three Troubles I Ran Into (And How to Fix Them)

  • Can’t connect?
    Check the firewall and make sure port 3306 is open.
  • Connection refused?
    The bind-address might still be 127.0.0.1. Double check the config file.
  • User doesn’t have permission?
    Re-check the GRANT command and IP address. MySQL is picky about it.

Final Thoughts

Setting up remote access for MySQL is like giving your database a phone line. It lets others call in—but only if you give them the number and permission.

When I first tried it, I messed up the bind-address and forgot to restart MySQL. I stared at “Connection refused” for hours. But once I got it right, it worked like a charm.

Now I can manage my databases without logging into my server every time. It makes my life easier—and my fingers happier.

Take it slow, be careful with access, and always test from a safe machine. Want extra protection? Wrap it in a VPN. Just like a burrito, your data deserves to be wrapped safely.

 

 

Leave a Reply