Percona监控数据库解决方案

Percona Server由领先的MySQL咨询公司Percona发布。 Percona Server是一款独立的数据库产品，其可以完全与MySQL兼容，可以在不更改代码的情况了下将存储引擎更换成XtraDB 。
Percona团队的最终声明是“Percona Server是由Oracle发布的最接近官方MySQL Enterprise发行版的版本”，因此与其他更改了大量基本核心MySQL代码的分支有所区别。 Percona Server的一个缺点是他们自己管理代码，不接受外部开发人员的贡献，以这种方式确保他们对产品中所包含功能的控制。
Percona提供了高性能XtraDB引擎，还提供PXC高可用解决方案，并且附带了perconatoolkit等DBA管理工具箱
官网地址 高可用方案
Percona监控和管理percona Monitoring and Management(PMM)是一个用于MySQL、PostgreSQL和MongoDB的开源数据库监控、管理和可观察性解决方案。
它允许您观察数据库系统的健康状况，探索其行为的新模式，对其进行故障排除，并执行数据库管理操作，无论它们位于本地还是云中。
☑ 后内侧肌收集数千种开箱即用的性能韵律学从数据库和它们的主机。
☑ PMM软件工程师 可视化数据在仪表板.
☑ 其他功能包括数据库运行状况评估顾问.

Agent端

wget https://downloads.percona.com/downloads/percona-monitoring-plugins/percona-monitoring-plugins-1.1.8/binary/redhat/7/x86_64/percona-zabbix-templates-1.1.8-1.noarch.rpm
yum -y localinstall percona-zabbix-templates-1.1.8-1.noarch.rpm
1
2

[root@localhost ~]# rpm -ql percona-zabbix-templates

[root@localhost ~]# cp -a /var/lib/zabbix/percona/templates/userparameter_percona_mysql.conf /etc/zabbix/zabbix_agent2.d/ 
[root@localhost ~]# mysqladmin -uroot password 123.com
[root@localhost ~]# vim /var/lib/zabbix/percona/scripts/ss_get_mysql_stats.php 
  30 $mysql_user = 'root';
  31 $mysql_pass = '123.com';
  32 $mysql_port = 3306;
  33 $mysql_socket = NULL;	//根据实际情况修改
  34 $mysql_flags = 0;
  35 $mysql_ssl  = FALSE;   # Whether to use SSL to connect to MySQL.
  36 $mysql_ssl_key  = '/etc/pki/tls/certs/mysql/client-key.pem';
  37 $mysql_ssl_cert = '/etc/pki/tls/certs/mysql/client-cert.pem';
  38 $mysql_ssl_ca   = '/etc/pki/tls/certs/mysql/ca-cert.pem';
  39 $mysql_connection_timeout = 5;
[root@localhost ~]# php -q /var/lib/zabbix/percona/scripts/ss_get_mysql_stats.php --host localhost --items gg
gg:1
[root@localhost ~]# zabbix_agent2 -t MySQL.Questions
MySQL.Questions                               [s|7]
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

Server端

zabbix_get -s 192.168.235.11 -k MySQL.Questions
sz 模板
1
2

Web界面导入zabbix模板
主机链接模板–更新监控项的间隔时间(不要太短)[不能用的监控项chown zabbix:zabbix]

[root@localhost tmp]# chown zabbix:zabbix localhost-mysql_cacti_stats.txt
1

导入模板失败：标签无效"/zabbix_export/date": “YYYY-MM-DDThh:mm:ssZ” 预计。
percona提供的模板只适配与zabbix3.0之前的版本！

问题

1.MySQLi/获取不到相应值

[root@localhost ~]# sh -x /var/lib/zabbix/percona/scripts/get_mysql_stats_wrapper.sh ju
+ ITEM=ju
+ HOST=localhost
++ dirname /var/lib/zabbix/percona/scripts/get_mysql_stats_wrapper.sh
+ DIR=/var/lib/zabbix/percona/scripts
+ CMD='/usr/bin/php -q /var/lib/zabbix/percona/scripts/ss_get_mysql_stats.php --host localhost --items gg'
+ CACHEFILE=/tmp/localhost-mysql_cacti_stats.txt
+ '[' ju = running-slave ']'
+ '[' -e /tmp/localhost-mysql_cacti_stats.txt ']'
++ stat -c %Y /tmp/localhost-mysql_cacti_stats.txt
+ TIMEFLM=1647088578
++ date +%s
+ TIMENOW=1647088592
++ expr 1647088592 - 1647088578
+ '[' 14 -gt 300 ']'
+ '[' -e /tmp/localhost-mysql_cacti_stats.txt ']'
+ awk -F: '{print $2}'
+ cat /tmp/localhost-mysql_cacti_stats.txt
+ sed 's/ /\n/g; s/-1/0/g'
+ grep ju
[root@localhost ~]# /usr/bin/php -q /var/lib/zabbix/percona/scripts/ss_get_mysql_stats.php --host localhost --items gg
PHP MySQLi extension is not loaded

# 解决方法:
[root@localhost ~]# yum -y install php php-mysql				//先安装php在安装php-mysql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

2.特殊原因

[root@zabbx5 ~]# zabbix_get -s 172.16.17.112 -k MySQL.Questions
ERROR: run the command manually to investigate the problem: /usr/bin/php -q /var/lib/zabbix/percona/scripts/ss_get_mysql_stats.php --host localhost --items gg

定位问题,在脚本上面继续排查；问题为php生产的临时文件多个数字(随机)
[root@bj-bg-17.112_13:51 zabbix2.4.5]# cat /var/lib/zabbix/percona/scripts/get_mysql_stats_wrapper.sh 
#!/bin/sh
# The wrapper for Cacti PHP script.
# It runs the script every 5 min. and parses the cache file on each following run.
# Version: 1.1.8
#
# This program is part of Percona Monitoring Plugins
# License: GPL License (see COPYING)
# Copyright: 2018 Percona
# Authors: Roman Vynar

ITEM=$1
HOST=localhost
DIR=`dirname $0`
CMD="/usr/bin/php -q $DIR/ss_get_mysql_stats.php --host $HOST --items gg"
CACHEFILE="/tmp/$HOST-mysql_cacti_stats.txt"

if [ "$ITEM" = "running-slave" ]; then
    # Check for running slave
    RES=`HOME=~zabbix mysql -e 'SHOW SLAVE STATUS\G' | egrep '(Slave_IO_Running|Slave_SQL_Running):' | awk -F: '{print $2}' | tr '\n' ','`
    if [ "$RES" = " Yes, Yes," ]; then
        echo 1
    else
        echo 0
    fi
    exit
elif [ -e ${CACHEFILE}* ]; then
    # Check and run the script
    TIMEFLM=`stat -c %Y ${CACHEFILE}*`
    TIMENOW=`date +%s`
    if [ `expr $TIMENOW - $TIMEFLM` -gt 300 ]; then
        rm -f $CACHEFILE
        $CMD 2>&1 > /dev/null
    fi
else
    $CMD 2>&1 > /dev/null
fi

# 解析缓存文件
if [ -e ${CACHEFILE}* ]; then
    cat ${CACHEFILE}* | sed 's/ /\n/g; s/-1/0/g'| grep $ITEM | awk -F: '{print $2}'
else
    echo "ERROR: run the command manually to investigate the problem: $CMD"
fi
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48

3.MySQL running slave不可用

Value of type “string” is not suitable for value type “Numeric (unsigned)”. Value "ERROR 1045 (28000): Access denied for user ‘huaizhe.zhao’@‘localhost’ (using password: NO)

[root@BJ-BG-19.3 ~]# mysql -uroot -p -e 'SHOW SLAVE STATUS\G' | egrep '(Slave_IO_Running|Slave_SQL_Running):' | awk -F: '{print $2}' | tr '\n' ',' 
 No, No,
1
2

Since already back to less than in the past, it is better to think about your future.