Shulou(Shulou.com)06/01 Report--

When using lepus3.7 to monitor MySQL database, we encountered the following problems. This blog gives the causes of these problems and the corresponding solutions.

1. The problem 1:php page could not connect to the database

Using the php program to execute the php file directly, you can connect to mysql, but the same php page in httpd cannot connect to mysql.

When lepus's web program (PHP code) cannot connect to the database, nothing can continue on the web interface.

The simplest PDO connection test code is written for this purpose:

The php code is as follows:

[x@coe2coe lepus] $cat mysql.php

The php program executes php files directly:

[x@coe2coe lepus] $php mysql.phpresult:2018-09-27 00:03:44

Access this page through a browser:

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

The error message given by lepus's web program is even more vague.

Reason:

After some baidu, I finally saw a more reliable analysis.

The selinux security mechanism of Linux (CentOS7) prohibits modules in httpd from accessing the network.

[x@coe2coe lepus] $sudo getsebool-a | grep httpdhttpd_anon_write-- > offhttpd_builtin_scripting-- > onhttpd_can_check_spam-- > offhttpd_can_connect_ftp-- > offhttpd_can_connect_ldap-- > offhttpd_can_connect_mythtv-- > offhttpd_can_connect_zabbix-- > offhttpd_can_network_connect-- > offhttpd_can_network_connect_cobbler-- > offhttpd_can_network_connect_db-- > offhttpd_can_network_memcache- -> offhttpd_can_network_relay-- > offhttpd_can_sendmail-- > offhttpd_dbus_avahi-- > offhttpd_dbus_sssd-- > offhttpd_dontaudit_search_dirs-- > offhttpd_enable_cgi-- > onhttpd_enable_ftp_server-- > offhttpd_enable_homedirs-- > offhttpd_execmem-- > offhttpd_graceful_shutdown-- > onhttpd_manage_ipa-- > offhttpd_mod_auth_ntlm_winbind-- > offhttpd_mod_auth_pam-- > offhttpd_read_ User_content-- > offhttpd_run_ipa-- > offhttpd_run_preupgrade-- > offhttpd_run_stickshift-- > offhttpd_serve_cobbler_files-- > offhttpd_setrlimit-- > offhttpd_ssi_exec-- > offhttpd_sys_script_anon_write-- > offhttpd_tmp_exec-- > offhttpd_tty_comm-- > offhttpd_unified-- > offhttpd_use_cifs-- > offhttpd_use_fusefs-- > offhttpd_use_gpg-- > offhttpd_use_nfs-- > Offhttpd_use_openstack-- > offhttpd_use_sasl-- > offhttpd_verify_dns-- > off

Solution:

Temporary solution: temporarily disable SELINUX.

[x@coe2coe lepus] $sudo setenforce 0

Permanent solution: modify the selinux configuration file and disable SELINUX.

[x@coe2coe 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=enforcingSELINUX=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

Verify:

Visit the php page in the browser again:

Result:2018-09-27 00:09:26

two。 A group by warning appears in the problem 2:lepus log.

2018-09-27 01:12:41 [WARNING] check mysql 11.1.1.11 failure 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

Reason:

This is the log written by the lepus backend monitor.

Sql_mode contains ONLY_FULL_GROUP_BY by default.

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)

Solution:

Get rid of 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. Problem 3: the replication monitoring query cannot find the data.

No data was found.

Solution:

Show_compatibility_56=1

4. Question 4: there is no data in tablespace analysis.

5. Question 5: slow queries have no data.

Premise:

The slow query log is already configured in MySQL's my.cnf configuration file.

Slow_query_log=1long_query_time=10log_slow_admin_statements=1log_slow_slave_statements=1

Reason:

1.lepus slow query analysis is based on the pt-query-digest program in the pecona-toolkit toolkit. You need to install this toolkit first.

2. The pt-query-digest program conflicts with the table built by 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_ Secrety` (`che cksum`, `sample`, `serverid_ max`, `db_ max`, `user_ max`, `ts_ min`

.

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

Modify mysql_slow_query_review:

Mysql > alter table mysql_slow_query_review modify checksum varchar not null;Query OK, 0 rows affected (0.03 sec) Records: 0 Duplicates: 0 Warnings: 0 modified 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: 0mysql > 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

Modify the script:

There are some problems with the original lepus_slowquery.sh file.

(1) lepus_server_id needs to be specified manually. This script needs to be deployed on each MySQL server, so if you have a lot of MySQL to monitor, it will be more error-prone.

The parameter lepus_server_id is very important. The following code automatically fetches this 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) if multiple MySQL service instances are deployed on the same machine, only one scheduled task is needed, and multiple local MySQL service instances are checked at the same time in another script.

The total timing script is as follows. Six MySQL instances are opened during the test, and the port is: 3306 3307 3308 3406 3407 3408. Among them, 3306 and 3406 are MASTER, and the others are SLAVE. Lepus_slowquery.sh is called for each instance in this overall script.

[x@coe2coe mysql] $cat slowquery.sh# FileName: slowquery.sh# Author: coe2coe@qq.com# Created: 2018-09-27 # Description: http://www.cnblogs.com/coe2coe/#### #! / bin/bashports= (3306 3307 3308 3406 3407 3408) i=0while [$I-lt ${# ports [*]}] do port=$ {ports [$I]} echo-e "/ lepus_slowquery.sh $port". / lepus_slowquery.sh $port let i=i+1done

(3) the original lepus_slowquery.sh will modify the global configuration parameters of MySQL. I don't think it is necessary to modify them. These two configurations should be based on the configuration in the my.cnf file of the MySQL server, and this parameter should not be modified arbitrarily just because a lepus monitoring system is deployed. So I commented out the last few lines of code directly.

Long_query_timeslow_query_log_file

The complete modified lepus_slowquery.sh file is as follows:

[x@coe2coe mysql] $cat lepuspuspuspuspushoquery.shandra Modify Date Modify Date: 2014-03 -25 10:01#***#port=$1id=$2if ["$port" = ""] | | [$port-lt 1] then echo-e "invalid argument port" exit 1fiecho-e "mysql port is: {$port}" # config lepus database serverlepus_db_host= "11.1.1.11" lepus_db_port=3306lepus_db_user= "lepus_monitor" lepus_db_password= "XXXXXXXXXX" lepus_db_database= "lepus" # config mysql servermysql_client= "/ usr/bin/mysql" mysql_host= "11.1.1.11" mysql_port=$portmysql_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 2fiecho-e" mysql lepus id is: {$id} "# config slowquryslowquery_dir=" / tmp/ "slowquery_long_time=1slowquery_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_idlepus_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 Dempsey pushroom database department history MySQL databases database review-- Dobbylepushroom database 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 slowquery 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. Problem 6:web slow query cannot query data in lepus

Slow queries are recorded in the mysql_slow_query_ query table, but there is no data on the lepus web interface.

Execution: select sleep (14) is sometimes not available in the web interface.

Reason: sometimes the db_max in the result generated by pt-query-digest is NULL, resulting in the query not coming out.

The original database installed in this field is NOT NULL, but in the case of NOT NULL, pt-query-digest sometimes inserts NULL data, resulting in an error. So change it to NULL.

After being modified to NULL, there is a problem with the SQL statement of the PHP program used in the query in the web interface, and the situation of NULL is not taken into account, resulting in this part of the data cannot be queried.

Solution:

Temporarily open the global parameter general_log, and then do web query slow log, you can quickly find the SQL statement, and then according to the SQL statement can find the problematic PHP code.

Comment out the following statement in application/controllers/lp_mysql.php.

Before modification:

$this- > db- > where ("b.db_max! =", 'information_schema' ")

After modification:

/ / $this- > db- > where ("b.db_max! =", 'information_schema' ")

7. Question 7: none of the three items in the host monitoring have data.

Reason: snmpd,snmptrapd is not installed on the monitoring host and the monitored host.

Solution:

Install snmpd and snmptrapd on all hosts.

Software package:

X@coe2coe snmp] $ls net-snmp*net-snmp-5.7.2-32.el7.x86_64.rpmnet-snmp-agent-libs-5.7.2-32.el7.x86_64.rpmnet-snmp-devel-5.7.2-32.el7.x86_64.rpmnet-snmp-libs-5.7.2-32.el7.x86_64.rpmnet-snmp-perl-5.7.2-32.el7.x86_64.rpmnet-snmp-python-5.7 .2-32.el7.x86_64.rpmnet-snmp-sysvinit-5.7.2-32.el7.x86_64.rpmnet-snmp-utils-5.7.2-32.el7.x86_64.rpm

These packages are available on CentOS7-everything-xxx.iso.

Start the snmpd and snmptrapd services after installation.

Summary

The above is the editor to introduce to you the use of MySQL database monitoring software lepus and solutions, I hope to help you, if you have any questions, please leave me a message, the editor will reply to you in time. Thank you very much for your support to the website!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.