![Anemometer]( "Anemometer")
首先安装LNMP环境,要求PHP-5.3以上版本. 参考:http://isadba.com/?p=82 或者参考http://isadba.com/?p=572 然后下载Anemometer git clonehttps://github.com/box 首先安装LNMP环境,要求PHP-5.3以上版本.参考:?p=82 或者参考 ?p=572 然后下载Anemometergit clone https://github.com/box/Anemometer.git anemometer配置LNMP将下载下来的anemometer部署到LAMP上面,可以通过web打开页面,会提示你没有配置网站.mysql -uroot -p mysql -uroot -p 安装percona的toolkit工具yum install yum install percona-toolkit -y修改配置文件cd /var/www/anemometer/conf/cp sample.config.inc.php config.inc.php修改35-40行的数据库配置,连接到anemometer的数据库,当然,你数据库也需要授权.#使用pt-query-digest工具,将一些慢查询数据导入数据库 pt-query-digest –user=anemometer –password=anemometerpass–review h=192.168.11.28,D=slow_query_log,t=global_query_review \ –history h=192.168.11.28,D=slow_query_log,t=global_query_review_history \ –no-report –limit=0% –filter=” \$event->{Bytes} = length(\$event->{arg}) and \$event->{hostname}=\”$HOSTNAME\”" \ /usr/local/mariamysql/data/localhost-slow.log执行命令以后,h=192.168.11.28,D=slow_query_log,t=global_query_review_history和global_query_review表已经已经有一些数据了,你可以尝试手工查询.现在可以再通过web访问anemometer,就能看到相关信息了.到这一步,anemometer算是跑通了,下一步就是真正的部署使用.现在的环境是,有多台MySQL服务器的slow log需要监控.我们有三种方案.1、手工导入的install.sql里面的global_query_review_history表包含了hostname_max和db_max,通过hostname_max区分把多个数据源存放到一个表里.2、每台mysql把处理后的binlog数据放到当前服务器,然后anemometer连接对应的服务器获取数据.3、所有的mysql把处理后的binlog数据统一存放到anemometer所在的数据库上,然后通过表名或者数据库名字区分.如果选择第二种或者第三种方案,需要定义多个数据源,并且anemometer_collect.sh.里面对应的history_db_name要对应.在conf/config.inc.php总修改数据源的配置,注意可以不同的数据源指向的是不同的db,一般可以按照项目区分.在conf/config.inc.php总修改数据源的配置,注意不同的数据源指向的是不同的db.$conf['datasources']['192.168.11.28'] = array('host' => '192.168.11.28','port' => 3306,'db' => 'slow_query_log','user' => 'anemometer','password' => 'anemometerpass','tables' => array('global_query_review' => 'fact','global_query_review_history' => 'dimension'),'source_type' => 'slow_query_log');$conf['datasources']['192.168.11.17'] = array('host' => '192.168.11.28','port' => 3306,'db' => 'slow_query_log_192_168_11_17','user' => 'anemometer','password' => 'anemometerpass','tables' => array('global_query_review' => 'fact','global_query_review_history' => 'dimension'),'source_type' => 'slow_query_log');在所有MySQL上部署scripts/anemometer_collect.sh收集慢查询日志.anemometer_collect.sh脚本有几个注意点1、PATH里面是否有mysql的bin路径2、如果history_db_name需要根据不同机器做修改,和config.inc.php定义的对应3、注意原始慢查询日志路径是否正确4、通过设置long_query_time设置慢查询的时长5、所有MySQL服务器上需要安装percona-toolkit的工具箱.并且在anemometer_collect.sh当前目录下增加下面两个配置文件.连接到本地MySQL服务器的账号,需要有super权限.[root@localhost scripts]# cat anemometer-localhost.cnf[client]user=rootpassword=host=localhostsocket=/tmp/mysql.sock连接到anemometer服务器数据库的账号,需要对anemometer_collect.sh脚本中history_db_name对应的数据库有all privileges权限.[root@localhost scripts]# cat anemometer-server.cnf[client]user=anemometerpassword=anemometerpass使用下面命令调试脚本.sh -x ./anemometer_collect.sh --interval 30 --history-db-host 192.168.11.17 --defaults-file ./anemometer-localhost.cnf --history-defaults-file ./anemometer-server.cnf调试通过以后,在crontab添加如下命令,实现定期采集慢查询日志到数据库存储.间隔可以根据自己的需求设置,我的设置基本上是采集所有的慢查询日志.*/1 * * * */opt/anemometer_collect.sh –interval 59 –history-db-host 192.168.11.28 –defaults-file /opt/anemometer-localhost.cnf –history-defaults-file /opt/anemometer-server.cnf由于anemometer不能跟踪SQL是哪个账号,哪个主机的请求,所以我们有必要保存原始日志,来做例外分析.下面是我修改后的anemometer_collect.sh,增加了可定制的慢查询问题,mysql path,慢查询时长变量设置,保存历史的慢查询日志.[root@localhost scripts]# cat anemometer_collect.sh#/usr/bin/env bash# anemometer collection script to gather and digest slow query logs# this is a quick draft script so please give feedback!## basic usage would be to add this to cron like this:# */5 * * * * anemometer_collect.sh --interval 15 --history-db-host anemometer-db.example.com## This will have to run as a user which has write privileges to the mysql slow log## Additionally there are two sets of permissions to worry about: The local mysql instance, and the remote digest storage instance# These are handled through defaults files, just create a file in the: my.cnf format such as:# [client]# user=# password=## use --defaults-file for permissions to the local mysql instance# and use --history-defaults-file for permissions to the remote digest storage instance##PATH=/usr/local/mysql-6/bin/:$PATHsocket= defaults_file= rate_limit= mysqlopts=interval=30digest='/usr/bin/pt-query-digest'#set log prefixLOG_PREFIX='/usr/local/mariamysql/data/'#set slow log history fileLOG_HISTORY_FILE='/var/log/slow_query_log_history_3306'long_query_time=0.1#SET IPHOSTNAME=`/sbin/ifconfig | grep 'inet addr' | egrep '172.|192.' | awk '{print $2}' | awk -F ":" '{print $2}'`PORT=3306HOSTNAME="$HOSTNAME\:$PORT"history_db_host=history_db_port=3306history_db_name='slow_query_log'history_defaults_file=help () {cat &2 "Invalid argument: $1";;esacshiftdoneif [ ! -e "${digest}" ];thenecho "Error: cannot find digest script at: ${digest}"exit 1fiif [ ! -z "${defaults_file}" ];thenmysqlopts="--defaults-file=${defaults_file}"fi# path to the slow query logLOG=$( mysql $mysqlopts -e " show global variables like 'slow_query_log_file'" -B | tail -n1 | awk '{ print $2 }' )LOG="$LOG_PREFIX$LOG"if [ $? -ne 0 ];thenecho "Error getting slow log file location"exit 1fiecho "Collecting from slow query log file: ${LOG}"# simple 30 second collectionif [ ! -z "${rate}" ];thenmysql $mysqlopts -e "SET GLOBAL log_slow_rate_limit=${rate}"fimysql $mysqlopts -e "SET GLOBAL long_query_time=$long_query_time"mysql $mysqlopts -e "SET GLOBAL slow_query_log=1"if [ $? -ne 0 ];thenecho "Error: cannot enable slow log. Aborting"exit 1fiecho "Slow log enabled; sleeping for ${interval} seconds"sleep "${interval}"mysql $mysqlopts -e "SET GLOBAL slow_query_log=0"echo "Done. Processing log and saving to ${history_db_host}:${history_db_port}/${history_db_name}"# process the logif [[ ! -e "$LOG" ]]thenecho "No slow log to process";exitfimv "$LOG" /tmp/tmp_slow_logif [ ! -z "${history_defaults_file}" ];thenpass_opt="--defaults-file=${history_defaults_file}"fi"${digest}" $pass_opt \ --review h="${history_db_host}",D="$history_db_name",t=global_query_review \ --history h="${history_db_host}",D="$history_db_name",t=global_query_review_history \ --no-report --limit=0\% \ --filter="\$event->{Bytes} = length(\$event->{arg}) and \$event->{hostname}=\"$HOSTNAME\" " \ "/tmp/tmp_slow_log"#store history slow_logcat /tmp/tmp_slow_log >> $LOG_HISTORY_FILE， 09-15 15:37