Zabbix自动监控MySQL多实例配置

本人在工作中一般喜欢把MySQL、Redis、Memcached、MongoDB等数据库按照实例的方式对外提供服务。一般都是一台高配的服务器上开启多个实例给每个业务使用。而监控是重中之重，我自己也尝试了多种监控方式，但对我来说感觉最简单最快的就是使用zabbix了，灵活定义key。

由于我是多实例，所以就需要用到zabbix的自动发现功能（LLD）。基本处理方式就是：

1、写自动发现脚本。

2、写状态取值脚本。

3、添加配置文件。

4、添加权限。

5、配置zabbix web。

一、写自动发现脚本
$ cat /etc/zabbix/zabbix_agentd.d/scripts/mysql_discovery.py
1

$ cat /etc/zabbix/zabbix_agentd.d/scripts/mysql_discovery.py

port_info = []
ports = []

for port in t.readlines():
    r = os.path.basename(port.strip())
    if r:
        port_info.append(r)

for port in s.readlines():
    r = os.path.basename(port.strip())
    if r:
        port_info.append(r)

port_info = list(set(port_info))

for port in port_info:
ports += [{'{#MYSQLPORT}':port}]
print(json.dumps({'data':ports},sort_keys=True,indent=4,separators=(',',':')))

#!/usr/bin/env python
import os
import json
t=os.popen("""sudo netstat -nltp|grep -w "mysqld"|grep -w "LISTEN"|grep -v grep|grep -v '^$'|awk -F: '{print $4}'""")
s=os.popen("""sudo netstat -nltp|grep -w "mysqld"|grep -w "LISTEN"|grep -v grep|grep -v '^$'|awk -F: '{print $2}'|awk '{print $1}'""")

port_info = []
ports = []

for port in t.readlines():
    r = os.path.basename(port.strip())
    if r:
        port_info.append(r)

for port in s.readlines():
    r = os.path.basename(port.strip())
    if r:
        port_info.append(r)

port_info = list(set(port_info))

for port in port_info:
    ports += [{'{#MYSQLPORT}':port}]
print(json.dumps({'data':ports},sort_keys=True,indent=4,separators=(',',':')))

执行脚本看输出结果（最好使用zabbix用户执行，才能看出效果）：

$ sudo -u zabbix /usr/bin/python /etc/zabbix/zabbix_agentd.d/scripts/mysql_discovery.py
{
    "data":[
        {
            "{#MYSQLPORT}":"3306"
        },
        {
            "{#MYSQLPORT}":"3307"
        }
}

我这个脚本中使用了sudo权限，zabbix用户在执行netstat时需要sudo权限。

另外就是，不同环境可能netstat看到的形式不同，根据自己的环境做awk切割即可。我这里写了两种，如果你有其他方式追加就行了，然后做一个聚合操作。

二、写状态取值脚本

MASTER
#!/bin/bash
#
#Auth: Pengdongwen
#Blog: www.ywnds.com
#Desc: mysql status monitoring
#dependent:
# 1)python mysql_discovery.py
# 2)grant select, replication slave, replication client on *.* to 'monitoruser'@'%' identified by '123456';
#########################

source /etc/profile

MYSQL_HOST="localhost"
MYSQL_USER="monitoruser"
MYSQL_PWD="123456"

if [ $# -lt 2 ];then
echo "please set argument"
exit 1
fi

CMD="mysql -h${MYSQL_HOST} -P$1 -u${MYSQL_USER} -p${MYSQL_PWD}"
result=`$CMD -e "show global status;" 2> /dev/null | grep -w "$2" | awk '{print $2}'`

case $2 in
    Slaves_connected)
        if [ -z $result ];then echo 0; else echo $result; fi
        ;;
    Uptime)
        if [ -z $result ];then echo 0; else echo $result; fi
        ;;
    Slow_queries)
        if [ -z $result ];then echo 0; else echo $result; fi
        ;;
    Queries)
        if [ -z $result ];then echo 0; else echo $result; fi
        ;;
    Questions)
        if [ -z $result ];then echo 0; else echo $result; fi
        ;;
    Flush_commands)
        if [ -z $result ];then echo 0; else echo $result; fi
        ;;
    Open_tables)
        if [ -z $result ];then echo 0; else echo $result; fi
        ;;
    Threads_connected)
        if [ -z $result ];then echo 0; else echo $result; fi
        ;;
    Threads_cached)
        if [ -z $result ];then echo 0; else echo $result; fi
        ;;
    Threads_running)
        if [ -z $result ];then echo 0; else echo $result; fi
        ;;
    Threads_created)
        if [ -z $result ];then echo 0; else echo $result; fi
        ;;
    Max_used_connections)
        if [ -z $result ];then echo 0; else echo $result; fi
        ;;
    Com_update)
        if [ -z $result ];then echo 0; else echo $result; fi
        ;;
    Com_select)
        if [ -z $result ];then echo 0; else echo $result; fi
        ;;
    Com_rollback)
        if [ -z $result ];then echo 0; else echo $result; fi
        ;;
    Com_insert)
        if [ -z $result ];then echo 0; else echo $result; fi
        ;;
    Com_delete)
        if [ -z $result ];then echo 0; else echo $result; fi
        ;;
    Com_commit)
        if [ -z $result ];then echo 0; else echo $result; fi
        ;;
    Com_begin)
        if [ -z $result ];then echo 0; else echo $result; fi
        ;;
    Com_lock_tables)
        if [ -z $result ];then echo 0; else echo $result; fi
        ;;
    Table_locks_immediate)
        if [ -z $result ];then echo 0; else echo $result; fi
        ;;
    Bytes_sent)
        if [ -z $result ];then echo 0; else echo $result; fi
        ;;
    Bytes_received)
        if [ -z $result ];then echo 0; else echo $result; fi
        ;;
    Innodb_buffer_pool_pages_data)
        if [ -z $result ];then echo 0; else echo $result; fi
        ;;
    Innodb_buffer_pool_bytes_data)
        if [ -z $result ];then echo 0; else echo $result; fi
        ;;
    Innodb_buffer_pool_pages_dirty)
        if [ -z $result ];then echo 0; else echo $result; fi
        ;;
    Innodb_buffer_pool_pages_flushed)
        if [ -z $result ];then echo 0; else echo $result; fi
        ;;
    Innodb_buffer_pool_pages_free)
        if [ -z $result ];then echo 0; else echo $result; fi
        ;;
    Innodb_buffer_pool_pages_latched)
        if [ -z $result ];then echo 0; else echo $result; fi
        ;;
    Innodb_buffer_pool_pages_misc)
        if [ -z $result ];then echo 0; else echo $result; fi
        ;;
    Innodb_buffer_pool_pages_total)
        if [ -z $result ];then echo 0; else echo $result; fi
        ;;
    Innodb_page_size)
        if [ -z $result ];then echo 0; else echo $result; fi
        ;;
    Innodb_pages_created)
        if [ -z $result ];then echo 0; else echo $result; fi
        ;;
    Innodb_pages_read)
        if [ -z $result ];then echo 0; else echo $result; fi
        ;;
    Innodb_pages_written)
        if [ -z $result ];then echo 0; else echo $result; fi
        ;;
    Innodb_row_lock_current_waits)
        if [ -z $result ];then echo 0; else echo $result; fi
        ;;
    Innodb_row_lock_time)
        if [ -z $result ];then echo 0; else echo $result; fi
        ;;
    Innodb_row_lock_time_avg)
        if [ -z $result ];then echo 0; else echo $result; fi
        ;;
    Innodb_row_lock_time_max)
        if [ -z $result ];then echo 0; else echo $result; fi
        ;;
    Innodb_rows_deleted)
        if [ -z $result ];then echo 0; else echo $result; fi
        ;;
    Innodb_rows_inserted)
        if [ -z $result ];then echo 0; else echo $result; fi
        ;;
    Innodb_rows_read)
        if [ -z $result ];then echo 0; else echo $result; fi
        ;;
    Innodb_rows_updated)
        if [ -z $result ];then echo 0; else echo $result; fi
        ;;
    Innodb_buffer_pool_write_requests)
        if [ -z $result ];then echo 0; else echo $result; fi
        ;;
    Innodb_buffer_pool_read_requests)
        if [ -z $result ];then echo 0; else echo $result; fi
        ;;
    Waiting_lock)
        result=`$CMD -e "select state from information_schema.processlist;" 2> /dev/null | grep "Waiting.*lock" | wc -l`
        if [ -z $result ];then echo 0; else echo $result; fi
        ;;
    Transaction)
        result=`$CMD -e "select count(time) as count from information_schema.processlist where COMMAND!='Binlog Dump' and COMMAND!='Sleep' and COMMAND!='Connect' and Time>=30;" 2> /dev/null -BN`
        if [ -z $result ];then echo 0; else echo $result; fi
        ;;
    *)
        echo "Usage:$0 arguments"
    ;;
esac

#!/bin/bash
#
#Auth: Pengdongwen
#Blog: www.ywnds.com
#Desc: mysql status monitoring
#dependent:
# 1)python mysql_discovery.py
# 2)grant select, replication slave, replication client on *.* to 'monitoruser'@'%' identified by '123456';
#########################

source /etc/profile

MYSQL_HOST="localhost"
MYSQL_USER="monitoruser"
MYSQL_PWD="123456"

if [ $# -lt 2 ];then
    echo "please set argument"
    exit 1
fi

CMD="mysql -h${MYSQL_HOST} -P$1 -u${MYSQL_USER} -p${MYSQL_PWD}"
result=`$CMD -e "show global status;" 2> /dev/null | grep -w "$2" | awk '{print $2}'`

case $2 in
    Slaves_connected)
        if [ -z $result ];then echo 0; else echo $result; fi
        ;;
    Uptime)
        if [ -z $result ];then echo 0; else echo $result; fi
        ;;
    Slow_queries)
        if [ -z $result ];then echo 0; else echo $result; fi
        ;;
    Queries)
        if [ -z $result ];then echo 0; else echo $result; fi
        ;;
    Questions)
        if [ -z $result ];then echo 0; else echo $result; fi
        ;;
    Flush_commands)
        if [ -z $result ];then echo 0; else echo $result; fi
        ;;
    Open_tables)
        if [ -z $result ];then echo 0; else echo $result; fi
        ;;
    Threads_connected)
        if [ -z $result ];then echo 0; else echo $result; fi
        ;;
    Threads_cached)
        if [ -z $result ];then echo 0; else echo $result; fi
        ;;
    Threads_running)
        if [ -z $result ];then echo 0; else echo $result; fi
        ;;
    Threads_created)
        if [ -z $result ];then echo 0; else echo $result; fi
        ;;
    Max_used_connections)
        if [ -z $result ];then echo 0; else echo $result; fi
        ;;
    Com_update)
        if [ -z $result ];then echo 0; else echo $result; fi
        ;;
    Com_select)
        if [ -z $result ];then echo 0; else echo $result; fi
        ;;
    Com_rollback)
        if [ -z $result ];then echo 0; else echo $result; fi
        ;;
    Com_insert)
        if [ -z $result ];then echo 0; else echo $result; fi
        ;;
    Com_delete)
        if [ -z $result ];then echo 0; else echo $result; fi
        ;;
    Com_commit)
        if [ -z $result ];then echo 0; else echo $result; fi
        ;;
    Com_begin)
        if [ -z $result ];then echo 0; else echo $result; fi
        ;;
    Com_lock_tables)
        if [ -z $result ];then echo 0; else echo $result; fi
        ;;
    Table_locks_immediate)
        if [ -z $result ];then echo 0; else echo $result; fi
        ;;
    Bytes_sent)
        if [ -z $result ];then echo 0; else echo $result; fi
        ;;
    Bytes_received)
        if [ -z $result ];then echo 0; else echo $result; fi
        ;;
    Innodb_buffer_pool_pages_data)
        if [ -z $result ];then echo 0; else echo $result; fi
        ;;
    Innodb_buffer_pool_bytes_data)
        if [ -z $result ];then echo 0; else echo $result; fi
        ;;
    Innodb_buffer_pool_pages_dirty)
        if [ -z $result ];then echo 0; else echo $result; fi
        ;;
    Innodb_buffer_pool_pages_flushed)
        if [ -z $result ];then echo 0; else echo $result; fi
        ;;
    Innodb_buffer_pool_pages_free)
        if [ -z $result ];then echo 0; else echo $result; fi
        ;;
    Innodb_buffer_pool_pages_latched)
        if [ -z $result ];then echo 0; else echo $result; fi
        ;;
    Innodb_buffer_pool_pages_misc)
        if [ -z $result ];then echo 0; else echo $result; fi
        ;;
    Innodb_buffer_pool_pages_total)
        if [ -z $result ];then echo 0; else echo $result; fi
        ;;
    Innodb_page_size)
        if [ -z $result ];then echo 0; else echo $result; fi
        ;;
    Innodb_pages_created)
        if [ -z $result ];then echo 0; else echo $result; fi
        ;;
    Innodb_pages_read)
        if [ -z $result ];then echo 0; else echo $result; fi
        ;;
    Innodb_pages_written)
        if [ -z $result ];then echo 0; else echo $result; fi
        ;;
    Innodb_row_lock_current_waits)
        if [ -z $result ];then echo 0; else echo $result; fi
        ;;
    Innodb_row_lock_time)
        if [ -z $result ];then echo 0; else echo $result; fi
        ;;
    Innodb_row_lock_time_avg)
        if [ -z $result ];then echo 0; else echo $result; fi
        ;;
    Innodb_row_lock_time_max)
        if [ -z $result ];then echo 0; else echo $result; fi
        ;;
    Innodb_rows_deleted)
        if [ -z $result ];then echo 0; else echo $result; fi
        ;;
    Innodb_rows_inserted)
        if [ -z $result ];then echo 0; else echo $result; fi
        ;;
    Innodb_rows_read)
        if [ -z $result ];then echo 0; else echo $result; fi
        ;;
    Innodb_rows_updated)
        if [ -z $result ];then echo 0; else echo $result; fi
        ;;
    Innodb_buffer_pool_write_requests)
        if [ -z $result ];then echo 0; else echo $result; fi
        ;;
    Innodb_buffer_pool_read_requests)
        if [ -z $result ];then echo 0; else echo $result; fi
        ;;
    Waiting_lock)
        result=`$CMD -e "select state from information_schema.processlist;" 2> /dev/null | grep "Waiting.*lock" | wc -l`
        if [ -z $result ];then echo 0; else echo $result; fi
        ;;
    Transaction)
        result=`$CMD -e "select count(time) as count from information_schema.processlist where COMMAND!='Binlog Dump' and COMMAND!='Sleep' and COMMAND!='Connect' and Time>=30;" 2> /dev/null -BN`
        if [ -z $result ];then echo 0; else echo $result; fi
        ;;
    *)
        echo "Usage:$0 arguments"
    ;;
esac

SLAVE
#!/bin/bash
#
#Auth: Pengdongwen
#Blog: www.ywnds.com
#Desc: mysql slave status monitoring
#dependent:
# 1)python mysql_discovery.py
# 2)grant select, super, replication slave, replication client on *.* to 'monitoruser'@'localhost' identified by '123456';
#########################

MYSQL_HOST="localhost"
MYSQL_USER="monitoruser"
MYSQL_PWD="123456"

if [ $# -lt 2 ];then
echo "please set argument"
exit 1
fi

CMD="mysql -h$MYSQL_HOST -P$1 -u$MYSQL_USER -p$MYSQL_PWD"
result=`$CMD -e "show global status;" 2> /dev/null | grep -w "$2" | awk '{print $2}'`

case $2 in
    Slaves_connected)
        if [ -z $result ];then echo 0; else echo $result; fi
        ;;
    Uptime)
        if [ -z $result ];then echo 0; else echo $result; fi
        ;;
    Slow_queries)
        if [ -z $result ];then echo 0; else echo $result; fi
        ;;
    Queries)
        if [ -z $result ];then echo 0; else echo $result; fi
        ;;
    Questions)
        if [ -z $result ];then echo 0; else echo $result; fi
        ;;
    Flush_commands)
        if [ -z $result ];then echo 0; else echo $result; fi
        ;;
    Open_tables)
        if [ -z $result ];then echo 0; else echo $result; fi
        ;;
    Threads_connected)
        if [ -z $result ];then echo 0; else echo $result; fi
        ;;
    Threads_cached)
        if [ -z $result ];then echo 0; else echo $result; fi
        ;;
    Threads_running)
        if [ -z $result ];then echo 0; else echo $result; fi
        ;;
    Threads_created)
        if [ -z $result ];then echo 0; else echo $result; fi
        ;;
    Max_used_connections)
        if [ -z $result ];then echo 0; else echo $result; fi
        ;;
    Com_update)
        if [ -z $result ];then echo 0; else echo $result; fi
        ;;
    Com_select)
        if [ -z $result ];then echo 0; else echo $result; fi
        ;;
    Com_rollback)
        if [ -z $result ];then echo 0; else echo $result; fi
        ;;
    Com_insert)
        if [ -z $result ];then echo 0; else echo $result; fi
        ;;
    Com_delete)
        if [ -z $result ];then echo 0; else echo $result; fi
        ;;
    Com_commit)
        if [ -z $result ];then echo 0; else echo $result; fi
        ;;
    Com_begin)
        if [ -z $result ];then echo 0; else echo $result; fi
        ;;
    Com_lock_tables)
        if [ -z $result ];then echo 0; else echo $result; fi
        ;;
    Max_used_connections)
        if [ -z $result ];then echo 0; else echo $result; fi
        ;;
    Bytes_sent)
        if [ -z $result ];then echo 0; else echo $result; fi
        ;;
    Bytes_received)
        if [ -z $result ];then echo 0; else echo $result; fi
        ;;
    Table_locks_immediate)
        if [ -z $result ];then echo 0; else echo $result; fi
        ;;
    Innodb_buffer_pool_pages_data)
        if [ -z $result ];then echo 0; else echo $result; fi
        ;;
    Innodb_buffer_pool_pages_dirty)
        if [ -z $result ];then echo 0; else echo $result; fi
        ;;
    Innodb_buffer_pool_pages_flushed)
        if [ -z $result ];then echo 0; else echo $result; fi
        ;;
    Innodb_buffer_pool_pages_free)
        if [ -z $result ];then echo 0; else echo $result; fi
        ;;
    Innodb_buffer_pool_pages_latched)
        if [ -z $result ];then echo 0; else echo $result; fi
        ;;
    Innodb_buffer_pool_pages_misc)
        if [ -z $result ];then echo 0; else echo $result; fi
        ;;
    Innodb_buffer_pool_pages_total)
        if [ -z $result ];then echo 0; else echo $result; fi
        ;;
    Innodb_page_size)
        if [ -z $result ];then echo 0; else echo $result; fi
        ;;
    Innodb_pages_created)
        if [ -z $result ];then echo 0; else echo $result; fi
        ;;
    Innodb_pages_read)
        if [ -z $result ];then echo 0; else echo $result; fi
        ;;
    Innodb_pages_written)
        if [ -z $result ];then echo 0; else echo $result; fi
        ;;
    Innodb_row_lock_current_waits)
        if [ -z $result ];then echo 0; else echo $result; fi
        ;;
    Innodb_row_lock_time)
        if [ -z $result ];then echo 0; else echo $result; fi
        ;;
    Innodb_row_lock_time_avg)
        if [ -z $result ];then echo 0; else echo $result; fi
        ;;
    Innodb_row_lock_time_max)
        if [ -z $result ];then echo 0; else echo $result; fi
        ;;
    Innodb_rows_deleted)
        if [ -z $result ];then echo 0; else echo $result; fi
        ;;
    Innodb_rows_inserted)
        if [ -z $result ];then echo 0; else echo $result; fi
        ;;
    Innodb_rows_read)
        if [ -z $result ];then echo 0; else echo $result; fi
        ;;
    Innodb_rows_updated)
        if [ -z $result ];then echo 0; else echo $result; fi
        ;;
    Innodb_buffer_pool_write_requests)
        if [ -z $result ];then echo 0; else echo $result; fi
        ;;
    Innodb_buffer_pool_read_requests)
        if [ -z $result ];then echo 0; else echo $result; fi
        ;;
    Slave_IO_Running)
        result=`$CMD -e "show slave status\G" 2> /dev/null | grep -w "Slave_IO_Running" | awk '{print $2}'`
        if [ -z "${result}" ] || [ "${result}" == "No" ];then echo 0; else echo 1; fi
        ;;
    Slave_SQL_Running)
        result=`$CMD -e "show slave status\G" 2> /dev/null | grep -w "Slave_SQL_Running" | awk '{print $2}'`
        if [ -z "${result}" ] || [ "${result}" == "No" ];then echo 0; else echo 1; fi
        ;;
    Seconds_Behind_Master)
        result=`$CMD -e "show slave status\G" 2> /dev/null | grep -w "Seconds_Behind_Master" | awk '{print $2}'`
        if [ -z $result ];then echo 0; else echo $result; fi
        ;;
    Auto_Position)
        result=`$CMD -e "show slave status\G" 2> /dev/null | grep -w "Auto_Position" | awk '{print $2}'`
        if [ -z $result ];then echo 0; else echo $result; fi
        ;;
    read_only)
        result=`$CMD -e "show global variables like 'read_only'\G" 2> /dev/null | grep -w "read_only" | awk '{print $2}'`
        if [ $result == "ON" ];then echo 1; else echo 0; fi
        ;;
    Waiting_lock)
        result=`$CMD -e "select state from information_schema.processlist;" 2> /dev/null | grep "Waiting.*lock" | wc -l`
        if [ -z $result ];then echo 0; else echo $result; fi
        ;;
    *)
        echo "Usage:$0 arguments"
    ;;
esac

#!/bin/bash
#
#Auth: Pengdongwen
#Blog: www.ywnds.com
#Desc: mysql slave status monitoring
#dependent:
# 1)python mysql_discovery.py
# 2)grant select, super, replication slave, replication client on *.* to 'monitoruser'@'localhost' identified by '123456';
#########################

MYSQL_HOST="localhost"
MYSQL_USER="monitoruser"
MYSQL_PWD="123456"

if [ $# -lt 2 ];then
    echo "please set argument"
    exit 1
fi

CMD="mysql -h$MYSQL_HOST -P$1 -u$MYSQL_USER -p$MYSQL_PWD"
result=`$CMD -e "show global status;" 2> /dev/null | grep -w "$2" | awk '{print $2}'`

case $2 in
    Slaves_connected)
        if [ -z $result ];then echo 0; else echo $result; fi
        ;;
    Uptime)
        if [ -z $result ];then echo 0; else echo $result; fi
        ;;
    Slow_queries)
        if [ -z $result ];then echo 0; else echo $result; fi
        ;;
    Queries)
        if [ -z $result ];then echo 0; else echo $result; fi
        ;;
    Questions)
        if [ -z $result ];then echo 0; else echo $result; fi
        ;;
    Flush_commands)
        if [ -z $result ];then echo 0; else echo $result; fi
        ;;
    Open_tables)
        if [ -z $result ];then echo 0; else echo $result; fi
        ;;
    Threads_connected)
        if [ -z $result ];then echo 0; else echo $result; fi
        ;;
    Threads_cached)
        if [ -z $result ];then echo 0; else echo $result; fi
        ;;
    Threads_running)
        if [ -z $result ];then echo 0; else echo $result; fi
        ;;
    Threads_created)
        if [ -z $result ];then echo 0; else echo $result; fi
        ;;
    Max_used_connections)
        if [ -z $result ];then echo 0; else echo $result; fi
        ;;
    Com_update)
        if [ -z $result ];then echo 0; else echo $result; fi
        ;;
    Com_select)
        if [ -z $result ];then echo 0; else echo $result; fi
        ;;
    Com_rollback)
        if [ -z $result ];then echo 0; else echo $result; fi
        ;;
    Com_insert)
        if [ -z $result ];then echo 0; else echo $result; fi
        ;;
    Com_delete)
        if [ -z $result ];then echo 0; else echo $result; fi
        ;;
    Com_commit)
        if [ -z $result ];then echo 0; else echo $result; fi
        ;;
    Com_begin)
        if [ -z $result ];then echo 0; else echo $result; fi
        ;;
    Com_lock_tables)
        if [ -z $result ];then echo 0; else echo $result; fi
        ;;
    Max_used_connections)
        if [ -z $result ];then echo 0; else echo $result; fi
        ;;
    Bytes_sent)
        if [ -z $result ];then echo 0; else echo $result; fi
        ;;
    Bytes_received)
        if [ -z $result ];then echo 0; else echo $result; fi
        ;;
    Table_locks_immediate)
        if [ -z $result ];then echo 0; else echo $result; fi
        ;;
    Innodb_buffer_pool_pages_data)
        if [ -z $result ];then echo 0; else echo $result; fi
        ;;
    Innodb_buffer_pool_pages_dirty)
        if [ -z $result ];then echo 0; else echo $result; fi
        ;;
    Innodb_buffer_pool_pages_flushed)
        if [ -z $result ];then echo 0; else echo $result; fi
        ;;
    Innodb_buffer_pool_pages_free)
        if [ -z $result ];then echo 0; else echo $result; fi
        ;;
    Innodb_buffer_pool_pages_latched)
        if [ -z $result ];then echo 0; else echo $result; fi
        ;;
    Innodb_buffer_pool_pages_misc)
        if [ -z $result ];then echo 0; else echo $result; fi
        ;;
    Innodb_buffer_pool_pages_total)
        if [ -z $result ];then echo 0; else echo $result; fi
        ;;
    Innodb_page_size)
        if [ -z $result ];then echo 0; else echo $result; fi
        ;;
    Innodb_pages_created)
        if [ -z $result ];then echo 0; else echo $result; fi
        ;;
    Innodb_pages_read)
        if [ -z $result ];then echo 0; else echo $result; fi
        ;;
    Innodb_pages_written)
        if [ -z $result ];then echo 0; else echo $result; fi
        ;;
    Innodb_row_lock_current_waits)
        if [ -z $result ];then echo 0; else echo $result; fi
        ;;
    Innodb_row_lock_time)
        if [ -z $result ];then echo 0; else echo $result; fi
        ;;
    Innodb_row_lock_time_avg)
        if [ -z $result ];then echo 0; else echo $result; fi
        ;;
    Innodb_row_lock_time_max)
        if [ -z $result ];then echo 0; else echo $result; fi
        ;;
    Innodb_rows_deleted)
        if [ -z $result ];then echo 0; else echo $result; fi
        ;;
    Innodb_rows_inserted)
        if [ -z $result ];then echo 0; else echo $result; fi
        ;;
    Innodb_rows_read)
        if [ -z $result ];then echo 0; else echo $result; fi
        ;;
    Innodb_rows_updated)
        if [ -z $result ];then echo 0; else echo $result; fi
        ;;
    Innodb_buffer_pool_write_requests)
        if [ -z $result ];then echo 0; else echo $result; fi
        ;;
    Innodb_buffer_pool_read_requests)
        if [ -z $result ];then echo 0; else echo $result; fi
        ;;
    Slave_IO_Running)
        result=`$CMD -e "show slave status\G" 2> /dev/null | grep -w "Slave_IO_Running" | awk '{print $2}'`
        if [ -z "${result}" ] || [ "${result}" == "No" ];then echo 0; else echo 1; fi
        ;;
    Slave_SQL_Running)
        result=`$CMD -e "show slave status\G" 2> /dev/null | grep -w "Slave_SQL_Running" | awk '{print $2}'`
        if [ -z "${result}" ] || [ "${result}" == "No" ];then echo 0; else echo 1; fi
        ;;
    Seconds_Behind_Master)
        result=`$CMD -e "show slave status\G" 2> /dev/null | grep -w "Seconds_Behind_Master" | awk '{print $2}'`
        if [ -z $result ];then echo 0; else echo $result; fi
        ;;
    Auto_Position)
        result=`$CMD -e "show slave status\G" 2> /dev/null | grep -w "Auto_Position" | awk '{print $2}'`
        if [ -z $result ];then echo 0; else echo $result; fi
        ;;
    read_only)
        result=`$CMD -e "show global variables like 'read_only'\G" 2> /dev/null | grep -w "read_only" | awk '{print $2}'`
        if [ $result == "ON" ];then echo 1; else echo 0; fi
        ;;
    Waiting_lock)
        result=`$CMD -e "select state from information_schema.processlist;" 2> /dev/null | grep "Waiting.*lock" | wc -l`
        if [ -z $result ];then echo 0; else echo $result; fi
        ;;
    *)
        echo "Usage:$0 arguments"
    ;;
esac

脚本很简单，需要传给脚本两个参数，一个是端口号，另一个是监控值。

三、添加配置文件

$ cat /etc/zabbix/zabbix_agentd.d/userparameter_mysql.conf
UserParameter=mysql.discovery[*],python /etc/zabbix/zabbix_agentd.d/scripts/mysql_discovery.py
UserParameter=mysql[*],/bin/bash /etc/zabbix/zabbix_agentd.d/scripts/mysql_master_status.sh $1 $2
UserParameter=mysql.slave[*],/bin/bash /etc/zabbix/zabbix_agentd.d/scripts/mysql_slave_status.sh $1 $2

这里定义三个key，第一个key是用于自动发现的。第二个key是用于取不同实例（master）的状态值的，第三个key是用于取不同实例（slave）的状态值，传了两个参数，$1是端口号（从自动发现中获取的），第二个是传的参数。端口号和参数我会在zabbix页面配置传给mysql[*]这个key。

都配置完后就可以添加重启一下zabbix-agent了。

四、添加权限

上面说了，需要添加一个mysql_zabbix用户。
mysql> grant select, process, super, replication slave, replication client on *.* to 'mysql_zabbix'@'localhost' identified by '123456';
mysql> flush privileges;
1
2

mysql> grant select, process, super, replication slave, replication client on *.* to 'mysql_zabbix'@'localhost' identified by '123456';
mysql> flush privileges;

这几个权限是最低权限了，super是用来在从库执行”show slave stauts;”命令的。

需要给zabbix用户添加sudo权限。
$ cat /etc/sudoers.d/zabbix
Defaults:zabbix !requiretty
zabbix ALL=(ALL) NOPASSWD: SUPERVISORCTLZB
Cmnd_Alias SUPERVISORCTLZB = /sbin/ss,/usr/sbin/ss,/sbin/dmidecode,/usr/sbin/dmidecode,/sbin/service,/usr/sbin/service,/bin/netstat

$ cat /etc/sudoers.d/zabbix
Defaults:zabbix !requiretty
zabbix ALL=(ALL) NOPASSWD: SUPERVISORCTLZB
Cmnd_Alias SUPERVISORCTLZB = /sbin/ss,/usr/sbin/ss,/sbin/dmidecode,/usr/sbin/dmidecode,/sbin/service,/usr/sbin/service,/bin/netstat

另外需要注意的是，普通用户zabbix默认环境变量有如下这些：
$ echo $PATH
/usr/local/bin:/bin:/usr/bin:/usr/local/sbin:/usr/sbin

$ echo $PATH
/usr/local/bin:/bin:/usr/bin:/usr/local/sbin:/usr/sbin

所以你要确认你所有的执行程序都在这些路径下，不然zabbix是获取不到值的。看我的mysql客户端路径：
$ which mysql
/usr/bin/mysql

$ which mysql
/usr/bin/mysql

很多人喜欢把MySQL安装在其他路径，比如/usr/local/mysql下，然后使用export把执行路径追加到$PATH变量后，那么如果这样的话，zabbix就无法获取到值的，所以注意不要栽在这个上面。做一个软连接就可以解决了。
$ ln -sv /usr/local/mysql/bin/* /usr/local/bin/

$ ln -sv /usr/local/mysql/bin/* /usr/local/bin/

使用zabbix用户执行看是否正常。
$ sudo -u zabbix `which zabbix_agentd` -t mysql.discovery[*]
{
"data":[
{
"{#MYSQLPORT}":"3306"
},
{
"{#MYSQLPORT}":"3307"
}
}

$ sudo -u zabbix `which zabbix_agentd` -t mysql.discovery[*]
{
"data":[
{
"{#MYSQLPORT}":"3306"
},
{
"{#MYSQLPORT}":"3307"
}
}

然后获取一个值，测试是否运行正常：
$ sudo -u zabbix `which zabbix_agentd` -t mysql[3306,Queries]
mysql[3306,Queries] [t|79]

$ sudo -u zabbix `which zabbix_agentd` -t mysql[3306,Queries]
mysql[3306,Queries]                           [t|79]

如果自动发现没有问题，但是获取不到值，就要判断是否是相关命令的环境变量有问题，有一些环境变量路径zabbix。

五、配置zabbix web

前期工作都做完了，下面就可以配置zabbix web了。

首先创建一个模板（Template Linux MySQL Master Discovery），然后在模板中创建一个自动发现规则（Linux MySQL Discovery）。

在这个自动发现规则内创建多个item，我这里就给一个样例。

这个item包括Name定义、Key怎么定义、Type选项，Application定义，以及保存时间；最后一个比较重要的就是Store value，常用的有两种，一种是取出来的值是什么就存储为什么，第二种就是取每秒钟的差值（本次取值减去上次取值的差除以60，得到每秒钟的速率），这是因为我们从MySQL状态变量取来的值有些是累积值，利用zabbix这个功能就可以得到比如每秒钟的QPS/TPS等。也不是说每个值都需要这么取每秒速率，比如MySQL连接相关的状态变量就需要当前值。

创建完item后，接着就可以创建trigger了，比如下面我创建一个检查MySQL是否宕机的trigger。

更多关于zabbix的配置还是需要自己慢慢摸索。

来源：http://www.ywnds.com/?p=7203

Zabbix自动监控MySQL多实例配置

Zabbix自动监控MySQL多实例配置

相关推荐