Zabbix 5.2.6 MySQL数据库表结构

Kevin song

发布于 2021-04-29 10:57:12

4.1K0

发布于 2021-04-29 10:57:12

文章被收录于专栏：运维监控日志分析运维监控日志分析

概述

Zabbix 5.2.6 数据库共有170张表，Zabbix 数据表的名称都是复数。资源之间的关联关系是通过外键来完成的。比如host和item的关联关系，就是在items表中使用hostid与hosts表中的资源进行关联。

本文将介绍Zabbix 数据库中主要的数据表，以及如何对数据库中表进行增删改查操作，熟悉Zabbix 数据库表结构；

数据库版本

select version();

统计 zabbix 数据库中表项

select count(8) tables,table_schema from information_schema.tables where table_schema = 'zabbix';

查询zabbix 数据库大小

select table_schema as '数据库', sum(table_rows) as '记录数', sum(truncate(data_length/1024/1024, 2)) as '数据容量(MB)', sum(truncate(index_length/1024/1024, 2)) as '索引容量(MB)' from information_schema.tables where table_schema='zabbix';

查询zabbix库各表容量大小

select table_schema as '数据库', table_name as '表名', table_rows as '记录数', truncate(data_length/1024/1024, 2) as '数据容量(MB)', truncate(index_length/1024/1024, 2) as '索引容量(MB)' from information_schema.tables where table_schema='zabbix' order by data_length desc, index_length desc;

hosts

存储被监控主机的信息

常用字段介绍

Hostid：唯一标识Host在Zabbix及数据库中的id。不同表之间的关联也要用hostid。

Proxy_hostid：若启用“proxy-server”架构，才会出现被监控机器的proxy_hostid。

Host：被监控机器的名字。

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

查询name为Zabbix_Proxy2的主机信息

select hostid,proxy_hostid,host,status,name from hosts where name='Zabbix server';

批量查询主机信息

select hostid,proxy_hostid,host,status,name from hosts where name IN ('Zabbix server','ecs-01');

停用 name为ecs-01主机

update hosts set status='1' where host='ecs-01';

启用 name为ecs-01主机

update hosts set status='0' where host='ecs-01';

items

存储所有监控项

常用字段介绍

itemid：item的id

type：item的type，和前端见面配置item的type的对应。数据库中，这一列的值是0到17的数字，分别代表不同的类型。

hostid：item所在的host的hostid。如果该item是属于template，那么这里显示的是templateid

name：item的名字

key_：item的key

delay：这里的delay，实际就是在配置item时候配置的“Update Interval”

trends：前端配置中的存储trend的时间

status：item的状态

0：item是enabled状态

1：item是disabled状态

3：numeric unsigned

4：text

value_type：item返回值的类型，配置item时候配置的“Type of Information”

trapper_hosts：当item为trapper类型的时候，记录了也许发送的host

flags：

0：表示一个普通的item

4：表示是discover生成的item

interfaceid：当使用hosts类型的item时生效，用来选择host上不同的interface

port：使用SNMP监控室使用的端口号

description：Item配置界面上的“Description”

state：当前item的状态

0：正常

1：not supported

利用hostid在items表中查询该主机有那些监控项，itemid为监控项的id，name为监控项的名称，key_为键值，也就是表达式，怎么对监控项取值；

查询hostid 为10397 的监控项

select itemid,name, key_ from items where hostid=10397;

查询itemid 为34344和34336的监控项

select itemid,name,key_ from items where itemid IN ('34344','34336');

查询主机hostid=10397 和 name=memory 的监控项

select itemid,name, key_ from items where hostid=10397 AND name='Used memory';

查询主机hostid=10084 和 Used memory 和 name=Memory的监控项

select itemid,name, key_ from items where hostid=10084 AND name IN ('Used memory','Memory') ;

同时关联items 和hostid 表查询

select itemid,name, key_ from items where hostid IN (select hostid from hosts where name IN ('Zabbix server','ecs-01')) AND name IN ('Used memory','Memory');

triggers

存储所有触发器

查询triggerid=18495的触发器信息

select * from triggers where triggerid=18495\G;

常用字段介绍

expression: 问题触发器，{22217} 对应的是functions表中的functionid

description: 触发器名称

recovery_mode: 是否开启恢复表达式

recovery_expression: 恢复触发器{22217} 对应的是functions表中的functionid

functions

存储trigger中使用的表达式，例如max、last、nodata等函数。

查询triggerid=18495函数信息

select * from functions where triggerid=18495;

查询关联的items

select itemid,name, key_ from items where itemid=34444；

实例

触发器 triggerid =18478

依次查询触发器表--函数表达式表--监控项表

triggers -- functions -- items

hosts_groups

存储了host（主机）与host groups（主机组）的关联关系

select hostid,groupid from hosts_groups where hostid IN (10084,10397);

hosts_templates

存储主机和模版或者模版和模版之间的关系

查询hostid为10084关联的模板id

select hosttemplateid,hostid,templateid from hosts_templates where hostid=10084;

interface

存储所有设备的ip和端口的数据。（由于hosts表中不仅保存了设备信息还保存了模版信息，所以统计实际监控的设备，interface表更加准确）

interface 表结构

查询主机ip 对应的hostid

select hostid,ip from interface where ip='127.0.0.1';

批量查询主机ip对应的hostid

select hostid,ip from interface where ip IN ('127.0.0.1','192.168.10.55');

tags 相关表

event_tag

存储事件标签

查询eventid =44 的 event_tag

select eventtagid,eventid,tag,value from event_tag where eventid=44;

host_tag

存储主机和模板标签

查询模板标签

SELECT hosttagid,hostid,tag,value FROM host_tag where hostid=10001;

查询主机标签

SELECT hosttagid,hostid,tag,value FROM host_tag where hostid=10084;

增

INSERT INTO host_tag (hostid,tag,value,hosttagid) VALUES ('10397','zabbixecs','zabbixecs','4');

改

update zabbix.host_tag set value='zabbix' where hosttagid='4';

删

DELETE FROM host_tag WHERE hosttagid='4'

查

SELECT hosttagid,hostid,tag,value FROM host_tag;

批量增

insert into host_tag(hostid,tag,value,hosttagid) values ('10399','qkl','qkl','11'),('10400','qkl','qkl','12'),('10401','qkl','qkl','13');

批量删

DELETE FROM host_tag WHERE hosttagid IN ('11','12','13');

关联查询

select hosttagid,hostid,tag,value from host_tag where hostid IN (select hostid from interface where ip in ('192.168.99.50','192.168.99.51'));

problem_tag

存储问题标签

查询eventid=44 的标签

select problemtagid,eventid,tag,value from problem_tag where eventid=44;

trigger_tag

存储触发器标签

查询触发器id=18495的标签

select triggertagid,triggerid,tag,value from trigger_tag where triggerid=18495;

problem

存储问题事件

查询当前未恢复的问题事件Top 10

/usr/bin/mysql -uzabbix -pzabbix@zabbix zabbix -e "SELECT p.eventid,p.clock,p.name,p.severity FROM problem p WHERE p.source='0' AND p.object='0' AND NOT EXISTS (SELECT NULL FROM event_suppress es WHERE es.eventid=p.eventid) AND p.r_eventid IS NULL ORDER BY p.eventid DESC LIMIT 10;"

查询当前未恢复的问题事件Top10 并将时间戳转换为格式化时间

SELECT p.eventid as 事件id,FROM_UNIXTIME(p.clock,'%Y-%m-%d %H:%i:%s') as clock,p.name as 触发事件,p.severity as 事件等级 FROM problem p WHERE p.source='0' AND p.object='0' AND NOT EXISTS (SELECT NULL FROM event_suppress es WHERE es.eventid=p.eventid) AND p.r_eventid IS NULL ORDER BY p.eventid DESC LIMIT 10;

events

Zabbix 获取到一个数据就会检查跟这个item相关的trigger，然后无论是否触发action，都会生成一个事件

events 表结构

常用字段介绍

source: event可以由多种源头生成，souce记录event是由什么源头生成

0:trigger 生成的event

1:discovery rule生成的event

2:agent auto-registration 生成的event

3: internal 的event

object: 记录和event关联的zabbix 对象

trigge相关的event值为0

discovery相关的event，discovery host 值为1，discovery service 值为2

agent auto-registration 相关的event值为3

internal 相关的event，0表示trigger，4表示item，5表示low-level discovery

value: 和object字段类似，source 值不同这里的含义也不同

1）对于trigger类型的event，0表示trigger状态ok，1表示trigger状态problem

2) 对于discovery类型的event,0表示正常，1表示停止，2表示侦测到数据，3 表示丢失

3）对于internal类型的event，0表示normal正常，1表示unknow 或 not supported状态

sessions

存储每个用户的会话信息，比如用户登陆、注销操作

select sessionid,userid,from_unixtime(lastaccess),status from sessions where status=1;

Alerts

存储了历史的告警事件，可以从这个表里面去做一些统计分析，例如某个部门、某人、某类时间的告警统计，以及更深入的故障发生、恢复时间

alerts表结构

Actions

存储了当触发器触发时，需要采用的动作

actions表结构

查询name= 钉钉告警的动作

用户认证信息表（config、users、users_groups、usrgrp、role、 role_rule ）

用户和认证的信息涉及到四张表，分别为config（配置）、users（用户）、users_groups（用户和用户组关联表）、usrgrp（用户组）、role（用户权限组）、 role_rule（用户权限配置）。

其中，认证类型由config 表中 authentication_type 字段决定，值可以为0,1和2。

0 代表Internal,1代表LDAP，2代表HTTP。

config

存储了全局的参数，例如用户的自定义主题、登陆认证类型等

select authentication_type from config;

users

存储了用户信息，包括用户名，密码，语言，刷新时间等

select userid,alias,name,refresh,roleid,lang from users;

users_groups

存储了用户组和用户的对应关系

select * from users_groups;

usrgrp

存储了用户组的信息

Zabbix 5.2 支持灵活的用户角色，以便对用户权限进行更精细化的管理，从而控制对Zabbix UI、API方法和用户操作的各个部分的访问权。可以创建安全的客户门户、只读用户、仅能访问仪表板的用户、具有有限权限的超级管理员等等。

相关表

(root@127.0.0.1)[zabbix]> show   tables  like '%role%';
+---------------------------+
| Tables_in_zabbix (%role%) |
+---------------------------+
| role                      |
| role_rule                 |
+---------------------------+
2 rows in set (0.001 sec)

查询用户权限组

查询用户权限配置

当 zabbix 用户认证采用LDAP 方式，ldap 服务器异常处理操作步骤

#查询用户信息
select userid,alias,passwd from zabbix.users;
#修改认证类型（0为本地认证）
update zabbix.config set authentication_type=0;
#修改密码
update zabbix.users set passwd=md5("admin") where userid='1';
#刷新策略
flush privileges;

查询Admin用户的ID

select userid,alias,name,refresh,roleid,lang from users;

(root@127.0.0.1)[zabbix]> select userid,alias,name,refresh,roleid,lang from users;
+--------+--------+--------+---------+--------+---------+
| userid | alias  | name   | refresh | roleid | lang    |
+--------+--------+--------+---------+--------+---------+
|      1 | Admin  | Zabbix | 30s     |      3 | zh_CN   |
|      2 | guest  |        | 30s     |      4 | default |
|      3 | songhp |        | 30s     |      2 | default |
+--------+--------+--------+---------+--------+---------+
3 rows in set (0.000 sec)

查询权限组ID

select * from usrgrp;

(root@127.0.0.1)[zabbix]> select * from usrgrp;
+----------+---------------------------+------------+--------------+------------+
| usrgrpid | name                      | gui_access | users_status | debug_mode |
+----------+---------------------------+------------+--------------+------------+
|        7 | Zabbix administrators     |          0 |            0 |          0 |
|        8 | Guests                    |          1 |            0 |          0 |
|        9 | Disabled                  |          0 |            1 |          0 |
|       11 | Enabled debug mode        |          0 |            0 |          1 |
|       12 | No access to the frontend |          3 |            0 |          0 |
+----------+---------------------------+------------+--------------+------------+
5 rows in set (0.001 sec)

查询用户和组的对应关系

select * from users_groups;

select * from users_groups;
+----+----------+--------+
| id | usrgrpid | userid |
+----+----------+--------+
|  4 |        7 |      1 |
|  2 |        8 |      2 |
|  5 |        8 |      3 |
|  3 |        9 |      2 |
+----+----------+--------+
4 rows in set (0.000 sec)

修改用户所在权限组

update users_groups set usrgrpid='7' where userid=3;
flush privileges;

查询用户和组的对应关系

历史数据和趋势数据（history和trends表）

历史数据和趋势数据是Zabbix系统中存储所采集的监控项目数据的两种存储方式，分别为history和trends 表；

历史数据

Zabbix系统针对每个监控项在每次采集时所收集到的数据，这个数据保存Zabbix系统数据库的历史表中。因为是每次所采集到的数据都保存在历史表中，所以如果监控项的更新间隔越小，则在固定时间内所保存到历史表中的数据就越多。所以在我们监控的主机的数量较多的时候，zabbix系统每台产生的数量是非常庞大的，这对数据库是一种负担。因此建议对数据库进行分表或尽量减小历史数据的保留天数，以免给数据库系统带来很大的压力。

趋势数据

趋势数据的趋势方式是对应监控项目的历史数据在一个小时内的平均值、最大值、最小值以及这一个小时内该监控项目所采集到的数据的个数。所以不管一个监控项目的更新间隔是多少，它所对应的趋势数据在数据库中的记录都只有一条（每小时）。更新间隔越小，仅可能导致数据历史数据增大，而不会影响该监控项目在趋势表里的记录条数的。

历史数据还是趋势数据，都会周期性被Zabbix服务器端一种称之为“主妇（housekeeper）”进程进行清理，它会周期性的删除过期的历史数据和趋势数据。也正是因为这个进程的存在，才会使Zabbix系统数据的数据量不会一直的彭胀下去。如果对数据库进行分表需要关闭housekeeper进程。

注意

如果监控项目的“保留历史数据(天)”配置项被设置成0时，则数据库历史表中仅保留该监控项目所采集的最后一条数据，其它历史数据将数据将不会被会保留。而且，引用该监控项目的触发器也只能使用该项目所采集的最后数据。因此，此时如果在触发器里引用该项目时使用max、avg、min等函数据时将没有意义。

如果监控项目的“保留趋势数据(天)”配置项被设置成0时，则该项目在系统数据库的趋势表里将不保留任何数据。

history 相关表

(root@127.0.0.1)[zabbix]> show tables like 'history%'; 
+-----------------------------+
| Tables_in_zabbix (history%) |
+-----------------------------+
| history                     |
| history_log                 |
| history_str                 |
| history_text                |
| history_uint                |
+-----------------------------+
5 rows in set (0.001 sec)

history

存储信息类型为浮点数的监控项历史数据

history_log

存储信息类型为日志的监控项历史数据

history_str

存储信息类型为字符的监控项历史数据

history_text

存储信息类型为文本的监控项历史数据

history_uint

存储信息类型为数字(无正负)的监控项历史数据

history表结构

itemid: 监控项唯一标识id

clock: 时间戳整数部分

value：监控项的值

ns：纳秒数

查询 2021/04/02 00:00:00 -2021/04/03 00:00:00 itemid 29175 浮点数监控项历史数据

select itemid,from_unixtime(clock) as time,value from history where itemid=29175 and clock >= unix_timestamp('2021/04/07 00:00:00') and clock <= unix_timestamp('2021/04/08 00:00:00');

查询 2021/04/02 00:00:00 -2021/04/03 00:00:00 itemid 37957 文本监控项历史数据

select itemid,from_unixtime(clock) as time,value from history_text where itemid=37957 and clock >= unix_timestamp('2021/04/02 00:00:00') and clock <= unix_timestamp('2021/04/02 01:00:00') LIMIT 1;

查询 2021/04/02 00:00:00 -2021/04/03 00:00:00 itemid 36765 数字(无正负)监控项历史数据

select itemid,from_unixtime(clock) as time,value from history_uint where itemid=34444 and clock >= unix_timestamp('2021/04/08 09:00:00') and clock <= unix_timestamp('2021/04/08 13:00:00') LIMIT 10;

trends相关表

(root@127.0.0.1)[zabbix]> show tables like 'trend%'; 
+---------------------------+
| Tables_in_zabbix (trend%) |
+---------------------------+
| trends                    |
| trends_uint               |
+---------------------------+
2 rows in set (0.001 sec)

trends

存储信息类型为数字(无正负)的监控项趋势数据

trends_uint

存储信息类型为数字(无正负)的监控项趋势数据

trends 表结构

itemid: 监控项唯一标识id

clock: 时间戳整数部分

num：该小时内用了多少数据用于计算平均值、最小值、最大值

value_min：趋势时间内最小值(趋势数据时间间隔为一小时)

value_avg：趋势时间内平均值(趋势数据时间间隔为一小时)

value_max：趋势时间内最大值(趋势数据时间间隔为一小时)

查询 2021/04/02 00:00:00 -2021/04/03 00:00:00 itemid 29175 的趋势数据

select itemid,from_unixtime(clock) as time,value_max from trends where itemid=29175 and clock >= unix_timestamp('2021/04/02 00:00:00') and clock <= unix_timestamp('2021/04/03 00:00:00');

unix_timestamp()函数是将格式化时间转换为时间戳，计算机可读；

from_unixtime()函数是将时间戳转换为格式化时间，人类可读；

MySQL 数据库备份

备份整个数据库

more  bkzabbix.sh
#!/bin/bash
/usr/bin/mysqldump -uzabbix -pzabbix@zabbix zabbix | gzip > /mysql-backup/zabbix_$(date +%Y%m%d_%H%M%S).sql.gz

定期删除数据库的分别文件

more rmysql.sh
#!/bin/bash
find /mysql-backup -name zabbix"*.sql.gz" -type f -mtime +20 -exec rm -rf {} \; > /dev/null 2>&1

计划任务（每天08:30 执行bkzabbix.sh和rmysql.sh脚本）

30 11 * * *  /mysql-backup/bkzabbix.sh
30 11 * * *  /mysql-backup/rmysql.sh

过滤历史数据和趋势数据的大表备份数据库

/usr/bin/mysqldump -uroot -pzabbix@zabbix zabbix --ignore-table=zabbix.history --ignore-table=zabbix.history_str --ignore-table=zabbix.history_uint --ignore-table=zabbix.trends --ignore-table=zabbix.trends_uint | gzip > /mysql-backup/zabbix_$(date +%Y%m%d_%H%M%S).sql.gz

本文参与腾讯云自媒体分享计划，分享自微信公众号。

原始发表：2021-04-15，如有侵权请联系 cloudcommunity@tencent.com 删除

数据库