anemometer慢日志工具介绍使用

1、Anemometer慢日志图形化工具介绍Anemometer基于pt-query-digest，将MySQL慢查询可视化。安装-安装：cd /u02/git clone git:/ anemometercd anemometer/  由于表定义中存在0000-00-00 00:00:00 的日期默认值，需要修改sql_mode，将其zero_date的sql_mode 关闭，同时关闭only_full_group_bymysql -h14 -uroot -p123456 -P3306 < install.sql mysql -h192.168.0

2、.114 -uroot -p123456 -P3306 < install.sql -e "grant ALL ON slow_query_log.* to 'anemometer''%' IDENTIFIED BY '123456'" grant select ,super on *.* to 'anemometer''%' pt-query-digest -user=anemometer -password=123456 -port=3306 -review h=1

3、14,D=slow_query_log,t=global_query_review -history h=14,D=slow_query_log,t=global_query_review_history -no-report -limit=0% -filter=" $event->Bytes = length($event->arg) and $event->hostname="$HOSTNAME"" /u02/slow_queries.log  cd /u02/anemo

4、meter/conf/cp sample.config.inc.php config.inc.php sed -i '285s#root#anemometer#g' config.inc.phpsed -i "286s#''#'123456'#g" config.inc.php cp datasource_localhost.inc.php datasource_localhost.inc.phpbakvi datasource_localhost.inc.phpmv anemometer/ /var/ww

5、w/html/登陆14/anemometer使用汇总按照时间区间查询查看执行计划自动化抓取+切割慢日志-授权用户CREATE USER 'log_rotate''localhost' IDENTIFIED BY '123456' GRANT RELOAD,SUPER ON *.* to 'log_rotate''localhost' FLUSH PRIVILEGES; -自动抓取/收集脚本rootmaster scripts# pwd/var/www/html/anemo

6、meter/scriptsrootmaster scripts# cat slow_db_export.sh #!/bin/bashpt-query-digest -user=anemometer -password=123456 -port=3306 -review h=14,D=slow_query_log,t=global_query_review -history h=14,D=slow_query_log,t=global_query_review_history -no-report -limit=0% -filter=" $e

7、vent->Bytes = length($event->arg) and $event->hostname="$HOSTNAME"" /u02/slow_queries.log -给权限rootmaster scripts# chmod -R 755 slow_db_export.sh 切割每日的慢查询日志，可以使用下面的logratate，也可以用类似archery工具的pt-query-digest 的-since功能-安装logrotaterootmaster scripts# yum install logrotat

8、e -y -配置每日切割慢查询日志rootmaster scripts# vi /etc/logrotate.d/mysql/u02/slow_queries.log create 660 mysql mysqldateextnotifemptydailymaxage 60rotate 30missingokcompressolddir /u02/oldlogpostrotate#run if mysqld is runningif mysqladmin ping -ulog_rotate -p'123456' -S /u01/data/mysql/data/mysq

9、l.sock &>/dev/null; then mysqladmin flush-logs -ulog_rotate -p'123456' -S /u01/data/mysql/data/mysql.sock fiendscript -给权限rootmaster scripts# chmod -R 755 /etc/logrotate.d/mysql -验证 预演方式运行：rootmaster scripts# /usr/sbin/logrotate -d -v /etc/logrotate.d/mysqlreading

10、config file /etc/logrotate.d/mysqlreading config info for /u02/slow_queries.log error: /etc/logrotate.d/mysql:6 argument expected after maxage counterror: /etc/logrotate.d/mysql:7 argument expected after rotate countolddir is now /u02/oldlog Handling 1 logs rotating pattern: /u02/slow_quer

11、ies.log after 1 days (no old logs will be kept)olddir is /u02/oldlog, empty log files are not rotated, old logs are removedconsidering log /u02/slow_queries.log log does not need rotatingnot running postrotate script, since no logs were rotated  强制方式运行rootmaster scripts# /usr/sbin/logrotat

12、e -fv /etc/logrotate.d/mysqlreading config file /etc/logrotate.d/mysqlreading config info for /u02/slow_queries.log olddir is now /u02/oldlog Handling 1 logs rotating pattern: /u02/slow_queries.log forced from command line (30 rotations)olddir is /u02/oldlog, empty log files are not rotate

13、d, old logs are removedconsidering log /u02/slow_queries.log log needs rotatingrotating log /u02/slow_queries.log, log->rotateCount is 30dateext suffix '-20201020'glob pattern '-0-90-90-90-90-90-90-90-9'glob finding old rotated logs failedrenaming /u02/slow_queries.log to /u02/old

14、log/slow_queries.log-20201020creating new /u02/slow_queries.log mode = 0660 uid = 3306 gid = 3306running postrotate scriptmysqladmin: Warning World-writable config file '/usr/local/mysql/etc/f' is ignored.mysqladmin: Warning Using a password on the command line interface can be pressing log with: /bin/gzip  查看： rootmaster oldlog# pwd/u02/oldlogrootmaster oldlog# ll -trhtotal 3.3M-rw-rw- 1 mysql mysql 3.3M Oct 20 14:20 slow_queries.log-20201020.gz rootmaster u02# ll -trh slow_queries.log -rw-r