zabbix mysql默认模板_zabbix修改默认模板监控mysql

目录：

1，安装zabbix

2，配置主从

3，监控mysql

4，监控主从

Zabbix监控基础模板优化:https://www.linuxea.com/1652.html

Zabbix监控Galera Cluster集群和Master slave主从:https://www.linuxea.com/1655.html

其他监控关注：https://www.linuxea.com/category/zabbix/

zabbix安装

rpm -ivh http://repo.zabbix.com/zabbix/2.4/rhel/6/x86_64/zabbix-release-2.4-1.el6.noarch.rpm

安装lmp

rpm -ivh http://repo.mysql.com/mysql-community-release-el6-5.noarch.rpm

rpm -Uvh https://mirror.webtatic.com/yum/el6/latest.rpm

yum -y install mysql-server mysql mysql-devel php56w php56w-fpm php56w-mysql php56w-common php56w-gd php56w-mbstring php56w-mcrypt php56w-devel php56w-xml php56w-imap php56w-pear php56w-snmp

rpm -ivh http://repo.zabbix.com/zabbix/2.4/rhel/6/x86_64/zabbix-release-2.4-1.el6.noarch.rpm

安装zabbix-server

rpm -ivh http://repo.zabbix.com/zabbix/2.4/rhel/6/x86_64/zabbix-release-2.4-1.el6.noarch.rpm

yum install zabbix zabbix-server zabbix-web zabbix-agent zabbix-web-mysql zabbix-get zabbix-sender

数据库授权

mysql -e "CREATE DATABASE zabbix CHARACTER SET utf8;"

mysql -e "GRANT ALL ON zabbix.* TO 'zuser'@'10.10.230.59' IDENTIFIED BY 'password';"

mysql -e "GRANT ALL ON zabbix.* TO 'zuser'@'localhost' IDENTIFIED BY 'password';"

导入zabbixsql文件，顺序不能错

mysql zabbix < /usr/share/doc/zabbix-server-mysql-2.4.7/create/schema.sql

mysql zabbix < /usr/share/doc/zabbix-server-mysql-2.4.7/create/images.sql

mysql zabbix < /usr/share/doc/zabbix-server-mysql-2.4.7/create/data.sql

修改zabbix配置文件

vim /etc/zabbix/zabbix_server.conf

DBUser=zuser

DBHost=localhost

DBName=zabbix

DBPassword=password

DBSocket=/var/lib/mysql/mysql.sock

启动

service httpd restart

service zabbix-server start

chkconfig --add zabbix-server

chkconfig zabbix-server on

chkconfig --add httpd

chkconfig httpd on

chkconfig --add mysqld

chkconfig mysqld on

chkconfig iptables off

setenforce 0

监控本机：

rpm -ivh http://repo.zabbix.com/zabbix/2.4/rhel/6/x86_64/zabbix-release-2.4-1.el6.noarch.rpm

yum -y install zabbix-agent zabbix-sender zabbix

vim /etc/zabbix/zabbix-agentd.conf

Server=192.168.233.130 zabbix-server服务端IP

ServerActive=192.168.233.130 zabbix-server服务端IP

Hostname=zabbix-agent 全局唯一，最好使用dns解析名。这里的hostname主要推送数据

service zabbix-agentd start

一，监控mysql如下：

添加zabbix-agent监控数据库权限，当然你也可给小的权限

GRANT ALL ON *.* TO 'test'@'localhost' IDENTIFIED BY 'password';

GRANT ALL ON *.* TO 'test'@'127.0.0.1' IDENTIFIED BY 'password';

添加脚本

创建脚本目录

mkdir /etc/zabbix/script/

创建脚本如下：

vim /etc/zabbix/script/chk_mysql.sh

#/bin/bash

DEF="--defaults-file=/etc/zabbix/script/mysql.conf"

MYSQL='/usr/local/webservers/mysql-5.6.19/bin/mysqladmin'

ARGS=1

if [ $# -ne "$ARGS" ];then

echo "Please input one arguement:"

fi

case $1 in

Uptime)

result=`${MYSQL} $DEF status|cut -f2 -d":"|cut -f1 -d"T"`

echo $result

;;

Com_update)

result=`${MYSQL} $DEF extended-status |grep -w "Com_update"|cut -d"|" -f3`

echo $result

;;

Slow_queries)

result=`${MYSQL} $DEF status |cut -f5 -d":"|cut -f1 -d"O"`

echo $result

;;

Com_select)

result=`${MYSQL} $DEF extended-status |grep -w "Com_select"|cut -d"|" -f3`

echo $result

;;

Com_rollback)

result=`${MYSQL} $DEF extended-status |grep -w "Com_rollback"|cut -d"|" -f3`

echo $result

;;

Questions)

result=`${MYSQL} $DEF status|cut -f4 -d":"|cut -f1 -d"S"`

echo $result

;;

Com_insert)

result=`${MYSQL} $DEF extended-status |grep -w "Com_insert"|cut -d"|" -f3`

echo $result

;;

Com_delete)

result=`${MYSQL} $DEF extended-status |grep -w "Com_delete"|cut -d"|" -f3`

echo $result

;;

Com_commit)

result=`${MYSQL} $DEF extended-status |grep -w "Com_commit"|cut -d"|" -f3`

echo $result

;;

Bytes_sent)

result=`${MYSQL} $DEF extended-status |grep -w "Bytes_sent" |cut -d"|" -f3`

echo $result

;;

Bytes_received)

result=`${MYSQL} $DEF extended-status |grep -w "Bytes_received" |cut -d"|" -f3`

echo $result

;;

Com_begin)

result=`${MYSQL} $DEF extended-status |grep -w "Com_begin"|cut -d"|" -f3`

echo $result

;;

*)

echo "Usage:$0(Uptime|Com_update|Slow_queries|Com_select|Com_rollback|Questions)"

;;

esac

编辑/etc/zabbix/script/mysql.conf

vim /etc/zabbix/script/mysql.conf

[client]

host=localhost

user=test

password='password'

socket = /data/mysql/mysql.sock

agent添加key文件

vim /etc/zabbix/zabbix_agent.d/myql-status.conf

UserParameter=mysql.version,/usr/local/mysql/bin/mysql -V

UserParameter=mysql.ping,/usr/local/mysql/mysqladmin --defaults-file=/etc/zabbix/script/mysql.conf ping | grep -c alive

UserParameter=mysql.status[*],/etc/zabbix/script/chk_mysql.sh $1 $2

修改zabbix-agent.conf

Server=192.168.233.130 zabbix-server服务端IP

ServerActive=192.168.233.130 zabbix-server服务端IP

Hostname=zabbix-agent 全局唯一，最好使用dns解析名。这里的hostname主要推送数据

Include=/usr/local/etc/zabbix_agentd.conf.d/myql-status.conf

测试：

[root@localhost zabbix]# zabbix_get -s 127.0.0.1 -k "mysql.ping"

1

[root@localhost zabbix]# zabbix_get -s 127.0.0.1 -k "mysql.status[Uptime]"

78967

[root@localhost zabbix]#

二，监控mysql从进程

1，安装mysql

tar xf mariadb-5.5.48-linux-x86_64.tar.gz

ln -s mariadb-5.5.48-linux-x86_64 mysql

mkdir -pv /mydata/data

groupadd -r -g 306 mysql

useradd -r -g 306 -u 306 mysql

chown -R mysql.mysql /mydata

cd mysql/

chown -R root.mysql ./*

scripts/mysql_install_db --user=mysql --datadir=/mydata/data

mkdir /etc/mysql

cp support-files/my-large.cnf /etc/mysql/my.cnf

cp support-files/mysql.server /etc/init.d/mysqld

chkconfig --add mysqld

service mysqld start

备份主数据，从导入数据

/usr/local/mysql/bin/mysql < /tmp/zabbix.sql

/usr/local/mysql/bin/mysql

MariaDB [(none)]> CHANGE MASTER TO MASTER_HOST='10.10.230.59',MASTER_USER='repluser',MASTER_PASSWORD='replpass',MASTER_LOG_FILE='master-bin.000003',MASTER_LOG_POS=30578124,MASTER_CONNECT_RETRY=5,MASTER_heartbeat_PERIOD=2;

MariaDB [(none)]> start slave;

安装zabbix

rpm -ivh http://repo.zabbix.com/zabbix/2.4/rhel/6/x86_64/zabbix-release-2.4-1.el6.noarch.rpm

yum -y install zabbix-agent zabbix-sender zabbix

创建目录

mkdir /etc/zabbix/script/

脚本mysql_ms.sh

vim /etc/zabbix/script/mysql_ms.sh

#!/bin/bash

/usr/local/mysql/bin/mysql --defaults-file=/etc/zabbix/script/my.conf -e 'show slave status\G' | grep -E "Slave_IO_Running:|Slave_SQL_Running:" | awk '{print $2}' | grep -c Yes

脚本2

vim /etc/zabbix/zabbix_agentd.d/mysql_ms.conf

UserParameter=mysql.ms,/etc/zabbix/script/mysql_ms.sh

授予zabbix监控mysql权限

MariaDB [(none)]> GRANT ALL ON *.* TO 'test'@'127.0.0.1' IDENTIFIED BY 'password';

my.conf配置文件

[root@localhost script]# cat my.conf

[client]

host=127.0.0.1

user=test

password='password'

socket = /tmp/mysql.sock

[root@localhost script]#

test

[root@localhost zabbix]# zabbix_get -s 10.10.230.38 -k "mysql.ms"

2

[root@localhost zabbix]#

打开web界面点击create host 添加一台主机，而后Items–>Create item

邮件通知内容：

MySQL.Repliaction

ERROR---MySQL master-slave -->{ITEM.VALUE1}

MySQL 主从出现问题，请检测主从状态！！！

告警主机 : {HOSTNAME1}

告警时间 : {EVENT.DATE} {EVENT.TIME}

告警等级 : {TRIGGER.SEVERITY}

告警信息 : {TRIGGER.NAME}

告警项目 : {TRIGGER.KEY1}

问题详情 : {ITEM.NAME}:{ITEM.VALUE}

当前状态 : {TRIGGER.STATUS}:{ITEM.VALUE1}

事件ID : {EVENT.ID}

恢复后的回复:

OK---MySQL master-slave -->{ITEM.VALUE1}

MySQL 主从问题恢复，请确认主从状态！！！

告警主机 : {HOSTNAME1}

告警时间 : {EVENT.DATE} {EVENT.TIME}

告警等级 : {TRIGGER.SEVERITY}

告警信息 : {TRIGGER.NAME}

告警项目 : {TRIGGER.KEY1}

问题详情 : {ITEM.NAME}:{ITEM.VALUE}

当前状态 : {TRIGGER.STATUS}:{ITEM.VALUE1}

事件ID : {EVENT.ID}

其中

bytes received表示从所有客户端接收到的字节数

bytes sent表示发送给所有客户端的字节数。

mysql begin 每秒的事务语句执行数

mysql commit 每秒提交语句的执行数

mysql delete 每秒删除语句的执行数

mysql insert 每秒插入语句的执行数

mysql rollback 每秒回滚语句的执行数

mysql select 每秒查询语句的执行数

mysql upadte 每秒修改语句的执行数

除非另有说明，否则本站上的内容根据以下许可进行许可: CC署名-非商业性使用-相同方式共享4.0国际许可协议4.0进行许可

本文作者：www.linuxea.com for Mark

文章链接：http://www.linuxea.com/1296.html (转载请注明本文出处和本章链接)