Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Remote MySQL using SSH

Posted on 2016-09-10
11
Medium Priority
?
187 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
  • 5
  • 5
11 Comments
 
LVL 60

Assisted Solution

by:Julian Hansen
Julian Hansen earned 1000 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 80

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
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
LVL 80

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 80

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 80

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 80

Accepted Solution

by:
arnold earned 1000 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

Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

Question has a verified solution.

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

In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
By, Vadim Tkachenko. In this article we’ll look at ClickHouse on its one year anniversary.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses
Course of the Month12 days, 9 hours left to enroll

972 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