MySQL数据库监控软件lepus使用问题以及解决办法

391次阅读

没有评论

共计 9562 个字符，预计需要花费 24 分钟才能阅读完成。

在使用 lepus3.7 监控 MySQL 数据库的时候，碰到了以下几个问题，本博客给出了这些问题产生的原因，以及相应的解决办法。

1. 问题 1：php 页面无法连接数据库

直接使用 php 程序执行 php 文件，可以连接 mysql，但是在 httpd 中同样的 php 页面无法连接 mysql。

lepus 的 web 程序 (PHP 代码) 无法连接数据库时，web 界面上什么操作也无法继续。

为此编写了最简单的 PDO 连接测试代码：

php 代码如下：

[linuxidc@linuxidc lepus]$ cat mysql.php

<?php

try{

#$dsn=”mysql:host=127.0.0.1;dbname=lepus;”;

$dsn=”mysql:host=11.1.1.11;dbname=lepus;”;

$user=”coe2coe”;

$pwd=”XXXXXXXXXX”;

$sql=”select now() as a”;

$dbh=new PDO($dsn,$user,$pwd);

$stmt=$dbh->prepare($sql);

$stmt->execute();

$row=$stmt->fetch(PDO::FETCH_ASSOC);

echo “result:”.$row[‘a’];

}

catch(PDOException $e) {

echo “FAILED:”.$e->getMessage();

}

?>

php 程序直接执行 php 文件：

[linuxidc@linuxidc lepus]$ php mysql.php

result:2018-09-27 00:03:44

通过浏览器访问这个页面：

FAILED:SQLSTATE[HY000] [2003] Can’t connect to MySQL server on ‘11.1.1.11’ (13)

lepus 的 web 程序给出的错误提示信息更加模糊。

原因：

通过一番 baidu 之后，终于看到了一个比较靠谱的分析。

Linux(CentOS7)的 selinux 安全机制禁止了 httpd 中的模块访问网络。

[linuxidc@linuxidc lepus]$ sudo getsebool -a |grep httpd

httpd_anon_write –> off

httpd_builtin_scripting –> on

httpd_can_check_spam –> off

httpd_can_connect_ftp –> off

httpd_can_connect_ldap –> off

httpd_can_connect_mythtv –> off

httpd_can_connect_zabbix –> off

httpd_can_network_connect –> off

httpd_can_network_connect_cobbler –> off

httpd_can_network_connect_db –> off

httpd_can_network_memcache –> off

httpd_can_network_relay –> off

httpd_can_sendmail –> off

httpd_dbus_avahi –> off

httpd_dbus_sssd –> off

httpd_dontaudit_search_dirs –> off

httpd_enable_cgi –> on

httpd_enable_ftp_server –> off

httpd_enable_homedirs –> off

httpd_execmem –> off

httpd_graceful_shutdown –> on

httpd_manage_ipa –> off

httpd_mod_auth_ntlm_winbind –> off

httpd_mod_auth_pam –> off

httpd_read_user_content –> off

httpd_run_ipa –> off

httpd_run_preupgrade –> off

httpd_run_stickshift –> off

httpd_serve_cobbler_files –> off

httpd_setrlimit –> off

httpd_ssi_exec –> off

httpd_sys_script_anon_write –> off

httpd_tmp_exec –> off

httpd_tty_comm –> off

httpd_unified –> off

httpd_use_cifs –> off

httpd_use_fusefs –> off

httpd_use_gpg –> off

httpd_use_nfs –> off

httpd_use_openstack –> off

httpd_use_sasl –> off

httpd_verify_dns –> off

解决办法：

临时办法：临时禁用 SELINUX。

[linuxidc@linuxidc lepus]$ sudo setenforce 0

永久办法：修改 selinux 配置文件，禁用 SELINUX。

[linuxidc@linuxidc lepus]$ cat /etc/selinux/config

# This file controls the state of SELinux on the system.

# SELINUX= can take one of these three values:

# enforcing – SELinux security policy is enforced.

# permissive – SELinux prints warnings instead of enforcing.

# disabled – No SELinux policy is loaded.

#SELINUX=enforcing

SELINUX=disabled

# SELINUXTYPE= can take one of three two values:

# targeted – Targeted processes are protected,

# minimum – Modification of targeted policy. Only selected processes are protected.

# mls – Multi Level Security protection.

SELINUXTYPE=targeted

验证：

再次在浏览器中访问这个 php 页面：

result:2018-09-27 00:09:26

2. 问题 2：lepus 日志中出现 group by 警告。

2018-09-27 01:12:41 [WARNING] check mysql 11.1.1.11:3408 failure: 1055 Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column ‘information_schema.processlist.USER’ which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

原因：

这是 lepus 后端监控程序写的 log。

默认情况下 sql_mode 包含 ONLY_FULL_GROUP_BY。

mysql> select @@sql_mode;

+——————————————————————————————————————————————-+

| @@sql_mode |

+——————————————————————————————————————————————-+

| ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |

+——————————————————————————————————————————————-+

1 row in set (0.01 sec)

解决办法：

去掉 ONLY_FULL_GROUP_BY。

sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

3. 问题 3：复制监控查询不到数据。

没有查询到数据.

解决办法：

show_compatibility_56=1

4. 问题 4：表空间分析没有数据。

5. 问题 5: 慢查询没有数据。

前提：

MySQL 的 my.cnf 配置文件中已经配置了慢查询日志。

slow_query_log=1

long_query_time=10

log_slow_admin_statements=1

log_slow_slave_statements=1

原因：

1.lepus 慢查询分析基于 pecona-toolkit 工具包中的 pt-query-digest 程序。需要先安装这个工具包。

2. pt-query-digest 程序与 lepus3.7 建的表有点冲突。

Pipeline process 5 (iteration) caused an error: DBD::mysql::st execute failed: Data truncated for column ‘checksum’ at row 1 [for Statement “REPLACE INTO `lepus`.`mysql_slow_query_review_history`(`checksum`, `sample`, `serverid_max`, `db_max`, `user_max`, `ts_min`,

…..

Terminating pipeline because process 4 (iteration) caused too many errors.

修改 mysql_slow_query_review：

mysql> alter table mysql_slow_query_review modify checksum varchar(100) not null ;

Query OK, 0 rows affected (0.03 sec)

Records: 0 Duplicates: 0 Warnings: 0

修改 mysql_slow_query_review_history:

mysql> alter table mysql_slow_query_review_history modify checksum varchar(100) not null;

Query OK, 0 rows affected (0.02 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> alter table mysql_slow_query_review_history modify serverid_max smallint(4) null;

Query OK, 0 rows affected (0.02 sec)

Records: 0 Duplicates: 0 Warnings: 0

修改脚本：

原始的 lepus_slowquery.sh 文件存在一些问题。

(1) 需要人工指定 lepus_server_id。这个脚本需要在每个 MySQL 服务器上部署，因此如果要监控的 MySQL 很多，会比较容易出错。

lepus_server_id 这个参数很重要。下面的代码可以自动取得这个 id。

id=$($mysql_client -h$lepus_db_host -P$lepus_db_port -u$lepus_db_user -p$lepus_db_password -e “select id,host,port from $lepus_db_database.db_servers_mysql where host=’$mysql_host’ and port=$mysql_port\G” 2>/dev/null |grep “id:” |awk -F”: ” ‘{print $2}’)

(2）同一台机器上如果部署有多个 MySQL 服务实例时，应该只需要一个定时任务即可，在另一脚本中同时对本机的多个 MySQL 服务实例进行检查。

这个总的定时脚本如下, 测试时开启了 6 个 MySQL 实例, 端口依次为：3306 3307 3308 3406 3407 3408. 其中 3306 和 3406 为 MASTER，其它为 SLAVE。在这个总的脚本中对每个实例调用 lepus_slowquery.sh。

[linuxidc@linuxidc mysql]$ cat slowquery.sh

##################################################################

# FileName：slowquery.sh

# Author : coe2coe@qq.com

# Created：2018-09-27

# Description：http://www.cnblogs.com/coe2coe/

#################################################################

#!/bin/bash

ports=(3306 3307 3308 3406 3407 3408)

i=0

while [$i -lt ${#ports[*]} ]

do

port=${ports[$i]}

echo -e “/lepus_slowquery.sh $port”

./lepus_slowquery.sh $port

let i=i+1

done

(3)原始的 lepus_slowquery.sh 会去修改 MySQL 的全局配置参数，个人认为不需要修改，这两个配置还是应该按照 MySQL 服务器的 my.cnf 文件中配置的为准, 不应该因为部署了一个 lepus 监控系统就随意的修改这个参数。因此直接注释掉了最后面的几行代码。

long_query_time

slow_query_log_file

修改后的完整的 lepus_slowquery.sh 文件如下：

[linuxidc@linuxidc mysql]$ cat lepus_slowquery.sh

#!/bin/bash

#****************************************************************#

# ScriptName: /usr/local/sbin/lepus_slowquery.sh

# Create Date: 2014-03-25 10:01

# Modify Date: 2014-03-25 10:01

#***************************************************************#

port=$1

id=$2

if [“$port” == “”] || [$port -lt 1]

then

echo -e “invalid argument port”

exit 1

fi

echo -e “mysql port is :{$port} “

#config lepus database server

lepus_db_host=”11.1.1.11″

lepus_db_port=3306

lepus_db_user=”lepus_monitor”

lepus_db_password=”XXXXXXXXXX”

lepus_db_database=”lepus”

#config mysql server

mysql_client=”/usr/bin/mysql”

mysql_host=”11.1.1.11″

mysql_port=$port

mysql_user=”lepus_monitor”

mysql_password=”XXXXXXXXXX”

id=$($mysql_client -h$lepus_db_host -P$lepus_db_port -u$lepus_db_user -p$lepus_db_password -e “select id,host,port from $lepus_db_database.db_servers_mysql where host=’$mysql_host’ and port=$mysql_port\G” 2>/dev/null |grep “id:” |awk -F”: ” ‘{print $2}’)

if [“$id” == “”] || [$id -lt 1]

then

echo -e “invalid argument id”

exit 2

fi

echo -e “mysql lepus id is :{$id}”

#config slowqury

slowquery_dir=”/tmp/”

slowquery_long_time=1

slowquery_file=`$mysql_client -h$mysql_host -P$mysql_port -u$mysql_user -p$mysql_password -e “show variables like ‘slow_query_log_file'” 2>/dev/null |grep log|awk ‘{print $2}’`

pt_query_digest=”/usr/bin/pt-query-digest”

#config server_id

lepus_server_id=$id

#collect mysql slowquery log into lepus database

$pt_query_digest –user=$lepus_db_user –password=$lepus_db_password –port=$lepus_db_port –review h=$lepus_db_host,D=$lepus_db_database,t=mysql_slow_query_review –history h=$lepus_db_host,D=$lepus_db_database,t=mysql_slow_query_review_history –no-report –limit=100% –filter=” \$event->{add_column} = length(\$event->{arg}) and \$event->{serverid}=$lepus_server_id ” $slowquery_file > /tmp/lepus_slowquery.log

##### set a new slow query log ###########

#tmp_log=`$mysql_client -h$mysql_host -P$mysql_port -u$mysql_user -p$mysql_password -e “select concat(‘$slowquery_dir’,’slowquery_’, ‘$port’,’_’, date_format(now(),’%Y%m%d%H’),’.log’);” 2>/dev/null |grep log|sed -n -e ‘2p’`

#config mysql slowquery

#$mysql_client -h$mysql_host -P$mysql_port -u$mysql_user -p$mysql_password -e “set global slow_query_log=1;set global long_query_time=$slowquery_long_time;” 2>/dev/null

#$mysql_client -h$mysql_host -P$mysql_port -u$mysql_user -p$mysql_password -e “set global slow_query_log_file = ‘$tmp_log’; “

#delete log before 7 days

#cd $slowquery_dir

#/usr/bin/find ./ -name ‘slowquery_*’ -mtime +7|xargs rm -rf ;

####END####

6. 问题 6：web 慢查询查询不到 lepus 中的数据

在 mysql_slow_query_review 表中记录了慢查询，但是在 lepus web 界面上没有数据。

执行：select sleep(14)有时候无法在 web 界面查询到。

原因：有时候 pt-query-digest 产生的结果中 db_max 为 NULL，导致查询不出来。

这个字段安装的原始数据库是 NOT NULL，但是在 NOT NULL 的情况下 pt-query-digest 有时会插入 NULL 数据，导致报错。所以修改为了 NULL。

修改为 NULL 后，web 界面中查询时使用的 PHP 程序的 SQL 语句有问题，没有考虑 NULL 的情况，导致查询不出来这部分数据。

解决办法：