We can securely access MySQL on a remote system by connecting MySQL over the SSH tunnel. This tutorial provides the steps required to remotely connect to MySQL server over SSH tunnel using Putty on Windows and SSH on Linux systems. It shows how to forward the local port of MySQL i.e. 3306 to the remote port of MySQL i.e. 3306 via SSH port 22 of the remote system. This ensures that we are communicating securely with the remote server over port 22, by connecting the local port 3306 to the port 3306 on the remote server.
Prerequisites
A remote server having MySQL server installed on it. You may follow How To Install MySQL 8 on Ubuntu 20.04 LTS.
Also, make sure that port 22 is open and port 3306 is blocked by the firewall on the remote system having MySQL server. This tutorial also assumes that the MySQL server is not running on the local system. This ensures that the port 3306 on the local system is free for port forwarding.
We also need SSH access to the system on which the MySQL server is installed and running. We can either use the SSH username and password or SSH username and the key to connect to the remote system.
Install Putty On Windows 10
We can use Putty on the Windows systems to access the MySQL server installed on a remote server. In case you are using a Linux based system, you can move to the SSH section.
Open the official Download Page of Putty to download the MSI installer as highlighted in Fig 1.
Fig 1
We can also use the Putty executable without installing it. Scroll down the Download Page to download the executable file.
Now launch Putty. The default options of Putty should be similar to Fig 2.
Fig 2
Secure Connection To MySQL using Putty
This section provides the steps to use Putty to securely connect to MySQL over the SSH tunnel from a remote system. Now fill the remote host details as shown in Fig 3.
Fig 3
Also, click the Connection -> SSH -> Tunnels as highlighted in Fig 3. Now fill the SSH port forwarding details as shown in Fig 4. The Source port should be set to 3306 and Destination should be set to 127.0.0.1:3306.
Fig 4
Click the Add Button after filling the Source port and Destination. It will update the Forwarded ports as shown in Fig 4. Also, click the Auth Option and choose the SSH key as shown in Fig 5. Make sure that you have generated the Private Key using Puttygen.
Fig 5
Now click the Open Button to open the connection. It will show the security alert for the first time as shown in Fig 6.
Fig 6
Click the Yes Button to accept the connection. It will ask for the passphrase in case you have set while generating the private key. On a successful connection, it will show the remote system details as shown in Fig 7.
Fig 7
Apart from connecting over SSH, it also forwarded the local port 3306 to the port 3306 on the remote server. Do not close Putty since it keeps the SSH tunnel open to the remote server. Now launch any MySQL Client and try to connect to the remote MySQL server. I have used MySQL Workbench for demonstration purposes. The Home Screen of Workbench should be similar to Fig 8. You may also follow Using Workbench To Connect To Remote MySQL Server Over SSH Tunnel.
Fig 8
Now click the Plus Icon to create a new Local Connection as highlighted in Fig 8. It will show the options to Add Connection as shown in Fig 9.
Fig 9
Provide a connection name, and keep the Hostname as 127.0.0.1 and Port as 3306 as shown in Fig 9. Now click the Test Connection to test the connection to the remote MySQL server over the SSH tunnel. It will ask for Password as shown in Fig 10 in case you have not stored it in the Vault.
Fig 10
Now fill the password and click the OK Button to test the connection. It should show a success message on connection success as shown in Fig 11.
Fig 11
Now click the OK Button to close the connection message and again click the OK Button to close the connection wizard. It will add the connection to the connections library as shown in Fig 12.
Fig 12
Now click the newly added connection. It should connect to the remote server and list the databases of the remote MySQL server as shown in Fig 13.
Fig 13
Now we can work on remote MySQL server using Workbench on the local system. Also, close Workbench and Putty to terminate the connection after completing the work on the remote MySQL server.
Secure Connection To MySQL using SSH
We can connect to the remote MySQL server by forwarding the local port 3306 to the remote port 3306 using the SSH command as shown below. Similar to the previous section, make sure that the local port 3306 is free.
Notes: The below mentioned commands have been tested on Ubuntu Desktop. The steps and commands should be similar on other Linux systems.
# Shutdown local MySQL - Else use a different local port sudo service mysql stop
# Install MySQL Client - If required sudo apt install mysql-client
# Port Forwarding - Command ssh -N -L 3306:127.0.0.1:3306 [USER]@[SERVER_IP] # OR ssh -N -L 3306:127.0.0.1:3306 -i [SSH Key] [USER]@[SERVER_IP]
# Port Forwarding - Example ssh -N -L 3306:127.0.0.1:3306 ubuntu@example.com # OR ssh -N -L 3306:127.0.0.1:3306 -i example.pem ubuntu@example.com
The above-mentioned commands open the SSH tunnel and connect the local port 3306 to the remote port 3306. Now we can connect to the remote MySQL server using the MySQL client. You can refer to the previous section to use MySQL Workbench installed on the local system.
Also, refer the Fig 14 and Fig 15 to open the SSH tunnel and connect to the remote MySQL server.
Fig 14
Fig 15
Summary
This tutorial provided the steps required to connect to the remote MySQL server over SSH tunnel for Windows and Linux systems.
