Alan Hou的个人博客

MySQL监控

可用性监控

确认数据库是否可以通过网络连接
mysqladmin -umonitor_user -p -h ping #远程服务器上执行
telent ip dp_port #手动
使用程序通过网络建立数据库连接(推荐)
read_only是否为off
建立监控表并对表中数据进行更新
执行简单的查询select @@version
监控数据库的连接数
show variables like ‘max_connections’;
show global status like ‘Threads_connected’
比如:Threads_connected/max_connections > 0.8进行报警

数据库性能监控

计算QPS和TPS
QPS=(Queries2-Queries1)/(Uptime_since_flush_status2-Uptime_since_flush_status1) #进行两次采样
TPS=((Com_insert2+Com_update2+Com_delete2)-(Com_insert1+Com_update1+Com_delete1))/(Uptime_since_flush_status2-Uptime_since_flush_status1)
监控数据库的并发请求数量
show global status like ‘Threads_running’
监控Innodb的阻塞

红色部分根据实际情况调整
SELECT b.trx_mysql_thread_id AS ‘被阻塞线程’
,b.trx_query AS ‘被阻塞SQL’
,c.trx_mysql_thread_id AS ‘阻塞线程’
,c.trx_query AS ‘阻塞SQL’
,(UNIX_TIMESTAMP()-UNIX_TIMESTAMP(c.trx_started)) AS ‘阻塞时间’
FROM information_schema.innodb_lock_waits a
JOIN information_schema.innodb_trx b ON a.requesting_trx_id=b.trx_id
JOIN information_schema.innodb_trx c ON a.blocking_trx_id= c.trx_id
WHERE(UNIX_TIMESTAMP()-UNIX_TIMESTAMP(c.trx_started))>60

MySQL主从复制监控

 

退出移动版