Solved

Remote MySQL using SSH

Posted on 2016-09-10
11
122 Views
Last Modified: 2016-09-10
Hi,

Can anyone please provide some guidance notes on how to setup secure remote access to a MySQL server running on a private Windows Server 2012 R2 server?  I think SSH is the way.  I'm using Navicat to administer.

Thanks
0
Comment
Question by:wayneinuk
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 5
11 Comments
 
LVL 58

Assisted Solution

by:Julian Hansen
Julian Hansen earned 250 total points
ID: 41792798
You want to read up on SSH Tunnelling. I am not familiar with Navicat but a quick search brought up this page which discusses how to setup an SSH tunnel on Navicat
https://www.navicat.com/manual/online_manual/en/navicat/rv_manual/SSHSettings.html
0
 
LVL 79

Expert Comment

by:arnold
ID: 41792869
ssh -t -L 3306:localhost:3306 user@remotehost


This establishes a connection without opening terminal, -t
-L setup local tunnel, Localport:remotehostname:remoteport
In the example I used localhost for the remote host since you indicated the MySQL is running on the server.

If you are using an ssh gateway to and through which, then the remote host will need to be the remote hostname that the ssh gateway will be establishing/completing the tunnel to.

MySQL localhost:3306 will go through the ssh tunnel, and will connect to the localhost on the remotehost port 3306.
Ssh, could mean any ssh application most follow the same
.......

You've provided very little detail, do you already have an ssh server configured/running on the Windows 2012?

You could look at Cygwin to setup in tge server with running ssh server........
0
 

Author Comment

by:wayneinuk
ID: 41792870
Hi,

Thanks for your comments but wont I need some SSH software installed on the server and port 22 open?
0
Database Solutions Engineer FAQs

In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller single-server environments.

 
LVL 79

Expert Comment

by:arnold
ID: 41792878
Yes, this is why I suggested cygwin.

The other option you initiate an ssh connection from the server reversing the tunneling from local, to remote which has the same effect as far as the remote user is concerned.
Initiated from the Windows server to the remote Linux gateway
Using -R instead of -L.
0
 

Author Comment

by:wayneinuk
ID: 41792910
Hi,

Sorry I missed that bit.  I simply have a MySQL server in the office running on Windows 2012 R2 connected to the Internet via broadband and I want to be able to connect using Navicat, Toad or MySQL Workbench from my Windows 7 PC at home to administer the DB and make changes to the schema.

I do not have a Linux server.

Thanks
0
 
LVL 79

Expert Comment

by:arnold
ID: 41792980
To connect from your home to anything at work, you have to have a VPN connection to your work.
If at work you have a gateway server running SSH to which you can connect remotely, you could use this server with SSH tunnel to setup a VPN like connection.

HOme computer <=> ssh connection with Tunnel <=> office Linux Server

In this scenario, you should look at whether your office router has a VPN functionality, failing that, you could setup VPN services (NPS) on your windows Server 2012 and configuring the firewall to pass the ports to the windows 2012 server.
Once the VPN connectio (VPN, SSL VPN) are established from your computer to the remote server, you will be able to access your office LAN from your home computer.........
0
 

Author Comment

by:wayneinuk
ID: 41792989
Hi Arnold,

Thanks for the info, the only reason I wanted to go SSH was that I also wanted to open up the MySQL server for a couple of developers without giving them access to the whole network which the VPN route would give.

Thanks
0
 
LVL 79

Expert Comment

by:arnold
ID: 41793014
Actually, ssh would give a wider access than a VPN that can be configured on a per user basis allowing specific IPs,ports to ve accessed.
Once one has an ssh connection, they can using tunneling .......access .......
You could setup Cygwin or the like on the server. Then in the configuration make sure to disable tunneling such that anyone connecting via the ssh connection can only connect to localhost which means your MySQL int ce must be available on the localhost......

On the external firewall you would open a port that they would use which will be forwarded to this server..

For more security, you might want to consider using ssh keys, no password authentication.....
0
 

Author Comment

by:wayneinuk
ID: 41793024
Thanks Arnold,

I don't suppose there are any handy guides you know of to help me?

Thanks for all your help :-)
0
 
LVL 79

Accepted Solution

by:
arnold earned 250 total points
ID: 41793028
Help you with?   "Ssh tunneling" "ssh configuration" ref OpenSSH FAQ could provide insight.......

Get Cygwin onto your computer and tinker/practice on that.......
0
 

Author Closing Comment

by:wayneinuk
ID: 41793031
Thanks for all your help.
0

Featured Post

Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

As a database administrator, you may need to audit your table(s) to determine whether the data types are optimal for your real-world data needs.  This Article is intended to be a resource for such a task. Preface The other day, I was involved …
We all know how boring and exhausting it is to transfer huge web projects developed locally to a webserver simply via FTP. The File Transfer Protocol is a really nice solution if you need to transfer small amounts of files, but if you're plannin…
Viewers will learn how to properly install and use Secure Shell (SSH) to work on projects or homework remotely. Download Secure Shell: Follow basic installation instructions: Open Secure Shell and use "Quick Connect" to enter credentials includi…
In this video, viewers will be given step by step instructions on adjusting mouse, pointer and cursor visibility in Microsoft Windows 10. The video seeks to educate those who are struggling with the new Windows 10 Graphical User Interface. Change Cu…

623 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question