Emacs how to use ssh tunnel to connect to remote MySQL

Question

I have a bunch of remote MySQL servers, that allow connection only from localhost. To connect to them I do the following:

ssh host
mysql -uuser -psecret -hhost.myhost.com

In emacs I configured the connection to local MySQL, using sql-mysql-mode:

(setq sql-connection-alist
      '((pool-a
         (sql-product 'mysql)
         (sql-server "localhost")
         (sql-user "user")
         (sql-password "secret")
         (sql-database "")
         (sql-port 3306))
        ))

(defun sql-connect-preset (name)
  "Connect to a predefined SQL connection listed in `sql-connection-alist'"
  (eval `(let ,(cdr (assoc name sql-connection-alist))
    (flet ((sql-get-login (&rest what)))
      (sql-product-interactive sql-product)))))

(defun sql-local ()
  "Connect to the local MySQL server"
  (interactive)
  (sql-connect-preset 'pool-a)
  (delete-other-windows))

(define-key global-map [f10] 'sql-local)

So, every time I click F10, I get MySQL shell.

Is it possible to tune sql-mysql, so it connected to external machine via ssh and used mysql program on that machine, so I could connect from Emacs to everywhere?

Zugzwang

Answer 1

Sql mode uses default-directory when it establishes the connection to the database, so if the variable is in TRAMP format, ssh (or whatever) will be used to connect to the remote host first, then use the database client locally. To automate that you can do something like

(defadvice sql-mysql (around sql-mysql-around activate)
  "SSH to linux, then connect"
  (let ((default-directory "/ssh:host.myhost.com:"))
    ad-do-it))

You can of course replace "/ssh:host.myhost.com:" with the function call that would ask you which host to connect, etc.

Loadstar

It gives an error "apply: Symbol's function definition is void: sql-get-login" – Zugzwang

sql-get-login is defined in sql.el. Did you load it? Try (require 'sql) prior to defining the advice. – Loadstar

Answer 2

Take a look to 'sql-mysql-program' to "and 'sql-mysql-options'. You can bind the first to "ssh" and the second to "host mysql". If emacs does not insert 'sql-mysql-login-params' between them, you will be in the clear. If that is not the case, you need to create a script that just does "ssh host mysql" and then point 'sql-mysql-program' to that script.

If you need to use both tunneled and not tunneled mysql connections at the same time, you might consider adding a new element to 'sql-product-alist' (something like "tunneled-mysql").

Kosher

Ok, let's go for the easier option first. Just create a shell script in your path named "mysql_host" that contains ssh -t your_host $*. Then do a (setq sql-mysql-program "mysql_host"). Let us know if this works for you. – Kosher

Answer 3

As of Emacs 25.1 there is a new sql-default-directory option in the sql-connection-alist. Setting this for each server that needs it allows you to use the backend program from any given server without any defadvice or product-specific tricks. Just the setting to your the connection like:

(sql-default-directory "/host.myhost.com:")

Or, if you need to specify ssh for some reason:

(sql-default-directory "/ssh:host.myhost.com:")

Benedikt

(setq sql-default-directory "/host.myhost.com:") works perfectly, thanks! – Johnston