ApsaraDB RDS:Use Go-MySQL-Driver to connect to an ApsaraDB RDS for MySQL instance

Limits

Only RDS instances that run MySQL 5.7 or 8.0 are supported.

Prerequisites

• Go 1.20 or later is installed. For more information, see Download and install.

• The IP address of your client is added to the IP address whitelist of the RDS instance. For more information, see Configure an IP address whitelist.

  Note

  If your application is deployed on an Elastic Compute Service (ECS) instance that resides in the same region and virtual private cloud (VPC) as the RDS instance, you do not need to configure an IP address whitelist.

Use Go-MySQL-Driver to connect to an RDS instance

Addr // Create a database connection.
    cfg := mysql.NewConfig()
    cfg.User = "****" // Replace the value with the username of an account in your RDS instance.
    cfg.Passwd = "****" // Replace the value with the password of the account.
    cfg.Net = "tcp" // The connection type is TCP. Retain the default value. You do not need to modify the value.
    cfg.Addr = "rm-2zefwjx1s8156******.mysql.rds.aliyuncs.com:3306" // Replace the value with the endpoint and port number of your RDS instance.
    cfg.DBName = "****" // Replace the value with the name of the RDS instance.

    conn, err := mysql.NewConnector(cfg)
    if err != nil {
	panic(err.Error())
    }

    db := sql.OpenDB(conn)
    defer db.Close()

    // Configure the parameters related to the connection pool.
    db.SetMaxOpenConns(20)   // Specify the maximum number of open connections in the connection pool. You can modify the value based on your business requirements.
    db.SetMaxIdleConns(2)    // Specify the maximum number of idle connections in the connection pool. You can modify the value based on your business requirements.
    db.SetConnMaxIdleTime(10 * time.Second)   // Specify the maximum period of time that a connection in the connection pool can stay idle. You can modify the value based on your business requirements.
    db.SetConnMaxLifetime(80 * time.Second)   // Specify the maximum period of time that a connection can be reused. You can modify the value based on your business requirements.

Perform operations on a database

_, err = db.Exec("create table  if not exists userinfo( uid int auto_increment, username varchar(20) not null default '', departname varchar(20) not null default '', created varchar(10) not null default '', primary key(uid) );")
if err != nil {
    fmt.Println("create table error ", err)
    return
}

result, err := db.Exec("INSERT INTO userinfo (username, departname, created) VALUES (?, ?, ?)", "Linda", "Testing", "2016-06-21")
checkErr(err)
ids, err := result.LastInsertId()
fmt.Println(ids)

stmt, err := db.Prepare("INSERT INTO userinfo SET username=?,departname=?,created=?")
res, err := stmt.Exec("James", "Research", "2016-06-17")
id, err := res.LastInsertId()
checkErr(err)
fmt.Println(id)

stmt, err = db.Prepare("UPDATE userinfo SET username=?, departname=?, created=?  WHERE uid=?")
if err != nil {
    // Handle the errors that may occur when the system prepares SQL statements for execution.
}
res, err = stmt.Exec("Robert", "Sales", "2024-09-23", 2)
if err != nil {
    fmt.Println(err)
}
rowCnt, _ := res.RowsAffected()
fmt.Println(rowCnt)

rows, err := db.Query("SELECT username,departname,created FROM userinfo WHERE username=?", "Linda")
for rows.Next() {
	var username, departname, created string
	if err := rows.Scan(&username, &departname, &created); err != nil {
		fmt.Println(err)
	}
	fmt.Println("username:", username, "departname:", departname, "created:", created)
}

stmt, err = db.Prepare("SELECT username,departname,created FROM userinfo WHERE username=?")
if err != nil {
	fmt.Println("prepare error", err)
	return
}
rows, err = stmt.Query("Linda")
if err != nil {
	fmt.Println("query data error", err)
	return
}
defer rows.Close()
for rows.Next() {
	var username, departname, created string
	if err := rows.Scan(&username, &departname, &created); err != nil {
		fmt.Println(err)
	}
	fmt.Println("username:", username, "departname:", departname, "created:", created)
}

del_stmt, _ := db.Prepare("DELETE FROM userinfo WHERE uid=?")
del_stmt.Exec(1)

Sample code

package main

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

func main() {
    // Create a database connection.
    cfg := mysql.NewConfig()
    cfg.User = "****"
    cfg.Passwd = "****"
    cfg.Net = "tcp"
    cfg.Addr = "rm-2zefwjx1s8156******.mysql.rds.aliyuncs.com:3306"
    cfg.DBName = "****"

    conn, err := mysql.NewConnector(cfg)
    if err != nil {
       panic(err.Error())
    }

    db := sql.OpenDB(conn)
    defer db.Close()

    // Configure the parameters related to the connection pool.
    db.SetMaxOpenConns(20)                 // Specify the maximum number of open connections in the connection pool.
    db.SetMaxIdleConns(2)                  // Specify the maximum number of idle connections in the connection pool.
    db.SetConnMaxIdleTime(5 * time.Minute) // Specify the maximum period of time that a connection in the connection pool can stay idle.
    db.SetConnMaxLifetime(8 * time.Minute) // Specify the maximum period of time that a connection can be reused.
    checkErr(err)

    _, err = db.Exec("create table  if not exists userinfo( uid int auto_increment, username varchar(20) not null default '', departname varchar(20) not null default '', created varchar(10) not null default '', primary key(uid) );")
    if err != nil {
       fmt.Println("create table error ", err)
       return
    }

    stmt, err := db.Prepare("INSERT userinfo SET username=?,departname=?,created=?")
    res, err := stmt.Exec("zhja", "R&D", "2016-06-17")
    id, err := res.LastInsertId()
    checkErr(err)
    fmt.Println(id)

    result, err := db.Exec("INSERT INTO userinfo (username, departname, created) VALUES (?, ?, ?)", "lily", "Sales", "2016-06-21")
    checkErr(err)
    ids, err := result.LastInsertId()
    fmt.Println(ids)

    del_stmt, _ := db.Prepare("DELETE FROM userinfo WHERE uid=?")
    del_stmt.Exec(1)

    stmt, err = db.Prepare("UPDATE userinfo SET username=?, departname=?, created=?  WHERE uid=?")
    if err != nil {
       // Handle errors.
    }
    res, err = stmt.Exec("lisi", "Test", "2024-09-23", 2)
    if err != nil {
       fmt.Println(err)
    }
    rowCnt, _ := res.RowsAffected()
    fmt.Println(rowCnt)

    rows, err := db.Query("SELECT username,departname,created FROM userinfo WHERE username=?", "lisi")
    for rows.Next() {
       var username, departname, created string
       if err := rows.Scan(&username, &departname, &created); err != nil {
          fmt.Println(err)
       }
       fmt.Println("username:", username, "departname:", departname, "created:", created)
    }

    stmt, err = db.Prepare("SELECT username,departname,created FROM userinfo WHERE username=?")
    if err != nil {
       fmt.Println("prepare error", err)
       return
    }
    rows, err = stmt.Query("lisi")
    if err != nil {
       fmt.Println("query data error", err)
       return
    }
    defer rows.Close()
    for rows.Next() {
       var username, departname, created string
       if err := rows.Scan(&username, &departname, &created); err != nil {
          fmt.Println(err)
       }
       fmt.Println("username:", username, "departname:", departname, "created:", created)
    }

}

func checkErr(err error) {
    if err != nil {
       panic(err)
    }
}

References

• For more information about how to use a connection pool, see Use DBUtils to connect to an ApsaraDB RDS for MySQL instance.

• For more information about how to use a connection pool, see Use Druid to connect to an ApsaraDB RDS for MySQL instance.

• For more information about the connection pooling feature of a database proxy in ApsaraDB RDS for MySQL, see Configure the connection pooling feature.