gather and send mysql statistics to be analysed

mysql_debug.sh

#!/bin/sh
user='root'
password='company'
adminmail='s.o@gmail.com'

> to_study.txt
> to_study_err.txt

mysqladmin -u$user -p$password debug
errorlog=`mysqladmin variables | grep log_error | awk '{print $4}'`

echo "##################################" >> to_study.txt
echo "error log" >> to_study.txt

tail -5000 $errorlog >> to_study.txt

echo "##################################" >> to_study.txt
echo "processlist and status" >> to_study.txt

mysql -u$user -p$password << commandlist >> to_study.txt 2>> to_study_err.txt
show engine innodb status\G
SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX\G
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS\G
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS\G
commandlist

echo "##################################" >> to_study.txt
echo "status " >> to_study.txt
mysqladmin -u$user -p$password status >> to_study.txt 2>> to_study_err.txt
mysqladmin -u$user -p$password extended-status >> to_study.txt 2>> to_study_err.txt

echo "##################################" >> to_study.txt
echo "processlist " >> to_study.txt
mysqladmin -u$user -p$password processlist --verbose >> to_study.txt 2>> to_study_err.txt

echo "##################################" >> to_study.txt
echo "variables" >> to_study.txt
mysqladmin -u$user -p$password variables >> to_study.txt 2>> to_study_err.txt 

echo "##################################" >> to_study.txt
echo "my.cn file " >> to_study.txt
cat /etc/my.cnf >> to_study.txt 2>> to_study_err.txt 

echo "##################################" >> to_study.txt
echo "crontab file " >> to_study.txt
crontab -l >> to_study.txt 2>> to_study_err.txt 

echo "##################################" >> to_study.txt
echo "slow log by count " >> to_study.txt
slowlog=`mysqladmin variables | grep slow_query_log_file | awk '{print $4}'`
tail -100000 $slowlog > slow.log
mysqldumpslow -s c slow.log | head -1000 >> to_study.txt 2>> to_study_err.txt

echo "##################################" >> to_study.txt
echo "slow log by seconds " >> to_study.txt
mysqldumpslow low.log | head -1000 >> to_study.txt 2>> to_study_err.txt

# add the following to my.cnf so that innodb_status.123 file will be created in the data directory
# this has output of show engine innodb status
# innodb-status-file=1

# enable lock monitoring by creating a table
# this will write debug info to error log
# mysql -e "create table test.innodb_lock_monitor (id int) engine=innodb"
  
# send status file as an attachment to the email address provided by the "adminmail" variable.
echo "mysql report file of `hostname` attached. " | mutt -s "mysql issue `hostname` " -a to_study.txt -- $adminmail

if [[ $? -ne 0  ]]; then echo "could not send mail. Try to send it again later"; fi