emacssql-mode

Emacs how to use ssh tunnel to connect to remote MySQL


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?


Solution

  • 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.