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

在使用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建的表有点冲突。

内容版权声明:除非注明,否则皆为本站原创文章。

转载注明出处:https://www.heiqu.com/6a7201baa9c057a561eba38d81b83f01.html