019-zabbix数据库表详解

1. 查看⽬前zabbix系统所有数据表：

12345671011121314151617[root@localhost /]# mysql -u root -p18Enter password:

19Welcome to the MariaDB monitor. Commands end with ; or \\g.20Your MariaDB connection id is 170786

Server version: 5.5.60-MariaDB MariaDB Server

21

22Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

23

24Type 'help;' or '\\h' for help. Type '\\c' to clear the current input statement.

25

26MariaDB [(none)]> show databases;27+--------------------+28| Database |+--------------------+

29| information_schema |30| mysql |

31| performance_schema |32| test |33| zabbix |+--------------------+345 rows in set (0.01 sec)

35

36MariaDB [(none)]> use zabbix

37Reading table information for completion of table and column names38You can turn off this feature to get a quicker startup with -A

39

40Database changed

41MariaDB [zabbix]> show tables;42+----------------------------+

| Tables_in_zabbix |43+----------------------------+44| acknowledges |45| actions |46| alerts |

| application_discovery |47| application_prototype |48| application_template |49| applications |50| auditlog || auditlog_details |51| autoreg_host |52| conditions |53| config || corr_condition |55| corr_condition_group || corr_condition_tag |56| corr_condition_tagpair |57| corr_condition_tagvalue |58| corr_operation |59| correlation || dashboard |60| dashboard_user |61| dashboard_usrgrp |62| dbversion |63| dchecks || dhosts || drules |65| dservices |66| escalations |67

| event_recovery || event_suppress || event_tag |

68| events |69| expressions |

| functions |70| globalmacro |71| globalvars |72| graph_discovery |73| graph_theme |

| graphs |74| graphs_items |75| group_discovery |76| group_prototype |77| history |

| history_log |78| history_str |79| history_text |80| history_uint |81| host_discovery |82| host_inventory |

| hostmacro |83| hosts |84| hosts_groups |85| hosts_templates |86| housekeeper |

| hstgrp |87| httpstep |88| httpstep_field || httpstepitem |90| httptest |

| httptest_field |91| httptestitem |92| icon_map |93| icon_mapping |94| ids |

| images |95

| interface |96| interface_discovery |97| item_application_prototype |98| item_condition |99| item_discovery |100| item_preproc |

| items |101| items_applications |102| maintenance_tag |103| maintenances |104| maintenances_groups |

| maintenances_hosts |105| maintenances_windows |106| mappings |107| media |108| media_type |

| opcommand |109| opcommand_grp |110| opcommand_hst |111| opconditions |112| operations |

| opgroup |113| opinventory |114| opmessage |115| opmessage_grp |116| opmessage_usr |117| optemplate |

| problem |118| problem_tag |119| profiles |

120| proxy_autoreg_host |121| proxy_dhistory |

| proxy_history |122| regexps |123| rights |124| screen_user |125| screen_usrgrp |

| screens |126| screens_items |127| scripts |128| service_alarms |129| services |

| services_links |130| services_times |131| sessions |132| slides |133| slideshow_user |134| slideshow_usrgrp |

| slideshows |

135| sysmap_element_trigger |136| sysmap_element_url |137| sysmap_shape |138| sysmap_url |

| sysmap_user |139| sysmap_usrgrp |140| sysmaps |141| sysmaps_elements |142| sysmaps_link_triggers |

| sysmaps_links |143| tag_filter |144| task |

145| task_acknowledge |146| task_check_now |

| task_close_problem |147| task_remote_command |148| task_remote_command_result |149| timeperiods |150| trends |151| trends_uint |

| trigger_depends |152| trigger_discovery |153| trigger_tag |1| triggers |155| users |

| users_groups |156| usrgrp |157| valuemaps |158| widget |159| widget_field |

+----------------------------+160

144 rows in set (0.00 sec)161

162MariaDB [zabbix]>1631165166

167168169170171172173174175176177178179

2. 查看hosts数据表

12345

6MariaDB [zabbix]> show create table hosts \\G;

7*************************** 1. row ***************************8 Table: hosts

9Create Table: CREATE TABLE `hosts` ( `hostid` bigint(20) unsigned NOT NULL,

10 `proxy_hostid` bigint(20) unsigned DEFAULT NULL,

11 `host` varchar(128) COLLATE utf8_bin NOT NULL DEFAULT '',12 `status` int(11) NOT NULL DEFAULT '0',

13 `disable_until` int(11) NOT NULL DEFAULT '0',

14 `error` varchar(2048) COLLATE utf8_bin NOT NULL DEFAULT '', `available` int(11) NOT NULL DEFAULT '0',15 `errors_from` int(11) NOT NULL DEFAULT '0',16 `lastaccess` int(11) NOT NULL DEFAULT '0',17 `ipmi_authtype` int(11) NOT NULL DEFAULT '-1',18 `ipmi_privilege` int(11) NOT NULL DEFAULT '2',

`ipmi_username` varchar(16) COLLATE utf8_bin NOT NULL DEFAULT '',19 `ipmi_password` varchar(20) COLLATE utf8_bin NOT NULL DEFAULT '',20 `ipmi_disable_until` int(11) NOT NULL DEFAULT '0',21 `ipmi_available` int(11) NOT NULL DEFAULT '0',

22 `snmp_disable_until` int(11) NOT NULL DEFAULT '0', `snmp_available` int(11) NOT NULL DEFAULT '0',23 `maintenanceid` bigint(20) unsigned DEFAULT NULL,24 `maintenance_status` int(11) NOT NULL DEFAULT '0',25 `maintenance_type` int(11) NOT NULL DEFAULT '0',26 `maintenance_from` int(11) NOT NULL DEFAULT '0', `ipmi_errors_from` int(11) NOT NULL DEFAULT '0',27 `snmp_errors_from` int(11) NOT NULL DEFAULT '0',

28 `ipmi_error` varchar(2048) COLLATE utf8_bin NOT NULL DEFAULT '',29 `snmp_error` varchar(2048) COLLATE utf8_bin NOT NULL DEFAULT '',30 `jmx_disable_until` int(11) NOT NULL DEFAULT '0',31 `jmx_available` int(11) NOT NULL DEFAULT '0', `jmx_errors_from` int(11) NOT NULL DEFAULT '0',

32 `jmx_error` varchar(2048) COLLATE utf8_bin NOT NULL DEFAULT '',33 `name` varchar(128) COLLATE utf8_bin NOT NULL DEFAULT '',34 `flags` int(11) NOT NULL DEFAULT '0',

35 `templateid` bigint(20) unsigned DEFAULT NULL, `description` text COLLATE utf8_bin NOT NULL,36 `tls_connect` int(11) NOT NULL DEFAULT '1',37 `tls_accept` int(11) NOT NULL DEFAULT '1',

38 `tls_issuer` varchar(1024) COLLATE utf8_bin NOT NULL DEFAULT '',39 `tls_subject` varchar(1024) COLLATE utf8_bin NOT NULL DEFAULT '', `tls_psk_identity` varchar(128) COLLATE utf8_bin NOT NULL DEFAULT '',40 `tls_psk` varchar(512) COLLATE utf8_bin NOT NULL DEFAULT '',

41 `proxy_address` varchar(255) COLLATE utf8_bin NOT NULL DEFAULT '',42 `auto_compress` int(11) NOT NULL DEFAULT '1',43 PRIMARY KEY (`hostid`), KEY `hosts_1` (`host`),44 KEY `hosts_2` (`status`),

45 KEY `hosts_3` (`proxy_hostid`),46 KEY `hosts_4` (`name`),

47 KEY `hosts_5` (`maintenanceid`),48 KEY `c_hosts_3` (`templateid`),

CONSTRAINT `c_hosts_3` FOREIGN KEY (`templateid`) REFERENCES `hosts` (`hostid`) ON DELETE CASCADE,49 CONSTRAINT `c_hosts_1` FOREIGN KEY (`proxy_hostid`) REFERENCES `hosts` (`hostid`),

50 CONSTRAINT `c_hosts_2` FOREIGN KEY (`maintenanceid`) REFERENCES `maintenances` (`maintenanceid`)51) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin

521 row in set (0.00 sec)53

ERROR: No query specified5556575859

3. 查看interface数据表

12

MariaDB [zabbix]> show create table interface \\G;3*************************** 1. row ***************************4 Table: interface

5Create Table: CREATE TABLE `interface` (6 `interfaceid` bigint(20) unsigned NOT NULL,7 `hostid` bigint(20) unsigned NOT NULL, `main` int(11) NOT NULL DEFAULT '0',8 `type` int(11) NOT NULL DEFAULT '0',9 `useip` int(11) NOT NULL DEFAULT '1',

10 `ip` varchar() COLLATE utf8_bin NOT NULL DEFAULT '127.0.0.1',11 `dns` varchar(255) COLLATE utf8_bin NOT NULL DEFAULT '',

`port` varchar() COLLATE utf8_bin NOT NULL DEFAULT '10050',12 `bulk` int(11) NOT NULL DEFAULT '1',13 PRIMARY KEY (`interfaceid`),14 KEY `interface_1` (`hostid`,`type`),15 KEY `interface_2` (`ip`,`dns`),

CONSTRAINT `c_interface_1` FOREIGN KEY (`hostid`) REFERENCES `hosts` (`hostid`) ON DELETE CASCADE16) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin171 row in set (0.00 sec)18

19ERROR: No query specified2021

⽬前zabbix系统监控2台主机：

查看⼀下hostid 10263 信息：　

12345

6MariaDB [zabbix]> select * from interface a inner join hosts b on a.hostid=b.hostid where a.hostid=10263 \\G;7*************************** 1. row ***************************8 interfaceid: 39 hostid: 10263 main: 110 type: 211 useip: 1

12 ip: 172.18.100.2513 dns: port: 16114 bulk: 1

15 hostid: 1026316 proxy_hostid: NULL

17 host: CARLOS_test_100.25 status: 018 disable_until: 019 error:20 available: 021 errors_from: 022 lastaccess: 0 ipmi_authtype: -123 ipmi_privilege: 224 ipmi_username:25 ipmi_password:26ipmi_disable_until: 0 ipmi_available: 027snmp_disable_until: 028 snmp_available: 129 maintenanceid: NULL30maintenance_status: 0 maintenance_type: 031 maintenance_from: 032 ipmi_errors_from: 033 snmp_errors_from: 034 ipmi_error: snmp_error:35 jmx_disable_until: 036 jmx_available: 037 jmx_errors_from: 038 jmx_error:

name: CARLOS_test_100.25

39 flags: 0

40 templateid: NULL description:41 tls_connect: 142 tls_accept: 143 tls_issuer:44 tls_subject: tls_psk_identity:45 tls_psk:46 proxy_address:47 auto_compress: 14849505152

4. 查看items数据表

12345678

MariaDB [zabbix]> show create table items \\G;

9*************************** 1. row ***************************10 Table: items

11Create Table: CREATE TABLE `items` (12 `itemid` bigint(20) unsigned NOT NULL,13 `type` int(11) NOT NULL DEFAULT '0',

`snmp_community` varchar() COLLATE utf8_bin NOT NULL DEFAULT '',14 `snmp_oid` varchar(512) COLLATE utf8_bin NOT NULL DEFAULT '',15 `hostid` bigint(20) unsigned NOT NULL,

16 `name` varchar(255) COLLATE utf8_bin NOT NULL DEFAULT '',17 `key_` varchar(255) COLLATE utf8_bin NOT NULL DEFAULT '', `delay` varchar(1024) COLLATE utf8_bin NOT NULL DEFAULT '0',18 `history` varchar(255) COLLATE utf8_bin NOT NULL DEFAULT '90d',19 `trends` varchar(255) COLLATE utf8_bin NOT NULL DEFAULT '365d',20 `status` int(11) NOT NULL DEFAULT '0',

21 `value_type` int(11) NOT NULL DEFAULT '0',

`trapper_hosts` varchar(255) COLLATE utf8_bin NOT NULL DEFAULT '',22 `units` varchar(255) COLLATE utf8_bin NOT NULL DEFAULT '',

23 `snmpv3_securityname` varchar() COLLATE utf8_bin NOT NULL DEFAULT '',24 `snmpv3_securitylevel` int(11) NOT NULL DEFAULT '0',

25 `snmpv3_authpassphrase` varchar() COLLATE utf8_bin NOT NULL DEFAULT '', `snmpv3_privpassphrase` varchar() COLLATE utf8_bin NOT NULL DEFAULT '',26 `formula` varchar(255) COLLATE utf8_bin NOT NULL DEFAULT '',27 `error` varchar(2048) COLLATE utf8_bin NOT NULL DEFAULT '',28 `lastlogsize` bigint(20) unsigned NOT NULL DEFAULT '0',

29 `logtimefmt` varchar() COLLATE utf8_bin NOT NULL DEFAULT '',30 `templateid` bigint(20) unsigned DEFAULT NULL,

`valuemapid` bigint(20) unsigned DEFAULT NULL,

31 `params` text COLLATE utf8_bin NOT NULL,

32 `ipmi_sensor` varchar(128) COLLATE utf8_bin NOT NULL DEFAULT '', `authtype` int(11) NOT NULL DEFAULT '0',

33 `username` varchar() COLLATE utf8_bin NOT NULL DEFAULT '',34 `password` varchar() COLLATE utf8_bin NOT NULL DEFAULT '',35 `publickey` varchar() COLLATE utf8_bin NOT NULL DEFAULT '',36 `privatekey` varchar() COLLATE utf8_bin NOT NULL DEFAULT '', `mtime` int(11) NOT NULL DEFAULT '0',37 `flags` int(11) NOT NULL DEFAULT '0',

38 `interfaceid` bigint(20) unsigned DEFAULT NULL,

39 `port` varchar() COLLATE utf8_bin NOT NULL DEFAULT '',40 `description` text COLLATE utf8_bin NOT NULL, `inventory_link` int(11) NOT NULL DEFAULT '0',

41 `lifetime` varchar(255) COLLATE utf8_bin NOT NULL DEFAULT '30d',42 `snmpv3_authprotocol` int(11) NOT NULL DEFAULT '0',43 `snmpv3_privprotocol` int(11) NOT NULL DEFAULT '0',44 `state` int(11) NOT NULL DEFAULT '0',

45 `snmpv3_contextname` varchar(255) COLLATE utf8_bin NOT NULL DEFAULT '', `evaltype` int(11) NOT NULL DEFAULT '0',

46 `jmx_endpoint` varchar(255) COLLATE utf8_bin NOT NULL DEFAULT '',47 `master_itemid` bigint(20) unsigned DEFAULT NULL,

48 `timeout` varchar(255) COLLATE utf8_bin NOT NULL DEFAULT '3s',49 `url` varchar(2048) COLLATE utf8_bin NOT NULL DEFAULT '',

`query_fields` varchar(2048) COLLATE utf8_bin NOT NULL DEFAULT '',50 `posts` text COLLATE utf8_bin NOT NULL,

51 `status_codes` varchar(255) COLLATE utf8_bin NOT NULL DEFAULT '200',52 `follow_redirects` int(11) NOT NULL DEFAULT '1',53 `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,55 `retrieve_mode` int(11) NOT NULL DEFAULT '0',56 `request_method` int(11) NOT NULL DEFAULT '0',57 `output_format` int(11) NOT NULL DEFAULT '0',

`ssl_cert_file` varchar(255) COLLATE utf8_bin NOT NULL DEFAULT '',58 `ssl_key_file` varchar(255) COLLATE utf8_bin NOT NULL DEFAULT '',

59 `ssl_key_password` varchar() COLLATE utf8_bin NOT NULL DEFAULT '',60 `verify_peer` int(11) NOT NULL DEFAULT '0',61 `verify_host` int(11) NOT NULL DEFAULT '0',62 `allow_traps` int(11) NOT NULL DEFAULT '0', PRIMARY KEY (`itemid`),

63 UNIQUE KEY `items_1` (`hostid`,`key_`), KEY `items_3` (`status`),65 KEY `items_4` (`templateid`),66 KEY `items_5` (`valuemapid`), KEY `items_6` (`interfaceid`),67 KEY `items_7` (`master_itemid`),

68 CONSTRAINT `c_items_5` FOREIGN KEY (`master_itemid`) REFERENCES `items` (`itemid`) ON DELETE CASCADE,69 CONSTRAINT `c_items_1` FOREIGN KEY (`hostid`) REFERENCES `hosts` (`hostid`) ON DELETE CASCADE,70 CONSTRAINT `c_items_2` FOREIGN KEY (`templateid`) REFERENCES `items` (`itemid`) ON DELETE CASCADE, CONSTRAINT `c_items_3` FOREIGN KEY (`valuemapid`) REFERENCES `valuemaps` (`valuemapid`),71 CONSTRAINT `c_items_4` FOREIGN KEY (`interfaceid`) REFERENCES `interface` (`interfaceid`)72) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin　　7374757677787980

查看当前zabbix系统items数量：

1select 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相关：

1select 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=210263 and c.key_ like \"%cpu%\" \\G;

查看⼀下与主机相关联的模板数：1show tables like \"%host%\";

1select * from hosts_templates;

以上显⽰主机hostid=10263⼀共关联6个模板，在zabbix页⾯确认⼀下：

5. 查看⽬前zabbix系统history表：

1select * from history

123456

7MariaDB [zabbix]> select * from items where itemid=28565 and value_type=0 \\G;8*************************** 1. row ***************************9 itemid: 2856510 type: 3

snmp_community:11 snmp_oid:

12 hostid: 10263

13 name: ICMP response time14 key_: icmppingsec delay: 60s15 history: 90d16 trends: 365d17 status: 018 value_type: 0 trapper_hosts:19 units:

20 snmpv3_securityname:21 snmpv3_securitylevel: 022snmpv3_authpassphrase:snmpv3_privpassphrase:23 formula:24 error:25 lastlogsize: 026 logtimefmt:

27 templateid: 28511 valuemapid: NULL28 params:29 ipmi_sensor:30 authtype: 031 username: password:32 publickey:33 privatekey:34 mtime: 035 flags: 0 interfaceid: 336 port:37 description:38 inventory_link: 039 lifetime: 30d snmpv3_authprotocol: 040 snmpv3_privprotocol: 041

42 state: 0

43 snmpv3_contextname: evaltype: 044 jmx_endpoint:

45 master_itemid: NULL46 timeout: 3s47 url: query_fields:48 posts:

49 status_codes: 20050 follow_redirects: 151 post_type: 0 http_proxy:52 headers:53 retrieve_mode: 0 request_method: 055 output_format: 056 ssl_cert_file: ssl_key_file:57 ssl_key_password:58 verify_peer: 059 verify_host: 060 allow_traps: 061626365

6. 查看⽬前zabbix系统triggers表：

⾃学Zabbix之路15.1 Zabbix数据库表结构简单解析-Hosts表、Hosts_groups表、Interface表

说在前⾯：

数据库中的表的名称都是复数，⽐如存放Host信息的表的名字是Hosts等。数据库操作有风险，⼀旦出问题会造成Zabbix crash。需要谨慎操作。普通的查询可以在备库上进⾏。两边数据是实时同步的。概述：

Zabbix数据库共有108个表，表的名称都是复数的。

针对Zabbix中的每⼀个资源，都有⼀张表与其对应，⽐如hosts表，items表等。

每⼀张表中都有⼀个id字段，如hosts表中的hostid，items中的itemid等。⽽资源之间的关联关系是通过外键来完成的。

资源之间的关联关系是通过外键来完成的。⽐如host和item的关联关系，就是在items表中使⽤hostid与hosts表中的资源进⾏关联。

1. Hosts表

“host”就是⼀台被监控的对象。Hosts表结构如下：

Hostid：唯⼀标识Host在Zabbix及数据库中的id。不同表之间的关联也要⽤id。Proxy_hostid：若启⽤“proxy-server”架构，才会出现被监控机器的proxy_hostid。Host：被监控机器的名字。Dns：DNS的名称。Useip：是否⽤ip监控。Port：监控的端⼝。

Status：机器⽬前的状态。“0”为正常监控，“1”为disable。

disable_util，error，available，errors_from(ipmi_disable_util，ipmi_error…和 snmp_disable_until…都是此类)：这⼏个都是Zabbix Poller会去修改的值。我看了下poller.c的代码，当poller在第⼀次取不到值（根据值的类型不同会更新相应的列，Item类型为snmp就会更新snmp_XXX，默认为“zabbix”类型）的时候，会等15秒

（CONFIG_UNREACHABLE_DELAY）来重试，并且⽇志会显⽰“first network error”，如果15秒后依然取不到值，zabbix会在数据库更新这个host取不到值的信息，即这⼏列。并且⽇志⾥显⽰“another network error”。

Lastacess：表⽰proxy最后⼀次⼯作的时间。这⾥的“⼯作”指Zabbix Server收到Proxy数据。Ipmi*、snmp*、jmx*为使⽤这三种监控⽅式时的监控记录。

maintenanceid，maintenance_*：这是Zabbix另⼀个机制Maintaince有关，⽤于使Host 置于维护状态⽽不会报警。简单针对Host的操作：

更新机器的proxy。找到proxy的hostid，更新对⽤host的proxy_hostid：

select hostid from hosts where host='ProxyA' and ip='0.0.0.0'; -- get hostid: 1234 update hosts set proxy_hostid=1234 where host='Host_To_Update_Proxy'; 得到更新的Host状态（enable/disable）如下：

update hosts set status='0' where host='Host_To_Enable'; update hosts set status='1' where host='Host_To_Disable';

2.Hosts_groups 表

hosts_groups保存了host（主机）与host groups（主机组）的关联关系。

这部分信息可以在我们⾃⼰做⼀些批量查询，例如查询关联到某个主机组的所有设备的IP 、存活状态等，进⼀步去查询该批量设备的load、IO、mem等统计信息。

3.Interface表

Interface表⽤于存储每⼀个host的接⼝信息，此表与hosts表分离，⽅便了⽤户修改信息与管理。