Remote Access: Safe Ways to Connect

A database on a server is of no use if your application, your laptop, or a colleague cannot reach it. But MySQL exposed to the internet is attacked constantly. This lesson shows how to allow the connections you need and nothing more.

Layers of control

A remote connection must pass four checks, in this order:

1. Network: can the packet reach the server? (firewall, security group, routing)
2. bind_address: does MySQL listen on that network interface?
3. Account host: does an account exist for that user and that client host?
4. Privileges: what may that account do?

Failing at any of them gives a different error, which helps with diagnosis (below).

1. bind_address: where MySQL listens

1

SELECT @@bind_address AS listens_on, @@port AS port, @@skip_networking AS tcp_disabled, @@skip_name_resolve AS no_dns;

1
2
3
4
5
6

+------------+------+--------------+--------+
| listens_on | port | tcp_disabled | no_dns |
+------------+------+--------------+--------+
| 127.0.0.1  | 3384 |            0 |      0 |
+------------+------+--------------+--------+
1 row in set

For a database that only a web server on the same machine uses, keep 127.0.0.1 and no remote access at all. For a separate database server, bind it to the private network interface, never to a public address.

2. The firewall

Port 3306 (MySQL) should be reachable only from the machines that need it:

1
2
3

# Linux (ufw): allow the web server only
ufw allow from 10.0.0.12 to any port 3306 proto tcp
ufw deny 3306

On cloud providers, use security groups / firewall rules the same way: allow the application subnet, deny the rest. Never allow 0.0.0.0/0 (the whole internet) to port 3306.

3 and 4. Accounts for the right hosts

1
2
3

CREATE USER 'shop_app'@'10.0.0.12' IDENTIFIED BY 'Ap9!pass#Long#Random#1';
GRANT SELECT, INSERT, UPDATE, DELETE ON shop.* TO 'shop_app'@'10.0.0.12';
SELECT user, host FROM mysql.user WHERE user = 'shop_app';

1
2
3
4
5
6

+----------+-----------+
| user     | host      |
+----------+-----------+
| shop_app | 10.0.0.12 |
+----------+-----------+
1 row in set

The account is valid only from the web server’s address. A connection from any other host is refused, even with the right password (Creating Users).

Connecting from your own computer: the SSH tunnel

For administration and development, you rarely need to open MySQL to your own IP. Use an SSH tunnel: the database port stays closed to the world, and your tool talks through an encrypted SSH connection:

1
2

# local port 3307  ->  (through SSH to the server)  ->  MySQL on 127.0.0.1:3306 there
ssh -N -L 3307:127.0.0.1:3306 deploy@db.example.com

Then connect your client to 127.0.0.1:3307:

1

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

MySQL sees a local connection from the server itself ('shop_report'@'localhost'). MySQL Workbench, DBeaver and others have an “SSH tunnel” option built in. Only people with SSH access to the server can reach the database, and everything is encrypted.

Other safe options

Shared hosting

Many shared hosts block remote MySQL on purpose. They offer “Remote MySQL” in the control panel, where you add the IP address that may connect. Add only the addresses you really need (not %). Otherwise use their web phpMyAdmin or an SSH tunnel if SSH is available.

Diagnose connection errors

The host in the “Access denied” message is the one MySQL saw, which tells you which account it tried to match. If you connected through a NAT, a proxy or a tunnel, MySQL sees that address, not your laptop’s.

Test from the client side

1
2
3

# is the port reachable?
nc -vz db.example.com 3306            # Linux, macOS
Test-NetConnection db.example.com -Port 3306    # Windows PowerShell

and from MySQL itself, after connecting:

1

SELECT CURRENT_USER() AS matched_account, USER() AS connected_as, @@hostname AS server_host;

1
2
3
4
5

+----------------------+------------------------+-------------+
| matched_account      | connected_as           | server_host |
+----------------------+------------------------+-------------+
| shop_app@10.0.0.%    | shop_app@10.0.0.12     | db1         |
+----------------------+------------------------+-------------+

(An example of what you would see.) CURRENT_USER() is the account whose privileges apply. USER() is how the client identified itself and from where.

skip_name_resolve

By default, MySQL tries to resolve client IP addresses to host names (a reverse DNS lookup). That is slow, and breaks when DNS has problems. With skip_name_resolve = ON, MySQL uses IP addresses only, so every account host must be an IP or a pattern, not a name. It is the usual production setting.

What not to do

• bind_address = 0.0.0.0 and GRANT ... TO 'root'@'%': the classic combination that gets servers ransomed.
• Opening 3306 to the internet “just for a minute”.
• Using the same account and password for the website and for remote administration.
• Sending passwords over unencrypted connections across networks.

Within an hour of a new server’s port 3306 being exposed, bots start trying the common passwords for root, admin and mysql.