zabbix上怎么对mysql数据库做分区表

2025-03-12 技术教程

本篇内容介绍了“zabbix上怎么对mysql数据库做分区表”的有关知识，在实际案例的操作过程中，不少人都会遇到这样的困境，接下来就让小编带领大家学习一下如何处理这些情况吧！希望大家仔细阅读，能够学有所成！

独立表空间设置：

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>

创建存储过程：

增加分区的存储过程：

DELIMITER$$CREATEPROCEDURE`partition_create`(SCHEMANAMEvarchar(64),TABLENAMEvarchar(64),PARTITIONNAMEvarchar(64),CLOCKint)BEGIN/*SCHEMANAME=TheDBschemainwhichtomakechangesTABLENAME=ThetablewithpartitionstopotentiallydeletePARTITIONNAME=Thenameofthepartitiontocreate*//*Verifythatthepartitiondoesnotalreadyexist*/DECLARERETROWSINT;SELECTCOUNT(1)INTORETROWSFROMinformation_schema.partitionsWHEREtable_schema=SCHEMANAMEANDtable_name=TABLENAMEANDpartition_description>=CLOCK;IFRETROWS=0THEN/*1.Printamessageindicatingthatapartitionwascreated.2.CreatetheSQLtocreatethepartition.3.ExecutetheSQLfrom#2.*/SELECTCONCAT("partition_create(",SCHEMANAME,",",TABLENAME,",",PARTITIONNAME,",",CLOCK,")")ASmsg;SET@sql=CONCAT('ALTERTABLE',SCHEMANAME,'.',TABLENAME,'ADDPARTITION(PARTITION',PARTITIONNAME,'VALUESLESSTHAN(',CLOCK,'));');PREPARESTMTFROM@sql;EXECUTESTMT;DEALLOCATEPREPARESTMT;ENDIF;END$$DELIMITER;

删除分区的存储过程：

DELIMITER$$CREATEPROCEDURE`partition_drop`(SCHEMANAMEVARCHAR(64),TABLENAMEVARCHAR(64),DELETE_BELOW_PARTITION_DATEBIGINT)BEGIN/*SCHEMANAME=TheDBschemainwhichtomakechangesTABLENAME=ThetablewithpartitionstopotentiallydeleteDELETE_BELOW_PARTITION_DATE=Deleteanypartitionswithnamesthataredatesolderthanthisone(yyyy-mm-dd)*/DECLAREdoneINTDEFAULTFALSE;DECLAREdrop_part_nameVARCHAR(16);/*GetalistofallthepartitionsthatareolderthanthedateinDELETE_BELOW_PARTITION_DATE.Allpartitionsareprefixedwitha"p",souseSUBSTRINGTOgetridofthatcharacter.*/DECLAREmyCursorCURSORFORSELECTpartition_nameFROMinformation_schema.partitionsWHEREtable_schema=SCHEMANAMEANDtable_name=TABLENAMEANDCAST(SUBSTRING(partition_nameFROM2)ASUNSIGNED)<DELETE_BELOW_PARTITION_DATE;DECLARECONTINUEHANDLERFORNOTFOUNDSETdone=TRUE;/*Createthebasicsforwhenweneedtodropthepartition.Also,create@drop_partitionstoholdacomma-delimitedlistofallpartitionsthatshouldbedeleted.*/SET@alter_header=CONCAT("ALTERTABLE",SCHEMANAME,".",TABLENAME,"DROPPARTITION");SET@drop_partitions="";/*Startloopingthroughallthepartitionsthataretooold.*/OPENmyCursor;read_loop:LOOPFETCHmyCursorINTOdrop_part_name;IFdoneTHENLEAVEread_loop;ENDIF;SET@drop_partitions=IF(@drop_partitions="",drop_part_name,CONCAT(@drop_partitions,",",drop_part_name));ENDLOOP;IF@drop_partitions!=""THEN/*1.BuildtheSQLtodropallthenecessarypartitions.2.RuntheSQLtodropthepartitions.3.Printoutthetablepartitionsthatweredeleted.*/SET@full_sql=CONCAT(@alter_header,@drop_partitions,";");PREPARESTMTFROM@full_sql;EXECUTESTMT;DEALLOCATEPREPARESTMT;SELECTCONCAT(SCHEMANAME,".",TABLENAME)AS`table`,@drop_partitionsAS`partitions_deleted`;ELSE/*Nopartitionsarebeingdeleted,soprintout"N/A"(Notapplicable)toindicatethatnochangesweremade.*/SELECTCONCAT(SCHEMANAME,".",TABLENAME)AS`table`,"N/A"AS`partitions_deleted`;ENDIF;END$$DELIMITER;

维护分区的存储过程：

DELIMITER$$CREATEPROCEDURE`partition_maintenance`(SCHEMA_NAMEVARCHAR(32),TABLE_NAMEVARCHAR(32),KEEP_DATA_DAYSINT,HOURLY_INTERVALINT,CREATE_NEXT_INTERVALSINT)BEGINDECLAREOLDER_THAN_PARTITION_DATEVARCHAR(16);DECLAREPARTITION_NAMEVARCHAR(16);DECLAREOLD_PARTITION_NAMEVARCHAR(16);DECLARELESS_THAN_TIMESTAMPINT;DECLARECUR_TIMEINT;CALLpartition_verify(SCHEMA_NAME,TABLE_NAME,HOURLY_INTERVAL);SETCUR_TIME=UNIX_TIMESTAMP(DATE_FORMAT(NOW(),'%Y-%m-%d00:00:00'));SET@__interval=1;create_loop:LOOPIF@__interval>CREATE_NEXT_INTERVALSTHENLEAVEcreate_loop;ENDIF;SETLESS_THAN_TIMESTAMP=CUR_TIME+(HOURLY_INTERVAL*@__interval*3600);SETPARTITION_NAME=FROM_UNIXTIME(CUR_TIME+HOURLY_INTERVAL*(@__interval-1)*3600,'p%Y%m%d%H00');IF(PARTITION_NAME!=OLD_PARTITION_NAME)THENCALLpartition_create(SCHEMA_NAME,TABLE_NAME,PARTITION_NAME,LESS_THAN_TIMESTAMP);ENDIF;SET@__interval=@__interval+1;SETOLD_PARTITION_NAME=PARTITION_NAME;ENDLOOP;SETOLDER_THAN_PARTITION_DATE=DATE_FORMAT(DATE_SUB(NOW(),INTERVALKEEP_DATA_DAYSDAY),'%Y%m%d0000');CALLpartition_drop(SCHEMA_NAME,TABLE_NAME,OLDER_THAN_PARTITION_DATE);END$$DELIMITER;

检查分区、创建第一个分区的存储过程：

DELIMITER$$CREATEPROCEDURE`partition_verify`(SCHEMANAMEVARCHAR(64),TABLENAMEVARCHAR(64),HOURLYINTERVALINT(11))BEGINDECLAREPARTITION_NAMEVARCHAR(16);DECLARERETROWSINT(11);DECLAREFUTURE_TIMESTAMPTIMESTAMP;/**CheckifanypartitionsexistforthegivenSCHEMANAME.TABLENAME.*/SELECTCOUNT(1)INTORETROWSFROMinformation_schema.partitionsWHEREtable_schema=SCHEMANAMEANDtable_name=TABLENAMEANDpartition_nameISNULL;/**Ifpartitionsdonotexist,goaheadandpartitionthetable*/IFRETROWS=1THEN/**Takethecurrentdateat00:00:00andaddHOURLYINTERVALtoit.Thisisthetimestampbelowwhichwewillstorevalues.*Webeginpartitioningbasedonthebeginningofaday.Thisisbecausewedon'twanttogeneratearandompartition*thatwon'tnecessarilyfallinlinewiththedesiredpartitionnaming(ie:ifthehourintervalis24hours,wecould*endupcreatingapartitionnownamed"p201403270600"whenallotherpartitionswillbelike"p201403280000").*/SETFUTURE_TIMESTAMP=TIMESTAMPADD(HOUR,HOURLYINTERVAL,CONCAT(CURDATE(),"",'00:00:00'));SETPARTITION_NAME=DATE_FORMAT(CURDATE(),'p%Y%m%d%H00');--CreatethepartitioningquerySET@__PARTITION_SQL=CONCAT("ALTERTABLE",SCHEMANAME,".",TABLENAME,"PARTITIONBYRANGE(`clock`)");SET@__PARTITION_SQL=CONCAT(@__PARTITION_SQL,"(PARTITION",PARTITION_NAME,"VALUESLESSTHAN(",UNIX_TIMESTAMP(FUTURE_TIMESTAMP),"));");--RunthepartitioningqueryPREPARESTMTFROM@__PARTITION_SQL;EXECUTESTMT;DEALLOCATEPREPARESTMT;ENDIF;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$$CREATEPROCEDURE`partition_maintenance_all`(SCHEMA_NAMEVARCHAR(32))BEGINCALLpartition_maintenance(SCHEMA_NAME,'history',28,24,14);CALLpartition_maintenance(SCHEMA_NAME,'history_log',28,24,14);CALLpartition_maintenance(SCHEMA_NAME,'history_str',28,24,14);CALLpartition_maintenance(SCHEMA_NAME,'history_text',28,24,14);CALLpartition_maintenance(SCHEMA_NAME,'history_uint',28,24,14);CALLpartition_maintenance(SCHEMA_NAME,'trends',730,24,14);CALLpartition_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 timemysql-uzabbix -pzabbixzabbix-e "CALL partition_maintenance_all('zabbix');" &> /tmp/file.txt &

后面只需要调用这个存储过程就可以了，每天执行一次：

mysql-uzabbix -pzabbixzabbix-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>CALLpartition_maintenance('zabbix','history',28,24,14);+-----------------------------------------------------------+|msg|+-----------------------------------------------------------+|partition_create(zabbix,history,p201404160000,1397718000)|+-----------------------------------------------------------+1rowinset(0.39sec)+-----------------------------------------------------------+|msg|+-----------------------------------------------------------+|partition_create(zabbix,history,p201404170000,1397804400)|+-----------------------------------------------------------+1rowinset(0.51sec)

[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:

完成。