mysql分析日志_MySQL慢日志分析

最新推荐文章于 2024-01-04 19:10:42 发布

原创最新推荐文章于 2024-01-04 19:10:42 发布 · 182 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#mysql分析日志

本文介绍了如何使用Percona的pt-query-digest工具分析MySQL5.5的慢日志，并配置自动切割、分析及日志备份。通过创建脚本、设置定时任务，实现了每日自动分析并生成报告。

这里借助 percona的pt-query-digest工具对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. 配置自动切割mysql慢日志

2.1 复制自动切割的脚本

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的内容如下

/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读写权限

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

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

3. 配置自动分析慢日志

3.1 创建所需目录

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 修改脚本的权限

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

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

4. 创建定时任务

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

备注

在执行日志分析脚本的时候可能会出现如下错误:

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.

解决的办法就是安装确实的包

yum -y install perl-Time-HiRes

最后在对应的目录下会有慢日志分析报告生成：

2580bb2ebd97