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?

score 11 · Accepted Answer · answered Jun 24 '13 at 13:43

11

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.

answered Jun 24 '13 at 13:43

Alex Vorobiev

4,349
21
29

It gives an error "apply: Symbol's function definition is void: sql-get-login" – user4035 Jun 25 '13 at 21:00
sql-get-login is defined in sql.el. Did you load it? Try (require 'sql) prior to defining the advice. – Alex Vorobiev Jun 26 '13 at 02:58

juanleon · Answer 2 · 2013-06-25T10:40:33.800

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").

edited Jun 25 '13 at 10:40

answered Jun 24 '13 at 06:56

juanleon

9,220
30
41

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. – juanleon Jun 26 '13 at 07:25

score 2 · Answer 3 · answered Jan 16 '17 at 16:37

2

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:")

answered Jan 16 '17 at 16:37

Matthew Krauss

77
8

`(setq sql-default-directory "/host.myhost.com:")` works perfectly, thanks! – Peter Becich Apr 21 '23 at 04:57

Emacs how to use ssh tunnel to connect to remote MySQL

3 Answers3

Linked