05/8/14

perl 批量执行命令脚本

一、环境描述

监控发现多台服务器负载异常,登陆服务器查看,发现salt-minion进程狂多(>100个),而且cpu占用100%。从而导致服务器负责也飙上去。

二、原因

通过分析,应该是由于slat-master进程关闭,各个服务器的salt-minion客服端无法与服务端进行交互,导致客户端堆积需求salt-minion进程,消耗大量内存和cpu资源。

三、处理步骤

1、重启服务端salt-master进程,结果发现:服务端网卡及负载波动太大,并且执行salt命令会超时,无返回接口。

2、查询salt-minion日志,发现大量如下报错:

[salt.crypt ][ERROR   ] The Salt Master has cached the public key for this node, this salt minion will wait for 10 seconds before attempting to re-authenticate

经核实,该问题在salt 0.7.1版本中确实存在,主要时由于mine模块功能不全导致,后期版本已经修复这个bug。我们可以通过修改mine模块代码,限制salt-minion开启进程数。

3,面对salt相关命令执行无效的问题,采用将服务端和客服端的/etc/salt/kpi目录下缓存文件删除,并重新salt-key -A所有客户端minion连接。但服务器比较多,需要批量处理。

4、批量脚步采用了网上现成代码进行修改,主要逻辑是:使用perl的Net::SSH::Expect模块,模拟ssh到各个服务器执行一系列命令,从而达到解决问题的目的。

5、脚步包括一个perl脚步salt_minion_batch_restart.pl + 命令列表 commend_list.txt + 服务器ip_port列表 ip_list.txt

6、执行完脚步之后,slat-key -A 。然后,通过salt命令检查,如:salt ‘*’ cmd .run ‘uptime’。

四、代码展示

1、salt_minion_batch_restart.pl

#!/usr/bin/perl

#use strict;

use Net::SSH::Expect;

use Net::SCP::Expect;

 

my @ssh_list;

my $ip_list=’ip_list.txt’;

my $command_txt=’command_list.txt’;

 

open FH,$ip_list;

while(<FH>){

if (!($_ =~ m/\#|^$/)) #删除注释和空行

{

@ssh_list=split;

print “======= “,$ssh_list[0],” =======\n”;

print “开始时间:”,get_time(),”\n”;

print “正在登陆”,$ssh_list[0],”…\n”;

&salt_minion_restart(“$ssh_list[0]“,”$ssh_list[1]“);

print “结束时间:”,get_time().”\n”;

}

}

close FH;

 

sub  salt_minion_restart(){

my($host,$port)=@_;

my $ssh= Net::SSH::Expect->new(

host    =>      $host,

port    =>      $port,

user    =>      “root”,

no_terminal     =>0,

raw_pty=>1,

timeout=>       3,

);

$ssh->debug(0);

$ssh->run_ssh or die “SSH process cann’t start :$!”;

my $line;

if ($line=$ssh->read_line()){

print $line .”\n”;

if ($line =~ /Last/)

{

print “$ssh_list[0] has logined\n”;

}

else {

$ssh->send(“yes\n”);

print “$ssh_list[0] send yes\n “;

}

}

 

open F1,$command_txt;

while(<F1>){

if (!($_ =~ m/\#|^$/)) #删除注释和空行

{

my @command=split/\n/,$_;

$result=$ssh->exec(“$command[0]“);

print $command[0],” –>”,$result,”\n”;

}

#print $ssh_list[0],”命令执行完毕\n”;

}

close F1;

 

$ssh->close();

 

}

 

sub get_time {

my $time = shift || time();

my ($sec,$min,$hour,$day,$mon,$year,$wday) = localtime($time);

$year += 1900;

$mon += 1;

$min = ’0′.$min if length($min) < 2;

$sec = ’0′.$sec if length($sec) < 2;

$mon = ’0′.$mon if length($mon) < 2;

$day = ’0′.$day if length($day) < 2;

$hour = ’0′.$hour if length($hour) < 2;

my $weekday = (‘Sun’,’Mon’,’Tue’,’Wed’,’Thu’,’Fri’,’Sat’)[$wday];

my $time_now = “$year-$mon-$day $hour:$min:$sec $weekday”;

return $time_now;

}

 

2、commend_line.txt

test -e /etc/salt/pki/minion && (cd /etc/salt/pki && rm -rf minion)|| echo ‘not exist’ #判断pki下缓存目前是否存在,存在就删除,不存在的话就输出‘not exist’

test -e /etc/init.d/salt-minion && /etc/init.d/salt-minion start #判断salt-minion是否存在,存在的话就启动

ps aux|grep salt  #检查确认salt-minion是否启动成功

 

3、ip_list.txt

192.168.1.133 222

192.168.1.134 222

192.168.1.135 222

192.168.1.136 222

192.168.1.137 222

192.168.1.138 222

192.168.1.139 222

4、存在问题

没有完善的日志功能,从何导致执行过程中ctrl+c 终止了脚本,又要重新开始执行一遍。后期解决这个问题。

04/22/14

tcpdump 抓取MySQL执行语句

一、环境描述
192.168.1.116:3320 MySQL实例
192.168.1.118 当前主机
两台主机em2均为内网,em1为外网网口
二、命令实例
1、抓取当前主机与192.168.1.116的3320端口数据库实例sql,实时输出符号条件的sql,Ctrl+c 停止抓取。
tcpdump -i em2 -s 0 -l -w – dst 192.168.1.116 and port 3320|strings |grep -i -E ‘select|update|insert|delete|set’
2、在第1个基础上,可以指定抓取多少个包
tcpdump -i em2 -s 0 -l -c 10000 -w – dst 192.168.1.116 and port 3320|strings |grep -i -E ‘select|update|insert|delete|set’
3、在192.168.1.116实现上述功能
tcpdump -i em2 -c 2000 -w – dst port 3320|strings –target=utf8|grep -i -E ‘select|update|insert|delete|set’
4、tcpdump 结合perl
tcpdump -i em2 -s 0 -w – dst port 3320| strings –target=utf8| perl -e ‘
while(<>) { chomp; next if /^[^ ]+[ ]*$/;
if(/^(SELECT|UPDATE|DELETE|INSERT|SET|COMMIT|ROLLBACK|CREATE|DROP|ALTER|CALL)/i) {
if (defined $q) { print “$q\n”; }
$q=$_;
} else {
$_ =~ s/^[ \t]+//; $q.=” $_”;
}
}’

三、tcpdump用法
1、tcpdump采用命令行方式,它的命令格式为:
  tcpdump [ -adeflnNOpqStvx ] [ -c 数量 ] [ -F 文件名 ]
          [ -i 网络接口 ] [ -r 文件名] [ -s snaplen ]
          [ -T 类型 ] [ -w 文件名 ] [表达式 ]
2、tcpdump的选项介绍
   -a    将网络地址和广播地址转变成名字;
   -d    将匹配信息包的代码以人们能够理解的汇编格式给出;
   -dd    将匹配信息包的代码以c语言程序段的格式给出;
   -ddd    将匹配信息包的代码以十进制的形式给出;
   -e    在输出行打印出数据链路层的头部信息;
   -f    将外部的Internet地址以数字的形式打印出来;
   -l    使标准输出变为缓冲行形式;
   -n    不把网络地址转换成名字;
   -t    在输出的每一行不打印时间戳;
   -v    输出一个稍微详细的信息,例如在ip包中可以包括ttl和服务类型的信息;
   -vv    输出详细的报文信息;
   -c    在收到指定的包的数目后,tcpdump就会停止;
   -F    从指定的文件中读取表达式,忽略其它的表达式;
   -i    指定监听的网络接口;
   -r    从指定的文件中读取包(这些包一般通过-w选项产生);
   -w    直接将包写入文件中,并不分析和打印出来;
   -T    将监听到的包直接解释为指定的类型的报文,常见的类型有rpc (远程过程调用)和snmp(简单网络管理协议;)

12/31/13

perl 脚本【通过xml配置文件,监控mysql master\slave 同步】

2013年最后一天了,分享一个监控mysql 同步的perl脚本,给2013划个句号!

1、xml配置文件

<CiMysql_DBServer_Cluster>

                <master_list>

                                <name>Bbs_DB_Master </name>
                                <type>mysql </type>
                                <ip>xxx.xxx.xxx.242 </ip>
                                <port>3312</port>
                                <monitor_user>monitorxxxxxx</monitor_user>
                                <monitor_pass>xxxxxx </monitor_pass>
                                <monitor_db>bbs</monitor_db>
               </master_list>

                <slave_list>
                            <slave>
                                <name>Bbs123_3312</name>
                                <type>mysql</type>
                                <ip>xxx.xx.x.123</ip>
                                <port>3312</port>
                                <monitor_user>monitorxxxxxx</monitor_user>
                                <monitor_pass>xxxxxx</monitor_pass>
                                <monitor_db>bbs</monitor_db>
                            </slave>
                            <slave>
                                <name>Bbs13799_3312</name>
                                <type>mysql</type>
                                <ip>xxx.xxx.xxx.99</ip>
                                <port>3312</port>
                                <monitor_user>monitorxxxxxx</monitor_user>
                                <monitor_pass>xxxxxx</monitor_pass>
                                <monitor_db>bbs</monitor_db>
                              </slave>
                              <slave>
                                <name>Bbs253_3312</name>
                                <type>mysql</type>
                                <ip>xxx.xxx.xxx.253</ip>
                                <port>3312</port>
                                <monitor_user>monitorxxxxxx</monitor_user>
                                <monitor_pass>xxxxxx</monitor_pass>
                                <monitor_db>bbs</monitor_db>
                              </slave>
             </slave_list>
</CiMysql_DBServer_Cluster>

2、perl代码

#!/user/bin/perl
# add by chen-123 @phpdba 2013
use warnings;
use strict;
use DBI;
use Net::SMTP;
#use Net::SMTP_AUTH;
#use MIME::Base64;
use IO::Socket;
use XML::Simple;
use Time::HiRes;
use POSIX "strftime";
#use Data::Dumper;

my $simple;
my $xml;
my @data;
my ($M_dbh,$S_dbh,$M_connect,$S_connect);
my %ip_port_list;
my $app_name;
my $today = strftime("%Y-%m-%d",localtime(time));

my $check_log = 'mysql_check_log/mysql_replication_log'.strftime("%Y-%m-%d",localtime(time)).'.txt';
my $open_check_log = 'yes';
my $default_seconds_behind_limit = 20;
my $master_slave_table_diff_size = 30;
my $send_email_status = 0;
my $debug_print = 1;

my ($master_monitor_user,$master_monitor_pass,$master_monitor_db,$master_ip,$master_db_port,$master_type,$master_name);
my ($slave_monitor_user,$slave_monitor_pass,$slave_monitor_db,$slave_ip,$slave_db_port,$slave_type,$slave_name);

$simple = XML::Simple->new();
my $data   = $simple->XMLin('db_master_slave_config.xml');
my $tmp = $data->{server};
my @kk = keys(%$tmp);
foreach my $kv (@kk){
        print $kv."\n";
        my $master_list = $tmp->{$kv}->{master_list}->{master};
        my $slave_list = $tmp->{$kv}->{slave_list}->{slave};

        if($master_list->{name}){
                $master_monitor_user = $master_list->{monitor_user};
                $master_monitor_pass = $master_list->{monitor_pass};
                $master_monitor_db = $master_list->{monitor_db};
                $master_ip = $master_list->{ip};
                $master_db_port = $master_list->{port};
                $master_type = $master_list->{type};
                $master_name = $master_list->{name};
                #print "$master_name -> $master_type -> $master_monitor_db -> $master_ip -> $master_db_port ->$master_monitor_user -> $master_monitor_pass \n";
        }else{
                foreach my $k_ml ( keys(%$master_list)){
                        if(ref($master_list->{$k_ml}) eq "HASH"){
                                $master_monitor_user = $master_list->{$k_ml}->{monitor_user};
                                $master_monitor_pass = $master_list->{$k_ml}->{monitor_pass};
                                $master_monitor_db = $master_list->{$k_ml}->{monitor_db};
                                $master_ip = $master_list->{$k_ml}->{ip};
                                $master_db_port = $master_list->{$k_ml}->{port};
                                $master_type = $master_list->{$k_ml}->{type};
                                $master_name = $k_ml;
                                #print "$master_name -> $master_type -> $master_monitor_db -> $master_ip -> $master_db_port ->$master_monitor_user -> $master_monitor_pass \n";
                        }
                }
        }

        print "$master_name -> $master_type -> $master_monitor_db -> $master_ip -> $master_db_port ->$master_monitor_user -> $master_monitor_pass \n";

        if($slave_list->{name}){
                $slave_monitor_user = $slave_list->{monitor_user};
                $slave_monitor_pass = $slave_list->{monitor_pass};
                $slave_monitor_db = $slave_list->{monitor_db};
                $slave_ip = $slave_list->{ip};
                $slave_db_port = $slave_list->{port};
                $slave_type = $slave_list->{type};
                $slave_name = $slave_list->{name};
                $app_name = $slave_name;
                print "$slave_name -> $slave_type -> $slave_monitor_db -> $slave_ip -> $slave_db_port -> $slave_monitor_user -> $slave_monitor_pass \n";
                &monitor_mysql_master_slave();
        }else{
                foreach my $k_sl ( keys(%$slave_list)){
                        if(ref($slave_list->{$k_sl}) eq "HASH"){
                                $slave_monitor_user = $slave_list->{$k_sl}->{monitor_user};
                                $slave_monitor_pass = $slave_list->{$k_sl}->{monitor_pass};
                                $slave_monitor_db = $slave_list->{$k_sl}->{monitor_db};
                                $slave_ip = $slave_list->{$k_sl}->{ip};
                                $slave_db_port = $slave_list->{$k_sl}->{port};
                                $slave_type = $slave_list->{$k_sl}->{type};
                                $slave_name = $k_sl;
                                $app_name = $slave_name;
                                print "$slave_name -> $slave_type -> $slave_monitor_db -> $slave_ip -> $slave_db_port -> $slave_monitor_user -> $slave_monitor_pass \n";
                                &monitor_mysql_master_slave();
                        }
                }
        }
}

sub monitor_mysql_master_slave{
        &phpdba_log(&print_config());
        %ip_port_list = (
                        "$master_ip"=>"$master_db_port",
                        "$slave_ip"=>"$slave_db_port"
                        );

        $M_dbh = &get_connect($master_ip,$master_db_port,$master_monitor_db,$master_monitor_user,$master_monitor_pass) or
                &phpdba_log("ERROR:Can't connect to MASTER!\n $app_name checking over!\n -----------------------------------------\n");
        sleep 1;
        $S_dbh = &get_connect($slave_ip,$slave_db_port,$slave_monitor_db,$slave_monitor_user,$slave_monitor_pass) or
                &phpdba_log("ERROR:Can't connect to SLAVE!\n $app_name checking over!\n -----------------------------------------\n");

        $M_connect = $M_dbh && $M_dbh->ping;
        $S_connect = $S_dbh && $S_dbh->ping;
        if($M_connect && $S_connect){
                &main();
        }
}

sub print_config{
        return "=======================\n monitor db:$master_monitor_db\n \
           monitor ip:$master_ip \n master port:$master_db_port \n slave ip:$slave_ip\n slave port:$slave_db_port \n =======================\n";
}

sub main{
        &check_port_status();
        &phpdba_mail();
        &dbh_disconnect();
        if($send_email_status == 1){
                &phpdba_log($app_name." MySQL Slave is Error!");
        }else{
                &phpdba_log($app_name." MySQL Slave is OK!");
        }
        &phpdba_log($app_name." checking over! \n-----------------------------------------\n",1);
        $send_email_status = 0;
}

sub check_port_status{
        my $key;
        my $value;
        while(($key,$value)=each %ip_port_list){
                sleep 1;
                if(! &port_status($key,$value)){
                        &phpdba_log("$key:$value Downing ...!");
                        &mail_send($value,"Mysql_Port:$key","$key is Downing ...! ");
                }
        }
}

sub phpdba_mail{
        foreach my $k (@data){
                pop(@data);
        }
        #my @check_rep_status_arr = &check_rep_status();
        #print @check_rep_status_arr;
        if(my @check_rep_status_arr = &check_rep_status()){
                foreach my $data (@check_rep_status_arr){
                        push(@data,$data);
                }
        }

#       my $data = @data ? join('\r',@data):" ";
        if(! $M_connect){
                &mail_send($master_ip,"Mysql_Master_Ser","Mysql_Master_Ser is Downing...!");
                sleep 1;
        };
        if(! $S_connect){
                &mail_send($slave_ip,"Mysql_Slave_Ser","Mysql_Slave_ser is Downing...!");
                sleep 1;
        }
        }
        #print "\@data:@data\n";
        if(scalar(@data)>0){
                #&mail_send($slave_ip,"Replication","Replication Error ...!","$data");
                &check_master_slave_diff();
                sleep 1;
        }

        #if($send_email_status == 1){
        #       &phpdba_log($app_name." MySQL Slave is OK!");
        #}

sub phpdba_log{
        if($debug_print != 1){
                return ;
        }
        my $time = &get_time();
        my $content=$_[0];
        my $time_show = $_[1]?1:0;

        if($open_check_log eq "yes"){
                open (LOG ,'>>'.$check_log) or die "Log file: $!";
                if($time_show){
                        print LOG " $time\n $content  \n";
                }else{
                        print LOG " $content  \n";
                }
                close LOG;
        }
}

sub dbh_disconnect{
        $S_dbh->disconnect();
        $M_dbh->disconnect();
}
sub port_status {
        my $ip = shift;
        my $port = shift;
        my $sock = IO::Socket::INET->new(Proto=>'tcp',PeerAddr=>$ip,PeerPort=>$port,Timeout=>10);
        #phpdba_log("port_status");
        return $sock ? 1:0;
        #$sock ? return 1:return 0;
}

sub get_time {
        my $time = shift || time();
        my ($sec,$min,$hour,$day,$mon,$year,$wday) = localtime($time);
        $year += 1900;
        $mon += 1;
        $min = '0'.$min if length($min) < 2;
        $sec = '0'.$sec if length($sec) < 2;
        $mon = '0'.$mon if length($mon) < 2;
        $day = '0'.$day if length($day) < 2;
        $hour = '0'.$hour if length($hour) < 2;
        my $weekday = ('Sun','Mon','Tue','Wed','Thu','Fri','Sat')[$wday];
        my $time_now = "$year-$mon-$day $hour:$min:$sec $weekday";
        return $time_now; } sub get_connect{
         my $host = shift;
         my $db_port = shift;
         my $monitor_db = shift;
         my $monitor_user = shift;
         my $monitor_pass = shift;
         my $dsn = "DBI:mysql:$monitor_db:$host:$db_port";
         #&phpdba_log($dsn);
         my $dbh = DBI->connect($dsn,$monitor_user,$monitor_pass,{RaiseError=>0,PrintError=>0});
        if(!$dbh) {
                &phpdba_log("ERROR:Can't connect to MySQL (host=$host:$db_port,user=$monitor_user)!");
                &mail_send("$host","MySQL Connect Error","ERROR:Can't connect to MySQL (host=$host:$db_port,user=$monitor_user)!");
        }
        return $dbh;
}
sub check_rep_status{
        my ($error,%result,$data);
        my $sql = "Show Slave Status";
        my $sth = $S_dbh->prepare($sql);
        $sth->execute();
        %result = %{$data} while($data=$sth->fetchrow_hashref);
        $sth->finish();

        #return "ok";
        if((defined($result{'Slave_IO_Running'}) && $result{'Slave_IO_Running'} ne 'Yes') || (defined($result{'Slave_SQL_Running'}) && $result{'Slave_SQL_Running'} ne 'Yes')){
                &phpdba_log($app_name." MySQL Replication Error!\n Slave_IO_Running=".$result{'Slave_IO_Running'}."\n Slave_SQL_Running=".$result{'Slave_SQL_Running'}."\n");
                &mail_send("$slave_ip","MySQL Replication Error","\n Slave_IO_Running=".$result{'Slave_IO_Running'}."\n Slave_SQL_Running=".$result{'Slave_SQL_Running'}."\n");
                return ("Slave_IO_Running=".$result{'Slave_IO_Running'}."\nSlave_SQL_Running=".$result{'Slave_SQL_Running'});
        }

        if(defined($result{'Seconds_Behind_Master'}) && $result{'Seconds_Behind_Master'} >= $default_seconds_behind_limit){
                $error = "1004";
                return ("Seconds_Behind_Master=".$result{'Seconds_Behind_Master'});
        }

        if(defined($result{'Last_Errno'}) && $result{'Last_Errno'} != 0){
                $error = "1005";
                return ("Last_Errno=$result{'Last_Errno'}");
        }
        #return undef unless(%result);
        return undef;
}

sub check_master_slave_diff{
        my (@error,$data,%result_master,%result_slave,%result_diff);
        my @master_slave_table_diff;
        %result_slave = &get_table_count($S_dbh);
        sleep 1;
        %result_master = &get_table_count($M_dbh);

        #print "ok\n";
        while(my($key,$value) = each %result_slave){
                my $var = defined($result_master{$key})?$result_master{$key}-$value:0;
                #print $var;
                if($var >= $master_slave_table_diff_size){
                        #print "table :".$key."->master[".$result_master{$key}."]/slave[".$value."]/diff:$var\n";
                        push(@master_slave_table_diff,"table :".$key."->master[".$result_master{$key}."]/slave[".$value."]/diff:$var\n");
                        $result_diff{$key} = $var;
                }
                #if($var>$master_slave_table_diff_size){$result_diff{$key} = $var;}
        }

        my $master_slave_table_diff_str = join(" ",@master_slave_table_diff);
        print $master_slave_table_diff_str;
        &phpdba_log($master_slave_table_diff_str) if(@master_slave_table_diff);

        while(my($k,$v)= each %result_diff){
                push(@error,$k."\t".$v);
        }

        $data = join("\n",@error);
        &mail_send($slave_ip,"Master Slave Table Diff","All Table diff :"," $master_slave_table_diff_str") if(%result_diff);
        #&mail_send($slave_ip,"Master Slave Table Diff","All Table diff !","$data") if(%result_diff);
        #sleep 1;
        return %result_diff;
}

sub get_table_count{
        my $dbh = shift;
        my ($error,%result,$data,$count_sql);
        my $sql = "show tables";
        my $sth = $dbh->prepare($sql);
        $sth->execute();
        while(my $row=$sth->fetchrow_array){
                $count_sql = "select count(*) from ".$row." limit 1";
                my $count_sth = $S_dbh->prepare($count_sql);
                $count_sth->execute();
                my $count_row = $count_sth->fetchrow_array;
                $count_sth->finish();
                #print "row: $row";
                $result{$row} = $count_row;

        }
        #%result = %{$data} while($data=$sth->fetchrow_hashref);
        $sth->finish();
        return %result;
}

sub mail_send{
        my $subject = shift;
        my $subject_ip = shift;
        my $mail = shift;
        my $data = shift;
        my $time_now = &get_time();
        my $smtp_mail_host = 'smtp.163.com';
        my $mail_user_from = 'phpdba@163.com';
        my $mail_user = 'phpdba';
        my $work_time = time();
        my $mail_user_to;
        my ($sec,$min,$hour,$day,$mon,$year,$wday) = localtime($work_time);
        if($hour>9 && $hour<17){
                 $mail_user_to = 'chen-123@163.com';
         }else{
                 $mail_user_to = '15*********@139.com';
         }
         my $mail_user_pass = '123456';
         #my $mail_hello = 'mail.163.com';
         my $smtp = Net::SMTP->new(Host=>"$smtp_mail_host",timeout=>40, Debug=>0) or die "can not connect mail server";
                #Host=>"$smtp_mail_host",
                #Hello=>"$mail_hello",
                #timeout=>40,
                #Debug=>0) or die "can not connect mail server";
        $smtp->auth("$mail_user","$mail_user_pass") or die "auth failed!";
        #$smtp->mail("$mail_user_from","Mysql Replication Monitor");
        $smtp->mail("$mail_user_from");
        $smtp->to("$mail_user_to");
        $smtp->data();
        $smtp->datasend("Subject:warning:$app_name $subject_ip $subject\n");
        $smtp->datasend("From:$mail_user_from\n");
        $smtp->datasend("To:$mail_user_to\n");
        $smtp->datasend("\n");
        $smtp->datasend("Dear Noc:\n");
        $smtp->datasend("\t$subject $subject_ip\n");
        $smtp->datasend("\t$mail\n");
        $data?$smtp->datasend("\t$data\n\r"):$smtp->datasend("\n");
        $smtp->datasend("\t----------------\n");
        $smtp->datasend("$time_now\n\n\n");
        $smtp->dataend;
        $smtp->quit();
        print strftime("%Y-%m-%d %H:%I:%S",localtime(time))." $app_name mail send successful!\n";
        $send_email_status = 1;
}
12/26/13

perl 监控服务器负载

1、load_monitor_multiple_server.pl

#/usr/bin/perl
require "alarm.pl";#封装了日志和发邮件函数
require "server_config.pl";#服务器列表,ip:port
my $time=localtime;

my $log='./multiple_load_monitor.log';
for $server (sort keys %servers){
        my @ss;
        my $content;
        my @server_port = split ":",$servers{$server};
        if(@server_port>1){
                @ss=split " " ,`/usr/bin/ssh  root\@@server_port[0] -p @server_port[1] "cat /proc/loadavg"`;
                $content = "@server_port[0] current load is $ss[0]";
        }else{
                @ss=split " " ,`/usr/bin/ssh  root\@$servers{$server} "cat /proc/loadavg"`;
                $content = "$servers{$server} current load is $ss[0]";
        }

        if ($ss[0] < 6.5 ){
                 #print 'load is ok!';
                 phpdba_log ($time,$content,$log);
         }elsif ($ss[0]>=6.5){
                phpdba_log ($time,$content,$log);#记录日志
                phpdba_mail_load ($time,$content,$content,$ss[0]);#根据负载及上班状态等情况,判断发送短信或者邮件
        }
}
09/18/13

percona-toolkit mysql工具神器

一、Percona Toolkit 描述

Percona Toolkit 是一组高级的命令行工具,用来管理 MySQL 和系统任务,主要包括:
1、验证主节点和复制数据的一致性
2、有效的对记录行进行归档
3、找出重复的索引
4、总结 MySQL 服务器
5、从日志和 tcpdump 中分析查询
6、问题发生时收集重要的系统信息

二、源码安装

  1. tar zxf percona-toolkit-2.x.x.tar.gz
  2. cd percona-toolkit-2.1.3
  3. perl Makefile.PL
  4. make
  5. make install

三、Percona Toolkit 依赖关系基础环境

a、perl 的 DBI DBD::mysql

1、cpan模式

perl -e shell -MCPAN
cpan> install DBI
cpan> install DBD::mysql

2、源码编译模式

tar xvzf DBI-1.56.tar.gz
cd DBI-1.56
perl Makefile.PL
make
make test
make install
perl -MDBI –el (测试是否安装成功,若不报错则成功)

perl Makefile.PL \
–libs=”-L/mysql目录/lib/mysql -lmysqlclient -lz” \
–cflags=-I/mysql目录/include/mysql
make
make test
make install

b、验证模式是否安装成功

#!/bin/perl
use DBI;
# Connect to target DB
my $dbh = DBI->connect(“DBI:mysql:xxx:localhost:3306″,”root”,”123456″, {‘RaiseError’ => 1});
# query
my $sqr = $dbh->prepare(“show databases”);
$sqr->execute();
while(my $ref = $sqr->fetchrow_hashref()) {
print “$ref->{‘Database’}\n”;
}
$dbh->disconnect();

四、Percona Toolkit 基本命令

1、服务器摘要

pt-summary

2、服务器磁盘监测

pt-diskstats

3、mysql服务状态摘要

pt-mysql-summary – –user=root –password=root

4、慢查询日志分析统计

pt-query-digest /var/logs/mysql/mysql-slow.log

5、表同步工具,和mk-tables-sync功能一样, 用法上 稍有不一样 ,–print的结果更详细

pt-table-sync –execute –print –no-check-slave –database=world  h=’127.0.0.1′ \

–user=root –password=123456 h=’192.168.0.212′ –user=root –password=123456

6、主从状态监测,提供给它一台mysql服务器的IP用户名密码,就可以分析出整个主从架构中每台服务器的信息,包括但不限于mysql版本,IP地址,server ID,mysql服务的启动时间,角色(主/从),Slave Status(落后于主服务器多少秒,有没有错误,slave有没有在运行)。

  1. [root@RHCE6 ~]# pt-slave-find –host=localhost –user=root –password=123456
  2. localhost
  3. Version         5.0.30-log
  4. Server ID       1
  5. Uptime          05:16:10 (started 2013-08-08)
  6. Replication     Is not a slave, has 1 slaves connected, is not read_only
  7. Filters
  8. Binary logging  STATEMENT
  9. Slave status
  10. Slave mode      STRICT
  11. Auto-increment  increment 1, offset 1
  12. InnoDB version  1.1.8
  13. +- 192.168.0.168
  14.    Version         5.5.23-log
  15.    Server ID       10
  16.    Uptime          38:19 (started 2012-08-08T14:09:54)
  17.    Replication     Is a slave, has 0 slaves connected, is not read_only
  18.    Filters
  19.    Binary logging  STATEMENT
  20.    Slave status    0 seconds behind, running, no errors
  21.    Slave mode      STRICT
  22.    Auto-increment  increment 1, offset 1
  23.    InnoDB version  1.1.8

7、mysql死锁监测

pt-deadlock-logger h=’127.0.0.1′ –user=root –password=123456

8.主键冲突检查

pt-duplicate-key-checker \

–database=world h=’127.0.0.1′ –user=root –password=123456

9.监测从库的复制延迟   ###经过测试 运行这个命令会使从库上的sql线程异常挂掉

pt-slave-delay –host 192.168.0.206 –user=root –password=123456

09/17/13

perl自动化批量执行脚本

#!/usr/bin/perl
#use strict;
use feature ‘say’;
use Net::SSH::Perl;
use Net::SCP::Expect;
use Net::OpenSSH;
use Term::ReadKey;

alarm 60;

####服务器列表
my @server_list = (
{ ‘username’=>’root’,’host’=>’192.168.1.108′},
{ ‘username’=>’root’,’host’=>’192.168.1.109′},
);

#拷贝脚本至多台服务器,并执行该脚本,用了Net::SCP::Expect模块
sub autocopy {
my($cmd) = @_;
foreach my $element (@Ad) {
my $host = $$element{‘host’};
my $username = $$element{‘username’};
my $password = $$element{‘passwd’};

my $ssh = Net::SSH::Perl->new($host);
$ssh->login($username,$password);
my($stdout, $stderr, $exit) = $ssh->cmd($cmd);

if(!$exit) {
my $scp = Net::SCP::Expect->new(‘auto_yes’=>1);##一般第一次连接服务器的时候会出现”Are you sure you want to continue connecting (yes/no)?”,这个’auto_yes’=>1 就是默认帮我们输入了yes
$scp->login($username,$password);
eval {
$scp->scp(‘/root/bin/pubkey.sh’,”$host:/root/bin/”);
};
if($@) {
print $host.”\n”;
print $@;
}
} else {
print $host.’ cmd error’;
exit;
}

}
}

####根据用户名密码登入操作
sub autoexec {
my($cmd) = @_;
foreach my $element (@Ad) {
my $host = $$element{‘host’};
my $username = $$element{‘username’};
my $password = $$element{‘passwd’};

my $ssh = Net::SSH::Perl->new($host);
$ssh->login($username,$password);
my($stdout, $stderr, $exit) = $ssh->cmd($cmd);

if($exit) {
say $host;
}

}
}

####通过加密过的密钥登入操作
sub auto_to_identity {
my($cmd) = @_;
ReadMode(‘noecho’); ###隐藏输入的密钥密码
print “Enter passphrase for keyfile ‘/root/.ssh/id_rsa’:”;
my $passphrase = ReadLine(0);
chomp($passphrase);
ReadMode(‘restore’);
say ”;

foreach my $element (@server_list) {
my $host = $$element{‘host’};
my $username = $$element{‘username’};
my $key = ‘/root/.ssh/id_rsa’;

my %param = (
user => $username,
passphrase => $passphrase,
key_path => $key,
timeout => 10
);
my $ssh = Net::OpenSSH->new($host,%param);
my ($stdout,$stderr) = $ssh->capture2($cmd);

if($stdout){
say $stdout;
} else {
say $ssh->error;
say $stderr;
}
}

}
my $cmd = ‘mkdir /root/dd’;
auto_to_identity($cmd);