zabbix8 ---- template mysql

zabbix template mysql

使用bash创建zabbix模板

1、配置Mysql主从
master

vim /etc/my.cnf
[mysqld]
server_id=1
log_bin
innodb_file_per_table=1

systemctl restart mariadb

grant  replication slave on *.* to 'repluser'@'192.168.10.%'  identified by 'pass123';
#revoke all on *.* from repluser@'192.168.10.%';
show variables like 'server_id';
show master logs;

mysqldump  -B zabbix -F -E -R --single-transaction --master-data=1 --flush-privileges --triggers --default-character-set=utf8 --hex-blob > zabbix_all.sql

show processlist;

slave

cat /etc/my.cnf
[mysqld]
server_id=16
read_only=ON
innodb_file_per_table=1
replicate-do-db=zabbix		#设定需要复制的数据库（多数据库使用逗号，隔开）
#replicate-ignore-db=zabbix_proxy_active,zabbix_proxy_passive  #设定需要忽略的复制数据库 （多数据库使用逗号，隔开）

systemctl restart mariadb

mysql < zabbix_all.sql 

CHANGE MASTER TO MASTER_HOST='192.168.10.11', MASTER_USER='repluser', MASTER_PASSWORD='pass123', MASTER_PORT=3306, MASTER_LOG_FILE='mariadb-bin.000003', MASTER_LOG_POS=245;

start slave;

2、配置zabbix_agent

grep ^[a-Z] zabbix_agentd.conf  
PidFile=/var/run/zabbix/zabbix_agentd.pid
LogFile=/var/log/zabbix/zabbix_agentd.log
LogFileSize=0
Server=192.168.10.0/24
ServerActive=192.168.10.3
Hostname=192.168.10.16
Include=/etc/zabbix/zabbix_agentd.d/*.conf

/etc/zabbix/zabbix_agentd.d/userparameter_mysqld.conf
UserParameter=mysql_status[*],/etc/zabbix/zabbix_agentd.d/mysql_monitor.sh "$1"

3、监控脚本验证

/etc/zabbix/zabbix_agentd.d/mysql_monitor.sh
Seconds_Behind_Master(){
    NUM=`mysql -uroot -hlocalhost   -e "show slave status\G;"  | grep "Seconds_Behind_Master:" | awk -F: '{print $2}'`
    echo $NUM
}

master_slave_check(){
    NUM1=`mysql -uroot -hlocalhost   -e "show slave status\G;"  | grep "Slave_IO_Running" | awk -F:  '{print $2}' | sed 's/^[ \t]*//g'`
    #echo $NUM1
    NUM2=`mysql -uroot -hlocalhost   -e "show slave status\G;"  | grep "Slave_SQL_Running:" | awk -F:  '{print $2}' | sed 's/^[ \t]*//g'`
    #echo $NUM2

    if [[ $NUM1 =~ "Yes" ]] && [[ $NUM2 =~ "Yes" ]];then
        echo 200
    else
        echo 404
    fi
}

main(){
    case $1 in
        Seconds_Behind_Master)
           Seconds_Behind_Master;
           ;;
        master_slave_check)
           master_slave_check
           ;;
        *)
            echo "$0 Seconds_Behind_Master|master_slave_check"
    esac
}

main $1

[root@zabbix-server ~]# zabbix_get -s 192.168.10.16 -k mysql_status["master_slave_check"]
200
[root@zabbix-server ~]# zabbix_get -s 192.168.10.16 -k mysql_status["master_slave_check"]
404

4、zabbix web添加模板


监控项


图形

触发器


5、zabbix web添加主机监控


6、验证

基于percona-monitoring-plugins创建模板

官方文档：https://www.percona.com/software/documentation
https://www.percona.com/doc/percona-monitoring-plugins/1.1/index.html
软件包获取：https://www.percona.com/downloads/percona-monitoring-plugins/
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

1、基于php的方式实现，需要安装php和php- mysql
yum install php php-mysql
2、安装模板.https://www.percona.com/downloads/percona-monitoring-plugins/

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
rpm -ivh percona-zabbix-templates-1.1.8-1.noarch.rpm 
以下有脚本及配置文件，模板需要修改时区及SNMP相关设置，功能性可参考此xml文件
[root@localhost ~]# rpm -ql percona-zabbix-templates 
/var/lib/zabbix/percona
/var/lib/zabbix/percona/scripts
/var/lib/zabbix/percona/scripts/get_mysql_stats_wrapper.sh
/var/lib/zabbix/percona/scripts/ss_get_mysql_stats.php
/var/lib/zabbix/percona/templates
/var/lib/zabbix/percona/templates/userparameter_percona_mysql.conf
/var/lib/zabbix/percona/templates/zabbix_agent_template_percona_mysql_server_ht_2.0.9-sver1.1.8.xml

3、复制con配置文件/var/lib/zabbix/percona/templates/userparameter_percona_mysql.conf到 zabbix agent目录

cp /var/lib/zabbix/percona/templates/userparameter_percona_mysql.conf /etc/zabbix/zabbix_agentd.d/.

4、创建php账户密码文件，/var/lib/zabbix/percona/scripts/ss_get_mysql_stats.php.cnf

<?php
$mysql_user = 'root';
$mysql_pass = '';

验证参数
[root@localhost ~]# /var/lib/zabbix/percona/scripts/get_mysql_stats_wrapper.sh gg
0
[root@localhost ~]# /var/lib/zabbix/percona/scripts/get_mysql_stats_wrapper.sh lh
81892921

5、导入模板并关联主机

导入模板失败

主机添加mysql模板

验证数据是否采集