MySQL allows you to connect to remote databases via direct connection or using SSH tunneling. When you connect to MySQL database via direct connection, you need to open port 3306 on your remote database server as well as its firewall. This can be insecure since this exposes your database to brute force attacks. Alternatively, you can configure your local MySQL client to connect to your remote MySQL database via SSH port 22, which is secure & encrypted and anyways open to run remote commands. In this article, we will learn how to connect to MySQL via SSH tunnel in Windows.
How to connect to MySQL via SSH tunnel in Windows
By default, Windows does not include an SSH client. So you will need to download & install puTTY software for this purpose. If you use MySQL clients like Workbench, or HeidiSQL, you don’t need to download puTTY software since they already have feature to create SSH tunnel.
In our case, you launch puTTY and enter IP address or hostname of remote database server.
Under connection menu in left pane, expand SSH and click Tunnels. Enter any port other than 3306 (e.g. 3336) in Source port and local port forwarding string 127.0.0.1:3306 in the Destination field. Although we have used 3306 as source port, it is advisable to avoid using port 3306 as source port since your local MySQL server might already be running on this port and may create conflict. If your remote MySQL server runs on a different.
Click on Add button to add the tunnel.
Go back to Session page to save the settings. This way you don’t need to enter this information every time you create SSH tunnel. Enter name to identify these session details and click Save button to save the information.
Select the saved session and click open button to log into remote server.
You will see a window asking you for SSH username & password. Enter their values to login to your remote server. Alternatively, you can set up key-based authentication by clicking Connection->SSH->Auth and browsing to the path of your SSH key, before you save the session.
Once your tunnel has been created you can open your local MySQL client and run commands as if you are sending them to your local MySQL server. Please note, you will need to send commands to the forwarded port 3336 and not MySQL port 3306. Replace MySQL_USER with your MySQL username.
C:\> mysql -u MYSQL_USER -p -h 127.0.0.1 -P 3336
Please note, this SSH tunnel will keep running in the background until your session terminates or your reboot your system.
In this article, we have learnt how to create SSH tunneling to connect to remote MySQL database server. As mentioned earlier, if you use a MySQL client like HeidiSQL or Workbench, it already contains provisions to make SSH connection, you just need to enter required details. If you don’t use any of these clients, you can use the above method to forward your local port to remote MySQL database.
Also read:
How to Connect to MySQL Server via SSH Tunnel in Linux
Git Diff Between Local & Remote Branch
Bash Loop Through Content of File
How to Update Row Based on Previous Row in MySQL
How to Disable Commands in Linux