How To Map Remote MySQL Server to Localhost Port

My remote MySQL database is located in a fully secured network and if I wanted access the db first had to ssh to Bastion Server and then to Deployment server and then connect to the database using mysql connection string.

After doing some R&D work found following method to map the remote database to localhost and then connect using localhost.

Edit the .ssh/config file and add below at the bottom:

Host sandbox-bastion
  HostName 31.223.37.144  

Host sandbox-mysql
  HostName deployment-server-03
  ProxyJump sandbox-bastion  
  IdentityFile /home/nuwan/.ssh/id_rsa_sandbox
  Localforward 3309 33.213.25.253:3306

If note the line IdentityFile /home/nuwan/.ssh/id_rsa_sandbox we must pass the sandbox-bastion server private key file from our local pc. Inorder to get the login to your sandbox-bastion server and get the file and copy to local location or copy the content and make a file.

Now use below ssh command to connect to the mysql host:

ssh sandbox-mysql

Now if you try to access the port in the browser you will see an out put similar to following.

All done now you can connect your MySQL workbench and view your databases as below:

But How …

Now let’s go bit deeper and see what did we do!

Host sandbox-bastion
  HostName 31.223.37.144

This code will help us to connect to the server using the host entry instead of using real hostname.

Host sandbox-mysql
  HostName deployment-server-03
  ProxyJump sandbox-bastion  
  IdentityFile /home/nuwan/.ssh/id_rsa_sandbox
  Localforward 3309 33.213.25.253:3306

This record connects to deployment-server-03 same as first command. But make note of the ProxyJump field, it will trigger the first ssh connection(sandbox-bastion) and then connect to sandbox-deployment-server-03

When using ProxyJump we must provide the ssh private key from our local machine to connect to the remote server.

Localforward 3309 33.213.25.253:3306

Above command will map the remote port 3306 at the given ip to port 3309 in local server.

Errors:

Caused by: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure

If you connection issues while running your application:

Add below to the end of the connection string:

useSSL=false&allowPublicKeyRetrieval=true