Tunneling MySQL ports through SSH

From CS Support
Jump to: navigation, search

Tunneling MySQL ports through SSH

To connect to the MySQL database, you must connecting from our department network for security reasons. This is easy if you are using the lab machines because your connection is already through our network, but if you're using your own laptop at school or you want to work from home, you will need to Tunnel your traffic from your computer through our network using SSH. SSH Tunneling allows you to securely connect to the Linux COW (linux.cs.wwu.edu) on the department's network in order to send and receive data regarding the MySQL database server through MySQL's port (3306) without having to physically use one of the machines on our network.

After tunneling connection through SSH, you will use the localhost address 127.0.0.1 with port 3306 to connect to the database server because your localhost connection is being tunneled to our network.

Tunneling in Linux/OSX/BSD

In a terminal, enter the following command:

ssh -p 922 -L 3306:mysql.cs.wwu.edu:3306 username@linux.cs.wwu.edu

After entering the command, leave the terminal window open until you are finished with your need for the tunneled connection.

Connecting to the Database Server

You can now use the localhost address 127.0.0.1 with port 3306 to connect to the database server using whichever method your professor specified (e.g. MySQL Workbench).

Tunneling in Windows 10 using a Linux Subsystem (Newer)

Since the Windows 10 Anniversary Update (Version 1603 and later), Microsoft has added the ability to enable the Windows Subsystem for Linux which allows Windows users to run a Bash shell in the Command Prompt or PowerShell. This allows for simple tasks that were not possible prior to the 1603 version update like connecting to another computer with SSH through the Command Prompt!

Once you enable the Windows Subsystem for Linux you will be able to use the same commands you would use in Linux (see above). This guide will walk you through setting the WSL and tunneling the MySQL port on Windows. If you need additional clarification, please consult Microsoft's Documentation on WSL.

  1. Ensure you have Windows 10 Version 1603 or higher by going to Settings > About.
    WindowsVersion1603+.png
  2. Run PowerShell as Administrator and type in the command:
    Enable-WindowsOptionalFeature -Online -FeatureName Microsoft-Windows-Subsystem-Linux
  3. Follow the on-screen instructions and restart your computer when prompted.

Now that you have WSL installed, you can open the Command Prompt or PowerShell and enter bash to open the Bash shell. In this bash shell you can run many native Unix commands like ls, cp, ssh, and many more.

Finally, we are able to run the command like we would in plain old Linux:

ssh -p 922 -L 3306:mysql.cs.wwu.edu:3306 username@linux.cs.wwu.edu

After entering the command, leave the window open until you are finished with your need for the tunneled connection.

Connecting to the Database Server

You can now use the localhost address 127.0.0.1 with port 3306 to connect to the database server using whichever method your professor specified (e.g. MySQL Workbench).


Tunneling with PuTTY in Windows (Older)

PuTTY is a graphical interface for SSH and other such connections in Windows. Before the Windows 10 Anniversary Update (Version 1603), this was an easy way to use SSH in Windows without jumping through hoops.

The following instructions will show you how to setup an SSH tunnel using Putty, to forward the default port (3306) from your machine through another machine behind a firewall to the MySQL server. This allows you to connect to the remote server's MySQL database as if it were running on your local box, and will traverse any firewalls that block port 3306, but allow port 922.

Setting up the tunnel configuration

PuttyConfig1.png

Run PuTTY

  1. Under category expand Connection
  2. Expand SSH
  3. Select Tunnels
  4. Select Source port and enter 3306
  5. Select Destination and enter mysql.cs.wwu.edu:3306
  6. Make sure the Local and Auto radio buttons are selected
  7. Select Add

Saving the Session

PuttyConfig2.png
  1. Under category selectSession
  2. Under Host Name (or IP address), enter username@linux.cs.wwu.edu. Make sure you replace username with your username
  3. Under Port, enter 922
  4. Under Saved Sessions, enter a name
  5. Select Save
  6. Select Open to connect to the server

Connecting to the Database Server

You can now use the localhost address 127.0.0.1 with port 3306 to connect to the database server using whichever method your professor specified (e.g. MySQL Workbench).