Emacs how to use ssh tunnel to connect to remote mysql

Question

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

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

In emacs, I configured a 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 press F10 , I get a MySQL shell.

Is it possible to configure sql-mysql, so it connected to the external machine via ssh and used that mysql program on this machine so that I could connect from Emacs in all directions?

Answer 1

Sql mode uses default-directory when it establishes a connection to the database, so if the variable is in TRAMP format, ssh (or something else) will be used for the first connection to the remote host, and then use the database client locally. To automate this, 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 a function call that asks you which host should connect, etc.

Answer 2

Take a look at sql-mysql-program on sql-mysql-options. You can bind the former to ssh and the latter to host mysql. If emacs does not insert 'sql-mysql-login-params 'in between, you will be in the know. If this is not the case, you need to create a script that is just “ssh host mysql” and then point “sql-mysql-program” to this script.

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

Answer 3

Starting with Emacs version 25.1, sql-connection-alist has a new sql-default-directory option. Setting this for each server that needs it allows you to use the backend program from any server without any oversights or special tricks. Just set up for your connection as:

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

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

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