25 Zabbix系统数据表结构介绍
自学Zabbix之路15.1 Zabbix数据库表结构简单解析-Hosts表、Hosts_groups表、Interface表
自学Zabbix之路15.2 Zabbix数据库表结构简单解析-Items表
自学Zabbix之路15.3 Zabbix数据库表结构简单解析-Triggers表、Applications表、 Mapplings表
自学Zabbix之路15.4 Zabbix数据库表结构简单解析-Expressions表、Media表、 Events表 自学Zabbix之路15.5 Zabbix数据库表结构简单解析-其他 表
1. 查看目前zabbix系统所有数据表：
[root@localhost /]# mysql -u root -p
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 170786
Server version: 5.5.60-MariaDB MariaDB Server Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
| zabbix |
+--------------------+
5 rows in set (0.01 sec) MariaDB [(none)]> use zabbix
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A Database changed
MariaDB [zabbix]> show tables;
+----------------------------+
| Tables_in_zabbix |
+----------------------------+
| acknowledges |
| actions |
| alerts |
| application_discovery |
| application_prototype |
| application_template |
| applications |
| auditlog |
| auditlog_details |
| autoreg_host |
| conditions |
| config |
| corr_condition |
| corr_condition_group |
| corr_condition_tag |
| corr_condition_tagpair |
| corr_condition_tagvalue |
| corr_operation |
| correlation |
| dashboard |
| dashboard_user |
| dashboard_usrgrp |
| dbversion |
| dchecks |
| dhosts |
| drules |
| dservices |
| escalations |
| event_recovery |
| event_suppress |
| event_tag |
| events |
| expressions |
| functions |
| globalmacro |
| globalvars |
| graph_discovery |
| graph_theme |
| graphs |
| graphs_items |
| group_discovery |
| group_prototype |
| history |
| history_log |
| history_str |
| history_text |
| history_uint |
| host_discovery |
| host_inventory |
| hostmacro |
| hosts |
| hosts_groups |
| hosts_templates |
| housekeeper |
| hstgrp |
| httpstep |
| httpstep_field |
| httpstepitem |
| httptest |
| httptest_field |
| httptestitem |
| icon_map |
| icon_mapping |
| ids |
| images |
| interface |
| interface_discovery |
| item_application_prototype |
| item_condition |
| item_discovery |
| item_preproc |
| items |
| items_applications |
| maintenance_tag |
| maintenances |
| maintenances_groups |
| maintenances_hosts |
| maintenances_windows |
| mappings |
| media |
| media_type |
| opcommand |
| opcommand_grp |
| opcommand_hst |
| opconditions |
| operations |
| opgroup |
| opinventory |
| opmessage |
| opmessage_grp |
| opmessage_usr |
| optemplate |
| problem |
| problem_tag |
| profiles |
| proxy_autoreg_host |
| proxy_dhistory |
| proxy_history |
| regexps |
| rights |
| screen_user |
| screen_usrgrp |
| screens |
| screens_items |
| scripts |
| service_alarms |
| services |
| services_links |
| services_times |
| sessions |
| slides |
| slideshow_user |
| slideshow_usrgrp |
| slideshows |
| sysmap_element_trigger |
| sysmap_element_url |
| sysmap_shape |
| sysmap_url |
| sysmap_user |
| sysmap_usrgrp |
| sysmaps |
| sysmaps_elements |
| sysmaps_link_triggers |
| sysmaps_links |
| tag_filter |
| task |
| task_acknowledge |
| task_check_now |
| task_close_problem |
| task_remote_command |
| task_remote_command_result |
| timeperiods |
| trends |
| trends_uint |
| trigger_depends |
| trigger_discovery |
| trigger_tag |
| triggers |
| users |
| users_groups |
| usrgrp |
| valuemaps |
| widget |
| widget_field |
+----------------------------+
144 rows in set (0.00 sec) MariaDB [zabbix]>
2. 查看hosts数据表
MariaDB [zabbix]> show create table hosts \G;
*************************** 1. row ***************************
Table: hosts
Create Table: CREATE TABLE `hosts` (
`hostid` bigint(20) unsigned NOT NULL,
`proxy_hostid` bigint(20) unsigned DEFAULT NULL,
`host` varchar(128) COLLATE utf8_bin NOT NULL DEFAULT '',
`status` int(11) NOT NULL DEFAULT '0',
`disable_until` int(11) NOT NULL DEFAULT '0',
`error` varchar(2048) COLLATE utf8_bin NOT NULL DEFAULT '',
`available` int(11) NOT NULL DEFAULT '0',
`errors_from` int(11) NOT NULL DEFAULT '0',
`lastaccess` int(11) NOT NULL DEFAULT '0',
`ipmi_authtype` int(11) NOT NULL DEFAULT '-1',
`ipmi_privilege` int(11) NOT NULL DEFAULT '2',
`ipmi_username` varchar(16) COLLATE utf8_bin NOT NULL DEFAULT '',
`ipmi_password` varchar(20) COLLATE utf8_bin NOT NULL DEFAULT '',
`ipmi_disable_until` int(11) NOT NULL DEFAULT '0',
`ipmi_available` int(11) NOT NULL DEFAULT '0',
`snmp_disable_until` int(11) NOT NULL DEFAULT '0',
`snmp_available` int(11) NOT NULL DEFAULT '0',
`maintenanceid` bigint(20) unsigned DEFAULT NULL,
`maintenance_status` int(11) NOT NULL DEFAULT '0',
`maintenance_type` int(11) NOT NULL DEFAULT '0',
`maintenance_from` int(11) NOT NULL DEFAULT '0',
`ipmi_errors_from` int(11) NOT NULL DEFAULT '0',
`snmp_errors_from` int(11) NOT NULL DEFAULT '0',
`ipmi_error` varchar(2048) COLLATE utf8_bin NOT NULL DEFAULT '',
`snmp_error` varchar(2048) COLLATE utf8_bin NOT NULL DEFAULT '',
`jmx_disable_until` int(11) NOT NULL DEFAULT '0',
`jmx_available` int(11) NOT NULL DEFAULT '0',
`jmx_errors_from` int(11) NOT NULL DEFAULT '0',
`jmx_error` varchar(2048) COLLATE utf8_bin NOT NULL DEFAULT '',
`name` varchar(128) COLLATE utf8_bin NOT NULL DEFAULT '',
`flags` int(11) NOT NULL DEFAULT '0',
`templateid` bigint(20) unsigned DEFAULT NULL,
`description` text COLLATE utf8_bin NOT NULL,
`tls_connect` int(11) NOT NULL DEFAULT '1',
`tls_accept` int(11) NOT NULL DEFAULT '1',
`tls_issuer` varchar(1024) COLLATE utf8_bin NOT NULL DEFAULT '',
`tls_subject` varchar(1024) COLLATE utf8_bin NOT NULL DEFAULT '',
`tls_psk_identity` varchar(128) COLLATE utf8_bin NOT NULL DEFAULT '',
`tls_psk` varchar(512) COLLATE utf8_bin NOT NULL DEFAULT '',
`proxy_address` varchar(255) COLLATE utf8_bin NOT NULL DEFAULT '',
`auto_compress` int(11) NOT NULL DEFAULT '1',
PRIMARY KEY (`hostid`),
KEY `hosts_1` (`host`),
KEY `hosts_2` (`status`),
KEY `hosts_3` (`proxy_hostid`),
KEY `hosts_4` (`name`),
KEY `hosts_5` (`maintenanceid`),
KEY `c_hosts_3` (`templateid`),
CONSTRAINT `c_hosts_3` FOREIGN KEY (`templateid`) REFERENCES `hosts` (`hostid`) ON DELETE CASCADE,
CONSTRAINT `c_hosts_1` FOREIGN KEY (`proxy_hostid`) REFERENCES `hosts` (`hostid`),
CONSTRAINT `c_hosts_2` FOREIGN KEY (`maintenanceid`) REFERENCES `maintenances` (`maintenanceid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin
1 row in set (0.00 sec) ERROR: No query specified
3. 查看interface数据表
MariaDB [zabbix]> show create table interface \G;
*************************** 1. row ***************************
Table: interface
Create Table: CREATE TABLE `interface` (
`interfaceid` bigint(20) unsigned NOT NULL,
`hostid` bigint(20) unsigned NOT NULL,
`main` int(11) NOT NULL DEFAULT '0',
`type` int(11) NOT NULL DEFAULT '0',
`useip` int(11) NOT NULL DEFAULT '1',
`ip` varchar(64) COLLATE utf8_bin NOT NULL DEFAULT '127.0.0.1',
`dns` varchar(255) COLLATE utf8_bin NOT NULL DEFAULT '',
`port` varchar(64) COLLATE utf8_bin NOT NULL DEFAULT '10050',
`bulk` int(11) NOT NULL DEFAULT '1',
PRIMARY KEY (`interfaceid`),
KEY `interface_1` (`hostid`,`type`),
KEY `interface_2` (`ip`,`dns`),
CONSTRAINT `c_interface_1` FOREIGN KEY (`hostid`) REFERENCES `hosts` (`hostid`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin
1 row in set (0.00 sec) ERROR: No query specified
目前zabbix系统监控2台主机：
查看一下hostid 10263 信息：
MariaDB [zabbix]> select * from interface a inner join hosts b on a.hostid=b.hostid where a.hostid=10263 \G;
*************************** 1. row ***************************
interfaceid: 3
hostid: 10263
main: 1
type: 2
useip: 1
ip: 172.18.100.25
dns:
port: 161
bulk: 1
hostid: 10263
proxy_hostid: NULL
host: CARLOS_test_100.25
status: 0
disable_until: 0
error:
available: 0
errors_from: 0
lastaccess: 0
ipmi_authtype: -1
ipmi_privilege: 2
ipmi_username:
ipmi_password:
ipmi_disable_until: 0
ipmi_available: 0
snmp_disable_until: 0
snmp_available: 1
maintenanceid: NULL
maintenance_status: 0
maintenance_type: 0
maintenance_from: 0
ipmi_errors_from: 0
snmp_errors_from: 0
ipmi_error:
snmp_error:
jmx_disable_until: 0
jmx_available: 0
jmx_errors_from: 0
jmx_error:
name: CARLOS_test_100.25
flags: 0
templateid: NULL
description:
tls_connect: 1
tls_accept: 1
tls_issuer:
tls_subject:
tls_psk_identity:
tls_psk:
proxy_address:
auto_compress: 1
4. 查看items数据表
MariaDB [zabbix]> show create table items \G;
*************************** 1. row ***************************
Table: items
Create Table: CREATE TABLE `items` (
`itemid` bigint(20) unsigned NOT NULL,
`type` int(11) NOT NULL DEFAULT '0',
`snmp_community` varchar(64) COLLATE utf8_bin NOT NULL DEFAULT '',
`snmp_oid` varchar(512) COLLATE utf8_bin NOT NULL DEFAULT '',
`hostid` bigint(20) unsigned NOT NULL,
`name` varchar(255) COLLATE utf8_bin NOT NULL DEFAULT '',
`key_` varchar(255) COLLATE utf8_bin NOT NULL DEFAULT '',
`delay` varchar(1024) COLLATE utf8_bin NOT NULL DEFAULT '0',
`history` varchar(255) COLLATE utf8_bin NOT NULL DEFAULT '90d',
`trends` varchar(255) COLLATE utf8_bin NOT NULL DEFAULT '365d',
`status` int(11) NOT NULL DEFAULT '0',
`value_type` int(11) NOT NULL DEFAULT '0',
`trapper_hosts` varchar(255) COLLATE utf8_bin NOT NULL DEFAULT '',
`units` varchar(255) COLLATE utf8_bin NOT NULL DEFAULT '',
`snmpv3_securityname` varchar(64) COLLATE utf8_bin NOT NULL DEFAULT '',
`snmpv3_securitylevel` int(11) NOT NULL DEFAULT '0',
`snmpv3_authpassphrase` varchar(64) COLLATE utf8_bin NOT NULL DEFAULT '',
`snmpv3_privpassphrase` varchar(64) COLLATE utf8_bin NOT NULL DEFAULT '',
`formula` varchar(255) COLLATE utf8_bin NOT NULL DEFAULT '',
`error` varchar(2048) COLLATE utf8_bin NOT NULL DEFAULT '',
`lastlogsize` bigint(20) unsigned NOT NULL DEFAULT '0',
`logtimefmt` varchar(64) COLLATE utf8_bin NOT NULL DEFAULT '',
`templateid` bigint(20) unsigned DEFAULT NULL,
`valuemapid` bigint(20) unsigned DEFAULT NULL,
`params` text COLLATE utf8_bin NOT NULL,
`ipmi_sensor` varchar(128) COLLATE utf8_bin NOT NULL DEFAULT '',
`authtype` int(11) NOT NULL DEFAULT '0',
`username` varchar(64) COLLATE utf8_bin NOT NULL DEFAULT '',
`password` varchar(64) COLLATE utf8_bin NOT NULL DEFAULT '',
`publickey` varchar(64) COLLATE utf8_bin NOT NULL DEFAULT '',
`privatekey` varchar(64) COLLATE utf8_bin NOT NULL DEFAULT '',
`mtime` int(11) NOT NULL DEFAULT '0',
`flags` int(11) NOT NULL DEFAULT '0',
`interfaceid` bigint(20) unsigned DEFAULT NULL,
`port` varchar(64) COLLATE utf8_bin NOT NULL DEFAULT '',
`description` text COLLATE utf8_bin NOT NULL,
`inventory_link` int(11) NOT NULL DEFAULT '0',
`lifetime` varchar(255) COLLATE utf8_bin NOT NULL DEFAULT '30d',
`snmpv3_authprotocol` int(11) NOT NULL DEFAULT '0',
`snmpv3_privprotocol` int(11) NOT NULL DEFAULT '0',
`state` int(11) NOT NULL DEFAULT '0',
`snmpv3_contextname` varchar(255) COLLATE utf8_bin NOT NULL DEFAULT '',
`evaltype` int(11) NOT NULL DEFAULT '0',
`jmx_endpoint` varchar(255) COLLATE utf8_bin NOT NULL DEFAULT '',
`master_itemid` bigint(20) unsigned DEFAULT NULL,
`timeout` varchar(255) COLLATE utf8_bin NOT NULL DEFAULT '3s',
`url` varchar(2048) COLLATE utf8_bin NOT NULL DEFAULT '',
`query_fields` varchar(2048) COLLATE utf8_bin NOT NULL DEFAULT '',
`posts` text COLLATE utf8_bin NOT NULL,
`status_codes` varchar(255) COLLATE utf8_bin NOT NULL DEFAULT '200',
`follow_redirects` int(11) NOT NULL DEFAULT '1',
`post_type` int(11) NOT NULL DEFAULT '0',
`http_proxy` varchar(255) COLLATE utf8_bin NOT NULL DEFAULT '',
`headers` text COLLATE utf8_bin NOT NULL,
`retrieve_mode` int(11) NOT NULL DEFAULT '0',
`request_method` int(11) NOT NULL DEFAULT '0',
`output_format` int(11) NOT NULL DEFAULT '0',
`ssl_cert_file` varchar(255) COLLATE utf8_bin NOT NULL DEFAULT '',
`ssl_key_file` varchar(255) COLLATE utf8_bin NOT NULL DEFAULT '',
`ssl_key_password` varchar(64) COLLATE utf8_bin NOT NULL DEFAULT '',
`verify_peer` int(11) NOT NULL DEFAULT '0',
`verify_host` int(11) NOT NULL DEFAULT '0',
`allow_traps` int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`itemid`),
UNIQUE KEY `items_1` (`hostid`,`key_`),
KEY `items_3` (`status`),
KEY `items_4` (`templateid`),
KEY `items_5` (`valuemapid`),
KEY `items_6` (`interfaceid`),
KEY `items_7` (`master_itemid`),
CONSTRAINT `c_items_5` FOREIGN KEY (`master_itemid`) REFERENCES `items` (`itemid`) ON DELETE CASCADE,
CONSTRAINT `c_items_1` FOREIGN KEY (`hostid`) REFERENCES `hosts` (`hostid`) ON DELETE CASCADE,
CONSTRAINT `c_items_2` FOREIGN KEY (`templateid`) REFERENCES `items` (`itemid`) ON DELETE CASCADE,
CONSTRAINT `c_items_3` FOREIGN KEY (`valuemapid`) REFERENCES `valuemaps` (`valuemapid`),
CONSTRAINT `c_items_4` FOREIGN KEY (`interfaceid`) REFERENCES `interface` (`interfaceid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin
查看当前zabbix系统items数量：
select count(*) from interface a inner join hosts b on a.hostid=b.hostid inner join items c on c.hostid=a.hostid where a.hostid=10263 \G;
查看当前zabbix系统单台主机key值与cpu相关：
select c.key_,b.hostid,a.ip from interface a inner join hosts b on a.hostid=b.hostid inner join items c on c.hostid=a.hostid where a.hostid=
10263 and c.key_ like "%cpu%" \G;
查看一下与主机相关联的模板数：
show tables like "%host%";
select * from hosts_templates;
以上显示主机hostid=10263一共关联6个模板，在zabbix页面确认一下：
5. 查看目前zabbix系统history表：
select * from history
MariaDB [zabbix]> select * from items where itemid=28565 and value_type=0 \G;
*************************** 1. row ***************************
itemid: 28565
type: 3
snmp_community:
snmp_oid:
hostid: 10263
name: ICMP response time
key_: icmppingsec
delay: 60s
history: 90d
trends: 365d
status: 0
value_type: 0
trapper_hosts:
units:
snmpv3_securityname:
snmpv3_securitylevel: 0
snmpv3_authpassphrase:
snmpv3_privpassphrase:
formula:
error:
lastlogsize: 0
logtimefmt:
templateid: 28511
valuemapid: NULL
params:
ipmi_sensor:
authtype: 0
username:
password:
publickey:
privatekey:
mtime: 0
flags: 0
interfaceid: 3
port:
description:
inventory_link: 0
lifetime: 30d
snmpv3_authprotocol: 0
snmpv3_privprotocol: 0
state: 0
snmpv3_contextname:
evaltype: 0
jmx_endpoint:
master_itemid: NULL
timeout: 3s
url:
query_fields:
posts:
status_codes: 200
follow_redirects: 1
post_type: 0
http_proxy:
headers:
retrieve_mode: 0
request_method: 0
output_format: 0
ssl_cert_file:
ssl_key_file:
ssl_key_password:
verify_peer: 0
verify_host: 0
allow_traps: 0
6. 查看目前zabbix系统triggers表：
.....