看到Zabbix的数据表结构吧，就知道数据量大了 性能问题很让人担忧，不过基于Zabbix数据库导出报表，或自动跑报表的时候，就必须去了解一下zabbix的数据表结构了，得知道XX放在哪才能找到XX，既然能找到XX了，让他数据可视化起来也就不是什么问题了，废话少说开撸
0、Hosts表
mysql> DESC hosts;
###这里面存有hostid、proxyid、节点信息、状态以及XXX各种信息，比较核心的一张表###
+--------------------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------------+---------------------+------+-----+---------+-------+
| hostid | bigint(20) unsigned | NO | PRI | NULL | |
| proxy_hostid | bigint(20) unsigned | YES | MUL | NULL | |
| host | varchar(64) | NO | MUL | | |
| 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 | |
| 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 | YES | MUL | NULL | |
| maintenance_status | int(11) | NO | | 0 | |
| maintenance_type | int(11) | NO | | 0 | |
| maintenance_from | int(11) | NO | | 0 | |
| ipmi_errors_from | int(11) | NO | | 0 | |
| snmp_errors_from | int(11) | NO | | 0 | |
| ipmi_error | varchar(128) | NO | | | |
| snmp_error | varchar(128) | NO | | | |
| jmx_disable_until | int(11) | NO | | 0 | |
| jmx_available | int(11) | NO | | 0 | |
| jmx_errors_from | int(11) | NO | | 0 | |
| jmx_error | varchar(128) | NO | | | |
| name | varchar(64) | NO | MUL | | |
| flags | int(11) | NO | | 0 | |
| templateid | bigint(20) unsigned | YES | MUL | NULL | |
+--------------------+---------------------+------+-----+---------+-------+
1、groups表
mysql> DESC groups;
##这张表结构主要就是组名和组ID##
+----------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+---------------------+------+-----+---------+-------+
| groupid | bigint(20) unsigned | NO | PRI | NULL | |
| name | varchar(64) | NO | MUL | | |
| internal | int(11) | NO | | 0 | |
| flags | int(11) | NO | | 0 | |
+----------+---------------------+------+-----+---------+-------+
3、hosts_groups表
mysql> DESC hosts_groups;
###hosts[主机] 和 groups[组] 的关联关系###
+-------------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+---------------------+------+-----+---------+-------+
| hostgroupid | bigint(20) unsigned | NO | PRI | NULL | |
| hostid | bigint(20) unsigned | NO | MUL | NULL | |
| groupid | bigint(20) unsigned | NO | MUL | NULL | |
+-------------+---------------------+------+-----+---------+-------+
4、items表
mysql> DESC items;
###存有各种监控项的信息###
+-----------------------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------------------+---------------------+------+-----+---------+-------+
| itemid | bigint(20) unsigned | NO | PRI | NULL | |
| type | int(11) | NO | | 0 | |
| snmp_community | varchar(64) | NO | | | |
| snmp_oid | varchar(255) | NO | | | |
| hostid | bigint(20) unsigned | NO | MUL | NULL | |
| name | varchar(255) | NO | | | |
| key_ | varchar(255) | NO | | | |
| delay | int(11) | NO | | 0 | |
| history | int(11) | NO | | 90 | |
| trends | int(11) | NO | | 365 | |
| status | int(11) | NO | MUL | 0 | |
| value_type | int(11) | NO | | 0 | |
| trapper_hosts | varchar(255) | NO | | | |
| units | varchar(255) | NO | | | |
| multiplier | int(11) | NO | | 0 | |
| delta | int(11) | NO | | 0 | |
| 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 | bigint(20) unsigned | NO | | 0 | |
| logtimefmt | varchar(64) | NO | | | |
| templateid | bigint(20) unsigned | YES | MUL | NULL | |
| valuemapid | bigint(20) unsigned | YES | MUL | NULL | |
| 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 | |
| flags | int(11) | NO | | 0 | |
| filter | varchar(255) | NO | | | |
| interfaceid | bigint(20) unsigned | YES | MUL | NULL | |
| port | varchar(64) | NO | | | |
| description | text | NO | | NULL | |
| inventory_link | int(11) | NO | | 0 | |
| lifetime | varchar(64) | NO | | 30 | |
| snmpv3_authprotocol | int(11) | NO | | 0 | |
| snmpv3_privprotocol | int(11) | NO | | 0 | |
| state | int(11) | NO | | 0 | |
| snmpv3_contextname | varchar(255) | NO | | | |
+-----------------------+---------------------+------+-----+---------+-------+
5、media表
###存有XX用户的Media配置和相对应的告警方式###
mysql> DESC media;
+-------------+---------------------+------+-----+-----------------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+---------------------+------+-----+-----------------+-------+
| mediaid | bigint(20) unsigned | NO | PRI | NULL | |
| userid | bigint(20) unsigned | NO | MUL | NULL | |
| mediatypeid | bigint(20) unsigned | NO | MUL | NULL | |
| sendto | varchar(100) | NO | | | |
| active | int(11) | NO | | 0 | |
| severity | int(11) | NO | | 63 | |
| period | varchar(100) | NO | | 1-7,00:00-24:00 | |
+-------------+---------------------+------+-----+-----------------+-------+
6、media_type表
mysql> desc media_type;
##存有media告警方式对应的执行脚本的信息##
+-------------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+---------------------+------+-----+---------+-------+
| mediatypeid | bigint(20) unsigned | NO | PRI | NULL | |
| 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 | | | |
| status | int(11) | NO | | 0 | |
+-------------+---------------------+------+-----+---------+-------+
7、triggers表
mysql> DESC triggers;
###存有触发器相关所有信息###
+-------------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+---------------------+------+-----+---------+-------+
| triggerid | bigint(20) unsigned | NO | PRI | NULL | |
| expression | varchar(2048) | 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 | |
| comments | text | NO | | NULL | |
| error | varchar(128) | NO | | | |
| templateid | bigint(20) unsigned | YES | MUL | NULL | |
| type | int(11) | NO | | 0 | |
| state | int(11) | NO | | 0 | |
| flags | int(11) | NO | | 0 | |
+-------------+---------------------+------+-----+---------+-------+
8、trigger_depends表
mysql> DESC trigger_depends;
##存有触发器的依存信息###
+----------------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------+---------------------+------+-----+---------+-------+
| triggerdepid | bigint(20) unsigned | NO | PRI | NULL | |
| triggerid_down | bigint(20) unsigned | NO | MUL | NULL | |
| triggerid_up | bigint(20) unsigned | NO | MUL | NULL | |
+----------------+---------------------+------+-----+---------+-------+
9、graphs表
mysql> DESC graphs;
##该表存有用户定义的图表信息###
+------------------+---------------------+------+-----+----------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------------+---------------------+------+-----+----------+-------+
| graphid | bigint(20) unsigned | NO | PRI | NULL | |
| name | varchar(128) | NO | MUL | | |
| width | int(11) | NO | | 900 | |
| height | int(11) | NO | | 200 | |
| yaxismin | double(16,4) | NO | | 0.0000 | |
| yaxismax | double(16,4) | NO | | 100.0000 | |
| templateid | bigint(20) unsigned | YES | MUL | NULL | |
| show_work_period | int(11) | NO | | 1 | |
| show_triggers | int(11) | NO | | 1 | |
| graphtype | int(#11) | NO | | 0 | |
| show_legend | int(11) | NO | | 1 | |
| 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 | YES | MUL | NULL | |
| ymax_itemid | bigint(20) unsigned | YES | MUL | NULL | |
| flags | int(11) | NO | | 0 | |
+------------------+---------------------+------+-----+----------+-------+
10、graphs_items表
mysql> DESC graphs_items;
##graphs_items 保存了属于某个图表的所有的监控项信息###
+-----------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+---------------------+------+-----+---------+-------+
| gitemid | bigint(20) unsigned | NO | PRI | NULL | |
| graphid | bigint(20) unsigned | NO | MUL | NULL | |
| itemid | bigint(20) unsigned | NO | MUL | NULL | |
| drawtype | int(11) | NO | | 0 | |
| sortorder | int(11) | NO | | 0 | |
| color | varchar(6) | NO | | 009600 | |
| yaxisside | int(11) | NO | | 0 | |
| calc_fnc | int(11) | NO | | 2 | |
| type | int(11) | NO | | 0 | |
+-----------+---------------------+------+-----+---------+-------+
11、screens表
mysql> desc screens;
##存有用户定义的一些监控图片组##
+------------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+---------------------+------+-----+---------+-------+
| screenid | bigint(20) unsigned | NO | PRI | NULL | |
| name | varchar(255) | NO | | NULL | |
| hsize | int(11) | NO | | 1 | |
| vsize | int(11) | NO | | 1 | |
| templateid | bigint(20) unsigned | YES | MUL | NULL | |
+------------+---------------------+------+-----+---------+-------+
12、screens_items表
mysql> desc screens_items;
##和前面graphs_items概念一样##
+---------------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------------+---------------------+------+-----+---------+-------+
| screenitemid | bigint(20) unsigned | NO | PRI | NULL | |
| screenid | bigint(20) unsigned | NO | MUL | NULL | |
| 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 | |
| sort_triggers | int(11) | NO | | 0 | |
| application | varchar(255) | NO | | | |
+---------------+---------------------+------+-----+---------+-------+
13、functions表
mysql> DESC functions;
##比较重要的一张表，存有触发器里面各种表达式##
+------------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+---------------------+------+-----+---------+-------+
| functionid | bigint(20) unsigned | NO | PRI | NULL | |
| itemid | bigint(20) unsigned | NO | MUL | NULL | |
| triggerid | bigint(20) unsigned | NO | MUL | NULL | |
| function | varchar(12) | NO | | | |
| parameter | varchar(255) | NO | | 0 | |
+------------+---------------------+------+-----+---------+-------+
14、sessions表
mysql> DESC sessions;
##存有各个用户的session，登入登出都会操作该表##
+------------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+---------------------+------+-----+---------+-------+
| sessionid | varchar(32) | NO | PRI | | |
| userid | bigint(20) unsigned | NO | MUL | NULL | |
| lastaccess | int(11) | NO | | 0 | |
| status | int(11) | NO | | 0 | |
+------------+---------------------+------+-----+---------+-------+
» 转载保留版权：IT辰逸 » 《Zabbix 数据库表结构》
» 本文链接地址：http://www.ipython.me/centos/zabbix-datatable-struc.html
» 本文版权采取： BY-NC-SA 协议进行授权，转载注明出处。除IT-Tools、News以及特别标注，本站所有文章均为原创。
» 如果喜欢可以： 点此订阅本站