Connect to a remote MariaDB instance securely without exposing TCP ports by creating an SSH local tunnel that forwards traffic to the database server’s Unix socket file. This method allows your local client to communicate with the remote database as if it were running on your machine, leveraging the existing SSH connection for encryption and authentication while bypassing network-level firewall restrictions entirely.
Prerequisites and Server Configuration
Before establishing the connection, ensure that the MariaDB service is installed and configured correctly on the remote server. The most critical requirement is that the
skip-networking directive is either absent or set to
OFF in the
mariadb.cnf configuration file. If this option is active, MariaDB will ignore all TCP/IP connections, which is standard for security but incompatible with direct network access. However, since we are using a Unix socket, the server must have the socket file enabled. Locate the socket path, typically found at
/var/run/mysqld/mysqld.sock or
/tmp/mysql.sock, depending on your distribution. Verify that the user account you intend to use has permissions to connect via the localhost interface, as the SSH tunnel will make the connection appear to originate from
127.0.0.1.
Establishing the SSH Tunnel
The core of this process involves forwarding a local port on your client machine to the specific socket file on the remote server through SSH. You do not need to know the remote IP address of the database port because you are targeting the filesystem path directly. Open your terminal and execute the following command structure:
ssh -L 3306:/var/run/mysqld/mysqld.sock user@remote_host
In this command,
-L 3306 specifies that port 3306 on your local machine will be forwarded. The target
/var/run/mysqld/mysqld.sock is the absolute path to the socket file on the remote server. Replace
user with your SSH username and
remote_host with the server's hostname or IP address. Once authenticated, keep this terminal session open; closing it terminates the tunnel. For background execution, you can add the
-f flag to send SSH to the background after authentication, though keeping it visible helps diagnose connectivity issues early.
Connecting via Localhost
With the tunnel active, your local MariaDB client can now interact with the remote database. Since the tunnel makes the remote socket available locally, you connect to
127.0.0.1 or
localhost just as you would with a local installation. Use the
mysql command-line tool or any GUI client like DBeaver or MySQL Workbench. Specify the host as
127.0.0.1 rather than
localhost to force a TCP connection to the loopback interface, which then routes through the SSH tunnel. Avoid using
localhost in some clients, as they may attempt to use a local Unix socket instead of the TCP loopback address, causing the connection to fail.
mysql -h 127.0.0.1 -P 3306 -u your_db_user -p
Enter your database password when prompted. If successful, you will see the MariaDB monitor prompt. This approach effectively isolates the database from the public internet, as no inbound rules on the server’s firewall need to allow traffic on port 3306. Only SSH port 22 needs to be open, which is already secured by your SSH key or password authentication. This method is particularly useful for development environments where rapid iteration is needed without configuring complex SSL certificates or public DNS records.
Troubleshooting Common Issues
If the connection fails, first verify that the SSH tunnel is still active by checking the process list or attempting to ping the local port. A common error is "Connection refused," which usually means the tunnel is down or the socket path is incorrect. Ensure the path provided in the
-L argument matches exactly what is defined in the server’s
mariadb.cnf under the
[mysqld] section. Another frequent issue arises from permission errors on the socket file itself. The user running the MariaDB service must have read/write access to the socket, and the SSH user must have read access to traverse the directory. Additionally, check the MariaDB logs for any denied login attempts, which might indicate that the user does not have privileges for the
127.0.0.1 host.
For persistent connections, consider using tools like
autossh to automatically restart the tunnel if the network drops. This ensures that your development workflow is not interrupted by temporary connectivity loss. While this method provides robust security, it ties your access to the SSH server’s availability. If the SSH server goes down, so does your database access. Always maintain a separate out-of-band management channel, such as a console access provider, to recover if the SSH tunnel mechanism becomes unreliable.
Security Considerations
Using an SSH tunnel for database access significantly reduces the attack surface compared to exposing the database port. It eliminates the risk of brute-force attacks on the database port from external sources. However, security relies heavily on the strength of your SSH credentials. Disable password authentication for SSH and use strong RSA or Ed25519 keys. Furthermore, restrict the SSH user’s shell access if possible, ensuring they cannot execute arbitrary commands on the server beyond maintaining the tunnel. Regularly audit the SSH logs for unauthorized connection attempts. This setup is ideal for developers who need secure, low-latency access to production-like databases without the overhead of managing TLS certificates for every client application.
Frequently Asked Questions
Can I use this method with MySQL instead of MariaDB?
Yes, the process is identical for MySQL since both systems support Unix sockets and share similar configuration structures. Just ensure the socket path matches your MySQL installation, often located at
/var/run/mysqld/mysqld.sock.
What happens if the SSH connection drops?
The database connection will immediately fail because the tunnel closes. You must reconnect via SSH to re-establish the tunnel before resuming database operations. Using background tools like autossh can mitigate this by automatically restarting the tunnel.
Is this slower than a direct TCP connection?
There is minimal performance overhead compared to a standard TCP connection over the same network, as the data is only encrypted once by SSH. The latency is determined by the network distance between your client and the SSH server, not the database protocol itself.
Do I need to configure firewalls on the remote server?
No, you do not need to open port 3306 in the server’s firewall. Only port 22 (SSH) needs to be accessible. This simplifies firewall rules and enhances security by hiding the database service from external scans.
How do I find the correct socket path?
Check the
mariadb.cnf or
my.cnf file on the server for the
socket directive under the
[mysqld] section. Alternatively, run
mysqladmin variables | grep socket while connected locally to see the active path.