Skip to content

Instantly share code, notes, and snippets.

@jayjanssen
Last active March 16, 2023 02:21
Show Gist options
  • Save jayjanssen/8e74bc4c5bdefc880ffd to your computer and use it in GitHub Desktop.
Save jayjanssen/8e74bc4c5bdefc880ffd to your computer and use it in GitHub Desktop.
Golang MySQL connection with multiple IPs and connection timeouts
package main
import (
"database/sql"
"fmt"
"github.com/go-sql-driver/mysql"
"log"
"net/http"
"strings"
"time"
"net"
"math/rand"
)
const (
pxc_hosts string = `pxc.service.consul:3306`
user = `test`
pass = `test`
schema = `sakila`
options = `timeout=500ms`
max_idle int = 0 // Can't keep idle conns open b/c of: https://github.com/go-sql-driver/mysql/issues/257
concurrency = 10 // Keep this script from blasting the database
retry_delay = 500 * time.Millisecond
lag = 50 * time.Millisecond
)
// Our global writer DB handle
var writer *sql.DB
var hits int
func main() {
var err error
// Setup our SQL pool
writer, err = sql.Open(`mysql`,
fmt.Sprintf("%s:%s@tcp(%s)/%s?%s", user, pass, pxc_hosts,
schema, options))
if err != nil {
log.Fatal("Got ", err, "trying to connect to writer")
}
writer.SetMaxIdleConns(max_idle)
writer.SetMaxOpenConns(concurrency)
mysql.RegisterDial( `tcp`, func( addr string ) (net.Conn, error) {
host, port, err := net.SplitHostPort( addr ) // MUST have the port component
if err != nil {
return nil, &net.OpError{Op: "dial", Net:`tcp`, Addr: nil, Err: err}
}
addrs, err := net.LookupHost( host )
if err != nil {
return nil, &net.OpError{Op: "dial", Net:`tcp`, Addr: nil, Err: err}
}
selected := addrs[rand.Intn(len(addrs))]
fmt.Println( "Selected: ", selected )
timeout, _ := time.ParseDuration(`500ms`)
nd := net.Dialer{Timeout: timeout }
conn, err := nd.Dial(`tcp`, net.JoinHostPort( selected, port ))
if err != nil {
return nil, &net.OpError{Op: "dial", Net:`tcp`, Addr: nil, Err: err}
}
// Close the connection after 10 seconds to force the pool to reconnect
// this isn't necessarily graceful, but it seems mostly ok.
go func() {
time.Sleep( 10 * time.Second )
conn.Close()
}()
return conn, nil
})
defer writer.Close()
@tonyabracadabra
Copy link

Does this caches connections from different databases?

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