Menu

MySQL Connection over SSH

Help
Doerak
2005-04-26
2012-09-19
  • Doerak

    Doerak - 2005-04-26

    Hello. I would like to connect to my MySQL server through a SSH tunnel with encryption/compression. There is a very neat SSH implementation for Python called Paramiko (http://www.lag.net/paramiko/). What is the best way to do this? I'm not that good in Python yet. What I now have:

    MySQL<--->SSH<--->Python:Socket<--->Pyhton:Paramiko<--->Python-MySQL

    It does work, but I have the feeling there must be a more efficint way to do this.

     
    • Andy Dustman

      Andy Dustman - 2005-04-26

      You could just use the SSH command-line client. If you want your remote server to look like a local server, do this:

      ssh -z -L 3306:localhost:3306 remote

      The -L option says to forward connections made to the local port 3306 to localhost:3306 on the remote side (localhost == remote's localhost). -z enables compression. You can also set these as defaults in your configuration.

      This assumes UNIX or Linux SSH. If you're using Windows, it sucks to be you, but you can also get Cygwin (which provides a UNIX-like run-time environment on Windows) and use the SSH that comes with it (openssh). If you hunt around, you may be able to find a stand-alone version of the Cygwin SSH.

      http://cygwin.com/

      Another possibility is to use SSL instead. This requires some server AND client configuration to work. You need SSL certificates for the server and client; I believe they can be self-signed ones, which you can generate with OpenSSL. The client cert needs to be included in a GRANT. Getting MySQL to work with SSL is almost a HOW-TO by itself. Search the on-line MySQL docs for SSL so you can get an idea of what's involved.

       
      • Doerak

        Doerak - 2005-04-26

        Thanks for your answers, maybe I was not clear enough.

        I am looking into building an POS system for my company (Apple Reseller). It should be as open as possible, and run on macs, and be fast and easy to use as well as eye-pleasing. For the client-side, I want to use Python and PyObjC because I like Python, and I love cocoa widgets. For the backend, I'd like to use any SQL database (for this moment MySQL). Instead of tunneling networks (backend will be colocated) I'd like each client to make an SSH connection to the backend. This way the data is secure AND compressed. SSL is not compressed, and is quite a hassle to set up with certificates (IMHO).

        Check my sketch at http://koen.nu/Diko/Schets.pdf to make it even more clear.

         
        • Andy Dustman

          Andy Dustman - 2005-04-26

          It's true that SSL is not compressed; however, you can enable compression in the MySQL protocol by passing compress=1 to connect(). Generally I would not recommend compression unless you are connecting over a WAN connection, which is what you seem to have based on your comment above.

          The previous ssh options I listed should work fine on Mac OS X, since it uses OpenSSH. However, you might want to think about whether or not to give each client it's own credentials. If you use shared credentials, compromising one client requires you to change the credentials for all clients. If you give each clients it's own credentials, then you can disable individual clients centrally. In ssh, this means either a) creating a user account for each client and/or b) generating SSH keys for each client and updating authorized_keys on the server.

          SSL is somewhat of a pain to set up correctly, but it might actually be easier than SSH for many clients. For example, I assume you would like your clients to be as identical as possible.

          With SSL, you need to at least have a server certifcate, signed by some certificate authority. With OpenSSL, you can be your own CA. This page describes how to generate the necessary certificates:

          http://dev.mysql.com/doc/mysql/en/secure-create-certs.html

          You can use client certificates to authenticate users, but it is not required; a client certificate is only needed if you have REQUIRE X509 (or one of the variants, such as REQUIRE ISSUER) in the GRANT. REQUIRE SSL does not, by itself, require a client certificate.

          It appears that a minimal SSL setup where you don't have to pay for anything requires:

          1) Create a certificate authority.

          2) Create a server certificate.

          3) Sign the server certificate with the CA.

          4) Install the CA public certificate on the clients.

          6) Create GRANTs for the clients with REQUIRE SSL.

          6) Configure the clients so they know where to find the CA certificate. This is can be done in a MySQL configuration file which is read with the read_default_file option to connect(), or use the ssl=dict(ssl_ca=path_to_ca_file) option to connection; using read_default_file is probably better. Just passing ssl={} to connect() should be enough to enable SSL, assuming you are doing the rest with configuration files.

          I don't think there's anyway to make the MySQL client library use paramiko to do it's tunneling unless you run paramiko as a separate process, in which case, you are probably better off just using OpenSSH.

           
          • Doerak

            Doerak - 2005-04-26

            Man. I owe you. Thanks for you great answers.

            I might be looking into SSL after all.

            The only cons I can think of now is that this way I am more obligated to use the MySQL server. But that's fine with me.

             
            • Andy Dustman

              Andy Dustman - 2005-04-26

              Not necessarily: PostgreSQL 8.0 supports SSL, and apparently so do the 7.x versions. It's not clear, though, if psycopg supports SSL, but there is more than one PostgreSQL module out there.

              As near as I can tell, Firebird does not support SSL, but I am far from an authority on Firebird.

              SQLObject doesn't seem to have direct support for SSL in it's connection string format. The docs are wrong about transaction support in MySQL: InnoDb is not the only storage engine that support transactions. BDB does, and so does the new NDB (clustering).

              http://sqlobject.org/docs/SQLObject.html#mysql

              Tip jar: https://sourceforge.net/project/project_donations.php?group_id=22307

               

Log in to post a comment.

Want the latest updates on software, tech news, and AI?
Get latest updates about software, tech news, and AI from SourceForge directly in your inbox once a month.