Linux下Zabbix监控之MySQL监控添加

Mysql数据库监控

• zabbix页面添加Mysql监控默认模板

Template DB MySQL

• 创建mysqladmin链接配置

touch /etc/zabbix/scripts/.my.cnf
[mysqladmin]
host=192.168.1.253
user=zabbix
password=zabbix

• Mysql监控数据获取脚本chk_mysql.sh

# 数据连接
MYSQL_CONN="/usr/local/mysql/bin/mysqladmin --defaults-extra-file=/etc/zabbix/scripts/.my.cnf"

# 参数是否正确
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)
    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

• 配置userparameter_mysql.conf

 vim /etc/zabbix/zabbix_agentd.d/userparameter_mysql.conf
UserParameter=mysql.status[*],/etc/zabbix/script/mysql/chk_mysql.sh $1    #Mysql数据库状态数据抽取;
UserParameter=mysql.ping,netstat -ntpl |grep 3306|grep mysql|wc |awk '{print $1}'    #Mysql数据库状态;
UserParameter=mysql.version,/usr/local/mysql/bin/mysql -V    #Mysql数据库版本信息;

• 权限配置

netstat 命令中，-p参数需要root用户权限；故做以下配置
chmod +s /bin/netstat
vim /etc/sudoers
#Defaults specification    #找到此行并注释
zabbix  ALL=(ALL)       NOPASSWD:/bin/netstat

• zabbix-server上测试

 zabbix_get -s192.168.1.253 -k "mysql.ping"
1
 zabbix_get -s192.168.1.253 -k "mysql.status[Uptime]"
1547

• 重启zabbix-agent服务

systemctl restart zabbix-agent.service

注意事项

1. mysqladmin相关报错:

zabbix_get -s192.168.1.253 -k "mysql.status[Com_rollback]"
mysqladmin: connect to server at '192.168.1.253' failed
error: 'Can't connect to MySQL server on '192.168.1.253' (13)'
Check that mysqld is running on 192.168.1.253 and that the port is 3306.
You can check this by doing 'telnet 192.168.1.253 3306'

此报错跟mysql账号权限有关，请测试mysqladmin -uroot -pXXXX -h192.168.1.253 是否可以登录，如果没有权限需要鉴权；

2. 获取状态异常：

 zabbix_get -s100.101.156.225 -k "mysql.ping"
(Not all processes could be identified, non-owned process info
 will not be shown, you would have to be root to see it all.)
0

此报错跟netstat权限有关，请参照“权限配置”；

3. Warning: Using a password on the command line interface can be insecure.
   此报错是因为mysql5.6及以后的版本对命令中明文密码的保护机制，可以采用--defaults-extra-file指定配置文件的方式解决；
4. zabbix-agent本地测试没问题，zabbix_get测试获取数据异常
   此报错是mysql和mysqladmin命令需要填写绝对路径；