使用zabbix监控阿里云数据库

阿里云数据库已经提供了很好的web管理界面，不过如果想通过zabbix进行集中管理，并对自定义异常实现监控报警，同时保留监控历史，就有必要使用zabbix的自动发现功能添加数据库的实例ID到zabiix服务器。

阿里云提供了丰富的API可以方便地实现，python代码如下：

数据库发现脚本： discovery_rds.py

#coding=UTF-8
from aliyunsdkcore import client
from aliyunsdkrds.request.v20140815 import DescribeDBInstancesRequest
import json

ID = 'LTAIYx3YBW9tfGee'
Secret = 'NbXui0Wo5JNVtjUFKdkSp76xxxxxxx'
RegionId = 'cn-shanghai'

clt = client.AcsClient(ID,Secret,RegionId)

DBInstanceIdList = []
DBInstanceIdDict = {}
ZabbixDataDict = {}
def GetRdsList():
    RdsRequest = DescribeDBInstancesRequest.DescribeDBInstancesRequest()
    RdsRequest.set_accept_format('json')
    #RdsInfo = clt.do_action(RdsRequest)
    RdsInfo = clt.do_action_with_exception(RdsRequest)
    for RdsInfoJson in (json.loads(RdsInfo))['Items']['DBInstance']:
        DBInstanceIdDict = {}
        try:
            DBInstanceIdDict["{#DBINSTANCEID}"] = RdsInfoJson['DBInstanceId']
            print(RdsInfoJson['DBInstanceDescription'])
            DBInstanceIdDict["{#DBINSTANCEDESCRIPTION}"] = RdsInfoJson['DBInstanceDescription'].encode('utf-8').decode('unicode_escape')
            DBInstanceIdList.append(DBInstanceIdDict)
        except Exception as e:
            print(e)
            print ("Please check the RDS alias !Alias must not be the same as DBInstanceId！！！")



GetRdsList()
ZabbixDataDict['data'] = DBInstanceIdList
print (json.dumps(ZabbixDataDict))

数据库检查脚本：

'''
check_rds.py
coding=utf-8
'''
from aliyunsdkcore import client
from aliyunsdkrds.request.v20140815 import DescribeResourceUsageRequest,DescribeDBInstancePerformanceRequest
import json,sys,datetime

ID = 'LTAIYx3YBW9tfGee'
Secret = 'NbXui0Wo5JNVtjUFKdkSp76xxxxxxx'
RegionId = 'cn-shanghai'

clt = client.AcsClient(ID,Secret,RegionId)

Type = sys.argv[1]
DBInstanceId = sys.argv[2]
Key = sys.argv[3]

# 阿里云返回的数据为UTC时间，因此要转换为东八区时间。其他时区同理。
UTC_End = datetime.datetime.today() - datetime.timedelta(hours=8)
UTC_Start = UTC_End - datetime.timedelta(minutes=25)

StartTime = datetime.datetime.strftime(UTC_Start, '%Y-%m-%dT%H:%MZ')
EndTime = datetime.datetime.strftime(UTC_End, '%Y-%m-%dT%H:%MZ')

def GetResourceUsage(DBInstanceId,Key):
    ResourceUsage = DescribeResourceUsageRequest.DescribeResourceUsageRequest()
    ResourceUsage.set_accept_format('json')
    ResourceUsage.set_DBInstanceId(DBInstanceId)
    ResourceUsageInfo = clt.do_action_with_exception(ResourceUsage)
    #print ResourceUsageInfo
    Info = (json.loads(ResourceUsageInfo))[Key]
    print (Info)

def GetPerformance(DBInstanceId,MasterKey,IndexNum,StartTime,EndTime):
    Performance = DescribeDBInstancePerformanceRequest.DescribeDBInstancePerformanceRequest()
    Performance.set_accept_format('json')
    Performance.set_DBInstanceId(DBInstanceId)
    Performance.set_Key(MasterKey)
    Performance.set_StartTime(StartTime)
    Performance.set_EndTime(EndTime)
    PerformanceInfo = clt.do_action_with_exception(Performance)
    #print PerformanceInfo
    Info = (json.loads(PerformanceInfo))
    Value = Info['PerformanceKeys']['PerformanceKey'][0]['Values']['PerformanceValue'][-1]['Value']
    print (str(Value).split('&')[IndexNum])


if (Type == "Disk"):
    GetResourceUsage(DBInstanceId, Key)

elif (Type == "Performance"):

    #平均每秒钟的输入流量
    if (Key == "MySQL_NetworkTraffic_In"):
        IndexNum = 0
        MasterKey = "MySQL_NetworkTraffic"
        GetPerformance(DBInstanceId,MasterKey,IndexNum,StartTime,EndTime)

    #平均每秒钟的输出流量
    elif (Key == "MySQL_NetworkTraffic_Out"):
        IndexNum = 1
        MasterKey = "MySQL_NetworkTraffic"
        GetPerformance(DBInstanceId,MasterKey,IndexNum,StartTime,EndTime)

    #每秒SQL语句执行次数
    elif (Key == "MySQL_QPS"):
        IndexNum = 0
        MasterKey = "MySQL_QPSTPS"
        GetPerformance(DBInstanceId,MasterKey,IndexNum,StartTime,EndTime)

    #平均每秒事务数
    elif (Key == "MySQL_TPS"):
        IndexNum = 1
        MasterKey = "MySQL_QPSTPS"
        GetPerformance(DBInstanceId,MasterKey,IndexNum,StartTime,EndTime)

    #当前活跃连接数
    elif (Key == "MySQL_Sessions_Active"):
        MasterKey = "MySQL_Sessions"
        IndexNum = 0
        GetPerformance(DBInstanceId, MasterKey, IndexNum, StartTime, EndTime)

    #当前总连接数
    elif (Key == "MySQL_Sessions_Totle"):
        MasterKey = "MySQL_Sessions"
        IndexNum = 1
        GetPerformance(DBInstanceId, MasterKey, IndexNum, StartTime, EndTime)

    #InnoDB缓冲池的读命中率
    elif (Key == "ibuf_read_hit"):
        MasterKey = "MySQL_InnoDBBufferRatio"
        IndexNum = 0
        GetPerformance(DBInstanceId, MasterKey, IndexNum, StartTime, EndTime)

    #InnoDB缓冲池的利用率
    elif (Key == "ibuf_use_ratio"):
        MasterKey = "MySQL_InnoDBBufferRatio"
        IndexNum = 1
        GetPerformance(DBInstanceId, MasterKey, IndexNum, StartTime, EndTime)

    #InnoDB缓冲池脏块的百分率
    elif (Key == "ibuf_dirty_ratio"):
        MasterKey = "MySQL_InnoDBBufferRatio"
        IndexNum = 2
        GetPerformance(DBInstanceId, MasterKey, IndexNum, StartTime, EndTime)

    #InnoDB平均每秒钟读取的数据量
    elif (Key == "inno_data_read"):
        MasterKey = "MySQL_InnoDBDataReadWriten"
        IndexNum = 0
        GetPerformance(DBInstanceId, MasterKey, IndexNum, StartTime, EndTime)

    #InnoDB平均每秒钟写入的数据量
    elif (Key == "inno_data_written"):
        MasterKey = "MySQL_InnoDBDataReadWriten"
        IndexNum = 1
        GetPerformance(DBInstanceId, MasterKey, IndexNum, StartTime, EndTime)

    #平均每秒向InnoDB缓冲池的读次数
    elif (Key == "ibuf_request_r"):
        MasterKey = "MySQL_InnoDBLogRequests"
        IndexNum = 0
        GetPerformance(DBInstanceId, MasterKey, IndexNum, StartTime, EndTime)

    #平均每秒向InnoDB缓冲池的写次数
    elif (Key == "ibuf_request_w"):
        MasterKey = "MySQL_InnoDBLogRequests"
        IndexNum = 1
        GetPerformance(DBInstanceId, MasterKey, IndexNum, StartTime, EndTime)

    #平均每秒日志写请求数
    elif (Key == "Innodb_log_write_requests"):
        MasterKey = "MySQL_InnoDBLogWrites"
        IndexNum = 0
        GetPerformance(DBInstanceId, MasterKey, IndexNum, StartTime, EndTime)

    #平均每秒向日志文件的物理写次数
    elif (Key == "Innodb_log_writes"):
        MasterKey = "MySQL_InnoDBLogWrites"
        IndexNum = 1
        GetPerformance(DBInstanceId, MasterKey, IndexNum, StartTime, EndTime)

    #平均每秒向日志文件完成的fsync()写数量
    elif (Key == "Innodb_os_log_fsyncs"):
        MasterKey = "MySQL_InnoDBLogWrites"
        IndexNum = 2
        GetPerformance(DBInstanceId, MasterKey, IndexNum, StartTime, EndTime)

    #MySQL执行语句时在硬盘上自动创建的临时表的数量
    elif (Key == "tb_tmp_disk"):
        MasterKey = "MySQL_TempDiskTableCreates"
        IndexNum = 0
        GetPerformance(DBInstanceId, MasterKey, IndexNum, StartTime, EndTime)

    #MyISAM平均每秒Key Buffer利用率
    elif (Key == "Key_usage_ratio"):
        MasterKey = "MySQL_MyISAMKeyBufferRatio"
        IndexNum = 0
        GetPerformance(DBInstanceId, MasterKey, IndexNum, StartTime, EndTime)

    #MyISAM平均每秒Key Buffer读命中率
    elif (Key == "Key_read_hit_ratio"):
        MasterKey = "MySQL_MyISAMKeyBufferRatio"
        IndexNum = 1
        GetPerformance(DBInstanceId, MasterKey, IndexNum, StartTime, EndTime)

    #MyISAM平均每秒Key Buffer写命中率
    elif (Key == "Key_write_hit_ratio"):
        MasterKey = "MySQL_MyISAMKeyBufferRatio"
        IndexNum = 2
        GetPerformance(DBInstanceId, MasterKey, IndexNum, StartTime, EndTime)

    #MyISAM平均每秒钟从缓冲池中的读取次数
    elif (Key == "myisam_keyr_r"):
        MasterKey = "MySQL_MyISAMKeyReadWrites"
        IndexNum = 0
        GetPerformance(DBInstanceId, MasterKey, IndexNum, StartTime, EndTime)

    #MyISAM平均每秒钟从缓冲池中的写入次数
    elif (Key == "myisam_keyr_w"):
        MasterKey = "MySQL_MyISAMKeyReadWrites"
        IndexNum = 1
        GetPerformance(DBInstanceId, MasterKey, IndexNum, StartTime, EndTime)

    #MyISAM平均每秒钟从硬盘上读取的次数
    elif (Key == "myisam_keyr"):
        MasterKey = "MySQL_MyISAMKeyReadWrites"
        IndexNum = 2
        GetPerformance(DBInstanceId, MasterKey, IndexNum, StartTime, EndTime)

    #MyISAM平均每秒钟从硬盘上写入的次数
    elif (Key == "myisam_keyw"):
        MasterKey = "MySQL_MyISAMKeyReadWrites"
        IndexNum = 3
        GetPerformance(DBInstanceId, MasterKey, IndexNum, StartTime, EndTime)

    #平均每秒Delete语句执行次数
    elif (Key == "com_delete"):
        MasterKey = "MySQL_COMDML"
        IndexNum = 0
        GetPerformance(DBInstanceId, MasterKey, IndexNum, StartTime, EndTime)

    #平均每秒Insert语句执行次数
    elif (Key == "com_insert"):
        MasterKey = "MySQL_COMDML"
        IndexNum = 1
        GetPerformance(DBInstanceId, MasterKey, IndexNum, StartTime, EndTime)

    #平均每秒Insert_Select语句执行次数
    elif (Key == "com_insert_select"):
        MasterKey = "MySQL_COMDML"
        IndexNum = 2
        GetPerformance(DBInstanceId, MasterKey, IndexNum, StartTime, EndTime)

    #平均每秒Replace语句执行次数
    elif (Key == "com_replace"):
        MasterKey = "MySQL_COMDML"
        IndexNum = 3
        GetPerformance(DBInstanceId, MasterKey, IndexNum, StartTime, EndTime)

    #平均每秒Replace_Select语句执行次数
    elif (Key == "com_replace_select"):
        MasterKey = "MySQL_COMDML"
        IndexNum = 4
        GetPerformance(DBInstanceId, MasterKey, IndexNum, StartTime, EndTime)

    #平均每秒Select语句执行次数
    elif (Key == "com_select"):
        MasterKey = "MySQL_COMDML"
        IndexNum = 5
        GetPerformance(DBInstanceId, MasterKey, IndexNum, StartTime, EndTime)

    #平均每秒Update语句执行次数
    elif (Key == "com_update"):
        MasterKey = "MySQL_COMDML"
        IndexNum = 6
        GetPerformance(DBInstanceId, MasterKey, IndexNum, StartTime, EndTime)

    #平均每秒从InnoDB表读取的行数
    elif (Key == "inno_row_readed"):
        MasterKey = "MySQL_RowDML"
        IndexNum = 0
        GetPerformance(DBInstanceId, MasterKey, IndexNum, StartTime, EndTime)

    #平均每秒从InnoDB表更新的行数
    elif (Key == "inno_row_update"):
        MasterKey = "MySQL_RowDML"
        IndexNum = 1
        GetPerformance(DBInstanceId, MasterKey, IndexNum, StartTime, EndTime)

    #平均每秒从InnoDB表删除的行数
    elif (Key == "inno_row_delete"):
        MasterKey = "MySQL_RowDML"
        IndexNum = 2
        GetPerformance(DBInstanceId, MasterKey, IndexNum, StartTime, EndTime)

    #平均每秒从InnoDB表插入的行数
    elif (Key == "inno_row_insert"):
        MasterKey = "MySQL_RowDML"
        IndexNum = 3
        GetPerformance(DBInstanceId, MasterKey, IndexNum, StartTime, EndTime)

    #平均每秒向日志文件的物理写次数
    elif (Key == "Inno_log_writes"):
        MasterKey = "MySQL_RowDML"
        IndexNum = 4
        GetPerformance(DBInstanceId, MasterKey, IndexNum, StartTime, EndTime)

    #MySQL实例CPU使用率(占操作系统总数)
    elif (Key == "cpuusage"):
        MasterKey = "MySQL_MemCpuUsage"
        IndexNum = 0
        GetPerformance(DBInstanceId, MasterKey, IndexNum, StartTime, EndTime)

    #MySQL实例内存使用率(占操作系统总数)
    elif (Key == "memusage"):
        MasterKey = "MySQL_MemCpuUsage"
        IndexNum = 1
        GetPerformance(DBInstanceId, MasterKey, IndexNum, StartTime, EndTime)

    #MySQL实例的IOPS（每秒IO请求次数）
    elif (Key == "io"):
        MasterKey = "MySQL_IOPS"
        IndexNum = 0
        GetPerformance(DBInstanceId, MasterKey, IndexNum, StartTime, EndTime)

    #ins_size实例总空间使用量
    elif (Key == "ins_size"):
        MasterKey = "MySQL_DetailedSpaceUsage"
        IndexNum = 0
        GetPerformance(DBInstanceId, MasterKey, IndexNum, StartTime, EndTime)

    #data_size数据空间
    elif (Key == "data_size"):
        MasterKey = "MySQL_DetailedSpaceUsage"
        IndexNum = 1
        GetPerformance(DBInstanceId, MasterKey, IndexNum, StartTime, EndTime)

    #log_size日志空间
    elif (Key == "log_size"):
        MasterKey = "MySQL_DetailedSpaceUsage"
        IndexNum = 2
        GetPerformance(DBInstanceId, MasterKey, IndexNum, StartTime, EndTime)

    #tmp_size临时空间
    elif (Key == "tmp_size"):
        MasterKey = "MySQL_DetailedSpaceUsage"
        IndexNum = 3
        GetPerformance(DBInstanceId, MasterKey, IndexNum, StartTime, EndTime)

    #other_size系统空间
    elif (Key == "other_size"):
        MasterKey = "MySQL_DetailedSpaceUsage"
        IndexNum = 4
        GetPerformance(DBInstanceId, MasterKey, IndexNum, StartTime, EndTime)

使用方法:

# zabbix-RDS-monitor
Aliyun RDS-mysql status monitor with zabbix   

zabbix通过阿里云api 自动发现、监控阿里云RDS-Mysql数据库      
本版本数据的图表展示，是以**监控项进行分组**，后期会再发布**以rds实例分组**的版本。
## 使用方法
### 注意事项
1. 脚本会收集RDS别名，
2. 不要默认别名
3. 不要使用中文别名（zabbix不识别）
4. 切记aliyun-python-sdk-core==2.3.5，新版本的sdk有bug
### 环境要求
python = 2.7
### 模块安装
shell
/usr/bin/env pip2.7 install aliyun-python-sdk-core==2.3.5 aliyun-python-sdk-rds==2.1.4 datetime
使用方法
1. 从阿里云控制台获取 **AccessKey** ,并修改脚本中的 **ID** 与 **Secret**
2. 修改区域 **RegionId**
3. 将两个脚本放置于以下目录
conf
/etc/zabbix/script

shell
chmod +x /etc/zabbix/script/*

4. 修改zabbix-agentd.conf，添加以下内容
conf
#rds
UserParameter=rds.discovery,/usr/bin/env python2.7 /etc/zabbix/script/discovery_rds.py
UserParameter=check.rds[*],/usr/bin/env python2.7 /etc/zabbix/script/check_rds.py $1 $2 $3

5. 重启zabbix-agent
6. zabbix控制台导入模板，并关联主机

在天堂 - 技术老男 博主简介

数据库技术点滴累积，个人运维经验分享

在天堂 - 技术老男 ›

运维技术 ›
使用zabbix监控阿里云数据库

阿里云数据库已经提供了很好的web管理界面，不过如果想通过zabbix进行集中管理，并对自定义异常实现监控报警，同时保留监控历史，就有必要使用zabbix的自动发现功能添加数据库的实例ID到zabiix服务器。

您还没有登录，请您注册 登录后发表评论。

阿里云数据库已经提供了很好的web管理界面，不过如果想通过zabbix进行集中管理，并对自定义异常实现监控报警，同时保留监控历史，就有必要使用zabbix的自动发现功能添加数据库的实例ID到zabiix服务器。

您还没有登录，请您 注册 登录后发表评论。

您还没有登录，请您注册登录后发表评论。