ThisCalculator

Everyday

SSH Tunnel for MySQL: Remote Access Guide

Updated · Standard formula, shown below

This guide shows you how to securely connect to a MySQL server that is not directly exposed to the internet by creating an SSH tunnel. You will learn how to forward a local port to the remote MySQL port, allowing your local MySQL client to talk to the remote database as if it were running on your own machine. This method encrypts all traffic and avoids opening MySQL to the public network.

What You’ll Need

  • SSH access to the server hosting MySQL (username, host/IP, and password or SSH key).
  • The MySQL server’s port (default is 3306) and the host it listens on (usually localhost or 127.0.0.1).
  • A MySQL client installed locally (e.g., mysql, MySQL Workbench, or a programming language’s MySQL library).
  • Network access to the SSH port (usually 22) on the remote server.
  • Basic command-line comfort—you’ll be typing commands in a terminal.

Step-by-Step

  1. Find the MySQL port and bind address. On the remote server, check the MySQL configuration (usually /etc/mysql/my.cnf or /etc/mysql/mysql.conf.d/mysqld.cnf). Look for port (default 3306) and bind-address. If it’s 127.0.0.1, that’s fine—your SSH tunnel will connect to it via localhost.
  2. Open a terminal on your local machine. On Windows, use PowerShell or Command Prompt; on macOS/Linux, use Terminal.
  3. Create the SSH tunnel with the following command, replacing placeholders:
    ssh -L 3307:127.0.0.1:3306 user@remote-server.com

    This maps local port 3307 to the remote server’s 127.0.0.1:3306. You can use any unused local port (e.g., 3307) to avoid conflicts. Enter your SSH password or use your key.

  4. Keep the SSH session open. The tunnel stays active as long as this SSH session runs. Do not close the terminal window. You can minimize it or run it in the background with -f (e.g., ssh -f -L 3307:127.0.0.1:3306 user@remote-server.com).
  5. Connect your MySQL client to the local port. In a new terminal, run:
    mysql -h 127.0.0.1 -P 3307 -u your_mysql_user -p

    Enter your MySQL password when prompted. You are now talking to the remote MySQL server through the encrypted tunnel.

  6. For GUI tools (e.g., MySQL Workbench): create a new connection, set Hostname to 127.0.0.1 and Port to 3307. Use your MySQL username and password. The tool connects to the local port, which is forwarded.
  7. To use a different remote host (if MySQL listens on a separate IP), replace 127.0.0.1 in the -L argument with that IP. For example, ssh -L 3307:192.168.1.50:3306 user@remote-server.com.
  8. Close the tunnel by typing exit in the SSH session or pressing Ctrl+C. If you used -f, find the process with ps aux | grep ssh and kill it.

Common Mistakes

  • Using the wrong local port. If port 3307 is already in use, the tunnel will fail. Choose a different port (e.g., 3308).
  • Connecting to the remote port directly. You must connect to 127.0.0.1:3307 on your local machine, not to the remote server’s IP.
  • Forgetting that the SSH session must stay open. If you close the terminal, the tunnel dies. Use -f to run it in the background, but remember to kill it later.
  • MySQL binding only to localhost. If MySQL is bound to 127.0.0.1, the tunnel will still work because you are forwarding to that address from the remote side. Do not change the bind-address to 0.0.0.0—that would expose MySQL directly.
  • Using the wrong SSH user. You need an SSH user, not necessarily the MySQL user. The SSH user must have permission to log in.

Tips

  • Use SSH keys instead of passwords for automation and convenience. Generate a key with ssh-keygen and copy it to the server with ssh-copy-id.
  • Add the -N flag if you don’t need a remote shell: ssh -N -L 3307:127.0.0.1:3306 user@remote-server.com. This prevents the SSH session from allocating a terminal, making it ideal for background use.
  • Use -C for compression if you have a slow connection: ssh -C -L 3307:127.0.0.1:3306 user@remote-server.com. This can speed up data transfer.
  • For multiple tunnels, you can specify multiple -L options in one command, e.g., ssh -L 3307:127.0.0.1:3306 -L 3308:127.0.0.1:3306 user@remote-server.com.
  • Test the tunnel with telnet 127.0.0.1 3307 (if installed) or simply try the MySQL connection. If you get a connection refused, the tunnel is not working.

Troubleshooting

  • Connection refused on local port. This usually means the SSH tunnel is not running or the local port is wrong. Check the SSH session for errors. Try a different local port.
  • Connection refused on remote side. The SSH tunnel is up, but MySQL is not listening on the specified remote address/port. Verify MySQL is running (sudo systemctl status mysql) and check its bind-address and port.
  • SSH connection fails. Verify you can SSH normally without the -L flag. Check the hostname/IP, username, and that port 22 is open. Use ssh -v for verbose output.
  • MySQL client says “Access denied”. This is a MySQL user permission issue. Ensure the MySQL user is allowed to connect from ‘localhost’ (since the connection appears to come from 127.0.0.1). You may need to create a user like ‘user’@’localhost’ or ‘user’@’127.0.0.1’.
  • Tunnel works but is slow. Add -C for compression, or check your network latency. Also consider using a persistent connection in your MySQL client.
  • Cannot bind to local port. Another process is using that port. Find it with netstat -tulpn | grep 3307 (Linux) or netstat -ano | findstr 3307 (Windows) and choose a different port.

Frequently Asked Questions

Do I need to change MySQL’s bind-address?

No. In fact, leaving it as 127.0.0.1 is safer because it prevents direct remote connections. The SSH tunnel connects to MySQL via localhost on the server, so it works regardless.

Can I use this on Windows without a terminal?

Yes. You can use PowerShell or Command Prompt with the same ssh command. Alternatively, use PuTTY: enter the server details, go to Connection > SSH > Tunnels, set Source port to 3307, Destination to 127.0.0.1:3306, click Add, then Open.

Is the connection encrypted?

Yes. All traffic between your local machine and the remote server is encrypted by SSH. This includes your MySQL queries and results.

What if I need to connect to multiple MySQL servers?

Create multiple tunnels with different local ports. For example, use 3307 for server A and 3308 for server B. Then connect to 127.0.0.1:3307 and 127.0.0.1:3308 respectively.

How do I run the tunnel in the background permanently?

You can use ssh -f -N -L 3307:127.0.0.1:3306 user@remote-server.com to run it in the background. To make it persistent across reboots, you can set up an autossh service or a systemd unit, but that’s more advanced.

Can I use a different SSH port?

Yes. If SSH runs on a non-default port (e.g., 2222), add -p 2222 to the command: ssh -p 2222 -L 3307:127.0.0.1:3306 user@remote-server.com.

Related

Method: the standard formula for this calculation, rounded as each result states. See how the calculators work or report an error.