Golang and MySQL – DigitalOcean managed cluster

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.

  1. You’ll need to provide the path to your downloaded ca-cert as the second argument
  2. 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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s