结合Zabbix做MySQL相关性能参数

以下代码由PHP站长网 52php.cn收集自互联网

现在PHP站长网小编把它分享给大家，仅供参考

#!/usr/bin/perl
use Getopt::Long;
use DBI;
use warnings;
use strict;

my $config_file='';
my $db_option='';

#用于保存从数据库得到的数据
my %status;

#Parameters
my $test_para='';
my $rcode='';

#Check Parameters 
if([email?protected]){
	&Useage;
	exit(123);
}

sub Useage{
     print "Please Use for example:n";
     print "$0 --config_file=path --db_option=web007n";
}

#sub Help{
#    print "--dbname=dbnamen";
#    print "--location=locationn";
#    print "--port=portn";
#    print "--db_user=db_usern";
#    print "--db_pass=passwordn";
#}

#Parse Parameters  解析参数的函数，其中需要指定配置文件的位置、数据库选项
$rcode = GetOptions('config_file=s' => $config_file,'db_option=s' => $db_option);
if(!$db_option){
print "please chose db_optionn";
exit(44);
}



#Get MySQL refer Parameters 取MySQL相关参数
my $dbname=&Read_config_file("db_name");
my $location=&Read_config_file("location");
#print "$location=$locationn";
my $port=&Read_config_file("port");
my $db_user=&Read_config_file("db_user");
my $db_pass=&Read_config_file("db_pass");
my $get_status=&Read_config_file("status");

#Connect MySQL Server,Get status and push into  array;Get one line everytime
#For example:
#First time @fetch_row=(Aborted_clients,4),so $fetch_row[0]=Aborted_clients,$fetch_row[1]=4
#and the Second time $fetch_row=(Aborted_connects,10),etc.
sub Connect_MySQL{
my $database="DBI:mysql:$dbname:$location:$port";
my $dbh=DBI->connect($database,$db_user,$db_pass) or die "Can not connect MySQL Server". $DBI::errstr;
my $sql="show global status";
my $sth=$dbh->prepare($sql);

$sth->execute() or die "Can not Execute SQL statement";

my @fetch_row;
my @table_name;
while (@fetch_row = $sth->fetchrow_array){
        my @[email?protected]_row;
	#print scalar(@row);
	push(@table_name,@row);
}

#List 
foreach my $table_name (@table_name){
	my($key,$value)=(@$table_name[0],@$table_name[1]);
	if(($key)&&($value)){
	$status{$key}=$value;
	}
}
}

sub Read_config_file{
#	print "Can Not Find $config_filen" if(!$config_file);
#	print "Can find $config_filen";
	open(CONFIG_FILE,"$config_file") or die "Can Not Open $config_file"; 
	my $get_parameters=shift;
	my $return_parameter='';
	#print "$get_parameters=$get_parametersn";

	my $read_format_head="&;$db_option&;";
	my $read_format_end="&;/$db_option&;";
	my @read_file=(<CONFIG_FILE>);
	my [email?protected]_file;
	for(my $i=0;$i<=($array_count-1);$i++){
		chomp($read_file[$i]);

		print "$db_option=$db_optionn";


	        my $is_valid_option_qr="&;(.*)&;";
		my @Refer_db_option;
		if($read_file[$i] =~ $is_valid_option_qr){
			push(@Refer_db_option,$1);
		}
		my $return_value = &is_valid_option(@Refer_db_option,$db_option);
		#print "$return_value=$return_valuen";



#		foreach my $judge_db_option(@Judge_db_option){
#		print "$judge_db_option=$judge_db_optionn";
#		last if($1 =~ $db_option);
#		}


		next if($read_file[$i] =~ /$read_format_head/);
		last  if($read_file[$i] =~ /$read_format_end/);
		#print "$read_file[$i]n";
		#$return_parameter=$read_file[$i] if($read_file[$i] =~ /$get_parameters=(.*)/);
		$return_parameter=$1 if($read_file[$i] =~ /$get_parameters=(.*)/);
		#print "$dbname=$dbnamen";
	}
	return $return_parameter;
}

sub is_valid_option{
	my @Judge_db_option=shift;
	my $get_db_option=shift;

	my [email?protected]_db_option;
	print "$Judge_db_option_count=$Judge_db_option_countn";
	print "$get_db_option=$get_db_optionn";
	#foreach my $judege_db (@Judge_db_option){
	#print "$judege_db=$judege_dbn";
	#last if($judege_db =~ $get_db_option);
	#}
	#return 0;

}


#Get MySQL status:QPS/Insert/Update etc.
#For example
#If want to get QPS,You should call GET_MySQL_Status(Queries,$Interval_time)
sub GET_MySQL_Status{
	my $MySQL_Status=shift;
	print "$MySQL_Status=$MySQL_Statusn";
	if(!$MySQL_Status){
	print "please Specify status value on configure file($config_file)n";
	}
	my $Interval_time=shift; 
	$MySQL_Status = 'Queries' if($MySQL_Status eq 'qps');
	#$MySQL_Status = "Com_$MySQL_Status" if ($MySQL_Status);
	
	#Call function Connect_MySQL for connect MySQL Server
	&Connect_MySQL;

	print "$MySQL_Status=$MySQL_Statusn";
	$Interval_time=2 if (!$Interval_time);
	print "$Interval_time=$Interval_timen";
	my $GET_MySQL_Status;
	my $MySQL_Status_old=$status{$MySQL_Status};
	#print "$status{$MySQL_Status}=$status{$MySQL_Status}n";

#debug
	#print "$MySQL_Status_old=";
	#print  sprintf("%0.2f",$MySQL_Status_old)."n";

	my $Uptime_old=$status{'Uptime'};
	sleep 2;
	my $MySQL_Status_now=$status{$MySQL_Status};

#debug
	#print "$MySQL_Status_now=";
	#print sprintf("%0.2f",$MySQL_Status_now)."n";


	my $Uptime_now=$status{'Uptime'};
	if(($MySQL_Status_now)&&($MySQL_Status_old)&&($Uptime_old)&&($Uptime_now)){
		if(($MySQL_Status_now = $MySQL_Status_old) || ($MySQL_Status_now == 0)){
		print "0n";
		}else{
		$GET_MySQL_Status=($MySQL_Status_now-$MySQL_Status_old)/($Uptime_now-$Uptime_old);
		#print sprintf("%.2f",$GET_MySQL_Status);	
		print "$GET_MySQL_Statusn";	
		}
	}
}
&GET_MySQL_Status($get_status,3);

以上内容由PHP站长网【52php.cn】收集整理供大家参考研究

如果以上内容对您有帮助，欢迎收藏、点赞、推荐、分享。

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!