這里借助 percona的pt-query-digest工具對(duì)mysql的慢日志做分析,這里使用mysql5.5做例子篙贸。

1. 下載分析工具

cd /usr/local/src
wget percona.com/get/pt-query-digest
cp /usr/local/src/pt-query-digest /usr/bin
chmod +x /usr/bin/pt-query-digest

2. 配置自動(dòng)切割mysql慢日志

2.1 復(fù)制自動(dòng)切割的腳本

cp /usr/local/mysql/support-files/mysql-log-rotate /etc/logrotate.d/
ls -al /etc/logrotate.d/mysql-log-rotate

2.2 編輯mysql-log-rotate的內(nèi)容如下

/data/mysqldata/data28002/mysql_slow.log {
    compress
    create 666 mysql mysql
    dateext
    missingok
    notifempty
    sharedscripts
    postrotate
       /usr/local/mysql/bin/mysql -uroot -pROOTPASSWD -S /tmp/mysql28002.sock -e 'select @@global.long_query_time into @lqt_save; set global long_query_time=2000; select sleep(2); FLUSH LOGS; select sleep(2); set global long_query_time=@lqt_save;'
    endscript
    rotate 150
}

2.3 限制ROOT讀寫權(quán)限

chmod 700 /etc/logrotate.d/mysql-log-rotate
ls -al /etc/logrotate.d/mysql-log-rotate

3. 配置自動(dòng)分析慢日志

3.1 創(chuàng)建所需目錄

mkdir -p  /data/soft/cron/mysqlslowlog/reports
mkdir -p  /data/soft/cron/mysqlslowlog/backup

3.2 新建日志分析腳本

vim /data/soft/cron/mysqlslowlog/analyze-slow-log.sh

#!/bin/sh

DD=`date -d today +"%Y%m%d"`

zcat /data/mysqldata/data28002/mysql_slow.log-$DD.gz | /usr/bin/pt-query-digest --order-by Query_time:max --report-all --limit 100%:50 > /data/soft/cron/mysqlslowlog/reports/28002report-$DD.log

chmod 644 /data/soft/cron/mysqlslowlog/reports/28002report-$DD.log
mv /data/mysqldata/data28002/mysql_slow.log-$DD.gz /data/soft/cron/mysqlslowlog/backup/
chown -R appadmin:appadmin /data/soft/cron/mysqlslowlog/backup/*
chown -R appadmin:appadmin /data/soft/cron/mysqlslowlog/reports/*

3.3 修改腳本的權(quán)限

chmod 700  /data/soft/cron/mysqlslowlog/analyze-slow-log.sh
ls -al /data/soft/cron/mysqlslowlog/analyze-slow-log.sh

4. 創(chuàng)建定時(shí)任務(wù)

crontal -e

#slice mysql-slow-log
50 4 * * * /usr/sbin/logrotate -f /etc/logrotate.d/mysql-log-rotate
#mysql-slow-log analyze
50 5 * * * /bin/sh /data/soft/cron/mysqlslowlog/analyze-slow-log.sh

備注
在執(zhí)行日志分析腳本的時(shí)候可能會(huì)出現(xiàn)如下錯(cuò)誤:

Can't locate Time/HiRes.pm in @INC (@INC contains: /usr/local/lib64/perl5 /usr/local/share/perl5 /usr/lib64/perl5/vendor_perl /usr/share/perl5/vendor_perl /usr/lib64/perl5 /usr/share/perl5 .) at /usr/bin/pt-query-digest line 3225.
BEGIN failed--compilation aborted at /usr/bin/pt-query-digest line 3225.

解決的辦法就是安裝確實(shí)的包

yum -y  install perl-Time-HiRes

最后在對(duì)應(yīng)的目錄下會(huì)有慢日志分析報(bào)告生成：

日志分析報(bào)告