zabbix修改默认模板监控mysql

目录：1，安装zabbix2，配置主从3，监控mysql4，监控主从

Zabbix监控基础模板优化:https://www.linuxea.com/1652.htmlZabbix监控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 statusG' | 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.RepliactionERROR---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 每秒修改语句的执行数