Offensive Security Researcher & Quantum Cryptography Analyst

How to Connect to a Remote MySQL Server

Abdul Wahab Junaid
How to Connect to a Remote MySQL Server

Connecting to a remote MySQL server sounds trivial until the moment it doesn’t work, and then I’m suddenly debugging firewall rules, bind addresses, user host permissions, and SSL certificates all at once. I’ve set up remote MySQL access for everything from a single developer connecting to a cloud database to entire application fleets talking to a managed database cluster. In this guide, I’ll walk through the full process along with the security considerations I never skip.

Understanding How Remote Connections Work

By default, MySQL is configured to only accept connections from localhost (127.0.0.1), which is a sensible security default. Enabling remote access means changing three layers, and I always think about them separately:

  1. MySQL server configuration – the bind-address setting controls which network interfaces MySQL listens on.
  2. MySQL user privileges – MySQL user accounts are scoped by host ('user'@'host'), and 'user'@'localhost' is a completely different account from 'user'@'%' as far as MySQL is concerned.
  3. Network/firewall layer – the operating system firewall and any cloud security groups must explicitly allow inbound traffic on port 3306 (or whatever custom port I’ve configured).
graph TD
    A[Remote Client] -->|1. Network/Firewall Layer| B{Port 3306 Open?}
    B -- No --> Z[Connection Refused/Timeout]
    B -- Yes --> C{bind-address allows this interface?}
    C -- No --> Z
    C -- Yes --> D{MySQL user host matches?}
    D -- No --> E[Access Denied]
    D -- Yes --> F[Authentication & Connection Established]

Step 1: Configuring MySQL to Listen for Remote Connections

On Linux, I edit the configuration file:

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

I change:

bind-address = 127.0.0.1

to either a specific private IP (my preference for security):

bind-address = 10.0.0.15

or, less securely, to all interfaces:

bind-address = 0.0.0.0

I restart the service to apply the change:

sudo systemctl restart mysql

Step 2: Creating a MySQL User That Allows Remote Access

CREATE USER 'remote_user'@'%' IDENTIFIED BY 'Str0ngP@ssw0rd!';
GRANT ALL PRIVILEGES ON shop_db.* TO 'remote_user'@'%';
FLUSH PRIVILEGES;

I almost never actually use '%' in production, since it allows any IP address to attempt authentication. Instead, I scope it to a known range or specific IP:

CREATE USER 'app_server'@'10.0.0.5' IDENTIFIED BY 'Str0ngP@ssw0rd!';
GRANT SELECT, INSERT, UPDATE, DELETE ON shop_db.* TO 'app_server'@'10.0.0.5';
FLUSH PRIVILEGES;

Step 3: Opening the Firewall

On Ubuntu with UFW, I only allow the specific source I trust:

sudo ufw allow from 10.0.0.5 to any port 3306

On a cloud provider like AWS, this equates to editing the security group attached to my RDS instance or EC2-hosted database to allow inbound TCP 3306 only from the application server’s security group, not 0.0.0.0/0.

Step 4: Connecting From a Remote Client

From my local machine or another server:

mysql -h 10.0.0.15 -u remote_user -p -P 3306

Output on success:

Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 87
Server version: 8.0.40 MySQL Community Server

mysql>

Connecting With SSL/TLS Enabled

I never connect to a remote production database over an untrusted network without SSL. MySQL supports this natively:

mysql -h db.example.com -u remote_user -p --ssl-ca=/path/to/ca-cert.pem --ssl-mode=VERIFY_IDENTITY

On the server side, I confirm SSL is enabled:

SHOW VARIABLES LIKE '%ssl%';
SHOW STATUS LIKE 'Ssl_cipher';

I also enforce SSL at the account level so plaintext connections are rejected outright:

ALTER USER 'remote_user'@'%' REQUIRE SSL;

Connecting Through an SSH Tunnel (My Preferred Method for Ad-Hoc Access)

For personal or administrative access to a production database, I almost always prefer tunneling through SSH rather than exposing port 3306 directly to the internet at all:

ssh -L 3307:127.0.0.1:3306 ahmad@myserver.example.com -N

Then I connect locally as if the database were on my own machine:

mysql -h 127.0.0.1 -P 3307 -u remote_user -p

This way, port 3306 never needs to be exposed publicly — only SSH (port 22, ideally key-based auth only) is internet-facing.

sequenceDiagram
    participant Me as My Laptop
    participant SSH as SSH Server (Bastion)
    participant DB as MySQL Server (private network)

    Me->>SSH: SSH connection, local port forward 3307->3306
    SSH->>DB: Forwarded traffic over private network
    DB-->>SSH: MySQL protocol response
    SSH-->>Me: Tunneled back to localhost:3307

Connecting From Application Code

Python (using mysql-connector-python):

import mysql.connector

conn = mysql.connector.connect(
    host="10.0.0.15",
    user="app_server",
    password="Str0ngP@ssw0rd!",
    database="shop_db",
    ssl_ca="/path/to/ca-cert.pem",
    ssl_verify_cert=True
)

Node.js (using mysql2):

const mysql = require('mysql2');

const connection = mysql.createConnection({
  host: '10.0.0.15',
  user: 'app_server',
  password: 'Str0ngP@ssw0rd!',
  database: 'shop_db',
  ssl: { ca: fs.readFileSync('/path/to/ca-cert.pem') }
});

I always load credentials from environment variables or a secrets manager rather than hardcoding them, even in example code I write for clients.

A Real-World Scenario: Connecting a Multi-Region Application Fleet

For a client running application servers across two cloud regions connecting to a single primary MySQL instance, my setup involved:

  1. Configuring bind-address to the private VPC IP only, never a public one.
  2. Setting up a VPN peering connection between regions so traffic never crossed the public internet.
  3. Creating per-region scoped MySQL users (app_useast@10.1.0.% and app_uswest@10.2.0.%) rather than a single generic account.
  4. Enforcing REQUIRE SSL on every remote account.
  5. Using ProxySQL as a connection pooling and routing layer in front of MySQL to handle connection multiplexing efficiently across regions.

This layered approach meant that even if application-layer credentials leaked, the attacker would still need network-level access to the VPC to do anything with them.

Security Best Practices for Remote Connections

Troubleshooting Common Remote Connection Issues

Issue: “Can’t connect to MySQL server” (connection timeout)

I check, in order: firewall/security group rules, whether bind-address actually includes the interface I’m connecting through, and whether the MySQL service is actually running.

sudo ss -tlnp | grep 3306

Issue: “Host ‘x.x.x.x’ is not allowed to connect to this MySQL server”

ERROR 1130 (HY000): Host '203.0.113.5' is not allowed to connect to this MySQL server

This means the connecting IP doesn’t match any existing 'user'@'host' entry. I check:

SELECT user, host FROM mysql.user;

And either create a matching user or adjust the host pattern.

Issue: SSL connection errors

ERROR 2026 (HY000): SSL connection error: certificate verify failed

I verify the CA certificate path is correct and that the server’s certificate hasn’t expired.

Performance Considerations for Remote Connections

Frequently Asked Questions

Q: Is it safe to expose MySQL directly to the public internet? A: I strongly avoid this. Even with strong passwords and SSL, I prefer restricting access through VPNs, SSH tunnels, or private networking whenever possible.

Q: What’s the difference between 'user'@'%' and 'user'@'10.0.0.5'? A: '%' allows connections from any host; a specific IP restricts the account to only that host. I always prefer the latter in production.

Q: How do I test if a remote port is reachable before troubleshooting MySQL itself? A:

telnet 10.0.0.15 3306

or

nc -zv 10.0.0.15 3306

Q: Can I connect to MySQL over IPv6? A: Yes, MySQL supports IPv6 if bind-address is configured accordingly (e.g., ::) and the network path supports it.

Interview Questions I’ve Encountered

  1. What are the three layers involved in enabling remote access to MySQL, and how do they interact?
  2. Why is 'user'@'%' considered a security risk, and what would you use instead?
  3. How would you securely connect to a production MySQL server without exposing port 3306 publicly?
  4. What does REQUIRE SSL do at the user account level?
  5. How would you troubleshoot a “Host is not allowed to connect” error?

Summary and Key Takeaways

Connecting to a remote MySQL server touches network configuration, user privilege scoping, and encryption all at once, and I’ve learned to think about each layer separately when something isn’t working. My default posture is always to minimize direct public exposure — preferring SSH tunnels, VPNs, or private networking — while enforcing SSL and tightly scoped user accounts wherever remote access is genuinely required.

Key takeaways:

References

Exit mobile version