[zabbix]zabbix分区表操作步骤
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#