由于现网设备量比较大，根据业务类型又分了上百个左右的业务模块。而基于zabbix搭建的基础告警每天吐出的告警信息特别多。为了提高告警的准确性和及时率，同时也便于后期查询和报表统计。考虑将zabbix的部分进行下修改。

一、alerts表信息提取

alerts 中存放的是通过短信、邮件或其他媒介发出的告警数据 。比如要提取当天的所有磁盘相应的已发出的所有告警，可以通过如下sql 语句实现：

• select FROM_UNIXTIME(clock),sendto,`subject` from alerts
• where `subject` like '%磁盘%'
• and DATE_FORMAT(FROM_UNIXTIME(clock),'%Y-%m-%d') = DATE_FORMAT(NOW(),'%Y-%m-%d');
  
  

由于其中的clock时间使用的是unixtime，所以这里需要查询的时候通过转化，就成datetime时间 。查询结果如下：

不过通过alter表获取的数据有以下缺点：

1、不好明确区分告警问题是否已恢复（通过告警恢复正常触发器可以判读，但 alerts 数据量比较大时，就不好处理了）；

2、并未配置媒介告警的事件类无法在该表体现；

3、不便区分告警问题的级别；

二、event事件表关联

通过event表，关联基他几个表的数据，可以将查询的结果生成一个临时表 。通过查询该临时表，可以得到我们关心的几个数据：

• #创建临时表
• DROP TABLE IF EXISTS `tmp1`;
• create table tmp1 as
•      (SELECT
•             `hosts`.`host`,
•             `triggers`.triggerid,
•             `triggers`.description,
•             `triggers`.priority,
•             `events`.`value`,
•             FROM_UNIXTIME(`events`.clock) time
•     FROM
•             `hosts`,
•             `triggers`,
•             `events`,
•             `items`,
•             `functions`,
•             `groups`,
•             `hosts_groups`
•     WHERE
•             `hosts`.hostid = `hosts_groups`.hostid
•             AND `hosts_groups`.groupid = `groups`.groupid
•             AND `triggers`.triggerid = `events`.objectid
•             AND `hosts`.hostid = `items`.hostid
•             AND `items`.itemid = `functions`.itemid
•             AND `functions`.triggerid = `triggers`.triggerid);
• #查询磁盘告警，且未恢复的
• select * from tmp1 where value=1
• and  triggerid not in (select triggerid from tmp1 where value=0)
• and  description like '%磁盘%';
  
  

其中triggers.priority字段表示的是事件的告警级别（如普通、严重），events.value代表的是告警事件是否恢复（1代表存在问题，0代表正常），triggers.description 为告警描述信息 。

event事件关联临时表虽然解决了我们的需求，不过也存在一些小瑕疵：由于设备量比较多，仅仅几个月的基础事件就有几百万条，每次查询为了保证事件的完整性，都需要执行删除临时表，再重新将查询结果生成到临时表的方法相对较慢 ，而且当这个查询执行的时候会对mysql 造成一点性能伤害 －－－尽管可以在zabbix 的mysql 备库上执行 。

三、mysql 触发器

为了满足后期一些报表定制查询的需要，决定使用触发器，对event事件表发生insert操作时，自动将查行一个select关联查询，并将关联查询的结果

库名	表		字段
	表名	表含义	字段	字段名称	其他
report	newevent	故障表	id	序列号	主键
			host	网元名称	报警的IP
			devtype	网元类别	设备类别（服务器/交换机）
			triggerid	告警ID
			description	告警描述
			priority	告警级别	普通、严重
			value	告警状态	是否恢复（1代表问题，0代表正常）
			time	发生时间	datetime字段
			cause	故障定位	事件原因，
			sendstatus	发送状态	报警是否发送
	dict	字典表	triggerid	告警ID
			description	告警描述
			cause	故障定位

这里只使用newevent事件表，其中devtype、cause、sendstatus字段也暂时不用，后期需要分表设计时，可以考虑使用。如果启用dict表，还有一个可优化的项，因为description类型只有几千个，而newevent事件有几百万条，可以去掉description字段，将该字段放到dict表里，通过descid 字段去关联。

这里先以最简单的方式进行实现，先建库建表，如下：

• DROP DATABASE IF EXISTS `report`;
• CREATE DATABASE report character set utf8;
• USE report;
• DROP TABLE IF EXISTS `newevent`;
• CREATE TABLE `newevent` (
•   `id` int(11) NOT NULL AUTO_INCREMENT,
•   `host` varchar(128) CHARACTER SET utf8 NOT NULL DEFAULT '',
•   `triggerid` bigint(20) unsigned NOT NULL,
•   `description` varchar(255) CHARACTER SET utf8 NOT NULL DEFAULT '',
•   `priority` int(11) NOT NULL DEFAULT '0',
•   `value` int(11) NOT NULL DEFAULT '0',
•   `time` datetime DEFAULT NULL,
•    primary key (id)
• ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  
  

触发器创建如下：

• DELIMITER $$
• DROP TRIGGER IF EXISTS after_insert_on_event;
• CREATE TRIGGER after_insert_on_event
• AFTER INSERT ON zabbix.`events`
• FOR EACH ROW
• BEGIN
•     INSERT INTO report.newevent (
•         report.newevent.host,
•         report.newevent.triggerid,
•         report.newevent.description,
•         report.newevent.priority,
•         report.newevent.value,
•         report.newevent.time
•     )
•     SELECT
•         zabbix.`hosts`.`host`,
•         zabbix.`triggers`.triggerid,
•         zabbix.`triggers`.description,
•         zabbix.`triggers`.priority,
•         zabbix.`events`.`value`,
•         FROM_UNIXTIME(zabbix.`events`.clock)
•     FROM
•         zabbix.`hosts`,
•         zabbix.`triggers`,
•         zabbix.`events`,
•         zabbix.items,
•         zabbix.functions,
•         zabbix.groups,
•         zabbix.hosts_groups
•     WHERE
•         zabbix.`hosts`.hostid = zabbix.hosts_groups.hostid
•         AND zabbix.hosts_groups.groupid = zabbix.groups.groupid
•         AND zabbix.`triggers`.triggerid = zabbix.`events`.objectid
•         AND zabbix.`hosts`.hostid = zabbix.items.hostid
•         AND zabbix.items.itemid = zabbix.functions.itemid
•         AND zabbix.functions.triggerid = zabbix.`triggers`.triggerid
•         AND zabbix.`events`.eventid=new.eventid;
• END;
• $$
• DELIMITER ;
  
  

注，这个新的事件表同样也可以创建在zabbix库内，这里单独又建一个库的目的主要是为了后期定制开发及和其他平台对接的需要。后续只要event表中每新增一条数据，对应的数据就会在新的表中增加。

四、待解决问题及其他
1、备库触发器问题

最早的设计是将mysql 的这个触发器和新库建在备库上，不过发现在备库上创建完成后，newevent表中并没有数据，在网上也查到过主备库同步，备库触发器不生效的问题。网上给的解释是由于主备库之间的同步模式为mixed或row级时，就会出现备库上不捕获inster这种操作的情况。改成基于sql 语句同步的方式会解决，不过发现更改为基于sql 语句后，也不生效。

不重启数据库，通过修改变量修改sql 模式的语名如下：

• SET GLOBAL binlog_format = 'STATEMENT';
  
  

mysql同步复制的三种模式如下：

• -- 基于SQL语句的复制(statement-based replication, SBR)，
• -- 基于行的复制(row-based replication, RBR)，
• -- 混合模式复制(mixed-based replication, MBR)。
  
  

具体三者之间的优缺点比对可以参看csdn上的一篇博文 －－－ MYSQL复制的几种模式 。

还有提到和mysql 事务隔离级别相关的，关于事务隔离级别部分的知识可以参看这篇博文 －－－ MySQL数据库事务隔离级别(Transaction Isolation Level) 。

2、其他

如果想基于老的想要后期查询或改档用，并且同时又想保证查询的速度，可以对历史的newevent做一个归档，比如，select每三个月的数据将其保存另一个带日志的表中。再清空该表的数据，重新接受触发数据库写入。

原创：DevOps

由于现网设备量比较大，根据业务类型又分了上百个左右的业务模块。而基于zabbix搭建的基础告警每天吐出的告警信息特别多。为了提高告警的准确性和及时率，同时也便于后期查询和报表统计。考虑将zabbix的部分进行下修改。

一、alerts表信息提取

alerts 中存放的是通过短信、邮件或其他媒介发出的告警数据 。比如要提取当天的所有磁盘相应的已发出的所有告警，可以通过如下sql 语句实现：

• select FROM_UNIXTIME(clock),sendto,`subject` from alerts
• where `subject` like '%磁盘%'
• and DATE_FORMAT(FROM_UNIXTIME(clock),'%Y-%m-%d') = DATE_FORMAT(NOW(),'%Y-%m-%d');
  
  

由于其中的clock时间使用的是unixtime，所以这里需要查询的时候通过转化，就成datetime时间 。查询结果如下：

不过通过alter表获取的数据有以下缺点：

1、不好明确区分告警问题是否已恢复（通过告警恢复正常触发器可以判读，但 alerts 数据量比较大时，就不好处理了）；

2、并未配置媒介告警的事件类无法在该表体现；

3、不便区分告警问题的级别；

二、event事件表关联

通过event表，关联基他几个表的数据，可以将查询的结果生成一个临时表 。通过查询该临时表，可以得到我们关心的几个数据：

• #创建临时表
• DROP TABLE IF EXISTS `tmp1`;
• create table tmp1 as
•      (SELECT
•             `hosts`.`host`,
•             `triggers`.triggerid,
•             `triggers`.description,
•             `triggers`.priority,
•             `events`.`value`,
•             FROM_UNIXTIME(`events`.clock) time
•     FROM
•             `hosts`,
•             `triggers`,
•             `events`,
•             `items`,
•             `functions`,
•             `groups`,
•             `hosts_groups`
•     WHERE
•             `hosts`.hostid = `hosts_groups`.hostid
•             AND `hosts_groups`.groupid = `groups`.groupid
•             AND `triggers`.triggerid = `events`.objectid
•             AND `hosts`.hostid = `items`.hostid
•             AND `items`.itemid = `functions`.itemid
•             AND `functions`.triggerid = `triggers`.triggerid);
• #查询磁盘告警，且未恢复的
• select * from tmp1 where value=1
• and  triggerid not in (select triggerid from tmp1 where value=0)
• and  description like '%磁盘%';
  
  

其中triggers.priority字段表示的是事件的告警级别（如普通、严重），events.value代表的是告警事件是否恢复（1代表存在问题，0代表正常），triggers.description 为告警描述信息 。

event事件关联临时表虽然解决了我们的需求，不过也存在一些小瑕疵：由于设备量比较多，仅仅几个月的基础事件就有几百万条，每次查询为了保证事件的完整性，都需要执行删除临时表，再重新将查询结果生成到临时表的方法相对较慢 ，而且当这个查询执行的时候会对mysql 造成一点性能伤害 －－－尽管可以在zabbix 的mysql 备库上执行 。

三、mysql 触发器

为了满足后期一些报表定制查询的需要，决定使用触发器，对event事件表发生insert操作时，自动将查行一个select关联查询，并将关联查询的结果

库名	表		字段
	表名	表含义	字段	字段名称	其他
report	newevent	故障表	id	序列号	主键
			host	网元名称	报警的IP
			devtype	网元类别	设备类别（服务器/交换机）
			triggerid	告警ID
			description	告警描述
			priority	告警级别	普通、严重
			value	告警状态	是否恢复（1代表问题，0代表正常）
			time	发生时间	datetime字段
			cause	故障定位	事件原因，
			sendstatus	发送状态	报警是否发送
	dict	字典表	triggerid	告警ID
			description	告警描述
			cause	故障定位

这里只使用newevent事件表，其中devtype、cause、sendstatus字段也暂时不用，后期需要分表设计时，可以考虑使用。如果启用dict表，还有一个可优化的项，因为description类型只有几千个，而newevent事件有几百万条，可以去掉description字段，将该字段放到dict表里，通过descid 字段去关联。

这里先以最简单的方式进行实现，先建库建表，如下：

• DROP DATABASE IF EXISTS `report`;
• CREATE DATABASE report character set utf8;
• USE report;
• DROP TABLE IF EXISTS `newevent`;
• CREATE TABLE `newevent` (
•   `id` int(11) NOT NULL AUTO_INCREMENT,
•   `host` varchar(128) CHARACTER SET utf8 NOT NULL DEFAULT '',
•   `triggerid` bigint(20) unsigned NOT NULL,
•   `description` varchar(255) CHARACTER SET utf8 NOT NULL DEFAULT '',
•   `priority` int(11) NOT NULL DEFAULT '0',
•   `value` int(11) NOT NULL DEFAULT '0',
•   `time` datetime DEFAULT NULL,
•    primary key (id)
• ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  
  

触发器创建如下：

• DELIMITER $$
• DROP TRIGGER IF EXISTS after_insert_on_event;
• CREATE TRIGGER after_insert_on_event
• AFTER INSERT ON zabbix.`events`
• FOR EACH ROW
• BEGIN
•     INSERT INTO report.newevent (
•         report.newevent.host,
•         report.newevent.triggerid,
•         report.newevent.description,
•         report.newevent.priority,
•         report.newevent.value,
•         report.newevent.time
•     )
•     SELECT
•         zabbix.`hosts`.`host`,
•         zabbix.`triggers`.triggerid,
•         zabbix.`triggers`.description,
•         zabbix.`triggers`.priority,
•         zabbix.`events`.`value`,
•         FROM_UNIXTIME(zabbix.`events`.clock)
•     FROM
•         zabbix.`hosts`,
•         zabbix.`triggers`,
•         zabbix.`events`,
•         zabbix.items,
•         zabbix.functions,
•         zabbix.groups,
•         zabbix.hosts_groups
•     WHERE
•         zabbix.`hosts`.hostid = zabbix.hosts_groups.hostid
•         AND zabbix.hosts_groups.groupid = zabbix.groups.groupid
•         AND zabbix.`triggers`.triggerid = zabbix.`events`.objectid
•         AND zabbix.`hosts`.hostid = zabbix.items.hostid
•         AND zabbix.items.itemid = zabbix.functions.itemid
•         AND zabbix.functions.triggerid = zabbix.`triggers`.triggerid
•         AND zabbix.`events`.eventid=new.eventid;
• END;
• $$
• DELIMITER ;
  
  

注，这个新的事件表同样也可以创建在zabbix库内，这里单独又建一个库的目的主要是为了后期定制开发及和其他平台对接的需要。后续只要event表中每新增一条数据，对应的数据就会在新的表中增加。

四、待解决问题及其他
1、备库触发器问题

最早的设计是将mysql 的这个触发器和新库建在备库上，不过发现在备库上创建完成后，newevent表中并没有数据，在网上也查到过主备库同步，备库触发器不生效的问题。网上给的解释是由于主备库之间的同步模式为mixed或row级时，就会出现备库上不捕获inster这种操作的情况。改成基于sql 语句同步的方式会解决，不过发现更改为基于sql 语句后，也不生效。

不重启数据库，通过修改变量修改sql 模式的语名如下：

• SET GLOBAL binlog_format = 'STATEMENT';
  
  

mysql同步复制的三种模式如下：

• -- 基于SQL语句的复制(statement-based replication, SBR)，
• -- 基于行的复制(row-based replication, RBR)，
• -- 混合模式复制(mixed-based replication, MBR)。
  
  

具体三者之间的优缺点比对可以参看csdn上的一篇博文 －－－ MYSQL复制的几种模式 。

还有提到和mysql 事务隔离级别相关的，关于事务隔离级别部分的知识可以参看这篇博文 －－－ MySQL数据库事务隔离级别(Transaction Isolation Level) 。

2、其他

如果想基于老的想要后期查询或改档用，并且同时又想保证查询的速度，可以对历史的newevent做一个归档，比如，select每三个月的数据将其保存另一个带日志的表中。再清空该表的数据，重新接受触发数据库写入。

原创：DevOps