安装MySQL慢查询日志工具Anemometer

PHPABC Mysql 812 次浏览 , , , , , 没有评论

首先安装LNMP环境,要求PHP-5.3以上版本.
参考:http://isadba.com/?p=82 或者参考 http://isadba.com/?p=572

然后下载Anemometer
git clone https://github.com/box/Anemometer.git anemometer

配置LNMP将下载下来的anemometer部署到LAMP上面,可以通过web打开页面,会提示你没有配置网站.
mysql -uroot -p < mysql56-install.sql #导入需要的表结构
mysql -uroot -p < install.sql #这个表结构一定要自己导进去,不然会缺少两个字段,导致不能区分主机和db.

安装percona的toolkit工具
yum install http://www.percona.com/downloads/percona-release/redhat/0.1-3/percona-release-0.1-3.noarch.rpm
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=root password= host=localhost socket=/tmp/mysql.sock 

连接到anemometer服务器数据库的账号,需要对anemometer_collect.sh脚本中history_db_name对应的数据库有all privileges权限.

 [root@localhost scripts]# cat anemometer-server.cnf [client] user=anemometer password=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/:$PATH socket= defaults_file= rate_limit= mysqlopts= interval=30 digest='/usr/bin/pt-query-digest' #set log prefix LOG_PREFIX='/usr/local/mariamysql/data/' #set slow log history file LOG_HISTORY_FILE='/var/log/slow_query_log_history_3306' long_query_time=0.1 #SET IP HOSTNAME=`/sbin/ifconfig | grep 'inet addr'  | egrep '172.|192.' | awk '{print $2}' | awk -F ":" '{print $2}'` PORT=3306 HOSTNAME="$HOSTNAME/:$PORT"  history_db_host= history_db_port=3306 history_db_name='slow_query_log' history_defaults_file=  help () {         cat <  Options:     --socket -S              The mysql socket to use     --defaults-file          The defaults file to use for the client     --interval -i            The collection duration     --rate                   Set log_slow_rate_limit (For Percona MySQL Only)      --history-db-host        Hostname of anemometer database server     --history-db-port        Port of anemometer database server     --history-db-name        Database name of anemometer database server (Default slow_query_log)     --history-defaults-file  Defaults file to pass to pt-query-digest for connecting to the remote anemometer database EOF }  while test $# -gt 0 do     case $1 in         --socket|-S)             socket=$2             shift             ;;         --defaults-file|-f)             defaults_file=$2             shift             ;;         --interval|-i)             interval=$2             shift             ;;         --rate|r)             rate=$2             shift             ;;         --pt-query-digest|-d)             digest=$2             shift             ;;         --help)             help             exit 0             ;;         --history-db-host)             history_db_host=$2             shift             ;;         --history-db-port)             history_db_port=$2             shift             ;;         --history-db-name)             history_db_name=$2             shift             ;;         --history-defaults-file)             history_defaults_file=$2             shift             ;;         *)             echo >&2 "Invalid argument: $1"             ;;     esac     shift done  if [ ! -e "${digest}" ]; then         echo "Error: cannot find digest script at: ${digest}"         exit 1 fi  if [ ! -z "${defaults_file}" ]; then         mysqlopts="--defaults-file=${defaults_file}" fi  # path to the slow query log LOG=$( mysql $mysqlopts -e " show global variables like 'slow_query_log_file'" -B  | tail -n1 | awk '{ print $2 }' ) LOG="$LOG_PREFIX$LOG" if [ $? -ne 0 ]; then         echo "Error getting slow log file location"         exit 1 fi  echo "Collecting from slow query log file: ${LOG}"  # simple 30 second collection if [ ! -z "${rate}" ]; then         mysql $mysqlopts -e "SET GLOBAL log_slow_rate_limit=${rate}" fi  mysql $mysqlopts -e "SET GLOBAL long_query_time=$long_query_time" mysql $mysqlopts -e "SET GLOBAL slow_query_log=1" if [ $? -ne 0 ]; then         echo "Error: cannot enable slow log. Aborting"         exit 1 fi echo "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 log if [[ ! -e "$LOG" ]] then         echo "No slow log to process";         exit fi mv "$LOG" /tmp/tmp_slow_log  if [ ! -z "${history_defaults_file}" ]; then         pass_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_log cat /tmp/tmp_slow_log >> $LOG_HISTORY_FILE 

发表评论

电子邮件地址不会被公开。 必填项已用*标注

此站点使用Akismet来减少垃圾评论。了解我们如何处理您的评论数据

Go