Golang操作MySQL复制输出的实践指南

在Golang中处理SHOW SLAVE STATUS这类动态列数的查询，可以使用sql.Rows来遍历结果集。以下是具体实现：

package main

import (
    "database/sql"
    "fmt"
    "log"

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

func main() {
    db, err := sql.Open("mysql", "$USER:$PASS@tcp($HOSTNAME:3306)/")
    if err != nil {
        log.Fatal(err)
    }
    defer db.Close()

    rows, err := db.Query("SHOW SLAVE STATUS")
    if err != nil {
        log.Fatal(err)
    }
    defer rows.Close()

    columns, err := rows.Columns()
    if err != nil {
        log.Fatal(err)
    }

    values := make([]sql.RawBytes, len(columns))
    scanArgs := make([]interface{}, len(values))
    for i := range values {
        scanArgs[i] = &values[i]
    }

    slaveStatus := make(map[string]string)
    
    for rows.Next() {
        err = rows.Scan(scanArgs...)
        if err != nil {
            log.Fatal(err)
        }

        for i, col := range values {
            if col == nil {
                slaveStatus[columns[i]] = "NULL"
            } else {
                slaveStatus[columns[i]] = string(col)
            }
        }
    }

    if err = rows.Err(); err != nil {
        log.Fatal(err)
    }

    // 访问特定字段
    if slaveStatus["Slave_IO_Running"] == "Yes" && slaveStatus["Slave_SQL_Running"] == "Yes" {
        fmt.Println("Slave is running")
    } else {
        fmt.Println("Slave is not running")
    }

    // 打印所有字段
    for key, value := range slaveStatus {
        fmt.Printf("%s: %s\n", key, value)
    }
}

对于动态列的处理，这里使用sql.RawBytes和interface{}切片来接收任意数量的列。rows.Columns()获取列名，然后通过rows.Scan()将每行数据扫描到values切片中。

检查复制状态的示例：

func checkReplicationStatus(status map[string]string) {
    ioRunning := status["Slave_IO_Running"]
    sqlRunning := status["Slave_SQL_Running"]
    secondsBehind := status["Seconds_Behind_Master"]

    fmt.Printf("IO Thread: %s\n", ioRunning)
    fmt.Printf("SQL Thread: %s\n", sqlRunning)
    fmt.Printf("Seconds Behind Master: %s\n", secondsBehind)

    if ioRunning == "Yes" && sqlRunning == "Yes" {
        if secondsBehind == "0" {
            fmt.Println("Replication is running and up to date")
        } else {
            fmt.Printf("Replication is running but lagging by %s seconds\n", secondsBehind)
        }
    } else {
        fmt.Println("Replication is not running properly")
        
        if status["Last_IO_Error"] != "" {
            fmt.Printf("Last IO Error: %s\n", status["Last_IO_Error"])
        }
        if status["Last_SQL_Error"] != "" {
            fmt.Printf("Last SQL Error: %s\n", status["Last_SQL_Error"])
        }
    }
}

处理多行结果的版本：

func getAllSlaveStatus(db *sql.DB) ([]map[string]string, error) {
    rows, err := db.Query("SHOW SLAVE STATUS")
    if err != nil {
        return nil, err
    }
    defer rows.Close()

    columns, err := rows.Columns()
    if err != nil {
        return nil, err
    }

    var results []map[string]string
    
    for rows.Next() {
        values := make([]sql.RawBytes, len(columns))
        scanArgs := make([]interface{}, len(values))
        for i := range values {
            scanArgs[i] = &values[i]
        }

        err = rows.Scan(scanArgs...)
        if err != nil {
            return nil, err
        }

        rowMap := make(map[string]string)
        for i, col := range values {
            if col == nil {
                rowMap[columns[i]] = "NULL"
            } else {
                rowMap[columns[i]] = string(col)
            }
        }
        results = append(results, rowMap)
    }

    return results, rows.Err()
}

这种方法通过map[string]string存储结果，可以处理任意数量和名称的列，适应不同MySQL版本的SHOW SLAVE STATUS输出差异。