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