ttlsa教程系列之MySQL—mysql数据库监控

1. 下载nagios监控插件
# wget http://www.percona.com/redir/downloads/percona-monitoring-plugins/LATEST/percona-monitoring-plugins-1.0.3.tar.gz

2. 监控命令定义
define command{
command_name check-mysql-processlist
command_line $USER1$/pmp-check-mysql-processlist -l $USER9$ -p $USER10$ -H $HOSTADDRESS$ -P $ARG1$ -w $ARG2$ -c $ARG3$
}文章源自运维生存时间-https://www.ttlsa.com/nagios/nagios-monitor-mysql/

define command{
command_name check-mysql-status-pct
command_line $USER1$/pmp-check-mysql-status -l $USER9$ -p $USER10$ -H $HOSTADDRESS$ -P $ARG1$ -x $ARG2$ -o $ARG3$ -y $ARG4$ -T pct -w $ARG5$
}文章源自运维生存时间-https://www.ttlsa.com/nagios/nagios-monitor-mysql/

define command{
command_name check-mysql-status-4-single-var
command_line $USER1$/pmp-check-mysql-status -l $USER9$ -p $USER10$ -H $HOSTADDRESS$ -P $ARG1$ -x $ARG2$ -w $ARG3$ -c $ARG4$
}文章源自运维生存时间-https://www.ttlsa.com/nagios/nagios-monitor-mysql/

define command{
command_name check-mysql-status-incr
command_line $USER1$/pmp-check-mysql-status -l $USER9$ -p $USER10$ -H $HOSTADDRESS$ -P $ARG1$ -x $ARG2$ -I $ARG3$ -w $ARG4$ -c $ARG5$
}文章源自运维生存时间-https://www.ttlsa.com/nagios/nagios-monitor-mysql/

define command{
command_name check-mysql-status-compare
command_line $USER1$/pmp-check-mysql-status -l $USER9$ -p $USER10$ -H $HOSTADDRESS$ -P $ARG1$ -x $ARG2$ -o $ARG3$ -y $ARG4$ -C '$ARG5$' -w $ARG6$ -c $ARG7$
}
说明：$USER9$是监控用户名，$USER10$密码。 在resource.cfg文件中定义。文章源自运维生存时间-https://www.ttlsa.com/nagios/nagios-monitor-mysql/

3. 数据库授权
mysql> grant super,porcess, REPLICATION CLIENT ON *.* TO 'nagiosuser'@'10.1.1.1' IDENTIFIED BY 'nagiospassword';文章源自运维生存时间-https://www.ttlsa.com/nagios/nagios-monitor-mysql/

4. 添加主机监控项
define service{
use mysql
host_name xxx.xxx.xxx.xxx
service_description check-mysql-processlist-3306
normal_check_interval 2
contact_groups admin_4
check_command check-mysql-processlist!3306!16!32
}文章源自运维生存时间-https://www.ttlsa.com/nagios/nagios-monitor-mysql/

define service{
use mysql
host_name xxx.xxx.xxx.xxx
service_description check-mysql-processlist-3308
normal_check_interval 2
contact_groups admin_4
check_command check-mysql-processlist!3308!16!32
}文章源自运维生存时间-https://www.ttlsa.com/nagios/nagios-monitor-mysql/

define service{
use mysql
host_name xxx.xxx.xxx.xxx
service_description check-mysql-status-pct-connection-3306
normal_check_interval 2
contact_groups admin_4
check_command check-mysql-status-pct!3306!Threads_connected!/!max_connections!80
}文章源自运维生存时间-https://www.ttlsa.com/nagios/nagios-monitor-mysql/

define service{
use mysql
host_name xxx.xxx.xxx.xxx
service_description check-mysql-status-pct-connection-3308
normal_check_interval 2
contact_groups admin_4
check_command check-mysql-status-pct!3308!Threads_connected!/!max_connections!80
}文章源自运维生存时间-https://www.ttlsa.com/nagios/nagios-monitor-mysql/

define service{
use mysql
host_name xxx.xxx.xxx.xxx
service_description check-mysql-Threads_running-3306
normal_check_interval 2
contact_groups admin_4
check_command check-mysql-status-4-single-var!3306!Threads_running!20!40
}文章源自运维生存时间-https://www.ttlsa.com/nagios/nagios-monitor-mysql/

define service{
use mysql
host_name xxx.xxx.xxx.xxx
service_description check-mysql-Threads_running-3308
normal_check_interval 2
contact_groups admin_4
check_command check-mysql-status-4-single-var!3308!Threads_running!20!40
}文章源自运维生存时间-https://www.ttlsa.com/nagios/nagios-monitor-mysql/

define service{
use mysql
host_name xxx.xxx.xxx.xxx
service_description check-mysql-Created_tmp_disk_tables-3306
normal_check_interval 2
contact_groups admin_4
check_command check-mysql-status-incr!3306!Created_tmp_disk_tables!10!10!30
}文章源自运维生存时间-https://www.ttlsa.com/nagios/nagios-monitor-mysql/

define service{
use mysql
host_name xxx.xxx.xxx.xxx
service_description check-mysql-Created_tmp_disk_tables-3308
normal_check_interval 2
contact_groups admin_4
check_command check-mysql-status-incr!3308!Created_tmp_disk_tables!10!10!30
}文章源自运维生存时间-https://www.ttlsa.com/nagios/nagios-monitor-mysql/

define service{
use mysql
host_name xxx.xxx.xxx.xxx
service_description check-mysql-Created_tmp_files-3306
normal_check_interval 2
contact_groups admin_4
check_command check-mysql-status-incr!3306!Created_tmp_files!10!10!30
}文章源自运维生存时间-https://www.ttlsa.com/nagios/nagios-monitor-mysql/

define service{
use mysql
host_name xxx.xxx.xxx.xxx
service_description check-mysql-Created_tmp_files-3308
normal_check_interval 2
contact_groups admin_4
check_command check-mysql-status-incr!3308!Created_tmp_files!10!10!30
}文章源自运维生存时间-https://www.ttlsa.com/nagios/nagios-monitor-mysql/

define service{
use mysql
host_name xxx.xxx.xxx.xxx
service_description check-mysql-Created_tmp_tables-3306
normal_check_interval 2
contact_groups admin_4
check_command check-mysql-status-incr!3306!Created_tmp_tables!10!10!30
}文章源自运维生存时间-https://www.ttlsa.com/nagios/nagios-monitor-mysql/

define service{
use mysql
host_name xxx.xxx.xxx.xxx
service_description check-mysql-Created_tmp_tables-3308
normal_check_interval 2
contact_groups admin_4
check_command check-mysql-status-incr!3308!Created_tmp_tables!10!10!30
}文章源自运维生存时间-https://www.ttlsa.com/nagios/nagios-monitor-mysql/

define service{
use mysql
host_name xxx.xxx.xxx.xxx
service_description check-mysql-Table_locks_waited-3306
normal_check_interval 2
contact_groups admin_4
check_command check-mysql-status-incr!3306!Table_locks_waited!10!10!30
}文章源自运维生存时间-https://www.ttlsa.com/nagios/nagios-monitor-mysql/

define service{
use mysql
host_name xxx.xxx.xxx.xxx
service_description check-mysql-Table_locks_waited-3308
normal_check_interval 2
contact_groups admin_4
check_command check-mysql-status-incr!3308!Table_locks_waited!10!10!30
}文章源自运维生存时间-https://www.ttlsa.com/nagios/nagios-monitor-mysql/

define service{
use mysql
host_name xxx.xxx.xxx.xxx
service_description check-mysql-key_hit_rate-3306
normal_check_interval 2
contact_groups admin_4
check_command check-mysql-status-compare!3306!Key_reads!/!Uptime!<!20!10
}

define service{
use mysql
host_name xxx.xxx.xxx.xxx
service_description check-mysql-key_hit_rate-3308
normal_check_interval 2
contact_groups admin_4
check_command check-mysql-status-compare!3306!Key_reads!/!Uptime!<!20!10
}

define service{
use mysql
host_name xxx.xxx.xxx.xxx
service_description check-mysql-full_table_scan-3306
normal_check_interval 2
contact_groups admin_4
check_command check-mysql-status-compare!3306!Handler_read_rnd_next!/!Handler_read_rnd!>!20!30
}

define service{
use mysql
host_name xxx.xxx.xxx.xxx
service_description check-mysql-full_table_scan-3308
normal_check_interval 2
contact_groups admin_4
check_command check-mysql-status-compare!3306!Handler_read_rnd_next!/!Handler_read_rnd!>!20!30
}

5. 展示

提供一个快速添加监控项脚本。

使用方法
perl mysql_jk.pl 监控文件目录 服务器列表 组名
如： perl mysql_jk.pl /usr/local/nagios/etc/conf/ttlsa.com serverlist.txt admin_4
serverlist.txt格式如下：
1.1.242.50,10.1.242.50,3306,3308

[codesyntax lang="perl"]

#!/usr/bin/perl

use strict;
use Data::Dumper;

sub content{
my ($server,$port,$admin) = @_;
my $content=<<"END";
define service{
use mysql
host_name $server
service_description check-mysql-processlist-$port
contact_groups $admin
check_command check-mysql-processlist!$port!16!32
}

define service{
use mysql
host_name $server
service_description check-mysql-status-pct-connection-$port
contact_groups $admin
check_command check-mysql-status-pct!$port!Threads_connected!/!max_connections!80
}

define service{
use mysql
host_name $server
service_description check-mysql-Threads_running-$port
contact_groups $admin
check_command check-mysql-status-4-single-var!$port!Threads_running!20!40
}

define service{
use mysql
host_name $server
service_description check-mysql-Created_tmp_disk_tables-$port
contact_groups $admin
check_command check-mysql-status-incr!$port!Created_tmp_disk_tables!10!10!30
}

define service{
use mysql
host_name $server
service_description check-mysql-Created_tmp_files-$port
contact_groups $admin
check_command check-mysql-status-incr!$port!Created_tmp_files!10!10!30
}

define service{
use mysql
host_name $server
service_description check-mysql-Created_tmp_tables-$port
contact_groups $admin
check_command check-mysql-status-incr!$port!Created_tmp_tables!10!10!30
}

define service{
use mysql
host_name $server
service_description check-mysql-Table_locks_waited-$port
contact_groups $admin
check_command check-mysql-status-incr!$port!Table_locks_waited!10!10!30
}

define service{
use mysql
host_name $server
service_description check-mysql-key_hit_rate-$port
contact_groups $admin
check_command check-mysql-status-compare!$port!Key_reads!/!Uptime!<!5!1
}

define service{
use mysql
host_name $server
service_description check-mysql-full_table_scan-$port
contact_groups $admin
check_command check-mysql-status-compare!$port!Handler_read_rnd_next!/!Handler_read_rnd!>!20!30
}
END
return $content;
}
opendir(my $CONFDIR,$ARGV[0]) || die;
my @filelist = readdir($CONFDIR);

open(my $serverlist, $ARGV[1]) || die;

while(<$serverlist>){
chomp;
my @info = split /,/;
foreach(@info){
s/\A\s+|\s+\z//g;
}
my @ports = @info[2..$#info];
if(grep (/$info[0]/,@filelist)){
my $server = $info[0];
foreach my $port (@ports){
my $content = content($server,$port,$ARGV[2]);
#print $content;
print {open my $fh, ">>","$ARGV[0]/$server.cfg"; $fh or die} $content;
print "server $server:$port\n";
}
}elsif(grep(/$info[1]/,@filelist)){
my $server = $info[1];
foreach my $port(@ports){
my $content = content($server,$port,$ARGV[2]);
#print $content;
print {open my $fh, ">>","$ARGV[0]/$server.cfg"; $fh or die} $content;
print "server $server:$port\n";
}
}
}

[/codesyntax]