0

Sorry if this is a bit of a broad question, but are there any best practices regarding accessing a remote MySQL via remote desktop versus locally through some other method? The goal here is to access a MySQL instance set up on a Windows 2008 r2 server and pull data mainly into Microsoft Excel. If you were to set this up, how would you do it (securely) and why?

3
  • MySQL can SELECT straight to CSV, so it's probably not necessary to do much, or to set up remote access to anything. Apr 11 '16 at 19:54
  • @MichaelHampton Thanks, would that work even on very large data sets though? Is there a size at which another method becomes better?
    – 114
    Apr 11 '16 at 20:04
  • Depends on how fast your database can SELECT the appropriate records, and how fast the disk is that you write the CSV to. Apr 11 '16 at 20:06
2

Most MySQL installations come with networking disabled by default, e.g the my.cnf contains a line similiar to bind-address = 127.0.0.1 which limits the network access to localhost for security reasons.

If you choose to enable networking by altering this line you need to make sure that you reconfigure all your MySQL users, to distinguish between local and remote MySQL users. You'll want to make sure all your remote users also have REQUIRE SSL in their GRANT statement to enforce encryption for all remote connections. MySQL has a step-by-step setup for secure remote connections here.

On most (all?) linux based webservers MySQL remoting is disabled and the commonly accepted way to open up a remote MySQL connection here, is to tunnel SQL through SSH, so MySQL can be left unchanged and configured to allow only local connections. This is also possible on Windows Servers, but of course only with the added overhead of installing an SSH server (for example via Cygwin) first.

I would personally prefer to use the SSH variant as it allows MySQL to be left unchanged - you don't need to mess with your users & permissions inside MySQL. Also as a matter of personal opinion, I would think opening up MySQL as a protocol over the network is widening the attack surface of the server to a greater extent, then having SSH running does - especially if you are using key based authentification with SSH.

4
  • Thanks, do you have a recommendation for an SSH tunnel setup? A link would do.
    – 114
    Apr 11 '16 at 21:34
  • @114 I would really use cygwin with the OpenSSH deamon for the server side setup. There are a lot of tutorials. This for example looks like a pretty complete/decent one. On the client side, almost any tool has native support for SSH tunneling, examples are the already mentioned MySQL Workbench (free) HeidiSQL (free) or SQLyog (commercial) or MySQL for Excel (free, using Putty for the SSH tunnel).
    – s1lv3r
    Apr 11 '16 at 21:48
  • One other problem - doesn't this open the server up to intrusion?
    – 114
    Apr 11 '16 at 22:00
  • Not sure, what you exactly mean with intrusion - if you want to access MySQL over the network (whether via RDP, SSH or the MySQL protocol directly) you have to allow some kind of connection. In my opinion the most secure way is using SSH with key based authentification.
    – s1lv3r
    Apr 11 '16 at 22:07
1

MySQL Workbench is a unified visual tool for database architects, developers, and DBAs. MySQL Workbench provides data modeling, SQL development, and comprehensive administration tools for server configuration, user administration, backup, and much more. MySQL Workbench is available on Windows, Linux and Mac OS X.

MySQL :: MySQL Workbench

4
  • Thanks, so you would say that doing anything via remote desktop would be unnecessary? MySQL Workbench does seem like a pretty easy solution.
    – 114
    Apr 11 '16 at 19:53
  • 1
    ... once you connect via RDP, you still need workbench to do your stuff, so why not install it on your computer locally instead of RDP to remote host to run same software? plus RDP = lag.
    – alexus
    Apr 11 '16 at 19:56
  • What about security? Is there a way to ensure this method is secure?
    – 114
    Apr 11 '16 at 20:22
  • yes, configure your mysql-server to do ssl, workbench supports ssl as well.
    – alexus
    Apr 11 '16 at 20:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.