实施过程
http://lihuipeng.blog.51cto.com/3064864/1561221
逻辑
一天创建一个分区，分区比目前时间少7天的删除掉，等于说保留一个礼拜的数据
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
|
zabbix 分区
zabbix_db_name：库名
table_name：表名
days_to_keep_data：保存多少天的数据
hourly_interval：每隔多久生成一个分区
num_future_intervals_to_create：本次一共生成多少个分区
这个例子就是history表最多保存
7
天的数据，每隔
24
小时生成一个分区，这次一共生成
7
个分区
history
history_1 itemid, clock Normal
0
A
0
15085
0
0
Alter table history_text drop primary key, add index (
id
), drop index history_text_2, add index history_text_2 (itemid,
id
)
Alter table history_log drop primary key, add index (
id
), drop index history_log_2, add index history_log_2 (itemid,
id
);
ERROR
1503
(HY000) at line
1
: A PRIMARY KEY must include
all
columns
in
the table's partitioning function
CALL partition_maintenance(SCHEMA_NAME,
'history'
,
7
,
24
,
7
);
CALL partition_maintenance(SCHEMA_NAME,
'history_log'
,
30
,
24
,
7
);
CALL partition_maintenance(SCHEMA_NAME,
'history_str'
,
30
,
24
,
7
);
CALL partition_maintenance(SCHEMA_NAME,
'history_text'
,
30
,
24
,
7
);
CALL partition_maintenance(SCHEMA_NAME,
'history_uint'
,
15
,
24
,
7
);
CALL partition_maintenance(SCHEMA_NAME,
'trends'
,
60
,
24
,
7
);
CALL partition_maintenance(SCHEMA_NAME,
'trends_uint'
,
60
,
24
,
7
);
DELIMITER $$
CREATE PROCEDURE `partition_maintenance_all`(SCHEMA_NAME VARCHAR(
32
))
BEGIN
CALL partition_maintenance(SCHEMA_NAME,
'history'
,
28
,
24
,
14
);
CALL partition_maintenance(SCHEMA_NAME,
'history_log'
,
28
,
24
,
14
);
CALL partition_maintenance(SCHEMA_NAME,
'history_str'
,
28
,
24
,
14
);
CALL partition_maintenance(SCHEMA_NAME,
'history_text'
,
28
,
24
,
14
);
CALL partition_maintenance(SCHEMA_NAME,
'history_uint'
,
28
,
24
,
14
);
CALL partition_maintenance(SCHEMA_NAME,
'trends'
,
730
,
24
,
14
);
CALL partition_maintenance(SCHEMA_NAME,
'trends_uint'
,
730
,
24
,
14
);
END$$
DELIMITER ;
|
partition_create
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
|
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(
"partition_create("
, 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
|
partition_drop
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
|
BEGIN
DECLARE done
INT
DEFAULT FALSE;
DECLARE drop_part_name VARCHAR(
16
);
DECLARE myCursor CURSOR FOR
SELECT partition_name
FROM information_schema.partitions
WHERE table_schema
=
SCHEMANAME AND TABLE_NAME
=
TABLENAME AND CAST(SUBSTRING(partition_name FROM
2
) AS UNSIGNED) < DELETE_BELOW_PARTITION_DATE;
DECLARE CONTINUE HANDLER FOR NOT FOUND
SET
done
=
TRUE;
SET
@alter_header
=
CONCAT(
"ALTER TABLE "
, SCHEMANAME,
"."
, TABLENAME,
" DROP PARTITION "
);
SET
@drop_partitions
=
"";
OPEN
myCursor;
read_loop: LOOP
FETCH myCursor INTO drop_part_name;
IF done THEN
LEAVE read_loop;
END IF;
SET
@drop_partitions
=
IF(@drop_partitions
=
"
", drop_part_name, CONCAT(@drop_partitions, "
,", drop_part_name));
END LOOP;
IF @drop_partitions !
=
"" THEN
SET
@full_sql
=
CONCAT(@alter_header, @drop_partitions,
";"
);
PREPARE STMT FROM @full_sql;
EXECUTE STMT;
DEALLOCATE PREPARE STMT;
SELECT CONCAT(SCHEMANAME,
"."
, TABLENAME) AS `table`, @drop_partitions AS `partitions_deleted`;
ELSE
SELECT CONCAT(SCHEMANAME,
"."
, TABLENAME) AS `table`,
"N/A"
AS `partitions_deleted`;
END IF;
END
|
partition_maintenance
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
|
BEGIN
DECLARE OLDER_THAN_PARTITION_DATE VARCHAR(
16
);
DECLARE PARTITION_NAME VARCHAR(
16
);
DECLARE LESS_THAN_TIMESTAMP
INT
;
DECLARE CUR_TIME
INT
;
CALL partition_verify(SCHEMA_NAME, TABLE_NAME, HOURLY_INTERVAL);
SET
CUR_TIME
=
UNIX_TIMESTAMP(DATE_FORMAT(NOW(),
'%Y-%m-%d 00:00:00'
));
IF DATE(NOW())
=
'2014-04-01'
THEN
SET
CUR_TIME
=
UNIX_TIMESTAMP(DATE_FORMAT(DATE_ADD(NOW(), INTERVAL
1
DAY),
'%Y-%m-%d 00:00:00'
));
END IF;
SET
@__interval
=
1
;
create_loop: LOOP
IF @__interval > CREATE_NEXT_INTERVALS THEN
LEAVE create_loop;
END IF;
SET
LESS_THAN_TIMESTAMP
=
CUR_TIME
+
(HOURLY_INTERVAL
*
@__interval
*
3600
);
SET
PARTITION_NAME
=
FROM_UNIXTIME(CUR_TIME
+
HOURLY_INTERVAL
*
(@__interval
-
1
)
*
3600
,
'p%Y%m%d%H00'
);
CALL partition_create(SCHEMA_NAME, TABLE_NAME, PARTITION_NAME, LESS_THAN_TIMESTAMP);
SET
@__interval
=
@__interval
+
1
;
END LOOP;
SET
OLDER_THAN_PARTITION_DATE
=
DATE_FORMAT(DATE_SUB(NOW(), INTERVAL KEEP_DATA_DAYS DAY),
'%Y%m%d0000'
);
CALL partition_drop(SCHEMA_NAME, TABLE_NAME, OLDER_THAN_PARTITION_DATE);
END
|
partition_maintenance_all
|
1
2
3
|
BEGIN
CALL partition_maintenance(SCHEMA_NAME,
'history'
,
7
,
24
,
7
);
END
|
partition_verify
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
|
BEGIN
DECLARE PARTITION_NAME VARCHAR(
16
);
DECLARE RETROWS
INT
(
11
);
DECLARE FUTURE_TIMESTAMP TIMESTAMP;
SELECT COUNT(
1
) INTO RETROWS
FROM information_schema.partitions
WHERE table_schema
=
SCHEMANAME AND TABLE_NAME
=
TABLENAME AND partition_name IS NULL;
IF RETROWS
=
1
THEN
SET
FUTURE_TIMESTAMP
=
TIMESTAMPADD(HOUR, HOURLYINTERVAL, CONCAT(CURDATE(),
" "
,
'00:00:00'
));
SET
PARTITION_NAME
=
DATE_FORMAT(CURDATE(),
'p%Y%m%d%H00'
);
SET
@__PARTITION_SQL
=
CONCAT(
"ALTER TABLE "
, SCHEMANAME,
"."
, TABLENAME,
" PARTITION BY RANGE(`clock`)"
);
SET
@__PARTITION_SQL
=
CONCAT(@__PARTITION_SQL,
"(PARTITION "
, PARTITION_NAME,
" VALUES LESS THAN ("
, UNIX_TIMESTAMP(FUTURE_TIMESTAMP),
"));"
);
PREPARE STMT FROM @__PARTITION_SQL;
EXECUTE STMT;
DEALLOCATE PREPARE STMT;
END IF;
END
|
cat /opt/zabbix_mysql_partition.sh
|
1
2
3
4
5
|
#!/bin/bash
if
[
-
f
/
root
/
.bash_profile ];then
source
/
root
/
.bash_profile
fi
mysql
-
uroot
-
p
'xx'
zabbix
-
e
"CALL partition_maintenance_all('zabbix');"
|
or
假如迁移Zabbix数据库到TokuDB
http://imysql.com/2014/06/24/migrate-zabbix-db-to-tokudb.shtml
利用event为zabbix数据表定期添加和删除分区
http://imysql.cn/2015/11/23/mysql-faq-use-event-in-zabbix-table-partitions.shtml
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
|
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
=
TokuDB DEFAULT CHARSET
=
utf8 ROW_FORMAT
=
TOKUDB_QUICKLZ
PARTITION BY
RANGE
(clock)
(PARTITION p20150531 VALUES LESS THAN (
1433088000
) ENGINE
=
TokuDB,
...
PARTITION p20160411 VALUES LESS THAN (
1460390400
) ENGINE
=
TokuDB);
delimiter $$$
drop event
if
exists zabbix_alter_partition_daily;
CREATE EVENT zabbix_alter_partition_daily
ON SCHEDULE EVERY
1
DAY
-
-
每天执行
DO
begin
-
-
记日志
insert into zlogs select
0
, now(), date_format(date_sub(now(), INTERVAL
180
DAY),
" ALTER TABLE history DROP PARTITION p%Y%m%d"
);
-
-
删除history表
180
天前的旧分区
-
-
用PREPARE & EXECUTE 准备和执行删除的SQL
SET
@drop_p_stmt
=
date_format(date_sub(now(), INTERVAL
180
DAY),
" ALTER TABLE history DROP PARTITION p%Y%m%d"
);
PREPARE drop_p_stmt FROM @drop_p_stmt;
EXECUTE drop_p_stmt;
-
-
创建history表
30
天后的新分区
insert into zlogs select
0
, now(), concat(
date_format(date_add(now(), INTERVAL
180
DAY),
" ALTER TABLE history ADD PARTITION ( PARTITION p%Y%m%d VALUES LESS THAN "
),
"("
,
unix_timestamp( date_add(date_format(now(),
"%Y%m%d"
), INTERVAL
31
DAY) ),
"))"
);
SET
@add_p_stmt
=
concat(
date_format(date_add(now(), INTERVAL
30
DAY),
" ALTER TABLE history ADD PARTITION ( PARTITION p%Y%m%d VALUES LESS THAN "
),
"("
,
unix_timestamp( date_add(date_format(now(),
"%Y%m%d"
), INTERVAL
31
DAY) ),
"))"
);
PREPARE add_p_stmt FROM @add_p_stmt;
EXECUTE add_p_stmt;
end $$$
delimiter ;
|
本文转自 liqius 51CTO博客，原文链接：http://blog.51cto.com/szgb17/1977751，如需转载请自行联系原作者
