zabbix数据库表结构
一、数据库表总览
zabbix:zabbix> show tables;
+-----------------------+
| Tables_in_zabbix |
+-----------------------+
| acknowledges |
| actions |
| alerts |
| applications |
| auditlog |
| auditlog_details |
| autoreg_host |
| conditions |
| config |
| dchecks |
| dhosts |
| drules |
| dservices |
| escalations |
| events |
| expressions |
| functions |
| globalmacro |
| globalvars |
| graph_discovery |
| graph_theme |
| graphs |
| graphs_items |
| groups |
| help_items |
| history |
| history_log |
| history_str |
| history_str_sync |
| history_sync |
| history_text |
| history_uint |
| history_uint_sync |
| host_inventory |
| hostmacro |
| hosts |
| hosts_groups |
| hosts_templates |
| housekeeper |
| httpstep |
| httpstepitem |
| httptest |
| httptestitem |
| icon_map |
| icon_mapping |
| ids |
| images |
| interface |
| item_discovery |
| items |
| items_applications |
| maintenances |
| maintenances_groups |
| maintenances_hosts |
| maintenances_windows |
| mappings |
| media |
| media_type |
| node_cksum |
| nodes |
| opcommand |
| opcommand_grp |
| opcommand_hst |
| opconditions |
| operations |
| opgroup |
| opmessage |
| opmessage_grp |
| opmessage_usr |
| optemplate |
| profiles |
| proxy_autoreg_host |
| proxy_dhistory |
| proxy_history |
| regexps |
| rights |
| screens |
| screens_items |
| scripts |
| service_alarms |
| services |
| services_links |
| services_times |
| sessions |
| slides |
| slideshows |
| sysmap_element_url |
| sysmap_url |
| sysmaps |
| sysmaps_elements |
| sysmaps_link_triggers |
| sysmaps_links |
| timeperiods |
| trends |
| trends_uint |
| trigger_depends |
| trigger_discovery |
| triggers |
| user_history |
| users |
| users_groups |
| usrgrp |
| valuemaps |
+-----------------------+
二、基本数据库表简介
- actions
actions表记录了当触发器触发时，需要采用的动作。
mysql> desc actions;
+---------------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------------+---------------------+------+-----+---------+-------+
| actionid | bigint(20) unsigned | NO | PRI | 0 | |
| name | varchar(255) | NO | | | |
| eventsource | int(11) | NO | MUL | 0 | |
| evaltype | int(11) | NO | | 0 | |
| status | int(11) | NO | | 0 | |
| esc_period | int(11) | NO | | 0 | |
| def_shortdata | varchar(255) | NO | | | |
| def_longdata | blob | NO | | NULL | |
| recovery_msg | int(11) | NO | | 0 | |
| r_shortdata | varchar(255) | NO | | | |
| r_longdata | blob | NO | | NULL | |
+---------------+---------------------+------+-----+---------+-------+
- alerts
alerts 表保存了历史的告警事件，可以从这个表里面去做一些统计分析，例如某个部门、 某人、某类时间的告警统计，以及更深入的故障发生、恢复时间，看你想怎么用了。
mysql> desc alerts;
+-------------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+---------------------+------+-----+---------+-------+
| alertid | bigint(20) unsigned | NO | PRI | 0 | |
| actionid | bigint(20) unsigned | NO | MUL | 0 | |
| eventid | bigint(20) unsigned | NO | MUL | 0 | |
| userid | bigint(20) unsigned | NO | MUL | 0 | |
| clock | int(11) | NO | PRI | 0 | |
| mediatypeid | bigint(20) unsigned | NO | MUL | 0 | |
| sendto | varchar(100) | NO | | | |
| subject | varchar(255) | NO | | | |
| message | blob | NO | | NULL | |
| status | int(11) | NO | MUL | 0 | |
| retries | int(11) | NO | | 0 | |
| error | varchar(128) | NO | | | |
| nextcheck | int(11) | NO | | 0 | |
| esc_step | int(11) | NO | | 0 | |
| alerttype | int(11) | NO | | 0 | |
+-------------+---------------------+------+-----+---------+-------+
- config
config表保存了全局的参数，前端包括后端也是，很多情况下会查询改表的参数的，例如用户的自定义主题、 登陆认证类型等，非常重要。
mysql> desc config;
+-------------------------+---------------------+------+-----+-----------------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------------------+---------------------+------+-----+-----------------+-------+
| configid | bigint(20) unsigned | NO | PRI | 0 | |
| alert_history | int(11) | NO | | 0 | |
| event_history | int(11) | NO | | 0 | |
| refresh_unsupported | int(11) | NO | | 0 | |
| work_period | varchar(100) | NO | | 1-5,00:00-24:00 | |
| alert_usrgrpid | bigint(20) unsigned | NO | | 0 | |
| event_ack_enable | int(11) | NO | | 1 | |
| event_expire | int(11) | NO | | 7 | |
| event_show_max | int(11) | NO | | 100 | |
| default_theme | varchar(128) | NO | | default.css | |
| authentication_type | int(11) | NO | | 0 | |
| ldap_host | varchar(255) | NO | | | |
| ldap_port | int(11) | NO | | 389 | |
| ldap_base_dn | varchar(255) | NO | | | |
| ldap_bind_dn | varchar(255) | NO | | | |
| ldap_bind_password | varchar(128) | NO | | | |
| ldap_search_attribute | varchar(128) | NO | | | |
| dropdown_first_entry | int(11) | NO | | 1 | |
| dropdown_first_remember | int(11) | NO | | 1 | |
| discovery_groupid | bigint(20) unsigned | NO | | 0 | |
| max_in_table | int(11) | NO | | 50 | |
| search_limit | int(11) | NO | | 1000 | |
+-------------------------+---------------------+------+-----+-----------------+-------+
- functions
function 表时非常重要的一个表了，记录了trigger中使用的表达式，例如max、last、nodata等函数。
mysql> desc functions ;
+------------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+---------------------+------+-----+---------+-------+
| functionid | bigint(20) unsigned | NO | PRI | 0 | |
| itemid | bigint(20) unsigned | NO | MUL | 0 | |
| triggerid | bigint(20) unsigned | NO | MUL | 0 | |
| lastvalue | varchar(255) | YES | | NULL | |
| function | varchar(12) | NO | | | |
| parameter | varchar(255) | NO | | 0 | |
+------------+---------------------+------+-----+---------+-------+
- graphs
graphs 表包含了用户定义的图表信息。
mysql> desc graphs;
+------------------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------------+---------------------+------+-----+---------+-------+
| graphid | bigint(20) unsigned | NO | PRI | 0 | |
| name | varchar(128) | NO | MUL | | |
| width | int(11) | NO | | 0 | |
| height | int(11) | NO | | 0 | |
| yaxismin | double(16,4) | NO | | 0.0000 | |
| yaxismax | double(16,4) | NO | | 0.0000 | |
| templateid | bigint(20) unsigned | NO | | 0 | |
| show_work_period | int(11) | NO | | 1 | |
| show_triggers | int(11) | NO | | 1 | |
| graphtype | int(11) | NO | | 0 | |
| show_legend | int(11) | NO | | 0 | |
| show_3d | int(11) | NO | | 0 | |
| percent_left | double(16,4) | NO | | 0.0000 | |
| percent_right | double(16,4) | NO | | 0.0000 | |
| ymin_type | int(11) | NO | | 0 | |
| ymax_type | int(11) | NO | | 0 | |
| ymin_itemid | bigint(20) unsigned | NO | | 0 | |
| ymax_itemid | bigint(20) unsigned | NO | | 0 | |
+------------------+---------------------+------+-----+---------+-------+
- graphs_items
graphs_items 保存了属于某个图表的所有的监控项信息。
mysql> desc graphs_items;
+-------------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+---------------------+------+-----+---------+-------+
| gitemid | bigint(20) unsigned | NO | PRI | 0 | |
| graphid | bigint(20) unsigned | NO | MUL | 0 | |
| itemid | bigint(20) unsigned | NO | MUL | 0 | |
| drawtype | int(11) | NO | | 0 | |
| sortorder | int(11) | NO | | 0 | |
| color | varchar(6) | NO | | 009600 | |
| yaxisside | int(11) | NO | | 1 | |
| calc_fnc | int(11) | NO | | 2 | |
| type | int(11) | NO | | 0 | |
| periods_cnt | int(11) | NO | | 5 | |
+-------------+---------------------+------+-----+---------+-------+
- groups
groups 保存了组名和组的ID 。
mysql> desc groups ;
+----------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+---------------------+------+-----+---------+-------+
| groupid | bigint(20) unsigned | NO | PRI | 0 | |
| name | varchar(64) | NO | MUL | | |
| internal | int(11) | NO | | 0 | |
+----------+---------------------+------+-----+---------+-------+
- history 、history_str、history_log 、history_uint_sync等
这部分表都差不多，唯一不同的是保存的数据类型，history_str保存的数据 类型就算str即字符类型的。这个是和采集时设置的数据类型一致的。
需要注意的时，因为history表有这么多的类型，那自己写报表系统等去查询 数据时，就需要判断下数据的采集类型，如果查错了表，那肯定时没有数据的。
mysql> desc history;
+--------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+---------------------+------+-----+---------+-------+
| itemid | bigint(20) unsigned | NO | PRI | 0 | |
| clock | int(11) | NO | PRI | 0 | |
| value | double(16,4) | NO | | 0.0000 | |
+--------+---------------------+------+-----+---------+-------+
mysql> desc history_str;
+--------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+---------------------+------+-----+---------+-------+
| itemid | bigint(20) unsigned | NO | MUL | 0 | |
| clock | int(11) | NO | | 0 | |
| value | varchar(255) | NO | | | |
+--------+---------------------+------+-----+---------+-------+
- trends、trends_uint
trends 也是保存了历史数据用的，和history不同的时，trends表仅仅保存了 小时平均的值，即你可以理解为是history表的数据压缩。所以trends表也有 很多的类型，对应history。
值的注意的trends和history表这两类表数据量都非常大，我们一天大概就要有 40G 的数据。
所以注意定是去做压缩、删除。
mysql> desc trends;
+-----------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+---------------------+------+-----+---------+-------+
| itemid | bigint(20) unsigned | NO | PRI | 0 | |
| clock | int(11) | NO | PRI | 0 | |
| num | int(11) | NO | | 0 | |
| value_min | double(16,4) | NO | | 0.0000 | |
| value_avg | double(16,4) | NO | | 0.0000 | |
| value_max | double(16,4) | NO | | 0.0000 | |
+-----------+---------------------+------+-----+---------+-------+
mysql> desc trends_uint;
+-----------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+---------------------+------+-----+---------+-------+
| itemid | bigint(20) unsigned | NO | PRI | 0 | |
| clock | int(11) | NO | PRI | 0 | |
| num | int(11) | NO | | 0 | |
| value_min | bigint(20) unsigned | NO | | 0 | |
| value_avg | bigint(20) unsigned | NO | | 0 | |
| value_max | bigint(20) unsigned | NO | | 0 | |
+-----------+---------------------+------+-----+---------+-------+
- hosts
hosts 非常重要，保存了每个agent、proxy等的IP 、hostid、状态、IPMI等信息， 几乎是记录了一台设备的所有的信息。
当然hostid是当中非常非常重要的信息，其他的表一般都时关联hostid的。
mysql> desc hosts;
+--------------------+---------------------+------+-----+-----------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------------+---------------------+------+-----+-----------+-------+
| hostid | bigint(20) unsigned | NO | PRI | 0 | |
| proxy_hostid | bigint(20) unsigned | NO | MUL | 0 | |
| host | varchar(64) | NO | MUL | | |
| dns | varchar(64) | NO | | | |
| useip | int(11) | NO | | 1 | |
| ip | varchar(39) | NO | | 127.0.0.1 | |
| port | int(11) | NO | | 10050 | |
| status | int(11) | NO | MUL | 0 | |
| disable_until | int(11) | NO | | 0 | |
| error | varchar(128) | NO | | | |
| available | int(11) | NO | | 0 | |
| errors_from | int(11) | NO | | 0 | |
| lastaccess | int(11) | NO | | 0 | |
| inbytes | bigint(20) unsigned | NO | | 0 | |
| outbytes | bigint(20) unsigned | NO | | 0 | |
| useipmi | int(11) | NO | | 0 | |
| ipmi_port | int(11) | NO | | 623 | |
| ipmi_authtype | int(11) | NO | | 0 | |
| ipmi_privilege | int(11) | NO | | 2 | |
| ipmi_username | varchar(16) | NO | | | |
| ipmi_password | varchar(20) | NO | | | |
| ipmi_disable_until | int(11) | NO | | 0 | |
| ipmi_available | int(11) | NO | | 0 | |
| snmp_disable_until | int(11) | NO | | 0 | |
| snmp_available | int(11) | NO | | 0 | |
| maintenanceid | bigint(20) unsigned | NO | | 0 | |
| maintenance_status | int(11) | NO | | 0 | |
| maintenance_type | int(11) | NO | | 0 | |
| maintenance_from | int(11) | NO | | 0 | |
| ipmi_ip | varchar(64) | NO | | 127.0.0.1 | |
| ipmi_errors_from | int(11) | NO | | 0 | |
| snmp_errors_from | int(11) | NO | | 0 | |
| ipmi_error | varchar(128) | NO | | | |
| snmp_error | varchar(128) | NO | | | |
+--------------------+---------------------+------+-----+-----------+-------+
- hosts_groups
hosts_groups 保存了host（主机）与host groups（主机组）的关联关系。
这部分信息可以在我们自己做一些批量查询，例如查询关联到某个主机组的所有 设备的IP 、存活状态等，进一步去查询该批量设备的load、IO、mem等统计信息。
mysql> desc hosts_groups ;
+-------------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+---------------------+------+-----+---------+-------+
| hostgroupid | bigint(20) unsigned | NO | PRI | 0 | |
| hostid | bigint(20) unsigned | NO | MUL | 0 | |
| groupid | bigint(20) unsigned | NO | MUL | 0 | |
+-------------+---------------------+------+-----+---------+-------+
- items
items 表保存了采集项的信息。
mysql> desc items ;
+-----------------------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------------------+---------------------+------+-----+---------+-------+
| itemid | bigint(20) unsigned | NO | PRI | 0 | |
| type | int(11) | NO | | 0 | |
| snmp_community | varchar(64) | NO | | | |
| snmp_oid | varchar(255) | NO | | | |
| snmp_port | int(11) | NO | | 161 | |
| hostid | bigint(20) unsigned | NO | MUL | 0 | |
| description | varchar(255) | NO | | | |
| key_ | varchar(255) | NO | | | |
| delay | int(11) | NO | | 0 | |
| history | int(11) | NO | | 90 | |
| trends | int(11) | NO | | 365 | |
| lastvalue | varchar(255) | YES | | NULL | |
| lastclock | int(11) | YES | | NULL | |
| prevvalue | varchar(255) | YES | | NULL | |
| status | int(11) | NO | MUL | 0 | |
| value_type | int(11) | NO | | 0 | |
| trapper_hosts | varchar(255) | NO | | | |
| units | varchar(10) | NO | | | |
| multiplier | int(11) | NO | | 0 | |
| delta | int(11) | NO | | 0 | |
| prevorgvalue | varchar(255) | YES | | NULL | |
| snmpv3_securityname | varchar(64) | NO | | | |
| snmpv3_securitylevel | int(11) | NO | | 0 | |
| snmpv3_authpassphrase | varchar(64) | NO | | | |
| snmpv3_privpassphrase | varchar(64) | NO | | | |
| formula | varchar(255) | NO | | 1 | |
| error | varchar(128) | NO | | | |
| lastlogsize | int(11) | NO | | 0 | |
| logtimefmt | varchar(64) | NO | | | |
| templateid | bigint(20) unsigned | NO | MUL | 0 | |
| valuemapid | bigint(20) unsigned | NO | | 0 | |
| delay_flex | varchar(255) | NO | | | |
| params | text | NO | | NULL | |
| ipmi_sensor | varchar(128) | NO | | | |
| data_type | int(11) | NO | | 0 | |
| authtype | int(11) | NO | | 0 | |
| username | varchar(64) | NO | | | |
| password | varchar(64) | NO | | | |
| publickey | varchar(64) | NO | | | |
| privatekey | varchar(64) | NO | | | |
| mtime | int(11) | NO | | 0 | |
+-----------------------+---------------------+------+-----+---------+-------+
- media
media 保存了某个用户的media配置项，即对应的告警方式。
mysql> desc media;
+-------------+---------------------+------+-----+-----------------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+---------------------+------+-----+-----------------+-------+
| mediaid | bigint(20) unsigned | NO | PRI | 0 | |
| userid | bigint(20) unsigned | NO | MUL | 0 | |
| mediatypeid | bigint(20) unsigned | NO | MUL | 0 | |
| sendto | varchar(100) | NO | | | |
| active | int(11) | NO | | 0 | |
| severity | int(11) | NO | | 63 | |
| period | varchar(100) | NO | | 1-7,00:00-23:59 | |
+-------------+---------------------+------+-----+-----------------+-------+
- media_type
media_type 表与media 表不同的是media_type 记录了某个告警方式对应的执行脚本，注意路径
只是相对路径。
mysql> desc media_type;
+-------------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+---------------------+------+-----+---------+-------+
| mediatypeid | bigint(20) unsigned | NO | PRI | 0 | |
| type | int(11) | NO | | 0 | |
| description | varchar(100) | NO | | | |
| smtp_server | varchar(255) | NO | | | |
| smtp_helo | varchar(255) | NO | | | |
| smtp_email | varchar(255) | NO | | | |
| exec_path | varchar(255) | NO | | | |
| gsm_modem | varchar(255) | NO | | | |
| username | varchar(255) | NO | | | |
| passwd | varchar(255) | NO | | | |
+-------------+---------------------+------+-----+---------+-------+
mysql> select mediatypeid,type,description,exec_path from media_type;
+-------------+------+--------------+--------------------------+
| mediatypeid | type | description | exec_path |
+-------------+------+--------------+--------------------------+
| 4 | 1 | Email | ZabbixSendMail.sh |
| 5 | 1 | sms | ZabbixSend_sms.sh |
| 6 | 1 | syslog_email | ZabbixSendMail_Syslog.sh |
| 8 | 1 | sms_test | ZabbixSend_sms_test.sh |
+-------------+------+--------------+--------------------------+
4 rows in set (0.00 sec)
media 与media_type 通过mediatypeid 键关联。
- profiles
profiles 表保存了用户的一些配置项。
mysql> desc profiles ;
+-----------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+---------------------+------+-----+---------+-------+
| profileid | bigint(20) unsigned | NO | PRI | 0 | |
| userid | bigint(20) unsigned | NO | MUL | 0 | |
| idx | varchar(96) | NO | | | |
| idx2 | bigint(20) unsigned | NO | | 0 | |
| value_id | bigint(20) unsigned | NO | | 0 | |
| value_int | int(11) | NO | | 0 | |
| value_str | varchar(255) | NO | | | |
| source | varchar(96) | NO | | | |
| type | int(11) | NO | | 0 | |
+-----------+---------------------+------+-----+---------+-------+
- rights
rights 表保存了用户组的权限信息，zabbix的权限一直也是我理不太清的地方， 其实这个表里面有详细的记录。
mysql> desc rights;
+------------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+---------------------+------+-----+---------+-------+
| rightid | bigint(20) unsigned | NO | PRI | 0 | |
| groupid | bigint(20) unsigned | NO | MUL | 0 | |
| permission | int(11) | NO | | 0 | |
| id | bigint(20) unsigned | YES | MUL | NULL | |
+------------+---------------------+------+-----+---------+-------+
- screeens
screeens 表保存了用户定义的图片。
mysql> desc graphs;
+------------------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------------+---------------------+------+-----+---------+-------+
| graphid | bigint(20) unsigned | NO | PRI | 0 | |
| name | varchar(128) | NO | MUL | | |
| width | int(11) | NO | | 0 | |
| height | int(11) | NO | | 0 | |
| yaxismin | double(16,4) | NO | | 0.0000 | |
| yaxismax | double(16,4) | NO | | 0.0000 | |
| templateid | bigint(20) unsigned | NO | | 0 | |
| show_work_period | int(11) | NO | | 1 | |
| show_triggers | int(11) | NO | | 1 | |
| graphtype | int(11) | NO | | 0 | |
| show_legend | int(11) | NO | | 0 | |
| show_3d | int(11) | NO | | 0 | |
| percent_left | double(16,4) | NO | | 0.0000 | |
| percent_right | double(16,4) | NO | | 0.0000 | |
| ymin_type | int(11) | NO | | 0 | |
| ymax_type | int(11) | NO | | 0 | |
| ymin_itemid | bigint(20) unsigned | NO | | 0 | |
| ymax_itemid | bigint(20) unsigned | NO | | 0 | |
+------------------+---------------------+------+-----+---------+-------+
- screeens_items
同graphs_items。
mysql> desc screens_items;
+--------------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------+---------------------+------+-----+---------+-------+
| screenitemid | bigint(20) unsigned | NO | PRI | 0 | |
| screenid | bigint(20) unsigned | NO | | 0 | |
| resourcetype | int(11) | NO | | 0 | |
| resourceid | bigint(20) unsigned | NO | | 0 | |
| width | int(11) | NO | | 320 | |
| height | int(11) | NO | | 200 | |
| x | int(11) | NO | | 0 | |
| y | int(11) | NO | | 0 | |
| colspan | int(11) | NO | | 0 | |
| rowspan | int(11) | NO | | 0 | |
| elements | int(11) | NO | | 25 | |
| valign | int(11) | NO | | 0 | |
| halign | int(11) | NO | | 0 | |
| style | int(11) | NO | | 0 | |
| url | varchar(255) | NO | | | |
| dynamic | int(11) | NO | | 0 | |
+--------------+---------------------+------+-----+---------+-------+
- sessions
sessions 表很重要，保存了每个用户的sessions,在登陆、注销的时候均会操作 该张表的。
mysql> desc sessions;
+------------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+---------------------+------+-----+---------+-------+
| sessionid | varchar(32) | NO | PRI | | |
| userid | bigint(20) unsigned | NO | MUL | 0 | |
| lastaccess | int(11) | NO | | 0 | |
| status | int(11) | NO | | 0 | |
+------------+---------------------+------+-----+---------+-------+
- triggers
triggers 顾名思义保存了trigger的所有信息。
mysql> desc triggers;
+-------------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+---------------------+------+-----+---------+-------+
| triggerid | bigint(20) unsigned | NO | PRI | 0 | |
| expression | varchar(255) | NO | | | |
| description | varchar(255) | NO | | | |
| url | varchar(255) | NO | | | |
| status | int(11) | NO | MUL | 0 | |
| value | int(11) | NO | MUL | 0 | |
| priority | int(11) | NO | | 0 | |
| lastchange | int(11) | NO | | 0 | |
| dep_level | int(11) | NO | | 0 | |
| comments | blob | NO | | NULL | |
| error | varchar(128) | NO | | | |
| templateid | bigint(20) unsigned | NO | | 0 | |
| type | int(11) | NO | | 0 | |
+-------------+---------------------+------+-----+---------+-------+
- trigger_depends
trigger_depends 保存了trigger的依赖关系。
mysql> desc trigger_depends;
+----------------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------+---------------------+------+-----+---------+-------+
| triggerdepid | bigint(20) unsigned | NO | PRI | 0 | |
| triggerid_down | bigint(20) unsigned | NO | MUL | 0 | |
| triggerid_up | bigint(20) unsigned | NO | MUL | 0 | |
+----------------+---------------------+------+-----+---------+-------+
- users
不需要解释了,值的一提的部分用户配置会在该表中，例如auotlogin、autologout、 url、theme等信息。
mysql> desc users;
+----------------+---------------------+------+-----+-------------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------+---------------------+------+-----+-------------+-------+
| userid | bigint(20) unsigned | NO | PRI | 0 | |
| alias | varchar(100) | NO | MUL | | |
| name | varchar(100) | NO | | | |
| surname | varchar(100) | NO | | | |
| passwd | char(32) | NO | | | |
| url | varchar(255) | NO | | | |
| autologin | int(11) | NO | | 0 | |
| autologout | int(11) | NO | | 900 | |
| lang | varchar(5) | NO | | en_gb | |
| refresh | int(11) | NO | | 30 | |
| type | int(11) | NO | | 0 | |
| theme | varchar(128) | NO | | default.css | |
| attempt_failed | int(11) | NO | | 0 | |
| attempt_ip | varchar(39) | NO | | | |
| attempt_clock | int(11) | NO | | 0 | |
| rows_per_page | int(11) | NO | | 50 | |
+----------------+---------------------+------+-----+-------------+-------+
三、数据库的基本操作
zabbix 最重要三张表：
-
items:存放最新一次采集项的值，存放采集项，模板信息等 -
history：这是一个系列的表如：history_uint,history_str,存放到哪张表是根据你 设置采集项选择数据类型的时候决定的，比如uint，存放的是整形的！ -
trends：每个小时根据对于history采集的值算出的平均值
手动查询数据库的基本逻辑：
1.通过匹配hosts表中host字段查找hostid
2.匹配items表中的hostid和key_字段查找itemid
3.匹配history_uint表itemid字段查询clock、value字段既是我们需要的时间和数值。
-
实例操作：
zabbix:zabbix> select hostid from hosts where host = "app10-115"; +--------+ | hostid | +--------+ | 10648 | +--------+ 1 row in set (0.00 sec) zabbix:zabbix> select itemid from items where hostid = "10648" and key_ = "net.if.in[eth0]"; +--------+ | itemid | +--------+ | 87488 | +--------+ 1 row in set (0.00 sec) zabbix:zabbix> select from_unixtime(clock) as Date_Time,round(value/1024/1024,2) as Traffic_in from history_uint where itemid = "87488" order by clock desc limit 10; +---------------------+------------+ | Date_Time | Traffic_in | +---------------------+------------+ | 2015-10-11 16:26:08 | 12.80 | | 2015-10-11 16:25:08 | 13.36 | | 2015-10-11 16:24:08 | 12.25 | | 2015-10-11 16:23:08 | 14.05 | | 2015-10-11 16:22:08 | 12.92 | | 2015-10-11 16:21:08 | 13.37 | | 2015-10-11 16:20:08 | 14.92 | | 2015-10-11 16:19:08 | 14.14 | | 2015-10-11 16:18:08 | 14.12 | | 2015-10-11 16:17:08 | 14.70 | +---------------------+------------+ 10 rows in set (0.04 sec)
日常处理需求：
-
查找模板id
select hostid from hosts where host like "%ops_dba%" and status = 3 // status = 0 为主机 -
查找itemid
select itemid from items where hostid = templateid -
查找触发器id
select triggerid from functions where itemid = itemid -
查找触发器
select * from triggers where triggerid = triggerid and status = 1 // status = 1 触发器停用 // status = 0 触发器可用
5.查找最近更新的一条采集项的值 查找特定键值的数据：
select *,from_unixtime(lastclock) from items where hostid = 10213 and key_ like "%lsi.raid%"\G;
四、数据库的优化清理
-
利用存储过程定时清理
-
对数据库表进行分区
文档下载: Zabbix数据库表结构解析.pdf
相关推荐
Zabbix表结构详解-PPT-共13页.zip ============================================== Zabbix表结构详解-PPT-共13页.zip ============================================== Zabbix表结构详解-PPT-共13页.zip ========...
### Zabbix 3.4.X 数据库表结构详解 Zabbix 是一款开源的企业级监控工具，用于监控网络服务器、网络设备以及其他IT基础设施的状态，并能够实时收集数据并进行分析。为了更好地理解和利用Zabbix，了解其数据库表结构...
### Zabbix数据库表结构解析 #### 一、引言 Zabbix是一款开源的企业级监控解决方案，广泛应用于网络设备、服务器及应用性能的监控之中。为了更高效地利用Zabbix进行监控管理和数据分析，深入理解其数据库表结构至...
随着Zabbix版本的更新，数据库表结构也会发生变化。Zabbix 3.0作为其中一个较新的版本，其数据库表结构具有以下特点和细节。 首先，Zabbix的数据库设计具有高度的规范化，每种类型的资源如主机(hosts)、项目(items)...
主要介绍Zabbix常用表结构之间的关联关系 适用于Zabbix开源软件使用人员、Zabbix想要进行对接的人员、针对想要通过BI工具（DataEase、PowerBI、帆软等）制作可视化Zabbix监控大屏等、Zabbix小白初步开始了解接口表字...
Zabbix 数据库表结构详解 Zabbix 是一个功能强大且广泛应用的监控平台，对于了解 Zabbix 的前端代码、进行深入的二次开发、甚至进行调优都是非常重要的。要达到这些目标，了解 Zabbix 的数据库表结构是必不可少的。...
该文档基于zabbix3.4.x自带的mysql数据库全140个表进行详细说明，每个字段的意义及关联关系，并写了前端显示的sql语句。对于二次开发有一定的帮助
zabbix表结构分析， zabbix各种应用案例，等等。 超大超全，总共200张以上的PPT，PDF等文档。 =================================================== 网上zabbix资料超级大汇总.rar 包含CSDN下载上几乎所有的zabbix...
Zabbix数据库表结构解析-PPT-共20页.zip ================================================ Zabbix数据库表结构解析-PPT-共20页.zip ================================================ Zabbix数据库表结构解析-PPT...
zabbix数据库表结构解析-PPT-共8页.zip ================================================= zabbix数据库表结构解析-PPT-共8页.zip ================================================= zabbix数据库表结构解析-...
zabbix数据库表结构-PPT-共21页.zip ================================================ zabbix数据库表结构-PPT-共21页.zip ================================================ zabbix数据库表结构-PPT-共21页....
在IT监控领域，Zabbix是一款广泛...了解这些表结构对于理解和优化Zabbix的性能、排查问题以及定制化扩展至关重要。通过分析这些数据，可以深入理解Zabbix如何收集、存储和处理监控数据，从而更好地利用这个强大的工具。
Zabbix5升级zabbix6需要导入的历史表结构 Zabbix5和zabbix6的表结构不同，需要导入该文件。 否则zabbix登录会报错，数据库结构不匹配！！
3. **修改Zabbix表结构**：在现有表上应用分区方案。这可能需要停止Zabbix服务以防止数据丢失。 4. **编写清理脚本**：创建一个脚本，根据预设的保留策略删除或归档过期的分区。这可能涉及到ALTER TABLE语句，删除...
- 此表结构未给出，通常用于关联应用程序与模板，允许模板中的应用集应用于多个主机。 这些表共同构成了Zabbix监控系统的核心数据结构，它们之间的交互确保了监控数据的有效管理和响应机制的正确执行。了解这些表...
以下是部分重要的Zabbix数据库表结构的详细说明： 1. **acknowledges 表**：此表记录了所有已确认的触发器事件，包括用户、确认时间、事件ID等信息，是管理报警和跟踪问题解决过程的关键。 2. **actions 表**：...
2. 手册结构：Zabbix 手册分为多个章节，每个章节介绍了 Zabbix 的一个方面，包括安装、配置、使用和故障排除等。 知识点：Zabbix 手册的结构清晰易懂，方便用户快速获取信息。 3. 什么是 Zabbix：Zabbix 是一个...
《ZABBIX 2.06数据库表结构详解》 ZABBIX，作为一个开源的企业级分布式监控解决方案，其核心组成部分包括数据库、服务器、代理、API等。深入理解ZABBIX的数据库表结构对于优化系统性能、排查问题以及进行二次开发至...
6. 维护与优化：通过对表空间的监控，管理员可以及时发现表空间的异常占用情况，并采取相应的措施，如优化表结构、调整存储引擎设置或增加磁盘空间等。这有助于维持Zabbix监控系统的性能，确保监控数据的准确性和...