Perl统计mysql 从库behind时间

最新推荐文章于 2024-10-06 06:16:12 发布

钟大發

最新推荐文章于 2024-10-06 06:16:12 发布

阅读量227

点赞数

分类专栏： Perl 文章标签： perl

本文链接：https://blog.csdn.net/zyxpaomian/article/details/104696451

版权

Perl 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

背景

之前由于公司从库用的服务器型号并不统一，主要是磁盘IO这块，相比主库的服务器来说差的有点大，于是只能通过脚本检查下从库落后的时间，以便于后期的更换和迁移,好几年前的脚本了，想想perl的现状，真是已经快下不来perl了

脚本

用perl 写的，主要因为DB只有本地登录密码，懒的在每台DB上加帐号加权限，所以只能用ssh的方式进行了登录后再查了，最后把结果更新在本地数据库里，也方便查询。由于数据库都分布在不同机房的不同网段，机器很多，所以这样其实也挺慢的，最好ssh的时候fork几个进程一起跑，会快不少。当时写的时候忘了加了- - 。

#! /usr/bin/perl -w
#auther Barney by 20160508

#! /usr/bin/perl

use strict;
use DBI;
use Net::SSH::Perl;
use Mail::Sender;

sub main {
&db_init;
&db_update;
&info_print;
my $check_result=`cat data/result.txt | grep "Slave Server"`;
if($check_result) {
&mail_send;
}
else {
print "no behind!!\n";
}
}

sub ssh_get {
	my ($ip,$passwd,$socket)=@_;
	my $cmd="`which mysql`  -ubarney -ppasswd  -e 'show slave status\\G;' --socket=$socket | grep -i \"Seconds_Behind_Master\" |awk -F \":\" '{print \$2}'"; #有些mysql 是编译安装的。所以路径不一定对。。
	my $osuser="barney";
	my %params = (
		port => '22',
		protocal => '2,1',
	);
	my $ssh = Net::SSH::Perl->new($ip,%params);
	$ssh->login($osuser,$passwd,$socket);
	my ($out,$err,$exit) = $ssh->cmd($cmd);
	return ($out);
}


sub db_init {
	my $driver="DBI:mysql";
	my $database1="DB_OPS";
	my $user="barney";
	my $password='barney@123';
	my $db_connect = DBI->connect("$driver:$database1:mysql_socket=/var/run/mysqld/mysqld_new.sock","$user","$password");
}

sub db_update {
	my $db_connect=&db_init;
	my ($i,$j);
	$db_connect->do("SET NAMES utf8"); 
	my $db_status=$db_connect->prepare("select * from DB_OPS.t_master_db_instance;");
	$db_status->execute;
	
	my( $ip, $project, $behind ,$mysql_cmd,$socket,$check_flag );
	$db_status->bind_columns( undef, \$ip, \$project, \$behind, \$mysql_cmd, \$socket,\$check_flag);
	while( $db_status->fetch() ) {
		if ($ip =~ /192.168.1./ || $ip =~ /192.168.2./ || $ip =~ /192.168.3./ || $ip =~ /192.168.4./ || $ip =~ /192.168.5./ || $ip =~ /192.168.6./) {
			my $passwd="barney@1";
			my $i=&ssh_get($ip,$passwd,$socket);
			my $j=$i/3600;
			my $now_behind = sprintf("%.1f",$j);
			my $db_update = $db_connect->do("update DB_OPS.t_master_db_instance set db_behind='$now_behind' where db_ip='$ip';");
			if ($now_behind =~ /0./) {
				my $check_flag = $db_connect->do("update DB_OPS.t_master_db_instance set check_flag='0' where db_ip='$ip';");
			}
			else {
				my $check_flag = $db_connect->do("update DB_OPS.t_master_db_instance set check_flag='1' where db_ip='$ip';");
				}
		}

}
	$db_status->finish();
	$db_connect->disconnect();
}

sub info_print {
	my $data_dir="./data";
	mkdir $data_dir,0775;
	open(INFO,">$data_dir/result.txt");
	print INFO "Dear All:\n\n\n Slave info:\n\n ++++++++++++++++++++++++\n";
	&result_filter;
	print INFO "++++++++++++++++++++++++\n \n\n End Message from 192.168.2.104\n";
	close (INFO);
}

sub result_filter {	##将有延迟的数据筛选出来
	my $db_connect=&db_init;
	$db_connect->do("SET NAMES utf8");
	my $db_status=$db_connect->prepare("select db_ip,db_project,db_behind,socket from DB_OPS.t_master_db_instance where check_flag=1;");
	$db_status->execute;

	my( $ip, $project, $behind ,$socket);
	$db_status->bind_columns( undef, \$ip,\$project, \$behind, \$socket);
	while( $db_status->fetch() ) {
		my $behind_info="\t\tSlave Server: $ip Project Name: $project Behind Time: ${behind} hour Socket Time: $socket \n\n";
		print INFO "$behind_info";
	}
}


sub mail_send { 	##发送邮件通知
my $sender = new Mail::Sender
{
  		smtp    => 'mail.shidc.barney.com',
  		from    => 'ops@barney.com',
} or die "error";
	my $message = `cat ./data/result.txt`;
	if($sender-> MailMsg ({
             to      => 'ops@barney.com',
             subject => 'warnning!!! 从库落后',
             msg     =>  $message })<0)
{
  die "$Mail::Sender::Error/n";
}
$sender->Close();
}	


&main();