MySQL Diagnostics

mysqldiag.pl

#!/usr/bin/perl
use strict;
use warnings;


system "mkdir /var/log/sec/" unless -d "/var/log/sec";
my $date=`date -Is`;
chomp $date;
my $fn="/var/log/sec/mysql-diag_${date}_$$.log";
open LOG, ">$fn";
my $hostname=`hostname -f`;
chomp $hostname;

print LOG "# MySQL Diagnostics File
# Version 1.000  Cameron King, Aftab Khan
# 
# Generated at: $date
# On Server: $hostname
# 
##### Variables #####
";

print LOG `mysql -e 'show full processlist;
   select * from information_schema.processlist order by time desc;
   
SELECT r.trx_id waiting_trx_id,  r.trx_mysql_thread_id waiting_thread,
r.trx_query waiting_query,       b.trx_id blocking_trx_id,
b.trx_mysql_thread_id blocking_thread,       b.trx_query blocking_query   FROM
information_schema.innodb_lock_waits w   INNER JOIN
information_schema.innodb_trx b  ON  b.trx_id = w.blocking_trx_id   INNER JOIN
information_schema.innodb_trx r  ON  r.trx_id = w.requesting_trx_id; show
engine innodb status\\G

   select sleep(5);

   show full processlist;select * from information_schema.processlist order by
   time desc;

   show engine innodb status\\G

SELECT r.trx_id waiting_trx_id,  r.trx_mysql_thread_id waiting_thread,
r.trx_query waiting_query,       b.trx_id blocking_trx_id,
b.trx_mysql_thread_id blocking_thread,       b.trx_query blocking_query   FROM
information_schema.innodb_lock_waits w   INNER JOIN
information_schema.innodb_trx b  ON  b.trx_id = w.blocking_trx_id   INNER JOIN
information_schema.innodb_trx r  ON  r.trx_id = w.requesting_trx_id; show
global variables; show global status; \\q'`;

print LOG "
##### MySQL Log
";
print LOG `tail -n100 /var/log/mysql.*`;


print LOG "
##### MySQL Slow Log
";
my $slowlog=`mysql -uroot  -Nse "show variables like 'slow_query_log_file'" | awk -F' ' '{print \$2}'`;
print LOG `tail -n5000 $slowlog`;

close LOG;
print "MySQL diagnistics in: $fn\n";