/* 基礎(chǔ)健康檢查SQL */
SELECT 1;  
/* 連接池狀態(tài)檢查 */
SHOW STATUS LIKE 'Threads_connected'; 

/* 關(guān)鍵性能指標(biāo)查詢(xún) */
SHOW GLOBAL STATUS WHERE Variable_name IN ('Queries','Com_commit','Com_rollback','Threads_running');

/* 阻塞會(huì)話檢測(cè) */
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;

/* 檢測(cè)阻塞事務(wù) */  
SELECT  
  waiting_trx_id,  
  blocking_trx_id,  
  waiting_query,  
  blocking_query  
FROM sys.innodb_lock_waits;  -- 依賴(lài)sys schema  

/* 主從狀態(tài)檢查 */
SHOW SLAVE STATUS\G
/* 延遲檢測(cè) */
SELECT NOW() - MAX(create_time) AS replication_delay 
FROM mysql.slave_relay_log_info;

/* 查看復(fù)制狀態(tài) */  
SHOW REPLICA STATUS\G  
/* 關(guān)鍵字段：Replica_IO_Running, Replica_SQL_Running, Seconds_Behind_Master */  

/* 表空間監(jiān)控 */
SELECT 
  table_schema AS `Database`,
  SUM(data_length + index_length) / 1024 / 1024 AS `Size_MB`
FROM information_schema.TABLES 
GROUP BY table_schema;

/* 查看表空間使用（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;  

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

telnet 192.168.1.100 3306 

-- 檢查讀能力 
SELECT @@version;
-- 寫(xiě)入測(cè)試（示例）
INSERT INTO monitor_table (id) VALUES (1) ON DUPLICATE KEY UPDATE id=1;

SHOW VARIABLES LIKE 'max_connections'; -- 最大連接數(shù)
# 或
SHOW GLOBAL VARIABLES LIKE 'max_connections';

SHOW GLOBAL STATUS LIKE 'Threads_connected'; -- 當(dāng)前連接數(shù) 
# 或
SHOW GLOBAL STATUS LIKE 'Threads_connected';

QPS = (Queries? - Queries?) / 時(shí)間間隔  
-- Queries 取自 SHOW GLOBAL STATUS 的輸出 

TPS = [(Com_insert? + Com_update? + Com_delete?) - 
      (Com_insert? + Com_update? + Com_delete?)] / 時(shí)間間隔 

SHOW GLOBAL STATUS LIKE 'Threads_running'; -- 實(shí)時(shí)并發(fā)數(shù) 

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; -- 阻塞超時(shí)閾值（秒）

SHOW SLAVE STATUS\G

-- 主庫(kù)執(zhí)行 
SHOW MASTER STATUS; -- File: mysql-bin.000001, Position: 154 

-- 從庫(kù)執(zhí)行 
SHOW SLAVE STATUS;   -- 對(duì)比 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;

pt-table-checksum \
  --user=monitor_user \
  --password='password' \
  --databases=mydatabase \
  --replicate=test.checksum # # 在test庫(kù)創(chuàng)建校驗(yàn)表 

GRANT SELECT, PROCESS, SUPER, REPLICATION SLAVE ON *.* TO 'monitor_user'@'%';

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 };  
  }  
}  

-- 檢查連接數(shù) 
SHOW GLOBAL STATUS WHERE Variable_name = 'Threads_connected';
-- 獲取 InnoDB 鎖阻塞 
SELECT * FROM information_schema.innodb_lock_waits;
-- 主從狀態(tài)檢查 
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;

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; // 假設(shè)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]);
  }
}

// 監(jiān)控端點(diǎn)控制器 (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服務(wù)層 (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
    };
  }
}