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/27/13

python 参数类型

1)必须的参数
2)可选的参数
3)过量的位置参数
4)过量的关键字参数

def complex_function(a, b=None, *c, **d):

1、*args和**kwargs只接受那些没有放进来的其他任何参数。
2、没有这个次序,当你调用一个带有位置参数的函数,python就不知道哪个值是已声明参数想要的,也不知道哪个被作为过量参数对待。
3、函数能接受许多必须的参数和可选的参数,那它只要定义一个过量的参数类型即可。
4、传递一个元组作为过量的位置参数时,是否要显式的传递关键字参数。因为python使用次序规则来扩展过量的参数,那位置参数要放在前面。

>>> def add(a, b, c):
… return a + b + c

>>> add(1, 2, 3)
6
>>> add(a=4, b=5, c=6)
15
>>> args = (2, 3)
>>> add(1, *args)
6
>>> kwargs={‘b’: 8, ‘c’: 9}
>>> add(a=7, **kwargs)
24
>>> add(a=7, *args)
Traceback (most recent call last):
File ““, line 1, in
TypeError: add() got multiple values for keyword argument ‘a’
>>> add(1, 2, a=7)
Traceback (most recent call last):
File ““, line 1, in
TypeError: add() got multiple values for keyword argument ‘a’

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]);#根据负载及上班状态等情况,判断发送短信或者邮件
        }
}
12/19/13

saltstack同步各client重要配置和脚本到master

1、shell backup_conf_com_sh.sh脚本

#!/bin/sh
for i in `find /opt/phpdba/ -maxdepth 4 -type f -regex ".*\.\(com\|conf\|cnf\|sh\)" -print\
               |grep -E 'lighttpd\.conf|httpd\.conf|nginx\.conf|squid\.conf|*\.com|my\.cnf|*\.sh'`;do
        #tmp=`basename $i`
        tmp=`echo "$i"|sed s#/#_#g`
        salt-call cp.push $i
        sleep 1
done

2、执行命令
salt ‘*’ cmd.script salt://script/backup_conf_com_sh.sh

12/19/13

mediumint(8)超范围问题

function database_alter_table($source,$target){
                $source_table_list = $this->show_tables($source['database'],$this->source_connect);
                foreach($source_table_list as $table){
                        $fields = mysql_list_fields($source['database'],$table,$this->source_connect);
                        $fileds_num = mysql_num_fields($fields);

                        #echo "table: ".$table." fields_num:".$fileds_num."\n";
                        for($i=0;$i<$fileds_num;$i++){
                                $type  = mysql_field_type($fields,$i);
                                $name  = mysql_field_name($fields,$i);
                                $len   = mysql_field_len($fields, $i);
                                $flags = mysql_field_flags($fields,$i);

                                if(strtolower($type) == "int" && $len<=8 && $len>1){
                                        echo $type." ".$name." ".$len." ".$flags."\n";
                                        if(preg_match("/auto_increment/i",strtolower($flags))){
                                                $alter_sql = "ALTER TABLE `$table` CHANGE `$name` `$name` INT( 11 ) NOT NULL AUTO_INCREMENT";
                                        }else{
                                                if(!preg_match("/unsigned/i",strtolower($flags)) \
                                                   && !preg_match("/auto_increment/i",strtolower($flags))){
                                                    $alter_sql = "ALTER TABLE `$table` CHANGE `$name` `$name` INT( 11 ) NOT NULL";
                                                }else{
                                                    $alter_sql = "ALTER TABLE `$table` CHANGE `$name` `$name` INT( 11 ) UNSIGNED DEFAULT '0'";
                                                }                                                
                                        }
                                        echo $alter_sql."\n";
                                        $result = $this->query($alter_sql,$this->source_connect);
                                }
                        }
                }
        }
12/18/13

shell ASCII text转码

1、现象:

cat 可以正常显示,vi却乱码

2、

[root@yf_113 ~]# file phpdba.txt
phpdba.txt: ASCII text, with escape sequences

3、转码shell脚本

#!/bin/bash
CSI=’\x1b\[‘            # the two-character csi escape sequence .Esc [., followed by i
Rp=’[0-9:;<=>?]‘        # an optional sequence of parameter bytes in the range x30.x3F,
Ri=’[- !\"#$%&'\''()*+,./]‘ # an optional sequence of intermediate bytes in the range x20.x2F,
Rf=’[]@A-Z[\\^_`a-z{|}~]‘   # and a final byte in the range x40.x7e.
# The set of standard control sequence functions are defined in Ecma-48 / ISO/IEC 6429.
cat phpdba.txt | sed -e ‘s/’”$CSI$Rp”‘*’”$Ri”‘*’”$Rf”‘//g’ > phpdbautf8.txt

utf8格式转换成功

12/12/13

shell 分析iosapi接口调用情况

一、代码展示
#!/bin/bash
INIT_PATH="/opt/phpdba/apache/logs"
FILEGREP='1208'
Date='08/Dec/2013'
hour=`cat hour.txt`

for a in $hour
do
i=($(echo $a|awk -F'/' '{print $1}'))
e=($(echo $a|awk -F'/' '{print $2}'))

cat $INIT_PATH/www.phpdba.com-access.$FILEGREP |grep $Date$i|grep 'ios/api'|awk -F'"'  '{print $2" "$3" "$1}'>>phpdba/${e}_iosapi.log
cat $INIT_PATH/www.phpdba.com-access.$FILEGREP |grep $Date$i|grep 'ios/api'|awk -F'"'  '{print $6}'>>phpdba/${e}_iosapi_refer2.log
cat phpdba/${e}_iosapi_refer2.log |sort -rn|uniq -c|sort -rn>>phpdba/${e}_iosapi_refer_sort.log
cat phpdba/${e}_iosapi.log |awk '{print $1" "$2" "$(NF-6)}'>>phpdba/${e}_iosapi_good.log
cat phpdba/${e}_iosapi_good.log |awk '{print $3" "$1" "$2}'>>phpdba/${e}_iosapi_good_new.log
cat phpdba/${e}_iosapi_good_new.log|awk -F'?' '{print $1}'>>phpdba/${e}_iosapi_good_ok.log
cat phpdba/${e}_iosapi_good_ok.log|sort -rn|uniq -c |sort -rn>>phpdba/${e}_iosapi_good_ok_sortip.log
cat phpdba/${e}_iosapi_good_ok.log|awk '{print $3}'|sort -rn|uniq -c |sort -rn>>phpdba/${e}_iosapi_good_ok_urlsort.log
cat phpdba/${e}_iosapi_good_ok.log|awk '{print $2" "$3}'|sort -rn|uniq -c |sort -rn>>phpdba/${e}_iosapi_good_ok_getpost.log
num=`cat phpdba/${e}_iosapi_good_ok.log|wc -l`
echo "$e => $num">>phpdba/time_total.txt
cat phpdba/${e}_iosapi_good_ok_getpost.log>>phpdba/iosapi_good_ok_getpost_total.log
cat phpdba/${e}_iosapi_good_ok_sortip.log>>phpdba/iosapi_good_ok_sortip_total.log
cat phpdba/${e}_iosapi_good_ok_urlsort.log>>phpdba/iosapi_good_ok_urlsort_total.log

done
cat phpdba/iosapi_good_ok_getpost_total.log |awk '{arr[$2" "$3]+=$1}END{for(s in arr) print arr[s],s}'|sort -rn>>phpdba/iosapi_good_ok_getpost_total_ok.txt
cat phpdba/iosapi_good_ok_sortip_total.log |awk '{arr[$2]+=$1}END{for(s in arr) print arr[s],s}'|sort -rn>>phpdba/iosapi_good_ok_sortip_total_ok.txt
cat phpdba/iosapi_good_ok_sortip_total.log |awk '{arr[$3" "$4]+=$1}END{for(s in arr) print arr[s],s}'|sort -rn>>phpdba/iosapi_good_ok_sortip_total_ok2.txt
cat phpdba/iosapi_good_ok_sortip_total.log |awk '{arr[$4]+=$1}END{for(s in arr) print arr[s],s}'|sort -rn>>phpdba/iosapi_good_ok_sortip_total_ok3.txt
cat phpdba/iosapi_good_ok_urlsort_total.log |awk '{arr[$2]+=$1}END{for(s in arr) print arr[s],s}'|sort -rn>>phpdba/iosapi_good_ok_urlsort_total_ok.txt 

二、代码下载
phpdba.tar
12/12/13

php kill掉超过max_sleep_time秒select like 的sql线程

<?php
define('MAX_SLEEP_TIME', 30);

$hostname = "127.0.0.1";
$username = "monitor";
$password = "123456";

$connect = mysql_connect($hostname, $username, $password) 
              or die(mysql_error());
$result = mysql_query("SHOW FULL PROCESSLIST", $connect)
              or die(mysql_error());
echo "\n开始时间:".date("Y-m-d H:i:s")."\n";
while ($proc = mysql_fetch_assoc($result)) {
        if ($proc["Command"] == "Sleep" && \
                  $proc["Time"] > MAX_SLEEP_TIME) {
                if($proc['State'] == 'Locked' || \
                 preg_match("/(.*)select(.*)like(.*)/i",$proc['Info'])){               
                        var_dump($proc);
                        @mysql_query("KILL " . $proc["Id"], $connect);
                }
        }
}
mysql_close($connect);
echo "\n结束时间:".date("Y-m-d H:i:s")."\n";
?>