網頁

2021/10/25

Golang 連線到AWS RDS MySQL資料庫 帳號密碼認證 connect to aws rds mysql instance auth by username and password

Go連線到AWS RDS MySQL資料庫實例,使用資料庫帳號密碼驗證(非IAM驗證)。


範例環境:

  • Go 1.16
  • Amazon RDS MySQL Community 8.0.23

建立AWS RDS MySQL實例


參考「AWS RDS 建立MySQL instance」建立RDS MySQL資料庫實例。

AWS RDS MySQL資料庫實例的endpoint為rds-mysql-free-001.c9u62rqfjvs8.us-east-2.rds.amazonaws.com, port 3306
資料庫驗證方式為密碼驗證(password authentication)。




取得Go-MySQL-Driver


使用Go-MySQL-Driver package即可連線,在命令列輸入go get -u github.com/go-sql-driver/mysql取得Go-MySQL-Driver函式庫。

$ go get -u github.com/go-sql-driver/mysql
go: downloading github.com/go-sql-driver/mysql v1.6.0
go get: added github.com/go-sql-driver/mysql v1.6.0


連線及查詢


使用Go-MySQL-Driver連線RDS MySQL實例及查詢方式如下。

main.go

package main

import (
    "database/sql"
    "fmt"

    _ "github.com/go-sql-driver/mysql"
)

func main() {
    db := connect()

    id := 1
    rows, err := db.Query("SELECT * FROM employee where id=?", id)
    if err != nil {
        panic(err)
    }
    defer rows.Close()

    for rows.Next() {
        var id int
        var name string
        var age int
        err = rows.Scan(&id, &name, &age)
        fmt.Printf("id=%d, name=%s, age=%d\n", id, name, age)
    }
}

func connect() *sql.DB {
    driver := "mysql"
    user := "mysqladmin"
    password := "mysqladmin"
    endpoint := "rds-mysql-free-001.c9u62rqfjvs8.us-east-2.rds.amazonaws.com"
    port := "3306"
    dbName := "mydb"
    charset := "charset=utf8mb4"
    dsn := fmt.Sprintf("%s:%s@tcp(%s:%s)/%s?%s", user, password, endpoint, port, dbName, charset)
    fmt.Println(dsn) // mysqladmin:mysqladmin@tcp(rds-mysql-free-001.c9u62rqfjvs8.us-east-2.rds.amazonaws.com:3306)/mydb?charset=utf8mb4

    db, err := sql.Open(driver, dsn)
    if err != nil {
        panic(err)
    }

    err = db.Ping()
    if err != nil {
        panic(err)
    }
    return db
}

執行後印出以下結果。

mysqladmin:mysqladmin@tcp(rds-mysql-free-001.c9u62rqfjvs8.us-east-2.rds.amazonaws.com:3306)/mydb?charset=utf8mb4
id=1, name=john, age=33

參考github


沒有留言:

張貼留言