#!/usr/bin/perl

[0] Fix | Delete

use Time::Local

[1] Fix | Delete

[2] Fix | Delete

#this is a cleaner rewrite of parse-mysql attempting to encapsulate all sections.

[3] Fix | Delete

$maxrunload=0;

[4] Fix | Delete

$myqps=0;

[5] Fix | Delete

$maxrunload=&maxRunLoad();

[6] Fix | Delete

$myqps=&curQPS();

[7] Fix | Delete

$user_qps_limit=20;

[8] Fix | Delete

$mysql_qps_baseline=125;

[9] Fix | Delete

$curload=&getLoad();

[10] Fix | Delete

$logdate=0;

[11] Fix | Delete

#Check qps

[12] Fix | Delete

if ($myqps > $mysql_qps_baseline){

[13] Fix | Delete

# print "mysql - qps greater than threshold... continuing\n";

[14] Fix | Delete

#Check load

[15] Fix | Delete

if ($maxrunload > $curload){

[16] Fix | Delete

# print "Load is low enough... continuing\n";

[17] Fix | Delete

#Check for parse-file

[18] Fix | Delete

if (-e '/var/log/mysqld.log.parse'){

[19] Fix | Delete

# print "$curload\n";

[20] Fix | Delete

&tempName();

[21] Fix | Delete

`rm -f /var/log/mysqld.log.parse`;

[22] Fix | Delete

}

[23] Fix | Delete

}

[24] Fix | Delete

}

[25] Fix | Delete

[26] Fix | Delete

#################################################

[27] Fix | Delete

### vvv All sub-routines live here vvv ###

[28] Fix | Delete

#################################################

[29] Fix | Delete

[30] Fix | Delete

sub getLoad{

[31] Fix | Delete

@load=`cat /proc/loadavg`;

[32] Fix | Delete

foreach(@load){

[33] Fix | Delete

if(/(\d+)/){

[34] Fix | Delete

return($1);

[35] Fix | Delete

}

[36] Fix | Delete

}

[37] Fix | Delete

}

[38] Fix | Delete

sub maxRunLoad{

[39] Fix | Delete

#check cpuinfo, count #of processors, set number of cpus to execution point.

[40] Fix | Delete

@cpuinfo=`cat /proc/cpuinfo`;

[41] Fix | Delete

$cpucount=0;

[42] Fix | Delete

foreach(@cpuinfo){

[43] Fix | Delete

if(/processor\t:/){

[44] Fix | Delete

$cpucount+=1;

[45] Fix | Delete

}

[46] Fix | Delete

}

[47] Fix | Delete

$cpucount*1.5;

[48] Fix | Delete

}

[49] Fix | Delete

[50] Fix | Delete

[51] Fix | Delete

sub curQPS{

[52] Fix | Delete

#call mysqladmin status, parse out QPS.

[53] Fix | Delete

@output_in_array=`mysqladmin status`;

[54] Fix | Delete

foreach(@output_in_array){

[55] Fix | Delete

if( /avg: (\d+)/){

[56] Fix | Delete

$sysqps=$1;

[57] Fix | Delete

last;

[58] Fix | Delete

}

[59] Fix | Delete

}

[60] Fix | Delete

$sysqps;

[61] Fix | Delete

}

[62] Fix | Delete

[63] Fix | Delete

[64] Fix | Delete

sub sendEmail{

[65] Fix | Delete

#encapsulated mail function - does not take shit.

[66] Fix | Delete

#$hostname = `echo \$HOSTNAME`;

[67] Fix | Delete

chomp($hostname=`echo \$HOSTNAME`);

[68] Fix | Delete

my $reply_to = "Reply-to: machinemessages\@forum.inmotionhosting.com\n";

[69] Fix | Delete

my $subject = "Subject: MySQL Abuse: $hostname, $uname, $uqps QPS. \n";

[70] Fix | Delete

my $content = "$uname needs to be contacted and or moved for high mysql usage(QPS) - please do not ignore this message. \n";

[71] Fix | Delete

my $send_to = "To: sadmin\@imhadmin.net\n";

[72] Fix | Delete

my $sendmail = "/usr/sbin/sendmail -t";

[73] Fix | Delete

[74] Fix | Delete

open(SENDMAIL, "|$sendmail") or die "Cannot open $sendmail: $! \n";

[75] Fix | Delete

print SENDMAIL $reply_to;

[76] Fix | Delete

print SENDMAIL $subject;

[77] Fix | Delete

print SENDMAIL $send_to;

[78] Fix | Delete

print SENDMAIL "Content-type: text/plain\n\n";

[79] Fix | Delete

print SENDMAIL $content;

[80] Fix | Delete

close(SENDMAIL);

[81] Fix | Delete

}

[82] Fix | Delete

[83] Fix | Delete

sub logData(){

[84] Fix | Delete

#write data to /var/log/abused.log

[85] Fix | Delete

`echo "$logdate - $uname: $uqps QPS" >> /var/log/mysqld.log.history`;

[86] Fix | Delete

}

[87] Fix | Delete

[88] Fix | Delete

sub tempName{

[89] Fix | Delete

### Log Processor: calculates howmany seconds long log is, uses duration to calculate QPS for usrs using /opt/sharedrads/mysql/parse-general

[90] Fix | Delete

#Open mysql log.

[91] Fix | Delete

open MYFILE, "/var/log/mysqld.log.1" or die;

[92] Fix | Delete

#read file in line by line - < expecting large log files > (I DONT WANT TO READ 1+gig in to memory)

[93] Fix | Delete

while (<MYFILE>) {

[94] Fix | Delete

$count += 1;

[95] Fix | Delete

if ( /(\d\d)([0-1]\d)([0-3]\d)\s([\s\d]\d):(\d\d):(\d\d)/ ) {

[96] Fix | Delete

$first_date = timelocal($6,$5,$4,$3,$2,$1);

[97] Fix | Delete

last;

[98] Fix | Delete

}

[99] Fix | Delete

}

[100] Fix | Delete

[101] Fix | Delete

# read the end of the file to save time in calculating.

[102] Fix | Delete

@endoffile=`tail -n 4000 /var/log/mysqld.log.1`;

[103] Fix | Delete

foreach(@endoffile){

[104] Fix | Delete

if ( /(\d\d)([0-1]\d)([0-3]\d)\s([\s\d]\d):(\d\d):(\d\d)/ ) {

[105] Fix | Delete

$last_date= timelocal($6,$5,$4,$3,$2,$1);

[106] Fix | Delete

$logdate="$1/$2/$3 $4:$5:$6";

[107] Fix | Delete

}

[108] Fix | Delete

}

[109] Fix | Delete

[110] Fix | Delete

$diff = $last_date - $first_date;

[111] Fix | Delete

[112] Fix | Delete

#parse data!!! Call Brandons script, housekeeping may needed.

[113] Fix | Delete

`/opt/sharedrads/mysql/parse-genqlog -i /var/log/mysqld.log.1 -o /var/log/mysqld.out.tmp`;

[114] Fix | Delete

[115] Fix | Delete

#read in data just created.

[116] Fix | Delete

@parse_general_output=`head -n 15 /var/log/mysqld.out.tmp`;

[117] Fix | Delete

foreach(@parse_general_output){

[118] Fix | Delete

if ( /(\w+)\s::\s(\d+)/ ){

[119] Fix | Delete

$uqps= $2/$diff; $uname=$1; $qnum=$2;

[120] Fix | Delete

# print "$uname, $qnum, $uqps\n";

[121] Fix | Delete

if ( $uqps > $user_qps_limit ){

[122] Fix | Delete

&logData();

[123] Fix | Delete

#&sendEmail();

[124] Fix | Delete

}

[125] Fix | Delete

}

[126] Fix | Delete

}

[127] Fix | Delete

}

[128] Fix | Delete

[129] Fix | Delete