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

Question

我目前在 Windows 8.1 上使用 MySQL Workbench，通过 SSH 使用标准 TCP/IP 访问 Linux 服务器上的远程 MySQL 数据库。基本上我有以下信息:

• SSH 主机名:dbserver.myorg.com:ssh-port
• SSH 用户名:myRemoteLoginUsername
• SSH 密码:(存储在保管库中)

• SSH key 文件:本地 .ppk 文件的路径

• MySQL 主机名:127.0.0.1

• MySQL 服务器端口:3306
• 用户名:myRemoteDb用户名
• 密码:(存储在保险库中)
• 默认架构:myRemoteDatabaseName

如何使用 github.com/go-sql-driver/mysql 从 Go 命令应用程序连接到数据库？

我在 sql.Open 语句中的 DataSourceName 字符串应该是什么样子的？

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

准备有效的 DataSourceName 字符串是否需要任何额外的工作？

在我的 Windows PC 上安装了 putty。我阅读了有关隧道的信息，并为端口 3306 (D3306) 添加了动态隧道。我希望这会让我使用到 localhost:3306 的连接进行连接，并在我使用 putty 连接到远程主机时自动将请求转发到远程数据库，但这也没有按预期工作。

Answer 1

我答应提供我的例子，它来了。基本上我的解决方案建立了一个到远程服务器的 ssh 隧道，并通过这个隧道查询远程数据库。 SSH 隧道是解决方案的一部分。

我必须做的第一件事是将我的 PuTTY .ppk 私钥文件转换为有效的 OpenSSH .pem key 文件。这可以使用 PuTTYgen 中的导出功能轻松完成。因为我想支持密码加密的私钥，所以我还需要一个函数来解密 key 并将其从解密的原始格式重新格式化为 golang.org/x/crypto/ssh/ParsePrivateKey 接受的有效格式，这是获取用于身份验证的签名者列表。

解决方案本身由包含在两个文件中的包组成。应用程序的主要部分在 main.go 中完成，其中包含所有相关数据分配以及与数据库查询相关的代码。与 ssh 隧道和 key 处理相关的所有内容都包含在 sshTunnel.go 中。

该解决方案不提供安全密码存储机制，也不要求输入密码。密码在代码中提供。但是，实现密码请求的回调方法并不会太复杂。

请注意:从性能的角度来看，这不是一个理想的解决方案。它还缺乏适当的错误处理。我已将此作为示例提供。

该示例是经过测试且有效的示例。我在 Windows 8.1 PC 上开发并使用了它。数据库服务器位于远程 Linux 系统上。您需要更改的只是 main.go 中的数据和查询部分。

这是 main.go 中包含的第一部分:

// mysqlSSHtunnel project main.go
// Establish an ssh tunnel and connect to a remote mysql server using
// go-sql-driver for database queries. Encrypted private key pem files
// are supported.
//
// This is an example to give an idea. It's far from a performant solution. It 
// lacks of proper error handling and I'm sure it could really be much better 
// implemented. Please forgive me, as I just started with Go about 2 weeks ago.
//
// The database used in this example is from a real Opensimulator installation.
// It queries the migrations table in the opensim database.
//
package main

import (
    "database/sql"
    "fmt"
    _ "github.com/go-sql-driver/mysql"
    "os"
)

// Declare your connection data and user credentials here
const (
    // ssh connection related data
    sshServerHost     = "test.example.com"
    sshServerPort     = 22
    sshUserName       = "tester"
    sshPrivateKeyFile = "testkey.pem" // exported as OpenSSH key from .ppk
    sshKeyPassphrase  = "testoster0n" // key file encrytion password

    // ssh tunneling related data
    sshLocalHost  = "localhost" // local localhost ip (client side)
    sshLocalPort  = 9000        // local port used to forward the connection
    sshRemoteHost = "127.0.0.1" // remote local ip (server side)
    sshRemotePort = 3306        // remote MySQL port

    // MySQL access data
    mySqlUsername = "opensim"
    mySqlPassword = "h0tgrits"
    mySqlDatabase = "opensimdb"
)

// The main entry point of the application
func main() {
    fmt.Println("-> mysqlSSHtunnel")

    tunnel := sshTunnel() // Initialize sshTunnel
    go tunnel.Start()     // Start the sshTunnel

    // Declare the dsn (aka database connection string)
    // dsn := "opensim:h0tgrits@tcp(localhost:9000)/opensimdb"
    dsn := fmt.Sprintf("%s:%s@tcp(%s:%d)/%s",
        mySqlUsername, mySqlPassword, sshLocalHost, sshLocalPort, mySqlDatabase)

    // Open the database
    db, err := sql.Open("mysql", dsn)
    if err != nil {
        dbErrorHandler(err)
    }
    defer db.Close() // keep it open until we are finished

    // Simple select query to check migrations (provided here as an example)
    rows, err := db.Query("SELECT * FROM migrations")
    if err != nil {
        dbErrorHandler(err)
    }
    defer rows.Close()

    // Iterate though the rows returned and print them
    for rows.Next() {
        var version int
        var name string
        if err := rows.Scan(&name, &version); err != nil {
            dbErrorHandler(err)
        }
        fmt.Printf("%s, %d\n", name, version)
    }
    if err := rows.Err(); err != nil {
        dbErrorHandler(err)
    }

    // Done for now
    fmt.Println("<- mysqlSSHtunnel")
}

// Simple mySql error handling (yet to implement)
func dbErrorHandler(err error) {
    switch err := err.(type) {
    default:
        fmt.Printf("Error %s\n", err)
        os.Exit(-1)
    }
}

现在是 sshTunnel.go 中的第二部分:

// mysqlSSHtunnel project sshTunnel.go
//
// Everything regarding the ssh tunnel goes here. Credits go to Svett Ralchev.
// Look at http://blog.ralch.com/tutorial/golang-ssh-tunneling for an excellent
// explanation and most ssh-tunneling related details used in this code.
//
// PEM key decryption is valid for password proected SSH-2 RSA Keys generated as
// .ppk files for putty and exported as OpenSSH .pem keyfile using PuTTYgen.
//
package main

import (
    "bytes"
    "crypto/x509"
    "encoding/base64"
    "encoding/pem"
    "fmt"
    "golang.org/x/crypto/ssh"
    "io"
    "io/ioutil"
    "net"
)

// Define an endpoint with ip and port
type Endpoint struct {
    Host string
    Port int
}

// Returns an endpoint as ip:port formatted string
func (endpoint *Endpoint) String() string {
    return fmt.Sprintf("%s:%d", endpoint.Host, endpoint.Port)
}

// Define the endpoints along the tunnel
type SSHtunnel struct {
    Local  *Endpoint
    Server *Endpoint
    Remote *Endpoint
    Config *ssh.ClientConfig
}

// Start the tunnel
func (tunnel *SSHtunnel) Start() error {
    listener, err := net.Listen("tcp", tunnel.Local.String())
    if err != nil {
        return err
    }
    defer listener.Close()

    for {
        conn, err := listener.Accept()
        if err != nil {
            return err
        }
        go tunnel.forward(conn)
    }
}

// Port forwarding
func (tunnel *SSHtunnel) forward(localConn net.Conn) {
    // Establish connection to the intermediate server
    serverConn, err := ssh.Dial("tcp", tunnel.Server.String(), tunnel.Config)
    if err != nil {
        fmt.Printf("Server dial error: %s\n", err)
        return
    }

    // access the target server
    remoteConn, err := serverConn.Dial("tcp", tunnel.Remote.String())
    if err != nil {
        fmt.Printf("Remote dial error: %s\n", err)
        return
    }

    // Transfer the data between  and the remote server
    copyConn := func(writer, reader net.Conn) {
        _, err := io.Copy(writer, reader)
        if err != nil {
            fmt.Printf("io.Copy error: %s", err)
        }
    }

    go copyConn(localConn, remoteConn)
    go copyConn(remoteConn, localConn)
}

// Decrypt encrypted PEM key data with a passphrase and embed it to key prefix
// and postfix header data to make it valid for further private key parsing.
func DecryptPEMkey(buffer []byte, passphrase string) []byte {
    block, _ := pem.Decode(buffer)
    der, err := x509.DecryptPEMBlock(block, []byte(passphrase))
    if err != nil {
        fmt.Println("decrypt failed: ", err)
    }
    encoded := base64.StdEncoding.EncodeToString(der)
    encoded = "-----BEGIN RSA PRIVATE KEY-----\n" + encoded +
        "\n-----END RSA PRIVATE KEY-----\n"
    return []byte(encoded)
}

// Get the signers from the OpenSSH key file (.pem) and return them for use in
// the Authentication method. Decrypt encrypted key data with the passphrase.
func PublicKeyFile(file string, passphrase string) ssh.AuthMethod {
    buffer, err := ioutil.ReadFile(file)
    if err != nil {
        return nil
    }

    if bytes.Contains(buffer, []byte("ENCRYPTED")) {
        // Decrypt the key with the passphrase if it has been encrypted
        buffer = DecryptPEMkey(buffer, passphrase)
    }

    // Get the signers from the key
    signers, err := ssh.ParsePrivateKey(buffer)
    if err != nil {
        return nil
    }
    return ssh.PublicKeys(signers)
}

// Define the ssh tunnel using its endpoint and config data
func sshTunnel() *SSHtunnel {
    localEndpoint := &Endpoint{
        Host: sshLocalHost,
        Port: sshLocalPort,
    }

    serverEndpoint := &Endpoint{
        Host: sshServerHost,
        Port: sshServerPort,
    }

    remoteEndpoint := &Endpoint{
        Host: sshRemoteHost,
        Port: sshRemotePort,
    }

    sshConfig := &ssh.ClientConfig{
        User: sshUserName,
        Auth: []ssh.AuthMethod{
            PublicKeyFile(sshPrivateKeyFile, sshKeyPassphrase)},
    }

    return &SSHtunnel{
        Config: sshConfig,
        Local:  localEndpoint,
        Server: serverEndpoint,
        Remote: remoteEndpoint,
    }
}