zabbix server进行表分区的话,zabbix的内部管家会失效,这个时候,如果有proxy的话,也要进行表分区,proxy表分区比较简单,也不用每天更换分区。
步骤如下,以zabbix3.4为例
1、彻底删除zabbix_proxy库
2、重新创建库表并设置权限
mysql -uroot -p
> use mysql;
> create database zabbix_proxy character set utf8 collate utf8_bin;
> grant all privileges on zabbix_proxy.* to zabbix@localhost identified by '1qaz@WSX';
> flush privileges;
3、导入库表
cd /usr/share/doc/zabbix-proxy-mysql-3.4.15
zcat schema.sql.gz | mysql -uroot -p zabbix_proxy
4、删除要做分区的表
drop table proxy_history
5、设置表分区,以15天分一次表为例
CREATE TABLE `proxy_history` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`itemid` bigint(20) unsigned NOT NULL,
`clock` int(11) NOT NULL DEFAULT '0',
`timestamp` int(11) NOT NULL DEFAULT '0',
`source` varchar(64) COLLATE utf8_bin NOT NULL DEFAULT '',
`severity` int(11) NOT NULL DEFAULT '0',
`value` longtext COLLATE utf8_bin NOT NULL,
`logeventid` int(11) NOT NULL DEFAULT '0',
`ns` int(11) NOT NULL DEFAULT '0',
`state` int(11) NOT NULL DEFAULT '0',
`lastlogsize` bigint(20) unsigned NOT NULL DEFAULT '0',
`mtime` int(11) NOT NULL DEFAULT '0',
`flags` int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`,clock)
) ENGINE=InnoDB AUTO_INCREMENT=242428784 DEFAULT CHARSET=utf8 COLLATE=utf8_bin
PARTITION BY RANGE ( clock)
(PARTITION p2022_01_09 VALUES LESS THAN (UNIX_TIMESTAMP("2022-01-09 00:00:00")) ENGINE = InnoDB,
PARTITION p2022_01_24 VALUES LESS THAN (UNIX_TIMESTAMP("2022-01-24 00:00:00")) ENGINE = InnoDB,
PARTITION p2022_02_09 VALUES LESS THAN (UNIX_TIMESTAMP("2022-02-09 00:00:00")) ENGINE = InnoDB,
PARTITION p2022_02_24 VALUES LESS THAN (UNIX_TIMESTAMP("2022-02-24 00:00:00")) ENGINE = InnoDB,
PARTITION p2022_03_09 VALUES LESS THAN (UNIX_TIMESTAMP("2022-03-09 00:00:00")) ENGINE = InnoDB,
PARTITION p2022_03_24 VALUES LESS THAN (UNIX_TIMESTAMP("2022-03-24 00:00:00")) ENGINE = InnoDB,
PARTITION p2022_04_09 VALUES LESS THAN (UNIX_TIMESTAMP("2022-04-09 00:00:00")) ENGINE = InnoDB,
PARTITION p2022_04_24 VALUES LESS THAN (UNIX_TIMESTAMP("2022-04-24 00:00:00")) ENGINE = InnoDB,
PARTITION p2022_05_09 VALUES LESS THAN (UNIX_TIMESTAMP("2022-05-09 00:00:00")) ENGINE = InnoDB,
PARTITION p2022_05_24 VALUES LESS THAN (UNIX_TIMESTAMP("2022-05-24 00:00:00")) ENGINE = InnoDB,
PARTITION p2022_06_09 VALUES LESS THAN (UNIX_TIMESTAMP("2022-06-09 00:00:00")) ENGINE = InnoDB,
PARTITION p2022_06_24 VALUES LESS THAN (UNIX_TIMESTAMP("2022-06-24 00:00:00")) ENGINE = InnoDB,
PARTITION p2022_07_09 VALUES LESS THAN (UNIX_TIMESTAMP("2022-07-09 00:00:00")) ENGINE = InnoDB,
PARTITION p2022_07_24 VALUES LESS THAN (UNIX_TIMESTAMP("2022-07-24 00:00:00")) ENGINE = InnoDB,
PARTITION p2022_08_09 VALUES LESS THAN (UNIX_TIMESTAMP("2022-08-09 00:00:00")) ENGINE = InnoDB,
PARTITION p2022_08_24 VALUES LESS THAN (UNIX_TIMESTAMP("2022-08-24 00:00:00")) ENGINE = InnoDB,
PARTITION p2022_09_09 VALUES LESS THAN (UNIX_TIMESTAMP("2022-09-09 00:00:00")) ENGINE = InnoDB,
PARTITION p2022_09_24 VALUES LESS THAN (UNIX_TIMESTAMP("2022-09-24 00:00:00")) ENGINE = InnoDB,
PARTITION p2022_10_09 VALUES LESS THAN (UNIX_TIMESTAMP("2022-10-09 00:00:00")) ENGINE = InnoDB,
PARTITION p2022_10_24 VALUES LESS THAN (UNIX_TIMESTAMP("2022-10-24 00:00:00")) ENGINE = InnoDB,
PARTITION p2022_11_09 VALUES LESS THAN (UNIX_TIMESTAMP("2022-11-09 00:00:00")) ENGINE = InnoDB,
PARTITION p2022_11_24 VALUES LESS THAN (UNIX_TIMESTAMP("2022-11-24 00:00:00")) ENGINE = InnoDB,
PARTITION p2022_12_09 VALUES LESS THAN (UNIX_TIMESTAMP("2022-12-09 00:00:00")) ENGINE = InnoDB,
PARTITION p2022_12_24 VALUES LESS THAN (UNIX_TIMESTAMP("2022-12-24 00:00:00")) ENGINE = InnoDB)