设为首页 收藏本站
查看: 1045|回复: 0

[经验分享] [zabbix]zabbix分区表操作步骤

[复制链接]

尚未签到

发表于 2015-9-9 08:39:10 | 显示全部楼层 |阅读模式
  Q&A:
  1.mul key:



1. 如果Key是空的, 那么该列值的可以重复, 表示该列没有索引, 或者是一个非唯一的复合索引的非前导列
2. 如果Key是PRI,  那么该列是主键的组成部分
3. 如果Key是UNI,  那么该列是一个唯一值索引的第一列(前导列),并别不能含有空值(NULL)
4. 如果Key是MUL,  那么该列的值可以重复, 该列是一个非唯一索引的前导列(第一列)或者是一个唯一性索引的组成部分但是可以含有空值NULL
  2.mysql分区表概述:



参考:
https://dev.mysql.com/doc/refman/5.1/zh/partitioning.html

分区表分为四种:
range分区:基于属于一个给定连续区间的列值,把多行分配给分区
list分区:类似于按RANGE分区,区别在于LIST分区是基于列值匹配一个离散值集合中的某个值来进行选择
hash分区:基于用户定义的表达式的返回值来进行选择的分区,该表达式使用将要插入到表中的这些行的列值进行计算
key分区:类似于按HASH分区,区别在于KEY分区只支持计算一列或多列
常用的是range和list分区
无论使用何种类型的分区,分区总是在创建时就自动的顺序编号,且从0开始记录,记住这一点非常重要。当有一新行插入到一个分区表中时,就是使用这些分区编号来识别正确的分区。
注1:
如果想在已经建好的表上进行分区,如果使用alter添加分区的话,mysql会提示错误:
ERROR 1505 <HY000> Partition management on a not partitioned table is not possible
正确的方法是新建一个具有分区的表,结构一致,然后用insert into 分区表 select * from 原始表;
http://blog.sina.com.cn/s/blog_5f54f0be01015tag.html

  
  
  预计过程:
  1.停止mysql,/etc/init.d/mysql stop
  2.记录下要修改的表的表结构,
  3.删除表,
  4.创建和之前表结构一致但带有分区表的数据表
  5.创建procedure,修改crontab
  
  问题,修改之后,一段时间之前的数据会被删除,但这些数据会不会被别的表引用;
  
  测试过程:
  http://zabbixzone.com/zabbix/partitioning-tables/



1.如果只做历史表的每日分割,删除如下表
history
history_uint
history_log
history_str
history_text
他们的创建语法是:
root@InnerServer1:/home/silence/zabbix-2.2.2/database/mysql# mysql -uroot -pfengmao -e 'show create table zabbix.history\G show create table zabbix.history_uint\G show create table zabbix.history_log\G show create table zabbix.history_str\G show create table zabbix.history_text\G '
*************************** 1. row ***************************
Table: history
Create Table: CREATE TABLE `history` (
`itemid` bigint(20) unsigned NOT NULL,
`clock` int(11) NOT NULL DEFAULT '0',
`value` double(16,4) NOT NULL DEFAULT '0.0000',
`ns` int(11) NOT NULL DEFAULT '0',
KEY `history_1` (`itemid`,`clock`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
*************************** 1. row ***************************
Table: history_uint
Create Table: CREATE TABLE `history_uint` (
`itemid` bigint(20) unsigned NOT NULL,
`clock` int(11) NOT NULL DEFAULT '0',
`value` bigint(20) unsigned NOT NULL DEFAULT '0',
`ns` int(11) NOT NULL DEFAULT '0',
KEY `history_uint_1` (`itemid`,`clock`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
*************************** 1. row ***************************
Table: history_log
Create Table: CREATE TABLE `history_log` (
`id` bigint(20) unsigned NOT NULL,
`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` text COLLATE utf8_bin NOT NULL,
`logeventid` int(11) NOT NULL DEFAULT '0',
`ns` int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`),
UNIQUE KEY `history_log_2` (`itemid`,`id`),
KEY `history_log_1` (`itemid`,`clock`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
*************************** 1. row ***************************
Table: history_str
Create Table: CREATE TABLE `history_str` (
`itemid` bigint(20) unsigned NOT NULL,
`clock` int(11) NOT NULL DEFAULT '0',
`value` varchar(255) COLLATE utf8_bin NOT NULL DEFAULT '',
`ns` int(11) NOT NULL DEFAULT '0',
KEY `history_str_1` (`itemid`,`clock`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
*************************** 1. row ***************************
Table: history_text
Create Table: CREATE TABLE `history_text` (
`id` bigint(20) unsigned NOT NULL,
`itemid` bigint(20) unsigned NOT NULL,
`clock` int(11) NOT NULL DEFAULT '0',
`value` text COLLATE utf8_bin NOT NULL,
`ns` int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`),
UNIQUE KEY `history_text_2` (`itemid`,`id`),
KEY `history_text_1` (`itemid`,`clock`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
  因为分区表使用的列必须为primary key,或者表没有主键,所以需要修改如下表:



ALTER TABLE `history_log` DROP PRIMARY KEY, ADD PRIMARY KEY (`itemid`,`id`,`clock`);
ALTER TABLE `history_log` DROP KEY `history_log_2`;
ALTER TABLE `history_text` DROP PRIMARY KEY, ADD PRIMARY KEY (`itemid`,`id`,`clock`);
ALTER TABLE `history_text` DROP KEY `history_text_2`;
  创建之前删除的相应的表,并给他们加上分区,类似如下形式:



CREATE TABLE `history` (
`itemid` bigint(20) unsigned NOT NULL,
`clock` int(11) NOT NULL DEFAULT '0',
`value` double(16,4) NOT NULL DEFAULT '0.0000',
`ns` int(11) NOT NULL DEFAULT '0',
KEY `history_1` (`itemid`,`clock`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin
partition by range(clock) (
PARTITION p20140310 VALUES LESS THAN (UNIX_TIMESTAMP("2014-03-10 00:00:00"))
);
  
  粘贴每日分区函数:



DELIMITER //
DROP PROCEDURE IF EXISTS `zabbix`.`create_zabbix_partitions` //
CREATE PROCEDURE `zabbix`.`create_zabbix_partitions` ()
BEGIN
CALL zabbix.create_next_partitions("zabbix","history");
CALL zabbix.create_next_partitions("zabbix","history_log");
CALL zabbix.create_next_partitions("zabbix","history_str");
CALL zabbix.create_next_partitions("zabbix","history_text");
CALL zabbix.create_next_partitions("zabbix","history_uint");
CALL zabbix.drop_old_partitions("zabbix","history");
CALL zabbix.drop_old_partitions("zabbix","history_log");
CALL zabbix.drop_old_partitions("zabbix","history_str");
CALL zabbix.drop_old_partitions("zabbix","history_text");
CALL zabbix.drop_old_partitions("zabbix","history_uint");
END //
DROP PROCEDURE IF EXISTS `zabbix`.`create_next_partitions` //
CREATE PROCEDURE `zabbix`.`create_next_partitions` (SCHEMANAME varchar(64), TABLENAME varchar(64))
BEGIN
DECLARE NEXTCLOCK timestamp;
DECLARE PARTITIONNAME varchar(16);
DECLARE CLOCK int;
SET @totaldays = 7;
SET @i = 1;
createloop: LOOP
SET NEXTCLOCK = DATE_ADD(NOW(),INTERVAL @i DAY);
SET PARTITIONNAME = DATE_FORMAT( NEXTCLOCK, 'p%Y%m%d' );
SET CLOCK = UNIX_TIMESTAMP(DATE_FORMAT(DATE_ADD( NEXTCLOCK ,INTERVAL 1 DAY),'%Y-%m-%d 00:00:00'));
CALL zabbix.create_partition( SCHEMANAME, TABLENAME, PARTITIONNAME, CLOCK );
SET @i=@i+1;
IF @i > @totaldays THEN
LEAVE createloop;
END IF;
END LOOP;
END //
DROP PROCEDURE IF EXISTS `zabbix`.`drop_old_partitions` //
CREATE PROCEDURE `zabbix`.`drop_old_partitions` (SCHEMANAME varchar(64), TABLENAME varchar(64))
BEGIN
DECLARE OLDCLOCK timestamp;
DECLARE PARTITIONNAME varchar(16);
DECLARE CLOCK int;
SET @mindays = 3;
SET @maxdays = @mindays+4;
SET @i = @maxdays;
droploop: LOOP
SET OLDCLOCK = DATE_SUB(NOW(),INTERVAL @i DAY);
SET PARTITIONNAME = DATE_FORMAT( OLDCLOCK, 'p%Y%m%d' );
CALL zabbix.drop_partition( SCHEMANAME, TABLENAME, PARTITIONNAME );
SET @i=@i-1;
IF @i <= @mindays THEN
LEAVE droploop;
END IF;
END LOOP;
END //
DROP PROCEDURE IF EXISTS `zabbix`.`create_partition` //
CREATE PROCEDURE `zabbix`.`create_partition` (SCHEMANAME varchar(64), TABLENAME varchar(64), PARTITIONNAME varchar(64), CLOCK int)
BEGIN
DECLARE RETROWS int;
SELECT COUNT(1) INTO RETROWS
FROM `information_schema`.`partitions`
WHERE `table_schema` = SCHEMANAME AND `table_name` = TABLENAME AND `partition_name` = PARTITIONNAME;
IF RETROWS = 0 THEN
SELECT CONCAT( "create_partition(", SCHEMANAME, ",", TABLENAME, ",", PARTITIONNAME, ",", CLOCK, ")" ) AS msg;
SET @sql = CONCAT( 'ALTER TABLE `', SCHEMANAME, '`.`', TABLENAME, '`',
' ADD PARTITION (PARTITION ', PARTITIONNAME, ' VALUES LESS THAN (', CLOCK, '));' );
PREPARE STMT FROM @sql;
EXECUTE STMT;
DEALLOCATE PREPARE STMT;
END IF;
END //
DROP PROCEDURE IF EXISTS `zabbix`.`drop_partition` //
CREATE PROCEDURE `zabbix`.`drop_partition` (SCHEMANAME varchar(64), TABLENAME varchar(64), PARTITIONNAME varchar(64))
BEGIN
DECLARE RETROWS int;
SELECT COUNT(1) INTO RETROWS
FROM `information_schema`.`partitions`
WHERE `table_schema` = SCHEMANAME AND `table_name` = TABLENAME AND `partition_name` = PARTITIONNAME;
IF RETROWS = 1 THEN
SELECT CONCAT( "drop_partition(", SCHEMANAME, ",", TABLENAME, ",", PARTITIONNAME, ")" ) AS msg;
SET @sql = CONCAT( 'ALTER TABLE `', SCHEMANAME, '`.`', TABLENAME, '`',
' DROP PARTITION ', PARTITIONNAME, ';' );
PREPARE STMT FROM @sql;
EXECUTE STMT;
DEALLOCATE PREPARE STMT;
END IF;
END //
DELIMITER ;
  
  最终的形式:



root@InnerServer1:/app/zabbix/sbin# mysql -uroot -pfengmao -e 'show create table zabbix.history\G show create table zabbix.history_uint\G show create table zabbix.history_log\G show create table zabbix.history_str\G show create table zabbix.history_text\G '
*************************** 1. row ***************************
Table: history
Create Table: CREATE TABLE `history` (
`itemid` bigint(20) unsigned NOT NULL,
`clock` int(11) NOT NULL DEFAULT '0',
`value` double(16,4) NOT NULL DEFAULT '0.0000',
`ns` int(11) NOT NULL DEFAULT '0',
KEY `history_1` (`itemid`,`clock`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin
/*!50100 PARTITION BY RANGE (clock)
(PARTITION p20140310 VALUES LESS THAN (1394380800) ENGINE = InnoDB,
PARTITION p20140312 VALUES LESS THAN (1394640000) ENGINE = InnoDB,
PARTITION p20140313 VALUES LESS THAN (1394726400) ENGINE = InnoDB,
PARTITION p20140314 VALUES LESS THAN (1394812800) ENGINE = InnoDB,
PARTITION p20140315 VALUES LESS THAN (1394899200) ENGINE = InnoDB,
PARTITION p20140316 VALUES LESS THAN (1394985600) ENGINE = InnoDB,
PARTITION p20140317 VALUES LESS THAN (1395072000) ENGINE = InnoDB,
PARTITION p20140318 VALUES LESS THAN (1395158400) ENGINE = InnoDB) */
*************************** 1. row ***************************
Table: history_uint
Create Table: CREATE TABLE `history_uint` (
`itemid` bigint(20) unsigned NOT NULL,
`clock` int(11) NOT NULL DEFAULT '0',
`value` bigint(20) unsigned NOT NULL DEFAULT '0',
`ns` int(11) NOT NULL DEFAULT '0',
KEY `history_uint_1` (`itemid`,`clock`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin
/*!50100 PARTITION BY RANGE (clock)
(PARTITION p20140310 VALUES LESS THAN (1394380800) ENGINE = InnoDB,
PARTITION p20140312 VALUES LESS THAN (1394640000) ENGINE = InnoDB,
PARTITION p20140313 VALUES LESS THAN (1394726400) ENGINE = InnoDB,
PARTITION p20140314 VALUES LESS THAN (1394812800) ENGINE = InnoDB,
PARTITION p20140315 VALUES LESS THAN (1394899200) ENGINE = InnoDB,
PARTITION p20140316 VALUES LESS THAN (1394985600) ENGINE = InnoDB,
PARTITION p20140317 VALUES LESS THAN (1395072000) ENGINE = InnoDB,
PARTITION p20140318 VALUES LESS THAN (1395158400) ENGINE = InnoDB) */
*************************** 1. row ***************************
Table: history_log
Create Table: CREATE TABLE `history_log` (
`id` bigint(20) unsigned NOT NULL,
`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` text COLLATE utf8_bin NOT NULL,
`logeventid` int(11) NOT NULL DEFAULT '0',
`ns` int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`itemid`,`id`,`clock`),
KEY `history_log_1` (`itemid`,`clock`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin
/*!50100 PARTITION BY RANGE (clock)
(PARTITION p20140310 VALUES LESS THAN (1394380800) ENGINE = InnoDB,
PARTITION p20140312 VALUES LESS THAN (1394640000) ENGINE = InnoDB,
PARTITION p20140313 VALUES LESS THAN (1394726400) ENGINE = InnoDB,
PARTITION p20140314 VALUES LESS THAN (1394812800) ENGINE = InnoDB,
PARTITION p20140315 VALUES LESS THAN (1394899200) ENGINE = InnoDB,
PARTITION p20140316 VALUES LESS THAN (1394985600) ENGINE = InnoDB,
PARTITION p20140317 VALUES LESS THAN (1395072000) ENGINE = InnoDB,
PARTITION p20140318 VALUES LESS THAN (1395158400) ENGINE = InnoDB) */
*************************** 1. row ***************************
Table: history_str
Create Table: CREATE TABLE `history_str` (
`itemid` bigint(20) unsigned NOT NULL,
`clock` int(11) NOT NULL DEFAULT '0',
`value` varchar(255) COLLATE utf8_bin NOT NULL DEFAULT '',
`ns` int(11) NOT NULL DEFAULT '0',
KEY `history_str_1` (`itemid`,`clock`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin
/*!50100 PARTITION BY RANGE (clock)
(PARTITION p20140310 VALUES LESS THAN (1394380800) ENGINE = InnoDB,
PARTITION p20140312 VALUES LESS THAN (1394640000) ENGINE = InnoDB,
PARTITION p20140313 VALUES LESS THAN (1394726400) ENGINE = InnoDB,
PARTITION p20140314 VALUES LESS THAN (1394812800) ENGINE = InnoDB,
PARTITION p20140315 VALUES LESS THAN (1394899200) ENGINE = InnoDB,
PARTITION p20140316 VALUES LESS THAN (1394985600) ENGINE = InnoDB,
PARTITION p20140317 VALUES LESS THAN (1395072000) ENGINE = InnoDB,
PARTITION p20140318 VALUES LESS THAN (1395158400) ENGINE = InnoDB) */
*************************** 1. row ***************************
Table: history_text
Create Table: CREATE TABLE `history_text` (
`id` bigint(20) unsigned NOT NULL,
`itemid` bigint(20) unsigned NOT NULL,
`clock` int(11) NOT NULL DEFAULT '0',
`value` text COLLATE utf8_bin NOT NULL,
`ns` int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`itemid`,`id`,`clock`),
KEY `history_text_1` (`itemid`,`clock`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin
/*!50100 PARTITION BY RANGE (clock)
(PARTITION p20140310 VALUES LESS THAN (1394380800) ENGINE = InnoDB,
PARTITION p20140312 VALUES LESS THAN (1394640000) ENGINE = InnoDB,
PARTITION p20140313 VALUES LESS THAN (1394726400) ENGINE = InnoDB,
PARTITION p20140314 VALUES LESS THAN (1394812800) ENGINE = InnoDB,
PARTITION p20140315 VALUES LESS THAN (1394899200) ENGINE = InnoDB,
PARTITION p20140316 VALUES LESS THAN (1394985600) ENGINE = InnoDB,
PARTITION p20140317 VALUES LESS THAN (1395072000) ENGINE = InnoDB,
PARTITION p20140318 VALUES LESS THAN (1395158400) ENGINE = InnoDB) */
root@InnerServer1:/app/zabbix/sbin#
  
  
  

运维网声明 1、欢迎大家加入本站运维交流群:群②:261659950 群⑤:202807635 群⑦870801961 群⑧679858003
2、本站所有主题由该帖子作者发表,该帖子作者与运维网享有帖子相关版权
3、所有作品的著作权均归原作者享有,请您和我们一样尊重他人的著作权等合法权益。如果您对作品感到满意,请购买正版
4、禁止制作、复制、发布和传播具有反动、淫秽、色情、暴力、凶杀等内容的信息,一经发现立即删除。若您因此触犯法律,一切后果自负,我们对此不承担任何责任
5、所有资源均系网友上传或者通过网络收集,我们仅提供一个展示、介绍、观摩学习的平台,我们不对其内容的准确性、可靠性、正当性、安全性、合法性等负责,亦不承担任何法律责任
6、所有作品仅供您个人学习、研究或欣赏,不得用于商业或者其他用途,否则,一切后果均由您自己承担,我们对此不承担任何法律责任
7、如涉及侵犯版权等问题,请您及时通知我们,我们将立即采取措施予以解决
8、联系人Email:admin@iyunv.com 网址:www.yunweiku.com

所有资源均系网友上传或者通过网络收集,我们仅提供一个展示、介绍、观摩学习的平台,我们不对其承担任何法律责任,如涉及侵犯版权等问题,请您及时通知我们,我们将立即处理,联系人Email:kefu@iyunv.com,QQ:1061981298 本贴地址:https://www.yunweiku.com/thread-111213-1-1.html 上篇帖子: zabbix proxy配置 下篇帖子: Zabbix监控Linux磁盘I/O
您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

扫码加入运维网微信交流群X

扫码加入运维网微信交流群

扫描二维码加入运维网微信交流群,最新一手资源尽在官方微信交流群!快快加入我们吧...

扫描微信二维码查看详情

客服E-mail:kefu@iyunv.com 客服QQ:1061981298


QQ群⑦:运维网交流群⑦ QQ群⑧:运维网交流群⑧ k8s群:运维网kubernetes交流群


提醒:禁止发布任何违反国家法律、法规的言论与图片等内容;本站内容均来自个人观点与网络等信息,非本站认同之观点.


本站大部分资源是网友从网上搜集分享而来,其版权均归原作者及其网站所有,我们尊重他人的合法权益,如有内容侵犯您的合法权益,请及时与我们联系进行核实删除!



合作伙伴: 青云cloud

快速回复 返回顶部 返回列表