MySQL中数据库监控核心要素与实施策略详解_Mysql

数据库监控的必要性与范畴

数据库监控是系统稳定性的基石，作为核心组件，数据库的稳定性直接决定系统可用性，因此监控至关重要。当前市场上存在多种监控工具（如 nagios、zabbix），支持通过插件或自定义脚本实现数据库监控，用户可根据习惯选择工具与脚本语言（如 python、shell）

监控核心内容：

1 ）服务可用性监控：

仅检测进程或端口存活并不充分，需通过网络连接数据库并执行简单查询（如 select 1）验证实际可用性

超越进程/端口检查：需建立真实数据库连接并执行基础查询

监控指标：连接成功率、响应延迟、简单查询执行状态

/* 基础健康检查sql */
select 1;  
/* 连接池状态检查 */
show status like 'threads_connected';

2 ）性能监控：

qps（每秒查询量）、tps（每秒事务量）、并发线程数（注意：并发线程指同时处理的 sql 请求数，通常远小于连接数）

并发线程数：活跃工作线程（非连接数）

/* 关键性能指标查询 */
show global status where variable_name in ('queries','com_commit','com_rollback','threads_running');

innodb阻塞监控：

/* 阻塞会话检测 */
select 
  bl.trx_mysql_thread_id as blocking_id,
  wt.trx_mysql_thread_id as waiting_id,
  wt.trx_query as waiting_query
from information_schema.innodb_lock_waits w 
join information_schema.innodb_trx bl on bl.trx_id = w.blocking_trx_id 
join information_schema.innodb_trx wt on wt.trx_id = w.requesting_trx_id;

简单真相：

/* 检测阻塞事务 */  
select  
  waiting_trx_id,  
  blocking_trx_id,  
  waiting_query,  
  blocking_query  
from sys.innodb_lock_waits;  -- 依赖sys schema

3 ）主从复制监控：

链路状态、延迟检测及数据一致性验证

复制链路状态（io/sql线程运行状态）

主从延迟（seconds_behind_master）

数据一致性校验

/* 主从状态检查 */
show slave status\g
/* 延迟检测 */
select now() - max(create_time) as replication_delay 
from mysql.slave_relay_log_info;

/* 查看复制状态 */  
show replica status\g  
/* 关键字段：replica_io_running, replica_sql_running, seconds_behind_master */

4 ）服务器资源监控：

磁盘空间（重点监控数据库专用分区，避免因日志或数据目录占满导致服务中断）、cpu、内存、swap 及网络 i/o（通用指标本文不赘述）

关键风险点：

磁盘空间监控：数据目录/日志分区容量（即使服务器总空间充足）
容量规划原则：数据目录需独立分区并设置阈值告警

示例1

/* 表空间监控 */
select 
  table_schema as `database`,
  sum(data_length + index_length) / 1024 / 1024 as `size_mb`
from information_schema.tables 
group by table_schema;

示例2

/* 查看表空间使用（innodb） */  
select table_schema, table_name,  
  round((data_length + index_length) / 1024 / 1024, 2) as size_mb  
from information_schema.tables  
order by size_mb desc;

其他核心资源：

cpu利用率（尤其user态占比）
内存使用（含swap交换趋势）
网络i/o（连接数、流量峰值）

关键细节：数据库专用分区空间不足是常见故障点。即使服务器总磁盘空间充足，若分配给 mysql 数据/日志目录的分区过小，仍会导致服务不可用

数据库可用性监控实现方案

1 ) 网络连接验证

仅本地连接成功不能确保远程网络可用（受防火墙、tcp 连接数限制影响）。需通过以下方式验证：

mysqladmin ping 命令：

mysqladmin -u monitor_user -p'password' -h 192.168.1.100 ping

建立监控专用账号，循环检测多台服务器，通过返回状态判断连通性。

telnet 端口检测（手动测试）：

telnet 192.168.1.100 3306

模拟应用连接（推荐）：

编写代码模拟真实应用连接方式（如特定驱动版本），规避因客户端兼容性问题导致的隐蔽故障。

2 ) 读写服务验证

检查 read_only 参数：主库必须关闭 read_only；主从切换后需确认新主库此参数为 off。

轻量级读写测试：定时对监控表执行简单操作（如 update monitor_table set check_time=now()），避免产生额外负载。

最低成本读验证：执行无依赖查询 select @@version，兼容所有 mysql 版本

参考：

-- 检查读能力 
select @@version;
-- 写入测试（示例）
insert into monitor_table (id) values (1) on duplicate key update id=1;

3 ) 连接数阈值监控

关键变量：

max_connections：最大允许连接数
threads_connected：当前连接数

连接数突增（如缓存失效或阻塞引发）可能导致连接耗尽，监控方法：

获取配置参数：

show variables like 'max_connections'; -- 最大连接数
# 或
show global variables like 'max_connections';

实时状态检测：

show global status like 'threads_connected'; -- 当前连接数 
# 或
show global status like 'threads_connected';

报警规则：(threads_connected / max_connections) > 80% 时触发告警。

数据库性能监控关键指标

性能监控需持续记录数据趋势，核心指标如下：

1 ) qps & tps 计算

qps（每秒查询量）：

qps = (queries₂ - queries₁) / 时间间隔  
-- queries 取自 show global status 的输出

tps（每秒事务量）：

tps = [(com_insert₂ + com_update₂ + com_delete₂) - 
      (com_insert₁ + com_update₁ + com_delete₁)] / 时间间隔

2 ) 并发线程监控

监控状态变量：

show global status like 'threads_running'; -- 实时并发数

并发量突增可能预示阻塞或缓存雪崩，需结合 cpu 使用率分析

3 ) innodb 阻塞监控

myisam 引擎缺乏原生锁监控，建议迁移至 innodb。innodb 阻塞检测 sql：

select 
    r.trx_id as waiting_trx_id,
    r.trx_mysql_thread_id as waiting_thread_id,
    r.trx_query as waiting_query,
    b.trx_id as blocking_trx_id,
    b.trx_mysql_thread_id as blocking_thread_id,
    b.trx_query as blocking_query,
    timestampdiff(second, r.trx_wait_started, now()) as wait_time_sec
from information_schema.innodb_lock_waits w 
join information_schema.innodb_trx b on b.trx_id = w.blocking_trx_id 
join information_schema.innodb_trx r on r.trx_id = w.requesting_trx_id 
where timestampdiff(second, r.trx_wait_started, now()) > 60; -- 阻塞超时阈值（秒）

注意：若阻塞事务已完成，可能无法捕获原始 sql，需结合日志分析

4 ) mysql 主从复制监控详解

复制链路状态监控

依赖 show slave status 输出：

show slave status\g

关键字段：

slave_io_running：i/o 线程状态（必须为 yes）
slave_sql_running：sql 线程状态（必须为 yes）

任一状态异常即触发告警

复制延迟精确检测

seconds_behind_master 不准确（网络中断时可能误判）。推荐方案：

主库获取二进制日志位置：

-- 主库执行 
show master status; -- file: mysql-bin.000001, position: 154 

-- 从库执行 
show slave status;   -- 对比 relay_master_log_file 和 exec_master_log_pos

从库对比同步进度：

select 
   relay_master_log_file, 
   exec_master_log_pos 
from performance_schema.replication_applier_status_by_worker;

判断延迟：

文件名不同：存在大延迟
文件名相同但 position 差距大：需告警

数据一致性校验

使用 percona toolkit 的 pt-table-checksum：

pt-table-checksum \
  --user=monitor_user \
  --password='password' \
  --databases=mydatabase \
  --replicate=test.checksum # # 在test库创建校验表

要求：主库账号需具备 select、process、super 权限

操作说明：

主库执行命令，在 test 库创建 checksum 表存储校验结果。

工具自动对比主从库数据差异。

需配置监控账号权限：

grant select, process, super, replication slave on *.* to 'monitor_user'@'%';

服务器资源监控补充

磁盘空间：重点监控数据目录（如 /var/lib/mysql）和日志分区

其他资源：

cpu 使用率（top 或 vmstat）
内存与 swap 使用（free -m）
网络 i/o（iftop 或 nload）

监控实施补充说明

1 ) 工具选择灵活性：

开发者可通过shell、python等编写脚本，集成到zabbix/nagios中

nestjs监控端点示例（获取数据库状态）：

import { controller, get } from '@nestjs/common';  
import { connection } from 'mysql2/promise';  

@controller('monitor')  
export class monitorcontroller {  
  constructor(private connection: connection) {}  

  @get('status')  
  async getdbstatus() {  
    const [rows] = await this.connection.query('show global status');  
    const qps = rows.find(row => row.variable_name === 'queries').value;  
    return { status: 'ok', qps };  
  }  
}

2 ) 性能优化关联性：

查询性能监控需结合执行计划分析（explain），但本文不重复展开

sql 示例

1 ) sql 监控脚本关键总结

-- 检查连接数 
show global status where variable_name = 'threads_connected';
-- 获取 innodb 锁阻塞 
select * from information_schema.innodb_lock_waits;
-- 主从状态检查 
show slave status\g;

/* 综合健康检查 */
select 
  (select variable_value from performance_schema.global_status 
   where variable_name='uptime') as uptime,
  (select sum(variable_value) from performance_schema.global_status 
   where variable_name in ('com_select','com_insert','com_update','com_delete')) as qps,
  (select variable_value from performance_schema.global_status 
   where variable_name='threads_running') as active_threads;

总结：

mysql 监控需覆盖可用性、性能、复制、资源四维度
通过工具与自定义脚本结合，精准捕获异常（如阻塞、延迟），并结合阈值告警实现主动运维
避免因磁盘占满、连接超限等“小问题”引发服务中断

nestjs 工程实例

1 ）方案1

import { controller, get } from '@nestjs/common';
import { execsync } from 'child_process';
 
@controller('monitor')
export class dbmonitorcontroller {
  @get('qps')
  getqps(): number {
    const prevqueries = this.getstatusvariable('queries');
    settimeout(() => {
      const currqueries = this.getstatusvariable('queries');
      return (currqueries - prevqueries) / 5; // 假设5秒间隔 
    }, 5000);
  }
 
  private getstatusvariable(name: string): number {
    const output = execsync(`mysql -u root -p[密码] -e "show global status like '${name}'"`);
    return parseint(output.tostring().split('\t')[1]);
  }
}

2 ）方案2

// 监控端点控制器 (monitor.controller.ts)
import { controller, get } from '@nestjs/common';
import { mysqlservice } from './mysql.service';
 
@controller('monitor')
export class monitorcontroller {
  constructor(private readonly mysqlservice: mysqlservice) {}
 
  @get('healthcheck')
  async healthcheck() {
    return {
      status: await this.mysqlservice.checkconnection(),
      metrics: await this.mysqlservice.getperformancemetrics()
    };
  }
}
 
// mysql服务层 (mysql.service.ts)
import { injectable } from '@nestjs/common';
import { connection } from 'mysql2/promise';
 
@injectable()
export class mysqlservice {
  constructor(private connection: connection) {}
 
  async checkconnection(): promise<string> {
    const [rows] = await this.connection.query('select 1 as status');
    return rows[0].status === 1 ? 'ok' : 'down';
  }
 
  async getperformancemetrics() {
    const [qpsres] = await this.connection.query(
      `show global status where variable_name in ('queries','threads_running')`
    );
    return {
      queries: qpsres.find(r => r.variable_name === 'queries').value,
      active_threads: qpsres.find(r => r.variable_name === 'threads_running').value
    };
  }
}