Skip to content

Instantly share code, notes, and snippets.

@vinzenz
Created November 7, 2016 10:27
Show Gist options
  • Save vinzenz/d8e6834d9e25bbd422c14326f357cce0 to your computer and use it in GitHub Desktop.
Save vinzenz/d8e6834d9e25bbd422c14326f357cce0 to your computer and use it in GitHub Desktop.
Using MySQL / MariaDB via SSH in Golang
package main
import (
"database/sql"
"fmt"
"net"
"os"
"github.com/go-sql-driver/mysql"
"golang.org/x/crypto/ssh"
"golang.org/x/crypto/ssh/agent"
)
type ViaSSHDialer struct {
client *ssh.Client
}
func (self *ViaSSHDialer) Dial(addr string) (net.Conn, error) {
return self.client.Dial("tcp", addr)
}
func main() {
sshHost := "example.com" // SSH Server Hostname/IP
sshPort := 22 // SSH Port
sshUser := "ssh-user" // SSH Username
sshPass := "ssh-pass" // Empty string for no password
dbUser := "dbuser" // DB username
dbPass := "dbpass" // DB Password
dbHost := "localhost:3306" // DB Hostname/IP
dbName := "database" // Database name
var agentClient agent.Agent
// Establish a connection to the local ssh-agent
if conn, err := net.Dial("unix", os.Getenv("SSH_AUTH_SOCK")); err == nil {
defer conn.Close()
// Create a new instance of the ssh agent
agentClient = agent.NewClient(conn)
}
// The client configuration with configuration option to use the ssh-agent
sshConfig := &ssh.ClientConfig{
User: sshUser,
Auth: []ssh.AuthMethod{},
}
// When the agentClient connection succeeded, add them as AuthMethod
if agentClient != nil {
sshConfig.Auth = append(sshConfig.Auth, ssh.PublicKeysCallback(agentClient.Signers))
}
// When there's a non empty password add the password AuthMethod
if sshPass != "" {
sshConfig.Auth = append(sshConfig.Auth, ssh.PasswordCallback(func() (string, error) {
return sshPass, nil
}))
}
// Connect to the SSH Server
if sshcon, err := ssh.Dial("tcp", fmt.Sprintf("%s:%d", sshHost, sshPort), sshConfig); err == nil {
defer sshcon.Close()
// Now we register the ViaSSHDialer with the ssh connection as a parameter
mysql.RegisterDial("mysql+tcp", (&ViaSSHDialer{sshcon}).Dial)
// And now we can use our new driver with the regular mysql connection string tunneled through the SSH connection
if db, err := sql.Open("mysql", fmt.Sprintf("%s:%s@mysql+tcp(%s)/%s", dbUser, dbPass, dbHost, dbName)); err == nil {
fmt.Printf("Successfully connected to the db\n")
if rows, err := db.Query("SELECT id, name FROM table ORDER BY id"); err == nil {
for rows.Next() {
var id int64
var name string
rows.Scan(&id, &name)
fmt.Printf("ID: %d Name: %s\n", id, name)
}
rows.Close()
} else {
fmt.Printf("Failure: %s", err.Error())
}
db.Close()
} else {
fmt.Printf("Failed to connect to the db: %s\n", err.Error())
}
}
}
@jlpellicer
Copy link

A couple of updates:
1- my dbHost had to be localhost, not 127.0.0.1
2- had to add HostKeyCallback: ssh.InsecureIgnoreHostKey(), to sshConfig

Thanks for the gist!

@latdev
Copy link

latdev commented Apr 25, 2019

Amazing! Thanks!

@jigetage
Copy link

very very good, buddy.
thanks a lot.

@p1an-lin-jung
Copy link

oh! dude, u have been a big help.

@eva-ave
Copy link

eva-ave commented Apr 27, 2021

This worked for me for a remote connection(with jlpellicer's updates)! thank you for sharing! :)

@jingkang99
Copy link

@ jlpellicer

wonderful comments. Thanks for you all.

@avdifua
Copy link

avdifua commented Jan 26, 2022

This method depricated line 64:

mysql.RegisterDial("mysql+tcp", (&ViaSSHDialer{sshcon}).Dial)

Do you know how to fix this?

@vinzenz
Copy link
Author

vinzenz commented Jan 26, 2022

@protos-kr

mysql.RegisterDialContext("mysql+tcp", func(_ context.Context, addr string) (net.Conn, error) {
                dialer := &ViaSSHDialer{sshcon})
		return dialer.Dial(addr)
	})

@avdifua
Copy link

avdifua commented Jan 26, 2022

@vinzenz thanks for your answer!

mysql.RegisterDialContext("mysql+tcp", func(_ context.Context, addr string) (net.Conn, error) {
	dialer := &ViaSSHDialer{sshcon}
	return dialer.Dial(addr)
})

Thanks! Now it's working!

@geekgogo
Copy link

geekgogo commented Sep 9, 2022

thanks dude

@Calyred
Copy link

Calyred commented Mar 30, 2023

is dbHost := "localhost:3306" is always localhost or it depend on my IP ?

@eddturtle
Copy link

This was really useful - I wrote up a blog post with my code using a connect function + cred struct: https://gophercoding.com/sql-query-through-ssh/

@nicoxb
Copy link

nicoxb commented Dec 6, 2023

Nice~

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment