Mysql数据库监控软件
Nagios
zabbix
ganglia
监控是否可以连接
mysqladmin -umonitor_user -p -h ping
telnet ip db_port
show variables like 'max_coonnections' ; 设置的最大连接数
show global status like 'Threads_connected' ; 当前系统连接的数量
Threads_connected / max_coonnections > 0.8 报警
性能监控
QPS = (Queries2-Queries1)/(Uptune_since_flush_status2 - Uptune_since_flush_status1)
TPS = ((Com_insert2 + Com_update2 + Com_delete2) - Com_insert1 + Com_update1 + Com_delete1) )/(Uptime_since_flush_status2 - Uptime_since_flush_statis1 )
监控Innodb的堵塞
SELECT b.trx_mysql_thread_id as '被阻塞线程'
,b.trx_query AS ‘被阻塞SQL’
,b.trx_mysql_thread_id AS '阻塞线程'
,c.trx_quer 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 )) > 60s
主从监控
工具 pt-table-checksum