Activities

November 2012
M T W T F S S
« Oct   Dec »
 1234
567891011
12131415161718
19202122232425
2627282930  

MySQL Slow query log : Automated email alert system

One the client wish to track down the all the issues which affect the performance of busy MySQL database server including slow log query. The practical issue i,

a. slow query log file will be grown up more than one GB in a week time.
b. Dev. team does not have access this DB server.
c. There will be a practical difficulties to share and operate the slow query log file which is above 400MB in size.
d. Need a MySQL restart to continue logging once the file had been emptied by hand.

So we need to place an automated mechanism to send these files to dev. team to automatically in daily basis. Stackoverflow site lead me to have two options either use FILE to log or use a table to store. I prefer FILE to store the log as it ‘s less CPU intensive process.

1. I’ve added following entries in “/etc/my.cnf” file.

long_query_time         = 1
log-slow-queries        = /sqldata/mysql_slow/mysql-slow.log
log-output=FILE

2. Now we need to execute the “mysql_upgrade” command to ensure that all the tables are properly formatted with the current version. hotcopy backup restoration from Mysql 5.1 to MySQL 5.5 will have issue. This command will create proper new tables in “mysql” database to felicitate the feature support of version 5.5 does. Otherwise this will not work and you get error “SET GLOBAL slow_query_log = ‘ON” on this statement.

3. Enable Mysql Password less authentication for current user.

[root@sql04]# cat ~/.my.cnf
[client]
user="root"
pass="pass"

4. Download the sendEmail program from aspian.dotconf.net/menu/Software/SendEmail/

[root@sql04]#cd /home/installation
[root@sql04]#wget http://caspian.dotconf.net/menu/Software/SendEmail/sendEmail-v1.56.tar.gz
[root@sql04]#tar -zxvf /home/installation
[root@sql04 sendEmail-v1.56]# pwd
/home/installation/sendEmail-v1.56
[root@sql04 sendEmail-v1.56]# cp sendEmail /usr/bin/
[root@sql04 sendEmail-v1.56]#touch /var/log/sendEmail

5. Here is the script to send email notification.

[root@sql04]#cat /home/installation/scripts/mon_slow_query.sh
SLOW_LOG_FILE="/sqldata/mysql_slow/mysql-slow.log"
file_size=$(du -sh $SLOW_LOG_FILE | awk '{print $1}')
default_size="4.0K"
if [[ $file_size != $default_size ]]; then
echo "$MAIL_BODY has data.and mail sending"
mysql -e ' SET GLOBAL slow_query_log = 'OFF';'
cd /sqldata/mysql_slow
## pt-query-digest is a command line report utility would be installed from Percona toolkit (http://www.percona.com/downloads/percona-toolkit/LATEST/)
/usr/local/bin/pt-query-digest  mysql-slow.log  >> mysql_slow_log_report.txt
zip /sqldata/temp/db04_slow_log_query_$(date +%Y-%m-%d).zip  mysql_slow_log_report.txt
echo -n > $SLOW_LOG_FILE
sendEmail -f admins@mydomain.com  -t dba1@mydomain.com, dba2@mydomain.com -cc support@mydomain.com  -u "Slow query log report on MySQL server" $(date +%d-%m-%Y)  -l /var/log/sendEmail  -o message-content-type=auto  -a /tmp/mysql_slow_log_query_$(date +%Y-%m-%d).zip  -m "PFA, contains the daily slow query log report of MySQLserver."      -s smtp.mydoomain.com:25 -xu mailuser@mydomain.com  -xp mypassword

mysql -e 'SET GLOBAL slow_query_log = 'ON';'
echo -n > mysql_slow_log_report.txt
else
touch /tmp/asd.log
fi ;

6. Set a cronjob to get daily alert

[root@sql04] crontab -l
00 21 * * * /bin/sh /home/installation/scripts/mon_slow_query.sh   >/dev/null 2>&1

Leave a Reply

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>