使用Anemometer分析MySQL慢查询记录
亿网数据库管理员一般是用percona的toolkit工具来分析MySQL慢查询记录，但是不够直观。
下面介绍一款比较直观的工具来统计分析MySQL慢查询记录anemometer。
在使用之前需要安装percona的toolkit工具，anemometer提供web界面。
1. 安装
| 1 2 3 | # cd /data/www/my.trustauth.cn # git clone https://github.com/box/Anemometer.git anemometer # cd anemometer |
2. 创建表和用户名
| 1 2 3 | mysql < install . sql mysql – e “grant ALL ON slow_query_log.* to ‘anemometer’@’trustauth.cn’ IDENTIFIED BY ‘my.trustauth.cn’;” mysql – e “grant SELECT ON *.* to ‘anemometer’@’trustauth.cn’ IDENTIFIED BY ‘my.trustauth.cn’;” |
3. 分析MySQL慢日志
| 1 2 3 4 | # pt-query-digest –user=anemometer –password=superSecurePass \ — review D = slow_query_log , t = global_query _review \ — review – history D = slow_query_log , t = global_query_review _history \ — no – report — limit = 0 % — filter = ” \$event->{Bytes} = length(\$event->{arg}) and \$event->{hostname}=\”$HOSTNAME\”” / data / log / mysql / slow .log |
4. 配置anemometer
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 | # cp conf/sample.config.inc.php conf/config.inc.php # vi conf/config.inc.php $conf [ ‘datasources’ ] [ ‘trustauth.cn’ ] = array ( ‘host’ = > ‘trustauth.cn’ , ‘port’ = > 3306 , ‘db’ = > ‘slow_query_log’ , ‘user’ = > ‘anemometer’ , ‘password’ = > ‘my.trustauth.cn’ , ‘tables’ = > array ( ‘global_query_review’ = > ‘fact’ , ‘global_query_review_history’ = > ‘dimension’ ) , ‘source_type’ = > ‘slow_query_log’ ) ; $conf [ ‘plugins’ ] = array ( ‘visual_explain’ = > ‘/usr/bin/pt-visual-explain’ , ‘query_advisor’ = > ‘/usr/bin/pt-query-advisor’ , #… other lines $conn [ ‘user’ ] = ‘anemometer’ ; $conn [ ‘password’ ] = ‘my.trustauth.cn’ ; return $conn ; } , |
5. 自动化处理
| 1 2 3 4 5 6 7 | # vi /etc/logrotate.d/mysql postrotate pt – query – digest — user = anemometer — password = superSecurePass \ — review D = slow_query_log , t = global_query _review \ — review – history D = slow_query_log , t = global_query_review _history \ — no – report — limit = 0 % — filter = ” \$event->{Bytes} = length(\$event->{arg}) and \$event->{hostname}=\”$HOSTNAME\”” / data / log / mysql / slow .log . 1 endscript |
文章转载来自：trustauth.cn