Zabbix监控Mysql性能

  • A+
所属分类:Zabbix

需求:使用zabbix自带监控mysql模板,监控项比较少,使用脚本(网上搜索)添加更多监控项。

一.zabbix server上添加mysql模板
Configuration--Hosts--选择需添加mysql监控的主机--Template
Zabbix监控Mysql性能
二.在zabbix agent上添加mysql用户
1.登录mysql

mysql -u root -p

2.创建zabbix用户

GRANT PROCESS,SUPER,REPLICATION CLIENT ON *.* TO zabbix@'localhost' IDENTIFIED BY 'Zabbix@123';

3.刷新权限

flush privileges;

三.编辑mysql脚本

vim /etc/zabbix/mysql.sh
#!/bin/bash
# 用户名
MYSQL_USER='zabbix'

# 密码
MYSQL_PWD='Zabbix@123'

# 主机地址/IP
MYSQL_HOST='127.0.0.1'

# 端口
MYSQL_PORT='3307'

# 数据连接
MYSQL_CONN="/usr/bin/mysqladmin -u ${MYSQL_USER} -p${MYSQL_PWD} -h${MYSQL_HOST} -P${MYSQL_PORT}"

# 参数是否正确
if [ $# -ne "1" ];then
    echo "arg error!" 
fi

# 获取数据
case $1 in
    Uptime)
        result=`${MYSQL_CONN} status|cut -f2 -d":"|cut -f1 -d"T"`
        echo $result 
        ;;
    Com_update)
        result=`${MYSQL_CONN} extended-status |grep -w "Com_update"|cut -d"|" -f3`
        echo $result 
        ;;
    Slow_queries)
        result=`${MYSQL_CONN} status |cut -f5 -d":"|cut -f1 -d"O"`
        echo $result 
        ;;
    Com_select)
        result=`${MYSQL_CONN} extended-status |grep -w "Com_select"|cut -d"|" -f3`
        echo $result 
                ;;
    Com_rollback)
        result=`${MYSQL_CONN} extended-status |grep -w "Com_rollback"|cut -d"|" -f3`
                echo $result 
                ;;
    Questions)
        result=`${MYSQL_CONN} status|cut -f4 -d":"|cut -f1 -d"S"`
                echo $result 
                ;;
    Com_insert)
        result=`${MYSQL_CONN} extended-status |grep -w "Com_insert"|cut -d"|" -f3`
                echo $result 
                ;;
    Com_delete)
        result=`${MYSQL_CONN} extended-status |grep -w "Com_delete"|cut -d"|" -f3`
                echo $result 
                ;;
    Com_commit)
        result=`${MYSQL_CONN} extended-status |grep -w "Com_commit"|cut -d"|" -f3`
                echo $result 
                ;;
    Bytes_sent)
        result=`${MYSQL_CONN} extended-status |grep -w "Bytes_sent" |cut -d"|" -f3`
                echo $result 
                ;;
    Bytes_received)
        result=`${MYSQL_CONN} extended-status |grep -w "Bytes_received" |cut -d"|" -f3`
                echo $result 
                ;;
    Com_begin)
        result=`${MYSQL_CONN} extended-status |grep -w "Com_begin"|cut -d"|" -f3`
                echo $result 
                ;;

        *)
        echo "Usage:$0(Uptime|Com_update|Slow_queries|Com_select|Com_rollback|Questions|Com_insert|Com_delete|Com_commit|Bytes_sent|Bytes_received|Com_begin)" 
        ;;
esac
chmod +x /etc/zabbix/mysql.sh

四.编辑mysql模板

vim /etc/zabbix/zabbix_agentd.d/userparameter_mysql.conf 
注销原配置,添加新的
UserParameter=mysql.status[*],bash /etc/zabbix/mysql.sh $1
UserParameter=mysql.ping,netstat -ntpl |grep 3307 |grep mysql |wc |awk '{print $1}'
UserParameter=mysql.version,mysql -V
UserParameter=mysql.process,/usr/bin/mysql -uzabbix -pZabbix@123 -P3307 -h127.0.0.1  -e "show processlist" 2>/dev/null|wc -l

避免zabbix-server远程调用netstat失败,添加权限

chmod +s /bin/netstat

五.测试
1.重启服务
systemctl restart zabbix-agent
2.在zabbix-server上测试,rpm安装zabbix-agent,可以直接执行zabbix_get

$zabbix_get -s 192.168.2.11 -p10050 -k mysql.status[Uptime];
31962
$ zabbix_get -s 192.168.2.11 -p10050 -k "system.cpu.load[all,avg15]"; 
0.620000

如若提示Warning: Using a password on the command line interface can be insecure.

vim /etc/zabbix/zabbix_agentd.d/.my.cnf
[client]
user = zabbix
password = Zabbix@123

Zabbix监控Mysql性能

avatar

发表评论

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen: