【数据库相关】mysql数据库巡检

巡检步骤

一、基础状态检查

1. 服务可用性
   • systemctl status mysql（检查服务状态）
   • telnet <IP> 3306 / nc -zv <IP> 3306（测试端口连通性）
   • SHOW GLOBAL VARIABLES LIKE 'server_id';（确认实例唯一性）

2. 版本与配置
   • SELECT VERSION();（核对MySQL版本）
   • 检查my.cnf配置文件（内存分配、线程池、字符集等关键参数）

二、服务器资源监控

CPU使用

• top/htop（观察MySQL进程CPU占比）
• mpstat 1 5（1秒间隔采样5次，分析CPU周期分布）
• 警戒值：持续>70%需排查慢查询或锁争用。

内存使用

• free -m（查看物理内存）
• ps aux --sort -rss | grep mysql（MySQL进程内存占用）
• InnoDB缓冲池：SHOW ENGINE INNODB STATUS LIKE 'Innodb_buffer_pool_size';
• 缓冲池使用率>90%时需扩容。

磁盘I/O

• iostat -dx 2（监控磁盘读写延迟与吞吐量）
• iotop -o（实时查看I/O密集型进程）
• 重点指标：await（平均等待时间）<20ms为佳。

网络流量

• netstat -antp | grep ESTABLISHED（检查 active 连接）
• ss -s（统计TCP连接数）
• 风险阈值：TIME_WAIT连接数>1000可能需调整tcp_fin_timeout。

三、数据库内部健康度

全局状态

• SHOW GLOBAL STATUS;（关键指标解析）：
• Threads_connected（当前连接数 vs max_connections）
• Queries_per_second（QPS趋势）
• Slow_queries（慢查询数量）

• SHOW ENGINE INNODB STATUS;（分析事务、锁、死锁）：
• 检查Innodb_row_lock_waits（行级锁等待）
• Innodb_trx（活跃事务数）

慢查询监控

• 启用慢查询日志：slow_query_log=1 + long_query_time=2
• 分析工具：pt-query-digest /var/log/mysql/slow.log
• 优化方向：索引缺失、临时表使用、全表扫描。

锁与并发

• SHOW STATUS LIKE 'innodb_lock_waits';（锁等待事件）
• INFORMATION_SCHEMA.INNODB_TRX（查看长事务）：
• trx_state为RUNNING且持续时间过长需终止。

四、存储引擎健康

InnoDB引擎

• 表空间文件检查：

SELECT file_name, tablespace_name, 
       ROUND((data_length + index_length)/1024/1024, 2) AS size_mb 
FROM information_schema.tables 
WHERE engine='InnoDB';

• 自适应哈希索引命中率：SHOW ENGINE INNODB STATUS LIKE 'adaptive_hash_index';
• 风险点：ibdata1文件过大时考虑表空间拆分。

MyISAM引擎

• CHECK TABLE <table_name>（修复表损坏）
• ANALYZE TABLE（更新统计信息）

五、日志与备份

1. 错误日志
   • 检查/var/log/mysql/error.log中的警告/错误（如主键冲突、连接拒绝）。

2. 二进制日志
   • SHOW MASTER STATUS;（确认binlog写入位置）
   • PURGE BINARY LOGS BEFORE '<date>';（清理旧日志）

3. 备份验证
   • 物理备份：xtrabackup --check --backup-dir=/path
   • 逻辑备份：mysqlcheck --all-databases --auto-repair

六、安全与权限

• SHOW GRANTS FOR USER '<user>'@'host';（最小权限原则）
• mysql_secure_installation（加固配置）
• 授权审计：定期清理过期账户。

七、高可用性检查

主从复制

• SHOW SLAVE STATUS\G（检查Slave_IO_Running和Slave_SQL_Running）
• 延迟监控：SHOW MASTER STATUS vs SHOW SLAVE STATUS的Relay_Master_Log_File和Exec_Master_Log_Pos。
• 工具推荐：Percona Toolkit的pt-table-checksum校验数据一致性。

集群状态（MySQL Group Replication/InnoDB Cluster）

• SELECT * FROM mysql.group_replication_members;（节点健康）
• 集群控制节点（CN）日志：/var/log/mysql/innodb-cluster.log

八、自动化监控建议

1. Prometheus + MySQL Exporter
   • 拉取指标：up{job="mysql", instance="localhost"}（服务状态）
   • 关键告警：QPS突增、慢查询率>5%、锁等待超时。

2. 自定义脚本
   • 示例：监控连接数脚本：

   mysql -e "SHOW STATUS LIKE 'Threads_connected'" | awk '/Threads_connected/ {print $2}'
   

3. 巡检工具
   • Percona Monitoring and Management (PMM)
   • Datadog MySQL Integration

九、维护与调优建议

1. 定期优化
   • OPTIMIZE TABLE（整理碎片）
   • ALTER TABLE ... ENGINE=InnoDB（迁移MyISAM表）

2. 参数调优示例
   • 根据内存调整InnoDB缓冲池：

   innodb_buffer_pool_size = (70-80% of total RAM)
   innodb_log_file_size = 256M
   

   • 限制并发连接：

   max_connections = 500
   thread_pool_size = 16
   

3. 版本升级
   • 评估MySQL 8.0的特性（如窗口函数、资源组管理）对业务的影响。

十、附录：关键命令速查

-- 查看实时线程状态
SHOW PROCESSLIST;

-- 获取InnoDB指标
SHOW ENGINE INNODB STATUS LIKE 'innodb_';

-- 分析慢查询
SELECT * FROM sys.slow_log;

通过以上清单，可系统性排查MySQL性能瓶颈与潜在风险。建议结合自动化工具实现持续监控，并根据业务增长动态调整资源配置。若遇到复杂问题（如死锁风暴、内存泄漏），建议启用innodb_force_recovery模式并联系专业支持。