This guide walks you through connecting MySQL Workbench to a MySQL server on a VPS (Virtual Private Server) using an SSH tunnel. This method encrypts your connection and lets you access a database that isn’t directly exposed to the internet, improving security. You’ll need SSH credentials (host, port, username, and either a password or private key) and the MySQL server’s credentials (username and password).
What You’ll Need
- MySQL Workbench installed on your local machine (available for Windows, macOS, and Linux).
- SSH access to your VPS: hostname or IP address, SSH port (usually 22), and a username (e.g.,
rootor a sudo user). - Authentication for SSH: either a password or a private key file (e.g.,
.pemor.ppk). - MySQL server running on the VPS, listening on
localhost(127.0.0.1) or a private IP. If it’s on a public IP, you can still use the tunnel, but it’s not required. - MySQL username and password for the database you want to access (e.g.,
adminor a specific user).
Step-by-Step
- Open MySQL Workbench. On the home screen, click the + icon next to “MySQL Connections” to create a new connection.
- Set up the connection tab. In the “Connection Name” field, enter a descriptive name (e.g., “My VPS Database”). Under “Connection Method,” select Standard TCP/IP over SSH from the dropdown.
- Enter SSH details. In the “SSH Hostname” field, enter your VPS’s IP address or hostname, followed by the SSH port (e.g.,
192.168.1.100:22). In “SSH Username,” enter your SSH login username (e.g.,root). - Choose SSH authentication. In the “SSH Password” field, enter your SSH password if you use password authentication. If you use a private key, click the “Store in Vault…” button next to “SSH Key File” and select your key file. For macOS/Linux, you may need to set the file permissions to 600 (e.g.,
chmod 600 key.pem). - Enter MySQL server details. In the “MySQL Hostname” field, enter
127.0.0.1(orlocalhost). This is because the SSH tunnel routes to the VPS’s localhost. In “MySQL Server Port,” enter3306(or your custom MySQL port if different). - Enter MySQL credentials. In the “Username” field, enter your MySQL username (e.g.,
admin). In the “Password” field, enter your MySQL password, or click “Store in Vault…” to save it securely. You can leave it blank to be prompted each time. - Test the connection. Click the Test Connection button at the bottom. If everything is set correctly, you’ll see a success message. If not, review the error message and check the troubleshooting section below.
- Save and connect. Click OK to save the connection. Then double-click the connection tile on the home screen to connect. You’ll be prompted for SSH and/or MySQL passwords if you didn’t store them.
Common Mistakes
- Using the public IP in MySQL Hostname: Always use
127.0.0.1unless your MySQL server is configured to listen on a different interface. Using the public IP defeats the purpose of the tunnel and may fail if the port isn’t open. - Wrong SSH port: Ensure you include the correct SSH port (default 22) in the SSH Hostname field. If your VPS uses a non-standard port, you must specify it (e.g.,
203.0.113.5:2222). - Private key permissions: On Linux/macOS, if your private key file has loose permissions (e.g., 644), SSH will reject it. Run
chmod 600 yourkey.pem. - MySQL user host mismatch: The MySQL user you’re using must be allowed to connect from
localhost(or127.0.0.1). If the user is restricted to a specific host, the connection will fail. Create or alter the user withCREATE USER 'user'@'localhost'or'user'@'127.0.0.1'. - Firewall blocking MySQL: Even with the tunnel, if the MySQL port (3306) is blocked by a firewall on the VPS, the tunnel can’t reach it. Ensure MySQL is listening and the port is open on the loopback interface (usually it is).
Tips
- Use SSH keys over passwords: SSH keys are more secure and allow for passwordless login. Generate a key pair with
ssh-keygenand add the public key to your VPS’s~/.ssh/authorized_keys. - Keep your MySQL port closed: For maximum security, configure MySQL to listen only on
127.0.0.1(inmy.cnformysqld.cnf:bind-address = 127.0.0.1). Then the only way in is through SSH. - Use a non-default SSH port: If your VPS provider allows it, changing the SSH port from 22 to something else reduces automated attacks. Just remember to update your connection settings.
- Save passwords in the vault: MySQL Workbench can store passwords securely in its vault, so you don’t have to type them every time. Use the “Store in Vault…” button.
- Test SSH separately: Before setting up Workbench, test your SSH connection with a terminal:
ssh user@vps_ip. If that works, the tunnel will likely work too.
Troubleshooting
- “SSH connection failed” or “Authentication error”: Double-check your SSH username, password, and port. If using a key, ensure the file path is correct and permissions are set. Try connecting via terminal to verify credentials.
- “Can’t connect to MySQL server on ‘127.0.0.1’”: This means the SSH tunnel is up, but MySQL isn’t reachable. Check that MySQL is running on the VPS (
sudo systemctl status mysql), and that it’s listening on 127.0.0.1 (check withnetstat -tlnp | grep 3306). Also, verify the MySQL port number in Workbench matches the actual port. - “Access denied for user ‘user’@’localhost'”: The MySQL user doesn’t exist or isn’t allowed to connect from localhost. Log into MySQL via SSH (
mysql -u root -p) and run:CREATE USER 'user'@'localhost' IDENTIFIED BY 'password';andGRANT ALL PRIVILEGES ON *.* TO 'user'@'localhost';(adjust privileges as needed). - Connection times out: Check that the VPS IP address is correct and that SSH port is reachable (try
telnet vps_ip 22). Also, ensure your local firewall isn’t blocking outbound SSH. - Private key not working: If you’re using a
.ppkfile (PuTTY format), convert it to OpenSSH format usingputtygen(Load the key, then Conversions > Export OpenSSH key).
Frequently Asked Questions
Do I need to open MySQL port 3306 in the VPS firewall?
No. The SSH tunnel only requires port 22 (SSH) to be open. Keeping 3306 closed adds security.
Can I use this method on a Windows machine?
Yes, MySQL Workbench works the same on Windows. For private keys, you may need to use PuTTY’s Pageant or convert the key to PPK format.
What if my MySQL server is on a different port (e.g., 3307)?
Simply enter that port in the “MySQL Server Port” field in Workbench. The tunnel will forward to that port on the VPS.
Is the SSH tunnel encrypted?
Yes, all data transferred through the tunnel is encrypted using SSH’s encryption protocol, protecting your queries and results.
Can I use this to connect to a remote MySQL server that isn’t on the VPS itself?
Yes, you can specify a different MySQL hostname (e.g., a private IP) in the “MySQL Hostname” field, as long as the VPS can reach it. The tunnel will forward to that host.
Why do I get “Connection closed by remote host”?
This often means the SSH server is configured to disallow port forwarding. Check your VPS’s SSH config (/etc/ssh/sshd_config) for AllowTcpForwarding yes. You may need to enable it and restart SSH.
Related
Method: the standard formula for this calculation, rounded as each result states. See how the calculators work or report an error.
More cards in this drawer
- Arctic Sea Ice Forecasts Disagree: What to Do
- Bluesky Beginners Guide: How to Get Started5.00 min/km
- LG V20 Won’t Turn On? Fixed
- Snagit vs Camtasia: Which One Do You Need?
- Scale a Small Business Without Losing Culture
- Dimensity 9000 Plus vs 9500s: Which Chip Is Faster?