使用GO开发MySQL巡检系统

MySQL数据库联盟 2024-08-28

426

MySQL运行久了，总会遇到各种问题。

比如自增主键用满了，或者某张表碎片率非常高，又或者某个参数不符合规范。

这种情况，我们就需要考虑为数据库部署一套巡检系统；

定期巡检MySQL，可以让MySQL更稳定的运行。

这一节内容，我们就来讲一下怎样开发一套自建MySQL的巡检系统，云上数据库的巡检项目开发可以点击跳转。

如果没有Go基础，可以看小编之前写的两篇基础文稿：

一文入门Go语言

Go语言操作MySQL

1 创建数据库用户和表

我们需要一套数据库，来存放巡检结果。

登录到MySQL

create database dbcheck;

创建表

use dbcheck


CREATE TABLE `mysql_check` (
  `id` int unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
  db_type VARCHAR(50) NOT NULL COMMENT '数据库类型',
  check_type VARCHAR(50) NOT NULL COMMENT '校验类型',
  ip_port VARCHAR(50) NOT NULL COMMENT 'IP端口',
  check_field VARCHAR(100) COMMENT '校验字段',
  check_values VARCHAR(100) COMMENT '校验值',  
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP COMMENT '记录创建时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB CHARSET=utf8mb4 COMMENT='数据库巡检结果';

创建用户

create user 'dbcheck_rw'@'%' identified with mysql_native_password by 'Id81G_ca91';
GRANT all ON dbcheck.* TO 'dbcheck_rw'@'%';

2 创建巡检项目

在Goland中创建项目（创建方式可参考这里），项目名dbcheck。

创建main函数

首先创建项目的入口：main.go

里面包含了main函数，是项目启动时首先执行的代码文件。

package main


import (
   "dbcheck/config"
   "dbcheck/service"
   "flag"
   "log"
)


func main() {
   // 加载配置文件
   var configPath string
   flag.StringVar(&configPath, "config", "config.yaml", "path to config file")
   flag.Parse()




   cfg, err := config.LoadConfig(configPath)
   if err != nil {
      log.Printf( "%v", err)
   }


   // 执行巡检函数
   err = service.CheckMysql(cfg)


   if err != nil {
      log.Printf( "%v", err)
   }
}

编写配置文件解析的函数

再来编写配置文件解析的代码。

文件是config/config.go

package config


import (
   "gopkg.in/yaml.v2"
   "os"
)


// Config 结构体，表示配置文件中变量，与config.yaml文件里的配置对应
type Config struct {
   DBHost       string `yaml:"DBHost"` // 用来存放校验结果的数据库Host，其中`yaml:"DBHost"`，表示将yaml文件中的DBHost键对应的值复制给这个结构体中的DBHost
   DBPort       string    `yaml:"DBPort"`  // 用来存放校验结果的数据库端口
   DBUsername   string `yaml:"DBUsername"`  // 用来存放校验结果的数据库用户名
   DBPassword   string `yaml:"DBPassword"` // 用来存放校验结果的数据库密码
   DBName       string `yaml:"DBName"`  // 用来存放校验结果的数据库库名
   CheckDBList  []string `yaml:"CheckDBList"`  //需要校验的数据库实例，这里注意，是切片类型，也就是可以有多个MySQL实例
   CheckDBPort     string `yaml:"CheckDBPort"`  // 需要校验的数据库端口
   CheckDBUsername     string `yaml:"CheckDBUsername"`  // 需要校验的数据库用户名
   CheckDBPassword     string `yaml:"CheckDBPassword"`  // 需要校验的数据库用户密码


}


// LoadConfig 初始化配置
func LoadConfig(configPath string) (*Config, error) {
   // 读取配置文件内容
   configFile, err := os.ReadFile(configPath)


   // 如果读取文件过程中发生错误，返回nil和错误信息
   if err != nil {
      return nil, err
   }
   // 创建一个空的Config结构体实例
   config := &Config{}


   // 将配置文件内容解析为Config结构体对象
   err = yaml.Unmarshal(configFile, config)


   // 如果解析配置文件过程中发生错误，返回nil和错误信息
   if err != nil {
      return nil, err
   }


   // 返回解析后的Config结构体对象和nil
   return config, nil
}

创建配置文件

再来创建yaml配置文件config.yaml。

里面存放了慢查询实例的连接信息，以及需要获取慢查询的实例。

CheckDBList:
  - 192.168.12.162
  - 192.168.12.163
CheckDBPort: 3306
CheckDBUsername: dbcheck_r
CheckDBPassword: Id8G7Gc1e


DBHost: 192.168.12.161
DBPort: 3306
DBUsername: dbcheck_rw
DBPassword: Id81G_ca91
DBName: dbcheck

其中：

CheckDBList是被校验的实例；

DBHost及后面的配置，是存放校验结果的实例。

定义巡检结果的结构体

再来创建巡检结果的结构体定义代码文件

文件是model/dbcheck.go

package model
//定义创建巡检结果的结构体
type DBCheck struct {
   DBType         string
   IPPort          string
   CheckType        string
   CheckField      string
   CheckValues     string
}

配置获取巡检结果的代码

再来创建获取巡检结果的代码文件

文件是service/checkMysql.go

package service


import (
   "database/sql"
   "dbcheck/config"
   "dbcheck/model"
   "dbcheck/repository"
   "fmt"
   _ "github.com/go-sql-driver/mysql"
   "log"
)


func CheckMysql(cfg *config.Config) error {
   var checkResult []model.DBCheck


   for _, mysqlHost := range cfg.CheckDBList {
      dsn := fmt.Sprintf("%s:%s@tcp(%s:%s)/information_schema", cfg.CheckDBUsername, cfg.CheckDBPassword, mysqlHost, cfg.CheckDBPort)
      db, err := sql.Open("mysql", dsn)
      if err != nil {
         log.Printf("Failed to connect to database instance %s: %v", mysqlHost, err)
         continue // 继续尝试下一个数据库实例
      }
      defer db.Close() // 确保关闭数据库连接


      // 巡检表行数top10
      rowsQuery := "select concat(table_schema,'.',table_name), table_rows from information_schema.tables where table_schema not in " +
         "('information_schema', 'mysql', 'performance_schema', 'sys') order by table_rows desc limit 10"


      tableRowsInfo, err := GetCheckResult(db, mysqlHost,rowsQuery,"TableRows",cfg)
      if err != nil {
         log.Printf("Failed to check tables for database instance %s: %v", mysqlHost, err)
         continue // 继续尝试下一个数据库实例
      }
      checkResult = append(checkResult, tableRowsInfo...)


      // 巡检表存储引擎是非InnoDB的
      engineQuery := " select concat(table_schema,'.',table_name), engine from information_schema.tables where table_schema not in " +
         "('information_schema', 'mysql', 'performance_schema', 'sys') and engine <> 'innodb'"
      tableEngineInfo, err := GetCheckResult(db, mysqlHost,engineQuery,"TableEngine",cfg)
      if err != nil {
         log.Printf("Failed to check tables for database instance %s: %v", mysqlHost, err)
         continue // 继续尝试下一个数据库实例
      }
      checkResult = append(checkResult, tableEngineInfo...)


      // 巡检自增值使用率top10
      autoIncrementQuery := "select concat(table_schema,'.',table_name), round((auto_increment  pow(2, 31)) * 100, 2) as 'auto_increment_usage' " +
         "from information_schema.tables where table_schema NOT IN ('information_schema', 'mysql', 'performance_schema', 'sys') " +
         "and auto_increment is not null order by auto_increment_usage desc limit 10"
      tableAutoIncrementInfo, err := GetCheckResult(db, mysqlHost,autoIncrementQuery,"TableAutoIncrement",cfg)
      if err != nil {
         log.Printf("Failed to check tables for database instance %s: %v", mysqlHost, err)
         continue // 继续尝试下一个数据库实例
      }
      checkResult = append(checkResult, tableAutoIncrementInfo...)


      // 巡检表碎片率top10
      fragmentationQuery := "select concat(table_schema,'.',table_name),if(data_length > 0, round(data_free/(data_length + index_length + data_free)" +
         " * 100, 2), 0) as 'fragmentation' from information_schema.tables  where table_schema not in ('information_schema', 'mysql', " +
         "'performance_schema', 'sys') and engine = 'innodb' order by fragmentation desc limit 10"
      tableFragmentationInfo, err := GetCheckResult(db, mysqlHost,fragmentationQuery,"TableFragmentation",cfg)
      if err != nil {
         log.Printf("Failed to check tables for database instance %s: %v", mysqlHost, err)
         continue // 继续尝试下一个数据库实例
      }
      checkResult = append(checkResult, tableFragmentationInfo...)




      // 巡检重要参数
      variablesQuery := "show global variables where Variable_name in ('innodb_flush_log_at_trx_commit','sync_binlog'," +
         "'binlog_format','character_set_server','system_time_zone');"
      variablesInfo, err := GetCheckResult(db, mysqlHost,variablesQuery,"Variables",cfg)
      if err != nil {
         log.Printf("Failed to check tables for database instance %s: %v", mysqlHost, err)
         continue // 继续尝试下一个数据库实例
      }
      checkResult = append(checkResult, variablesInfo...)




   }
   for _, info := range checkResult {
      fmt.Println(info)
   }


   err := repository.SaveCheckResult(checkResult,cfg)
   if err != nil {
      log.Printf("Failed to save check result: %v" , err)
       // 继续尝试下一个数据库实例
   }


   return nil // 所有数据库实例处理完成
}

创建去数据库里获取具体巡检结果的文件：

service/getCheckResult.go

package service


import (
   "database/sql"
   "dbcheck/config"
   "dbcheck/model"
   "log"
)


func GetCheckResult(db *sql.DB, mysqlHost,query,checkType string, cfg *config.Config) ([]model.DBCheck, error) {
   var tableRowsInfo []model.DBCheck


   // 执行巡检逻辑
   rows, err := db.Query(query)
   if err != nil {
      log.Printf("Failed to query source database: %v", err)
      return tableRowsInfo, err
   }
   defer rows.Close() // 确保关闭rows


   for rows.Next() {
      var ti model.DBCheck


      if err := rows.Scan(&ti.CheckField, &ti.CheckValues); err != nil {
         log.Printf("Failed to scan row: %v", err)
         return tableRowsInfo, err
      }


      ti.DBType = "MySQL"
      ti.IPPort = mysqlHost + ":" + cfg.CheckDBPort
      ti.CheckType = checkType
      tableRowsInfo = append(tableRowsInfo, ti)
   }


   return tableRowsInfo, nil
}

编写巡检结果写入MySQL的函数

再来创建校验结果写入数据库的代码文件

repository/checkResultRepository.go

package repository


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


// 将检验结果写入数据库
func SaveCheckResult(checkResult []model.DBCheck) error {
   // 加载配置
   cfg, err := config.LoadConfig()
   if err != nil {
      return err
   }
   // 连接数据库
   dsn := fmt.Sprintf("%s:%s@tcp(%s:%s)/%s", cfg.DBUsername, cfg.DBPassword, cfg.DBHost, cfg.DBPort, cfg.DBName)
   db, err := sql.Open("mysql", dsn)
   if err != nil {
      log.Printf("Failed to connect to database instance %s: %v", cfg.DBHost, err)
   }
   defer db.Close() // 确保关闭数据库连接


   // 清空校验结果表的历史数据
   _, err = db.Exec(`truncate table mysql_check`)
   if err != nil {
      log.Printf("Failed to truncate table mysql_check:%v", err)
      return err
   }


   // 向MySQL中插入巡检结果
   for _, row := range checkResult {
      _, err := db.Exec(`
            INSERT INTO mysql_check (db_type, ip_port, check_type, check_field, check_values) VALUES (?, ?, ?, ?, ?)`,
         row.DBType, row.IPPort, row.CheckType, row.CheckField, row.CheckValues)
      if err != nil {
         log.Printf("Failed to insert table mysql_check:%v", err)
      }
   }


   return nil
}

配置完之后，项目的结构如下：

3 运行测试

初始化代码

go mod tidy

在被校验的MySQL中创建项目连接用户：

create user 'dbcheck_r'@'%' identified with mysql_native_password by 'Id8G7Gc1e';
GRANT select ON *.* TO 'dbcheck_r'@'%';

运行项目

右击main.go，点击“Run go build main.go”

在存放校验结果的MySQL中查询：

 select * from mysql_check;

4 将项目放到Linux上运行

打包项目：

set GOARCH=amd64
go env -w GOARCH=amd64
set GOOS=linux
go env -w GOOS=linux
go build -o dbcheck main.go

在Linux机器上创建文件夹dbcheck。

把打包好的代码上传到机器上的dbcheck文件夹中。

进到文件夹，给文件赋予可执行权限：

chmod +x dbcheck

创建配置文件config.yaml

CheckDBList:
  - 192.168.12.162
  - 192.168.12.163
CheckDBPort: 3306
CheckDBUsername: dbcheck_r
CheckDBPassword: Id8G7Gc1e


DBHost: 192.168.12.161
DBPort: 3306
DBUsername: dbcheck_rw
DBPassword: Id81G_ca91
DBName: dbcheck

运行项目：

./dbcheck

增加到定时任务：

crontab -e

加入：

7 * * * * data/dbcheck/dbcheck

表示每天早上7点，会清空之前的巡检结果，再写入最新的巡检结果。

5 配置Grafana页面展示校验结果

展示结果，有很多方式，比如通过SQL语句查询异常的结果，并发送邮件告警。

或者使用运维平台展示（用低代码平台修改，后面有机会，会分享方法）。

我们这次就使用Grafana来展示巡检结果。

Grafana的安装：

yum install -y https://dl.grafana.com/oss/release/grafana-10.4.7-1.x86_64.rpm

启动Grafana：

systemctl start grafana-server.service

用户名密码都是 admin。登录后，会让我们修改密码，则按提示操作即可，当然也可以点击跳过。

添加MySQL数据源：

新建一个数据源，搜索MySQL

填写用来存放校验结果的MySQL实例连接信息：

点击下方的Save & test

如果显示Database Connection OK。

就表示配置数据源成功。

配置Grafana展示图

点击Add visualization。

Select data source这里，选择刚才添加的MySQL数据源。

再点击这个dashboard的设置，增加一个变量：

增加变量checkType；

在Name这里面填：checkType。

Query输入：

select distinct check_type from mysql_check

修改Dashboard

SELECT db_type as "数据库种类",ip_port as "IP端口",check_field as "校验项",check_values as "校验值" 
FROM 
dbcheck.mysql_check 
where check_type = "$checkType" 
order by "行数" desc LIMIT 10

表类型选择Table：

最后的效果如下图：

6 一些思考

关于新增巡检项

上面几个巡检项只是演示，工作中可能需要巡检更多的项目。

那就只需要在service/checkMysql.go这个文件中，复制下面这一段代码，把SQL和相关的变量改成新的巡检项就行。

       巡检表碎片率top10
      fragmentationQuery := "select concat(table_schema,'.',table_name),if(data_length > 0, round(data_free/(data_length + index_length + data_free)" +
         " * 100, 2), 0) as 'fragmentation' from information_schema.tables  where table_schema not in ('information_schema', 'mysql', " +
         "'performance_schema', 'sys') and engine = 'innodb' order by fragmentation desc limit 10"
      tableFragmentationInfo, err := GetCheckResult(db, mysqlHost,fragmentationQuery,"TableFragmentation",cfg)
      if err != nil {
         log.Printf("Failed to check tables for database instance %s: %v", mysqlHost, err)
         continue / 继续尝试下一个数据库实例
      }
      checkResult = append(checkResult, tableFragmentationInfo...)

如果要同时展示所有巡检项

如果想一个页面同时展示所有的巡检项，那可以在Grafana中，把变量改成IP端口，页面上为每个巡检项配置一张表，几个巡检项写几条SQL。

比如存储引擎的巡检结果，Grafana里的SQL可以这样写：

SELECT db_type as "数据库种类",ip_port as "IP端口",check_field as "库表",check_values
as "存储引擎"  
FROM  
dbcheck.mysql_check  
where ip_port='$ipport'  and  check_type = "TableEngine" LIMIT 10;

更多MySQL实战项目，欢迎订购小编在慕课网制作的DBA体系课（本月已经完结）。

慕课网平台会在9月2日~9月27日开启折扣活动。从今天开始到9月2日，这期间购买DBA体系课的，都会保价。

也就是这期间，你如果买的比9月2日~9月27日活动期间的价格贵了，可以去后台申请退还差价。

售前售后问题，可以加我微信：mating3306

关注公众号

回复“复制”，可以获取小编编写的69页MySQL主从复制PDF；

回复“高可用”，可获取8篇MySQL高可用文章；

回复“DBA面试题”，可获取36个DBA高频面试题及解析；

回复“社群”，可加入免费MySQL交流群。

点击左下方阅读原文，跳转到课程。

mysql go mysql创建数据库数据库文件数据库系统

文章转载自MySQL数据库联盟，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

使用GO开发MySQL巡检系统

评论