zabbix5.0.4自定义监控mysql5.7

1.登录agent服务器，创建zabbix的mysql用户

grant all on *.* to zabbix@"127.0.0.1" identified by "zabbix123456";flush privileges;

2.创建监控脚本

mkdir /etc/zabbix/script
vim /etc/zabbix/script/mysql_status.sh

脚本内容：

#!/bin/bash 
#Desc：zabbix 监控 MySQL 状态
#Date：2020-3-2
#by：unhejing

#主机
HOST="127.0.0.1"
#用户
USER="zabbix"
#密码
PASSWORD="zabbix123456"
#端口
PORT="3306"
#MySQL连接
CONNECTION="mysqladmin -h ${HOST} -u ${USER} -P ${PORT} -p${PASSWORD}"

if [ $# -ne "1" ];then
    echo "arg error!"
fi

case $1 in
    Uptime)
        result=`${CONNECTION} status 2>/dev/null |awk '{print $2}'`
        echo $result
        ;;
    Questions)
        result=`${CONNECTION} status 2>/dev/null |awk '{print $6}'`
        echo $result
        ;;
    Com_update)
        result=`${CONNECTION} extended-status 2>/dev/null |grep -w "Com_update" |awk '{print $4}'`
        echo $result
        ;;
    Slow_queries)
        result=`${CONNECTION} extended-status 2>/dev/null |grep -w "Slow_queries" |awk '{print $4}'`
        echo $result
        ;;
    Com_select)
        result=`${CONNECTION} extended-status 2>/dev/null |grep -w "Com_select" |awk '{print $4}'`
        echo $result
        ;;
    Com_rollback)
        result=`${CONNECTION} extended-status 2>/dev/null |grep -w "Com_rollback" |awk '{print $4}'`
        echo $result
        ;;
    Com_insert)
        result=`${CONNECTION} extended-status 2>/dev/null |grep -w "Com_insert" |awk '{print $4}'`
        echo $result
        ;;
    Com_delete)
        result=`${CONNECTION} extended-status 2>/dev/null |grep -w "Com_delete" |awk '{print $4}'`
        echo $result
        ;;
    Com_commit)
        result=`${CONNECTION} extended-status 2>/dev/null |grep -w "Com_commit" |awk '{print $4}'`
        echo $result
        ;;
    Bytes_sent)
        result=`${CONNECTION} extended-status 2>/dev/null |grep -w "Bytes_sent" |awk '{print $4}'`
        echo $result
        ;;
    Bytes_received)
        result=`${CONNECTION} extended-status 2>/dev/null |grep -w "Bytes_received" |awk '{print $4}'`
        echo $result
        ;;
    Com_begin)
        result=`${CONNECTION} extended-status 2>/dev/null |grep -w "Com_begin" |awk '{print $4}'`
        echo $result
        ;;*)
        echo "Usage:$0(Uptime|Questions|Com_update|Slow_queries|Com_select|Com_rollback|Com_insert|Com_delete|Com_commit|Bytes_sent|Bytes_received|Com_begin)"
        ;;
esac

3.赋权限

chmod +x /etc/zabbix/script/mysql_status.sh 

4.修改zabbix-agent配置文件

vim /etc/zabbix/zabbix_agentd.conf 

文件最后追加：

#获取MySQL性能指标，这个是上一步编辑好的脚本
UserParameter=mysql.status[*],/etc/zabbix/script/mysql_status.sh $1
#获取MySQL运行状态
UserParameter=mysql.ping,mysqladmin -u root -h 127.0.0.1 -pM9Ncehx92haser3r8usxx1qaz\!QAZ ping 2>/dev/null | grep -c alive
#获取MySQL版本
UserParameter=mysql.version,mysql -V
#慢查询sql数量
UserParameter=mysql.slow,export MYSQL_PWD=zabbix123456;mysql -uzabbix -h127.0.0.1 -P3306 -sN -e "select count(1) from information_schema.innodb_trx ORDER BY trx_started asc;"

5.重启 zabbix-agent 

service zabbix-agent restart

6.关闭selinux防火墙

setenforce 0

7.创建mysql软连接（否者到时候获取数据会报错 sh: mysql: 未找到命令）

ln -s /usr/local/mysql/bin/* /usr/bin

8.配置web页面

（1）创建模版

（2）创建触发器

（3）添加主机关联模版（template custom MYSQL是自己的自定义的模版）

（4）模版文件如下，可直接导入到模版库（zbx_export_templates.xml）

<?xml version="1.0" encoding="UTF-8"?>
<zabbix_export>
    <version>5.0</version>
    <date>2021-03-04T02:46:34Z</date>
    <groups>
        <group>
            <name>htflsys</name>
        </group>
    </groups>
    <templates>
        <template>
            <template>Template custom MYSQL</template>
            <name>Template custom MYSQL</name>
            <description>自定义mysql监控</description>
            <groups>
                <group>
                    <name>htflsys</name>
                </group>
            </groups>
            <items>
                <item>
                    <name>mysql.Com_insert</name>
                    <key>mysq.status[Com_insert]</key>
                    <delay>10s</delay>
                </item>
                <item>
                    <name>mysql.ping</name>
                    <key>mysql.ping</key>
                    <delay>10s</delay>
                    <triggers>
                        <trigger>
                            <expression>{last()}=0</expression>
                            <name>数据库存活状态</name>
                            <priority>HIGH</priority>
                        </trigger>
                    </triggers>
                </item>
                <item>
                    <name>mysql.slow</name>
                    <key>mysql.slow</key>
                    <triggers>
                        <trigger>
                            <expression>{last()}&gt;10</expression>
                            <name>慢查询</name>
                            <priority>HIGH</priority>
                        </trigger>
                    </triggers>
                </item>
                <item>
                    <name>mysql.Com_delete</name>
                    <key>mysql.status[Com_delete]</key>
                    <delay>10s</delay>
                </item>
                <item>
                    <name>mysql.Com_rollback</name>
                    <key>mysql.status[Com_rollback]</key>
                    <delay>10s</delay>
                </item>
                <item>
                    <name>mysql.Com_select</name>
                    <key>mysql.status[Com_select]</key>
                    <delay>10s</delay>
                </item>
                <item>
                    <name>mysql.Com_update</name>
                    <key>mysql.status[Com_update]</key>
                    <delay>10s</delay>
                </item>
                <item>
                    <name>mysql.Questions</name>
                    <key>mysql.status[Questions]</key>
                    <delay>10s</delay>
                </item>
                <item>
                    <name>mysql.Slow_queries</name>
                    <key>mysql.status[Slow_queries]</key>
                    <delay>10s</delay>
                </item>
                <item>
                    <name>mysql.uptime</name>
                    <key>mysql.status[Uptime]</key>
                    <delay>10s</delay>
                </item>
                <item>
                    <name>mysql.version</name>
                    <key>mysql.version</key>
                    <delay>10s</delay>
                    <trends>0</trends>
                    <value_type>CHAR</value_type>
                </item>
            </items>
        </template>
    </templates>
    <graphs>
        <graph>
            <name>慢查询</name>
            <ymin_type_1>FIXED</ymin_type_1>
            <ymax_type_1>FIXED</ymax_type_1>
            <graph_items>
                <graph_item>
                    <sortorder>1</sortorder>
                    <color>1A7C11</color>
                    <item>
                        <host>Template custom MYSQL</host>
                        <key>mysql.slow</key>
                    </item>
                </graph_item>
            </graph_items>
        </graph>
    </graphs>
</zabbix_export>

（5）配置成功以后查看最新数据

备注：

1.脚本里面使用mysql和mysqladmin时，host的地址使用127.0.0.1不要使用localhost，我遇到了此问题 研究了很久才找到这bug，所以我在开始赋予权限时也改成了127.0.0.1

2.如果127.0.0.1还是访问不到，则建议取消-h合格参数，脚本也可以执行成功

3.如果mysql一直没响应，可以在agent服务器上先执行脚本看是否成功 

sh /etc/zabbix/script/mysql_status.sh Uptime

如果返回有数字，则执行成功，如果返回为空，则脚本执行失败，换用mysqladmin原命令执行验证用户名密码是否能登录

mysqladmin  -u zabbix -P 3306 -pzabbix123456 status

如果上面两个指令都正常。则去zabbix-server服务器上执行远程命令

zabbix_get -s 192.168.31.57 -k mysql.status[Uptime]

以上三条命令大致能排查出问题所在。如果agent服务器上Uptime有值，server服务器上没值，则是linux防火墙的问题，即在agent服务器上执行：setenforce 0 即可

 

篇末赋上自动化脚本：

#!/bin/bash 
#Desc：自动部署zabbix 监控 MySQL 状态 agent端 说明执行改脚本需要传入主机、用户名、密码、端口信息。
#Date：2020-3-2
#by：unhejing


#MySQL连接信息
function MySQLInfo {
    read -p "主机：" HOST
    read -p "用户：" USER
    read -p "密码：" PASSWORD
    read -p "端口：" PORT
}

#创建mysql_status脚本
function MySQLScript {
cat >>/etc/zabbix/script/mysql_status.sh<<EOF
#!/bin/bash 
#Desc：zabbix 监控 MySQL 状态
#Date：2020-3-2
#by：unhejing

#主机
HOST=$HOST
#用户
USER=$USER
#密码
PASSWORD=$PASSWORD
#端口
PORT=$PORT
#MySQL连接
CONNECTION="mysqladmin -h ${HOST} -u ${USER} -P ${PORT} -p${PASSWORD}"

if [ \$# -ne "1" ];then
    echo "arg error!"
fi

case \$1 in
    Uptime)
        result=\`\${CONNECTION} status 2>/dev/null |awk '{print \$2}'\`
        echo \$result
        ;;
    Questions)
        result=\`\${CONNECTION} status 2>/dev/null |awk '{print \$6}'\`
        echo \$result
        ;;
    Com_update)
        result=\`\${CONNECTION} extended-status 2>/dev/null |grep -w "Com_update" |awk '{print \$4}'\`
        echo \$result
        ;;
    Slow_queries)
        result=\`\${CONNECTION} extended-status 2>/dev/null |grep -w "Slow_queries" |awk '{print \$4}'\`
        echo \$result
        ;;
    Com_select)
        result=\`\${CONNECTION} extended-status 2>/dev/null |grep -w "Com_select" |awk '{print \$4}'\`
        echo \$result
        ;;
    Com_rollback)
        result=\`\${CONNECTION} extended-status 2>/dev/null |grep -w "Com_rollback" |awk '{print \$4}'\`
        echo \$result
        ;;
    Com_insert)
        result=\`\${CONNECTION} extended-status 2>/dev/null |grep -w "Com_insert" |awk '{print \$4}'\`
        echo \$result
        ;;
    Com_delete)
        result=\`\${CONNECTION} extended-status 2>/dev/null |grep -w "Com_delete" |awk '{print \$4}'\`
        echo \$result
        ;;
    Com_commit)
        result=\`\${CONNECTION} extended-status 2>/dev/null |grep -w "Com_commit" |awk '{print \$4}'\`
        echo \$result
        ;;
    Bytes_sent)
        result=\`\${CONNECTION} extended-status 2>/dev/null |grep -w "Bytes_sent" |awk '{print \$4}'\`
        echo \$result
        ;;
    Bytes_received)
        result=\`\${CONNECTION} extended-status 2>/dev/null |grep -w "Bytes_received" |awk '{print \$4}'\`
        echo \$result
        ;;
    Com_begin)
        result=\`\${CONNECTION} extended-status 2>/dev/null |grep -w "Com_begin" |awk '{print \$4}'\`
        echo \$result
        ;;
    *)
        echo "Usage:\$0(Uptime|Questions|Com_update|Slow_queries|Com_select|Com_rollback|Com_insert|Com_delete|Com_commit|Bytes_sent|Bytes_received|Com_begin)"
        ;;
esac
EOF
    chmod +x /etc/zabbix/script/mysql_status.sh
}

#配置agent mysql配置文件
function Config {
cp /etc/zabbix/zabbix_agentd.conf{,back}
cat >>/etc/zabbix/zabbix_agentd.conf<<EOF
# 获取MySQL性能指标，这个是上一步编辑好的脚本
UserParameter=mysql.status[*],/etc/zabbix/script/mysql_status.sh \$1
# 获取MySQL运行状态
UserParameter=mysql.ping,mysqladmin -u $USER -h $HOST -p$PASSWORD ping 2>/dev/null | grep -c alive
# 获取MySQL版本
UserParameter=mysql.version,mysql -V
#慢查询sql数量
UserParameter=mysql.slow,export MYSQL_PWD=$PASSWORD;mysql -uzabbix -h$HOST -P3306 -sN -e "select count(1) from information_schema.innodb_trx ORDER BY trx_started asc;"
EOF
}

function main {
#创建脚本存放路径
mkdir /etc/zabbix/script
MySQLInfo
MySQLScript
Config
#重启zabbix-agent
service zabbix-agent restart
setenforce 0
}

main