共計 9763 個字符，預(yù)計需要花費 25 分鐘才能閱讀完成。

本篇內(nèi)容介紹了“zabbix 上怎么對 mysql 數(shù)據(jù)庫做分區(qū)表”的有關(guān)知識，在實際案例的操作過程中，不少人都會遇到這樣的困境，接下來就讓丸趣 TV 小編帶領(lǐng)大家學(xué)習(xí)一下如何處理這些情況吧！希望大家仔細(xì)閱讀，能夠?qū)W有所成！

獨立表空間設(shè)置：

innodb_file_per_table=1

修改表索引：

zabbix3.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

創(chuàng)建存儲過程：

增加分區(qū)的存儲過程：

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 ;

刪除分區(qū)的存儲過程：

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 ;

維護分區(qū)的存儲過程：

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 ;

檢查分區(qū)、創(chuàng)建第一個分區(qū)的存儲過程：

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

執(zhí)行：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：保存多少天的數(shù)據(jù)

hourly_interval：每隔多久生成一個分區(qū)

num_future_intervals_to_create：本次一共生成多少個分區(qū)

例如：

mysql CALL partition_maintenance(zabbix , history , 7, 24, 7);

這個例子就是 history 表最多保存 7 天的數(shù)據(jù)，每隔 24 小時生成一個分區(qū)，這次一共生成 7 個分區(qū)

統(tǒng)一調(diào)用存儲過程：

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，再次導(dǎo)入存儲過程

mysql -uzabbix -p zabbix  zabbix  partition_all.sql

計劃任務(wù)每天調(diào)用一次：

注意：

若數(shù)據(jù)量比較大，首次執(zhí)行的時間較長，請使用 nohup 執(zhí)行（我當(dāng)時執(zhí)行了 15 個小時左右，這期間 zabbix 是無法正常工作的，獲取的 agent 數(shù)據(jù)不展示, 但數(shù)據(jù)不會丟失）

nohup time mysql -uzabbix -pzabbix zabbix -e CALL partition_maintenance_all(zabbix /tmp/file.txt

后面只需要調(diào)用這個存儲過程就可以了，每天執(zhí)行一次：

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

執(zhí)行腳本：

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) |

關(guān)閉 housekeeping:

完成。

“zabbix 上怎么對 mysql 數(shù)據(jù)庫做分區(qū)表”的內(nèi)容就介紹到這里了，感謝大家的閱讀。如果想了解更多行業(yè)相關(guān)的知識可以關(guān)注丸趣 TV 網(wǎng)站，丸趣 TV 小編將為大家輸出更多高質(zhì)量的實用文章！