如何使用go-sql-driver通过SSH通过标准TCP / IP连接到MySQL？

Question

I'm currently using MySQL Workbench on Windows 8.1 to access a remote MySQL database on a Linux server using Standard TCP/IP over SSH. Basically I have the following information:

• SSH Hostname: dbserver.myorg.com:ssh-port
• SSH Username: myRemoteLoginUsername
• SSH Password: (stored in vault)

• SSH Key File: path to a local .ppk file

• MySQL Hostname: 127.0.0.1

• MySQL Server Port: 3306
• Username: myRemoteDbUsername
• Password: (stored in vault)
• Default schema: myRemoteDatabaseName

How can I connect to the database from a Go command application using github.com/go-sql-driver/mysql?

how should my DataSourceName string in the sql.Open statement look like?

    db, err := sql.Open("mysql", <DataSourceName> ) {}

Is there any extra work needed to prepare a working DataSourceName string?

On my Windows PC I have putty installed. I read about tunneling and added a Dynamic tunnel for port 3306 (D3306). I expected this will let me connect using a connection to localhost:3306 and automatically forward the request to the remote db whenever I'm connected to the remote host with putty, but this didn't work as expected either.

Answer 1

Well, you could do that "full Go", I think.

The SSH part and port-forwarding

I'd start with something like this (I failed to google a better example).

Note two problems with this code:

1. It's not actually correct: it connects to a remote socket before accepting a client connection while it should do the reverse: accept a client connection to a port-forwarded local socket then use the active SSH session to connect to the remote socket and if this succeeds, spawn two goroutines to shovel the data between those two sockets.

2. When configuring the SSH client, it explicitly allows password-based authentication for unknown reason. You don't need this as you're using pubkey-based auth.

An obstacle which might trip you there is managing an access to your SSH key. The problem with it is that a good key should be protected by a passphrase.

You say the key's password is "stored in valut" and I honestly have no idea what "the valut" is.

On the systems I use, an SSH client either asks for the password to decrypt the key or work with a so-called "SSH agent":

• On Linux-based systems it's typically an OpenSSH's ssh-agent binary working in the background which is accessed via a Unix-domain socket, and located by inspecting an environment variable named SSH_AUTH_SOCK.
• On Windows I use PuTTY, and it has its own agent, pageant.exe. I'm don't know which means PuTTY SSH client uses to locate it.

To access the OpenSSH agent, golang.org/x/crypto/ssh provides the agent subpackage which can be used to locate the agent and communicate with it. If you need to obtain keys from pageant, I'm afraid you'll need to figure out what protocol that uses and implement it.

The MySQL part

The next step would be to integrate this with go-sql-driver.

I'd start the simplest way possible:

1. When you have your SSH port forwarding working, make it listen for incoming connections on a random port on localhost. When the connection is opened, get the port from the returned connection object.
2. Use that port number to constuct the connection string to pass to an instance of sql.DB you will create to use go-sql-driver.

The driver will then connect to your port-forwarded port, and your SSH layer will do the rest.

After you have this working, I'd explore whether the driver of your choice allows some more fine-grained tweaking like allowing you to directly pass it an instance of io.ReadWriter (an opened socket) so that you could skip the port-forwarding setup entirely and just produce new TCP connections forwarded through SSH, that is, skip the "listening locally" step.