首页时事民生政务教育文化科技财富体娱健康情感

旅行百科职场楼市企业乐活学术汽车时尚创业美食幽默美体文摘

使用GO开发MySQL巡检系统

科技 2024-08-28 09:00 上海

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.163CheckDBPort: 3306CheckDBUsername: dbcheck_rCheckDBPassword: Id8G7Gc1e
DBHost: 192.168.12.161DBPort: 3306DBUsername: dbcheck_rwDBPassword: Id81G_ca91DBName: 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=amd64go env -w GOARCH=amd64set GOOS=linuxgo env -w GOOS=linuxgo build -o dbcheck main.go

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

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

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

chmod +x dbcheck

创建配置文件config.yaml

CheckDBList:  - 192.168.12.162  - 192.168.12.163CheckDBPort: 3306CheckDBUsername: dbcheck_rCheckDBPassword: Id8G7Gc1e
DBHost: 192.168.12.161DBPort: 3306DBUsername: dbcheck_rwDBPassword: Id81G_ca91DBName: 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_valuesas "存储引擎"  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日活动期间的价格贵了，可以去后台申请退还差价。