Shulou(Shulou.com)05/31 Report--

This article introduces the relevant knowledge of "how to do partition tables on mysql database on zabbix". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

Independent tablespace settings:

Innodb_file_per_table=1

Modify the table index:

Zabbix3.2 above skips this step

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 >

Create a stored procedure:

Add stored procedures for partitions:

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

Delete the stored procedure for the partition:

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

Check the stored procedure for the partition and create the first partition:

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 (), ", '00lv 0000'); 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

Write the above four stored procedure statements into a sql file, partition_call.sql

Execute: mysql-uzabbix-pumped zabbix 'zabbix

< partition_call.sql 使用存储过程： mysql>

CALL partition_maintenance (',',)

Zabbix_db_name: library name

Table_name: table name

Days_to_keep_data: how many days of data are saved

Hourly_interval: how often a partition is generated

Num_future_intervals_to_create: how many partitions are generated this time

For example:

Mysql > CALL partition_maintenance ('zabbix',' history', 7,24,7)

This example is that the history table saves data for up to 7 days and generates a partition every 24 hours. This time, a total of 7 partitions are generated.

Call stored procedures uniformly:

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

Save this statement as a sql file, partition_all.sql, and import the stored procedure again

Mysql-uzabbix-packs Zabibix 'zabbix

< partition_all.sql 计划任务每天调用一次： 注意： 若数据量比较大，首次执行的时间较长，请使用nohup执行（我当时执行了15个小时左右，这期间zabbix是无法正常工作的，获取的agent数据不展示,但数据不会丢失） nohup time mysql -uzabbix -pzabbix zabbix -e "CALL partition_maintenance_all('zabbix');" &>

/ tmp/file.txt &

Later, you only need to call this stored procedure, which is executed once a day:

Mysql-uzabbix-pzabbix zabbix-e "CALL partition_maintenance_all ('zabbix');"

Write as crontab:

# crontab-e

0 1 * / data/tools/mysql/bin/mysql-uzabbix-pzabbix zabbix-e "CALL partition_maintenance_all ('zabbix');"

Execute the script:

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 | NumberA | +-+- -+ 1 row in set (0.01 sec). +-+-+ | table | partitions_deleted | +-+-- -+ | zabbix.trends_uint | Namp A | +-+-+ 1 row in set (22.85 sec) Query OK 0 rows affected, 1 warning (22.85 sec)

[root@hk-zabbix ~] # mysql-uzabbix-packs Zabix '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) |

Turn off housekeeping:

Done.

This is the end of the content of "how to partition the mysql database on zabbix". Thank you for your reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.