Nagios Plugins for Monitoring My SQL Presented by

Nagios Plugins for Monitoring My. SQL Presented by: Sheeri K. Cabral Database Operations Manager www. palominodb. com

02/12/2020 What is Monitoring? Threshold alerting Graphing/trending 2

02/12/2020 Why Monitor? Problem alerting Find patterns Early warning for potential issues 3

Open Source Alerting Solutions Nagios Cacti Munin Zabbix Zenoss Open. NMS 02/12/2020 4

Commercial Alerting Solutions My. SQL Enterprise Monitor MONyog Oracle Grid Control Hyperic HQ 02/12/2020 5

Best to use what you already have 02/12/2020 6

Nagios is great because. . . anyone can write a plugin 02/12/2020 7

The problem with Nagios. . . anyone can write a plugin 02/12/2020 8

Official Nagios Plugins for My. SQL 02/12/2020 check_mysql_query 9

02/12/2020 check_mysql db connectivity slave running slave lag using seconds_behind_master 10

02/12/2020 check_mysql_query Checks the output of a query is within a certain range (numerical) 11

02/12/2020 My. SQL Plugins on Nagios Exchange 12

System vars Status vars Caching Calculations Check 1 yes no no check_mysql no yes no no check_mysqld_statu s no yes no no check_mysql_stats yes no check_mysqld no many no no check_mysql_health yes no Hard-coded Check 7 yes no Hard-coded 02/12/2020 13

“Let us know what you'd like to see!” 02/12/2020 14

What I want New check 02/12/2020 System vars Status vars Caching Calculations yes yes flexible 15

02/12/2020 Caching Save information to a file --cache-dir /path/to/dir/ Use the file instead of connecting again --max-cache-age <seconds> --no-cache to force connection 16

02/12/2020 --mode=varcomp %metadata{varstatus} SHOW GLOBAL VARIABLES SHOW GLOBAL STATUS --expression allows word replacement --comparison flexible 17

$Sample Command Definition define command { command_name check_mysql_tmp_tables command_line $USER 1$/mysql_health_check. pl --hostname $HOSTADDRESS$$

Sample Command Definition define command { command_name check_mysql_tmp_tables command_line $USER 1$/mysql_health_check. pl --hostname $HOSTADDRESS$ --user myuser --password mypass --cache-dir=/var/lib/nagios/mysql_cache --max-cache-age=300 --mode=varcomp --expression= "Created_tmp_tables/Uptime_since_flush_status" --comparison=">(10/60)" } 02/12/2020 18

$Sample Service Definition define service { use host_name service_description check_command } 02/12/2020 generic-service __HOSTNAME__$

Sample Service Definition define service { use host_name service_description check_command } 02/12/2020 generic-service __HOSTNAME__ My. SQL Temp Tables check_mysql_tmp_tables 19

$02/12/2020 --mode=long-query --mode=locked-query %metadata{proc_list} SHOW FULL PROCESSLIST 20$

02/12/2020 --mode=long-query --mode=locked-query %metadata{proc_list} SHOW FULL PROCESSLIST 20

$Extending Information sub fetch_server_meta_data {} add a new hash key to %meta_data $meta_data{innodb_status} =$

Extending Information sub fetch_server_meta_data {} add a new hash key to %meta_data $meta_data{innodb_status} = $dbh->selectall_arrayref( q|SHOW ENGINE INNODB STATUS|); 02/12/2020 21

$02/12/2020 Currently Unused %metadata{innodb_status} SHOW ENGINE INNODB STATUS %metadata{master_status} SHOW MASTER STATUS %metadata{slave_status} SHOW$

02/12/2020 Currently Unused %metadata{innodb_status} SHOW ENGINE INNODB STATUS %metadata{master_status} SHOW MASTER STATUS %metadata{slave_status} SHOW SLAVE STATUS 22

02/12/2020 Limitations --comparison for critical only, not warning (in progress) Cannot compare to previous (in progress) One check/calculation per Nagios service Does not output for performance data 23

02/12/2020 Questions? Comments? 24