How to connect to a distributed MySQL database using Go language

In a distributed system, the database plays a very key role. For large distributed systems, we often need to use multiple databases for amortized processing to meet the needs of the application. As a widely used relational database, MySQL can connect and manage multiple instances through Go language to build a highly available, high-performance, distributed system.

This article will introduce how to use the Go language to connect to a distributed MySQL database, which is divided into the following parts:

1. Install the Go language
2. Install the MySQL driver
3. Connect to MySQL database
4. Use connection pool to manage multiple instances
5. Use distributed transactions

1. Install Go language

First, we need to install Go language environment. We can download the Go language installation package from the Go official website: https://golang.org/dl/. Select the appropriate version for your operating system, download and install it.

2. Install the MySQL driver

You need to use the Go MySQL driver to connect to the MySQL database. We can use the official MySQL driver or a third-party driver. Here we take the official driver as an example. The official MySQL driver is: github.com/go-sql-driver/mysql

We can use the go get command to obtain and install the MySQL driver. Execute the following command in the terminal:

go get -u github.com/go-sql-driver/mysql

3. Connect to MySQL database

To connect to the MySQL database, you need to specify the IP address, port number, user name and password of the database instance. In Go, we can connect to the MySQL database through the API provided by the database/sql package. The following is a sample program to connect to the MySQL database:

package main

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

func main() {
    //连接MySQL数据库
    db, err := sql.Open("mysql", "root:123456@tcp(127.0.0.1:3306)/test")
    if err != nil {
        panic(err.Error())
    }
    defer db.Close()

    //查询MySQL数据库中的数据
    rows, err := db.Query("SELECT * from user")
    if err != nil {
        panic(err.Error())
    }
    defer rows.Close()

    //遍历查询结果
    for rows.Next() {
        var id int
        var name string
        if err := rows.Scan(&id, &name); err != nil {
            panic(err.Error())
        }
        fmt.Printf("id: %d, name: %s
", id, name)
    }
}

In the above sample program, we first use the sql.Open() function to connect to the MySQL database. The parameter "mysql" means using the MySQL database; "root:123456" is the user name and password; "127.0.0.1:3306" is the database address and port number; "/test" is the database name.

Then, we use the db.Query() function to query the data in the MySQL database. The query result is a Rows object. Finally, we use the rows.Next() function to iterate through the query results, obtain the data and output it.

4. Use connection pool to manage multiple instances

When using MySQL database in a distributed system, we usually need to use multiple database instances and use connection pool to manage them these examples. In Go language, we can use the sql.DB object provided by the database/sql package to implement connection pooling. The following is a sample program that uses a connection pool to manage multiple MySQL database instances:

package main

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

var dbMap = sync.Map{}

func getDBInstance(addr string) (*sql.DB, error) {
    var (
        db  *sql.DB
        err error
    )
    //从连接池中获取数据库实例
    if val, ok := dbMap.Load(addr); ok {
        db = val.(*sql.DB)
        return db, nil
    }

    //创建新的数据库实例
    db, err = sql.Open("mysql", "root:123456@"+addr+"/test")
    if err != nil {
        return nil, err
    }

    //将新的数据库实例加入连接池中
    dbMap.Store(addr, db)
    return db, nil
}

//查询数据库中数据
func query(addr string) {
    db, err := getDBInstance(addr)
    if err != nil {
        panic(err)
    }
    rows, err := db.Query("SELECT * from user")
    if err != nil {
        panic(err)
    }
    defer rows.Close()

    for rows.Next() {
        var id int
        var name string
        if err := rows.Scan(&id, &name); err != nil {
            panic(err)
        }
        fmt.Printf("id:%d name:%s
", id, name)
    }
}

func main() {
    addrList := []string{"127.0.0.1:3306", "127.0.0.1:3307"}
    for _, addr := range addrList {
        go query(addr)
    }
    select {}
}

In the above sample program, we use a sync.Map object dbMap to store all database instances in the connection pool.

When querying data, we first use the getDBInstance() function to obtain the database instance from the connection pool. If not found, use the sql.Open() function to create a new database instance, and then add this instance to the connection pool.

Then, we use the db.Query() function to query data from the database. Finally, we use the rows.Scan() function to traverse the query results, obtain the data and output it.

5. Using distributed transactions

When using multiple MySQL databases in a large distributed system, we may need to perform transaction operations on different data sources. In the Go language, we can use the Tx object provided by the database/sql package to manage distributed transactions. The following is a sample program using distributed transactions:

package main

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

var dbMap = sync.Map{}

func getDBInstance(addr string) (*sql.DB, error) {
    var (
        db  *sql.DB
        err error
    )
    if val, ok := dbMap.Load(addr); ok {
        db = val.(*sql.DB)
        return db, nil
    }

    db, err = sql.Open("mysql", "root:123456@"+addr+"/test")
    if err != nil {
        return nil, err
    }

    dbMap.Store(addr, db)
    return db, nil
}

func transfer(fromDB, toDB string, amount int) error {
    tx, err := getDBInstance(fromDB).Begin() //开始事务
    if err != nil {
        return err
    }
    defer tx.Rollback() //回滚事务

    //从fromDB转移amount到toDB
    _, err = tx.Exec("UPDATE account SET balance=balance-? WHERE id=1", amount)
    if err != nil {
        return err
    }
    _, err = getDBInstance(toDB).Exec("UPDATE account SET balance=balance+? WHERE id=2", amount)
    if err != nil {
        return err
    }

    err = tx.Commit() //提交事务
    if err != nil {
        tx.Rollback()
        return err
    }
    return nil
}

func main() {
    err := transfer("127.0.0.1:3306", "127.0.0.1:3307", 100)
    if err != nil {
        fmt.Println(err)
    } else {
        fmt.Println("transfer success")
    }
}

In the above sample program, we use the getDBInstance() function to obtain a database instance from the connection pool. Then, in the transfer() function, we use the tx.Begin() function to create a new transaction, and then use the tx.Exec() function to execute SQL statements in fromDB and toDB to complete the transfer operation.

Finally, use the tx.Commit() function to commit the transaction. If an error occurs in the transaction, use the tx.Rollback() function to roll back the transaction.

Summary

By using the API provided by the go-sql-driver/mysql package, we can easily connect to and interact with the MySQL database. In a distributed system, using connection pools to manage multiple MySQL database instances can improve system performance and availability. The Go language also provides support for the use of distributed transactions, and distributed transactions can be easily managed through the Tx object.

The above is the detailed content of How to connect to a distributed MySQL database using Go language. For more information, please follow other related articles on the PHP Chinese website!