Hey everyone,
Just sharing a helper function to get you started when trying to connect to a mysql managed cluster on DigitalOcean with Golang.
Before we get into the code you’ll need to grab a couple of things from the database dashboard (on DigitalOcean).
- Open the databases tab
- Look for the “Connection Details” section
- Download your ca cert file
- Copy down your “public network” settings
- If you’re moving this into a cluster you can use the “private network” settings instead
// initDb creates initialises the connection to mysql
func initDb(connectionString string, caCertPath string) (*sql.DB, error) {
log.Infof("initialising db connection")
// Prepare ssl if required: https://stackoverflow.com/a/54189333/522859
if caCertPath != "" {
log.Infof("Loading the ca-cert: %v", caCertPath)
// Load the CA cert
certBytes, err := ioutil.ReadFile(caCertPath)
if err != nil {
log.Fatal("unable to read in the cert file", err)
}
caCertPool := x509.NewCertPool()
if ok := caCertPool.AppendCertsFromPEM(certBytes); !ok {
log.Fatal("failed-to-parse-sql-ca", err)
}
tlsConfig := &tls.Config{
InsecureSkipVerify: false,
RootCAs: caCertPool,
}
mysql.RegisterTLSConfig("bbs-tls", tlsConfig)
}
var sqlDb, err = sql.Open("mysql", connectionString)
if err != nil {
return nil, fmt.Errorf("failed to connect to the database: %v", err)
}
// Ensure that the database can be reached
err = sqlDb.Ping()
if err != nil {
return nil, fmt.Errorf("error on opening database connection: %s", err.Error())
}
return sqlDb, nil
}
A couple of things to note in the helper above.
- You’ll need to provide the path to your downloaded ca-cert as the second argument
- Your connection string will need to look something like the following: USERNAME:PASSWORD@tcp(HOST_NAME:PORT_NUMBER)/DB_NAME
Note that the “tcp(…)” bit is required, see the following post for more info: https://whatibroke.com/2021/11/27/failed-to-connect-to-the-database-default-addr-for-network-unknown-mysql-and-golang/
Depending on which version of the mysql driver you’re using you may also need to revert to the legacy auth mechanism: https://docs.digitalocean.com/products/databases/mysql/resources/troubleshoot-connections/#authentication