How to Connect to MySQL Server via SSH Tunnel

MySQL allows you to connect to local as well as remote databases. While connecting to remote databases, many developers and database administrators open port 3306 on which MySQL server is running, to allow remote incoming connections. However, this can be a security risks since open ports are vulnerable to brute force attacks. So an alternative is to connect to remote MySQL server via SSH tunneling. In this case, we create an SSH tunnel from our client machine to the server machine. This creates a secure encrypted SSH connection between the two machines. It is used to relay connection information, SQL queries as well as their results without exposing any information to third-parties.

How to Connect to MySQL Server via SSH Tunnel

Here are the steps to connect to MySQL server via SSH tunneling. You need to have SSH access from client to remote server for this purpose. In most Linux systems, there is already an SSH client present. You can use this command to create SSH connection to your server.

$ ssh -N -L 3336:127.0.0.1:3306 [USER]@[SERVER_IP]

In the above command,

• -N – don’t execute remote command
• -L 3336:127.0.0.1:3306 – Local port forwarding. So all connections sent to local port 3336 are forwarded to remote port 3306 via SSH connection. Please note, you need to use a different local port number (e.g. 3336) in your port forwarding string, since your local MySQL server may already be running on port 3306 and it will not be available.
• [USER]@[SERVER_IP] – Remote user and server IP address

By default, the above command will run the SSH tunnel in foreground. If you want to run the tunnel in background, use -f option,

By default, SSH runs on port 22. If your SSH server runs on a different port use -p [PORT NUMBER] to specify the actual SSH port number.

Similarly, in your remote MySQL server runs on a port other than the default port 3306, you need to specify it in the local port forwarding string. For example, if your remote MySQL server runs on port 4000, then you need to update the local port forwarding as shown below.

-L 3336:127.0.0.1:4000

Once the SSH tunnel is established, you can send MySQL commands through it, as if you are issuing them to your local MySQL database. Here is an example. Replace MySQL_USER with MySQL database username.

$ mysql -u MYSQL_USER -p -h 127.0.0.1 -P 3336

Please note, you need to specify the local port number (e.g. 3336) that has been forwarded to remote MySQL server, using -P option. Otherwise, MySQL will try to connect to local MySQL database running on port 3306.

You can terminate SSH tunnel by entering Ctrl+C keys.

In this article, we have learnt how to connect to remote MySQL Server. You can run them as standalone commands in terminal, or from within shell scripts. It provides a very secure way to connect to remote database servers, run queries and transfer data, since everything is encrypted by the SSH connection and not exposed to any network.

Also read:

Git Diff Between Local & Remote Branch
Bash Loop Through Contents of File
How to Update Row Based on Previous Row in MySQL
How to Disable Commands in Linux
How to Install Printer in Ubuntu