zabbix上對mysql資料庫做分割槽表
參考：https://www.cnblogs.com/metasequoia/p/5805845.html
獨立表空間設定：
innodb_file_per_table=1
修改表索引：
zabbix 3.2以上版本跳過此步驟
mysql> Alter table history_text drop primary key, add index (id), drop index history_text_2, add index history_text_2 (itemid, id);
Query OK, 0 rows affected (0.49 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> Alter table history_log drop primary key, add index (id), drop index history_log_2, add index history_log_2 (itemid, id);
Query OK, 0 rows affected (2.71 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql>
建立儲存過程：
增加分割槽 的 儲存過程：
DELIMITER $$CREATE PROCEDURE `partition_create`(SCHEMANAME varchar(64), TABLENAME varchar(64), PARTITIONNAME varchar(64), CLOCK int)BEGIN
/* SCHEMANAME = The DB schema in which to make changes TABLENAME = The table with partitions to potentially delete PARTITIONNAME = The name of the partition to create */
/* Verify that the partition does not already exist */
DECLARE RETROWS INT;
SELECT COUNT(1) INTO RETROWS
FROM information_schema.partitions
WHERE table_schema = SCHEMANAME AND table_name = TABLENAME AND partition_description >= CLOCK;
IF RETROWS = 0 THEN
/* 1. Print a message indicating that a partition was created. 2. Create the SQL to create the partition. 3. Execute the SQL from #2. */
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$$DELIMITER ;
刪除分割槽 的 儲存過程：
DELIMITER $$CREATE PROCEDURE `partition_drop`(SCHEMANAME VARCHAR(64), TABLENAME VARCHAR(64), DELETE_BELOW_PARTITION_DATE BIGINT)BEGIN
/* SCHEMANAME = The DB schema in which to make changes TABLENAME = The table with partitions to potentially delete DELETE_BELOW_PARTITION_DATE = Delete any partitions with names that are dates older than this one (yyyy-mm-dd) */
DECLARE done INT DEFAULT FALSE;
DECLARE drop_part_name VARCHAR(16);
/* Get a list of all the partitions that are older than the date in DELETE_BELOW_PARTITION_DATE. All partitions are prefixed with a "p", so use SUBSTRING TO get rid of that character. */
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;
/* Create the basics for when we need to drop the partition. Also, create @drop_partitions to hold a comma-delimited list of all partitions that should be deleted. */
SET @alter_header = CONCAT("ALTER TABLE ", SCHEMANAME, ".", TABLENAME, " DROP PARTITION ");
SET @drop_partitions = "";
/* Start looping through all the partitions that are too old. */
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
/* 1. Build the SQL to drop all the necessary partitions. 2. Run the SQL to drop the partitions. 3. Print out the table partitions that were deleted. */
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
/* No partitions are being deleted, so print out "N/A" (Not applicable) to indicate that no changes were made. */
SELECT CONCAT(SCHEMANAME, ".", TABLENAME) AS `table`, "N/A" AS `partitions_deleted`;
END IF;END$$DELIMITER ;
維護分割槽
的
儲存過程：
DELIMITER $$CREATE PROCEDURE `partition_maintenance`(SCHEMA_NAME VARCHAR(32), TABLE_NAME VARCHAR(32), KEEP_DATA_DAYS INT, HOURLY_INTERVAL INT, CREATE_NEXT_INTERVALS INT)BEGIN DECLARE OLDER_THAN_PARTITION_DATE VARCHAR(16); DECLARE PARTITION_NAME VARCHAR(16); DECLARE OLD_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')); 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'); IF(PARTITION_NAME != OLD_PARTITION_NAME) THEN CALL partition_create(SCHEMA_NAME, TABLE_NAME, PARTITION_NAME, LESS_THAN_TIMESTAMP); END IF; SET @__interval=@__interval+1; SET OLD_PARTITION_NAME = PARTITION_NAME; 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$$DELIMITER ;
檢查分割槽、建立第一個分割槽
的
儲存過程：
DELIMITER $$CREATE PROCEDURE `partition_verify`(SCHEMANAME VARCHAR(64), TABLENAME VARCHAR(64), HOURLYINTERVAL INT(11))BEGIN
DECLARE PARTITION_NAME VARCHAR(16);
DECLARE RETROWS INT(11);
DECLARE FUTURE_TIMESTAMP TIMESTAMP;
/* * Check if any partitions exist for the given SCHEMANAME.TABLENAME. */
SELECT COUNT(1) INTO RETROWS
FROM information_schema.partitions
WHERE table_schema = SCHEMANAME AND table_name = TABLENAME AND partition_name IS NULL;
/* * If partitions do not exist, go ahead and partition the table */
IF RETROWS = 1 THEN
/* * Take the current date at 00:00:00 and add HOURLYINTERVAL to it. This is the timestamp below which we will store values. * We begin partitioning based on the beginning of a day. This is because we don't want to generate a random partition * that won't necessarily fall in line with the desired partition naming (ie: if the hour interval is 24 hours, we could * end up creating a partition now named "p201403270600" when all other partitions will be like "p201403280000"). */
SET FUTURE_TIMESTAMP = TIMESTAMPADD(HOUR, HOURLYINTERVAL, CONCAT(CURDATE(), " ", '00:00:00'));
SET PARTITION_NAME = DATE_FORMAT(CURDATE(), 'p%Y%m%d%H00');
-- Create the partitioning query
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), "));");
-- Run the partitioning query
PREPARE STMT FROM @__PARTITION_SQL;
EXECUTE STMT;
DEALLOCATE PREPARE STMT;
END IF;END$$DELIMITER ;
將上面4個儲存過程語句寫到一個sql檔案裡，partition_call.sql
執行： mysql -uzabbix -p' zabbix ' zabbix < partition_call.sql
使用儲存過程：
mysql> CALL partition_maintenance('<zabbix_db_name>', '<table_name>', <days_to_keep_data>, <hourly_interval>, <num_future_intervals_to_create>)
zabbix _db_name：庫名
table_name：表名
days_to_keep_data：儲存多少天 的 資料
hourly_interval：每隔多久生成一個分割槽
num_future_intervals_to_create：本次一共生成多少個分割槽
例如：
mysql
> CALL partition_maintenance('
zabbix
', 'history', 7, 24, 7);
這個例子就是 history表 最多儲存7天 的 資料，每隔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 ;
將這條語句儲存成sql檔案 partition_all.sql ，再次匯入儲存過程
mysql -uzabbix -p' zabbix ' zabbix < partition_all.sql
計劃任務每天呼叫一次：
注意：
若資料量比較大，首次執行 的 時間較長，請使用nohup執行（我當時執行了15個小時左右，這期間 zabbix 是無法正常工作 的 ，獲取 的 agent資料不展示,但資料不會丟失）
nohup time mysql -uzabbix -pzabbix zabbix -e "CALL partition_maintenance_all(' zabbix ');" &> /tmp/file.txt &
後面只需要呼叫這個儲存過程就可以了，每天執行一次：
mysql -uzabbix -pzabbix zabbix -e "CALL partition_maintenance_all(' zabbix ');"
寫成crontab:
# crontab -e
0 1 * * * /data/tools/mysql/bin/mysql -uzabbix -pzabbix zabbix -e "CALL partition_maintenance_all('zabbix');"
執行指令碼：
mysql> CALL partition_maintenance('zabbix', 'history', 28, 24, 14);
+-----------------------------------------------------------+
| msg |
+-----------------------------------------------------------+
| partition_create(zabbix,history,p201404160000,1397718000) |
+-----------------------------------------------------------+
1 row in set (0.39 sec)
+-----------------------------------------------------------+
| msg |
+-----------------------------------------------------------+
| partition_create(zabbix,history,p201404170000,1397804400) |
+-----------------------------------------------------------+
1 row in set (0.51 sec)
mysql> CALL partition_maintenance_all('zabbix');
+----------------+--------------------+
| table | partitions_deleted |
+----------------+--------------------+
| zabbix.history | N/A |
+----------------+--------------------+
1 row in set (0.01 sec)
....
....
....
+--------------------+--------------------+
| table | partitions_deleted |
+--------------------+--------------------+
| zabbix.trends_uint | N/A |
+--------------------+--------------------+
1 row in set (22.85 sec)
Query OK, 0 rows affected, 1 warning (22.85 sec)
[root@hk-zabbix ~]# mysql -uzabbix -p'zabbix' zabbix -e "CALL partition_maintenance_all('zabbix');"
mysql: [Warning] Using a password on the command line interface can be insecure.
+-----------------------------------------------------------+
| msg |
+-----------------------------------------------------------+
| partition_create(zabbix,history,p201811080000,1541692800) |
+-----------------------------------------------------------+
+-----------------------------------------------------------+
| msg |
+-----------------------------------------------------------+
| partition_create(zabbix,history,p201811090000,1541779200) |
關閉housekeeping:
完成。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/22996654/viewspace-2219142/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- Zabbix系統MySQL資料庫分割槽表的設定--精簡說明MySql資料庫
- MySQL資料表分割槽手記MySql
- 【MYSQL】 分割槽表MySql
- MySql分表、分庫、分片和分割槽MySql
- MySQL線上轉分割槽表（以及TiDB）MySqlTiDB
- MySql資料分割槽操作之新增分割槽操作MySql
- MySQL的nnodb引擎表資料分割槽儲存MySql
- MySQL 分割槽表探索MySql
- PG的非分割槽表線上轉分割槽表
- Mysql資料分片技術(一)——初識表分割槽MySql
- Mysql表分割槽實現MySql
- mysql 進行表分割槽MySql
- Mysql表分割槽實操MySql
- MySQL調優之分割槽表MySql
- MySQL 分割槽表知識整理MySql
- oracle分割槽表和分割槽表exchangeOracle
- ORACLE刪除-表分割槽和資料Oracle
- hive 動態分割槽插入資料表Hive
- (3) MySQL分割槽表使用方法MySql
- oracle 線上重新定義，普通表改變分割槽表，分割槽表可以更改型別、分割槽欄位等Oracle型別
- 資料庫系統設計：分割槽資料庫
- mysql~關於mysql分割槽表的測試MySql
- oracle分割槽表和非分割槽表exchangeOracle
- 對oracle分割槽表的理解整理Oracle
- mysql 分割槽MySql
- Oracle資料庫分割槽表SPLIT操作導致歸檔瘋漲Oracle資料庫
- oracle 更改分割槽表資料 ora-14402Oracle
- mysql分割槽表佔用大量容量處理（最佳化）及歸檔分割槽表MySql
- 第41期：MySQL 雜湊分割槽表MySql
- 第40期：MySQL 分割槽表案例分享MySql
- E6 資料庫分割槽技術資料庫
- MySQL分表後原分割槽表處理方案MySql
- PostgreSQL/LightDB 分割槽表之分割槽裁剪SQL
- 理解MySQL分割槽MySql
- 搞懂MySQL分割槽MySql
- MySql建立分割槽MySql
- Oracle查詢Interval partition分割槽表內資料Oracle
- AppBoxFuture: 大資料表分割槽的3種策略APP大資料