由于测试环境上面使用的zabbix主机配置比较低,经常会遇到性能瓶颈(主要是数据库和磁盘I/O等),于是倒逼我使用了一些方式来缓解这些问题。
主要是以前使用的那个备份数据库的脚本是对zabbix数据库进行全备的,使用的又是mysql自带的工具mysqldump,当数据量大了之后进行全备所花的时间比较长,这样将会造成数据库的锁读。。。从而使zabbix服务以为mysql死掉了,产生一大堆的报警。
后来发现原来造成数据库数据量大量增加的是zabbix数据库中的一些存储数据的大表导致的。于是备份数据库的时候可以选择跳过这些表进行备份,这样,将大大减少数据库备份所花的时间(PS:之前备份数据库所花时间在十分钟左右,现在跳过大表备份,所花时间在1S左右就能备份完,大大缩短了备份数据库时间)。
下面就贴出某位大神写的专门为zabbix数据库做备份以及恢复的脚本:
#!/bin/bash #author:itnihao red='\e[0;31m'#红色 RED='\e[1;31m' green='\e[0;32m'#绿色 GREEN='\e[1;32m' blue='\e[0;34m'#蓝色 BLUE='\e[1;34m' purple='\e[0;35m'#紫色 PURPLE='\e[1;35m' NC='\e[0m'#没有颜色 source/etc/bashrc source/etc/profile MySQL_USER=zabbix MySQL_PASSWORD=zabbix MySQL_HOST=localhost MySQL_PORT=3306 MySQL_DUMP_PATH=/opt/backup MYSQL_BIN_PATH=/opt/software/mysql/bin/mysql MYSQL_DUMP_BIN_PATH=/opt/software/mysql/bin/mysqldump MySQL_DATABASE_NAME=zabbix DATE=$(date'+%Y%m%d') MySQLDUMP(){ [-d${MySQL_DUMP_PATH}]||mkdir${MySQL_DUMP_PATH} cd${MySQL_DUMP_PATH} [-dlogs]||mkdirlogs [-d${DATE}]||mkdir${DATE} cd${DATE} #TABLE_NAME_ALL=$(${MYSQL_BIN_PATH}-u${MySQL_USER}-p${MySQL_PASSWORD}-h${MySQL_HOST}${MySQL_DATABASE_NAME}-e"showtables"|egrep-v"(Tables_in_zabbix)") TABLE_NAME_ALL=$(${MYSQL_BIN_PATH}-u${MySQL_USER}-p${MySQL_PASSWORD}-h${MySQL_HOST}${MySQL_DATABASE_NAME}-e"showtables"|egrep-v"(Tables_in_zabbix|history*|trends*|acknowledges|alerts|auditlog|events|service_alarms)") forTABLE_NAMEin${TABLE_NAME_ALL} do ${MYSQL_DUMP_BIN_PATH}--opt-u${MySQL_USER}-p${MySQL_PASSWORD}-P${MySQL_PORT}-h${MySQL_HOST}${MySQL_DATABASE_NAME}${TABLE_NAME}>${TABLE_NAME}.sql sleep0.01 done ["$?"==0]&&echo"${DATE}:Backupzabbixsucceed">>${MySQL_DUMP_PATH}/logs/ZabbixMysqlDump.log ["$?"!=0]&&echo"${DATE}:Backupzabbixnotsucceed">>${MySQL_DUMP_PATH}/logs/ZabbixMysqlDump.log cd${MySQL_DUMP_PATH}/ rm-rf$(date+%Y%m%d--date='5daysago') exit0 } MySQLImport(){ cd${MySQL_DUMP_PATH} DATE=$(ls${MySQL_DUMP_PATH}|egrep"\b^[0-9]+$\b") echo-e"${green}${DATE}" echo-e"${blue}whatDATEdoyouwanttoimport,pleaseinputdate:${NC}" readSELECT_DATE if[-d"${SELECT_DATE}"];then echo-e"youselectis${green}${SELECT_DATE}${NC},doyouwanttocontine,if,input${red}(yes|y|Y)${NC},elsethenexit" readInput [['yes|y|Y'=~"${Input}"]] status="$?" if["${status}"=="0"];then echo"nowimportSQL.......Pleasewait......." else exit1 fi cd${SELECT_DATE} forPER_TABEL_SQLin$(ls*.sql) do ${MYSQL_BIN_PATH}-u${MySQL_USER}-p${MySQL_PASSWORD}-h${MySQL_HOST}${MySQL_DATABASE_NAME}<${PER_TABEL_SQL} echo-e"import${PER_TABEL_SQL}${PURPLE}........................${NC}" done echo"FinishimportSQL,PleasecheckZabbixdatabase" else echo"Don'texist${SELECT_DATE}DIR" fi } case"$1"in MySQLDUMP|mysqldump) MySQLDUMP ;; MySQLImport|mysqlimport) MySQLImport ;; *) echo"Usage:$0{(MySQLDUMP|mysqldump)(MySQLImport|mysqlimport)}" ;; esac
该脚本源出处在这https://github.com/itnihao/zabbix-book/blob/master/03-chapter/Zabbix_MySQLdump_per_table_v2.sh
我这是在大神的脚本上做了修改之后形成的适合我自己备份的脚本,各位也可以自行修改成适合自己的备份脚本。这个脚本实现的效果上面已经说了,之前做全备的时候差不多有4G左右的数据量,现在只备份配置文件数据量只有不到10M,果断大大节省时间以及空间呀。
不过这样的话将无法保证数据的备份,我目前考虑使用xtradbbackup对数据进行增量备份,目前还未实现,留待过两天做吧。
好了,关于数据库备份的事情搞了,然后还需要对大数据量的表进行表分区,参考了zabbix官网的一篇文章https://www.zabbix.org/wiki/Docs/howto/mysql_partition各位有兴趣的话可以去看看,我这里将其总结在了一起,更加方便一点。
表分区可以对大数据量的表进行物理上的拆分成多个文件,但是逻辑上来看,还是一张表,对应用程序是透明的。另外,将这一张大表拆分成很多小表的话将使得数据查询速度能够更快。还可以随时删除旧的数据分区,删除过期数据。这种方式适用于大数据量的表,但是查询量比较少的应用场景。如果是大数据量的表,又有大量查询的话建议还是进行分库分表操作。
好了,不多扯了,开始作业了。
首先,登录数据库(PS:这个就不演示了)
然后登陆到zabbix库中修改两张表的结构:
usezabbix; Altertablehistory_textdropprimarykey,addindex(id),dropindexhistory_text_2,addindexhistory_text_2(itemid,id); Altertablehistory_logdropprimarykey,addindex(id),dropindexhistory_log_2,addindexhistory_log_2(itemid,id);
修改完之后再按照官网上的过程创建四个存储过程:
DELIMITER$$ CREATEPROCEDURE`partition_create`(SCHEMANAMEVARCHAR(64),TABLENAMEVARCHAR(64),PARTITIONNAMEVARCHAR(64),CLOCKINT) BEGIN /* SCHEMANAME=TheDBschemainwhichtomakechanges TABLENAME=Thetablewithpartitionstopotentiallydelete PARTITIONNAME=Thenameofthepartitiontocreate */ /* Verifythatthepartitiondoesnotalreadyexist */ DECLARERETROWSINT; SELECTCOUNT(1)INTORETROWS FROMinformation_schema.partitions WHEREtable_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=TheDBschemainwhichtomakechanges TABLENAME=Thetablewithpartitionstopotentiallydelete DELETE_BELOW_PARTITION_DATE=Deleteanypartitionswithnamesthataredatesolderthanthisone(yyyy-mm-dd) */ DECLAREdoneINTDEFAULTFALSE; DECLAREdrop_part_nameVARCHAR(16); /* Getalistofallthepartitionsthatareolderthanthedate inDELETE_BELOW_PARTITION_DATE.Allpartitionsareprefixedwith a"p",souseSUBSTRINGTOgetridofthatcharacter. */ DECLAREmyCursorCURSORFOR SELECTpartition_name FROMinformation_schema.partitions WHEREtable_schema=SCHEMANAMEANDTABLE_NAME=TABLENAMEANDCAST(SUBSTRING(partition_nameFROM2)ASUNSIGNED)<DELETE_BELOW_PARTITION_DATE; DECLARECONTINUEHANDLERFORNOTFOUNDSETdone=TRUE; /* Createthebasicsforwhenweneedtodropthepartition.Also,create @drop_partitionstoholdacomma-delimitedlistofallpartitionsthat shouldbedeleted. */ SET@alter_header=CONCAT("ALTERTABLE",SCHEMANAME,".",TABLENAME,"DROPPARTITION"); SET@drop_partitions=""; /* Startloopingthroughallthepartitionsthataretooold. */ OPENmyCursor; read_loop:LOOP FETCHmyCursorINTOdrop_part_name; IFdoneTHEN LEAVEread_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)toindicate thatnochangesweremade. */ 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) BEGIN DECLAREOLDER_THAN_PARTITION_DATEVARCHAR(16); DECLAREPARTITION_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:LOOP IF@__interval>CREATE_NEXT_INTERVALSTHEN LEAVEcreate_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'); CALLpartition_create(SCHEMA_NAME,TABLE_NAME,PARTITION_NAME,LESS_THAN_TIMESTAMP); SET@__interval=@__interval+1; 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)) BEGIN DECLAREPARTITION_NAMEVARCHAR(16); DECLARERETROWSINT(11); DECLAREFUTURE_TIMESTAMPTIMESTAMP; /* *CheckifanypartitionsexistforthegivenSCHEMANAME.TABLENAME. */ SELECTCOUNT(1)INTORETROWS FROMinformation_schema.partitions WHEREtable_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'); --Createthepartitioningquery SET@__PARTITION_SQL=CONCAT("ALTERTABLE",SCHEMANAME,".",TABLENAME,"PARTITIONBYRANGE(`clock`)"); SET@__PARTITION_SQL=CONCAT(@__PARTITION_SQL,"(PARTITION",PARTITION_NAME,"VALUESLESSTHAN(",UNIX_TIMESTAMP(FUTURE_TIMESTAMP),"));"); --Runthepartitioningquery PREPARESTMTFROM@__PARTITION_SQL; EXECUTESTMT; DEALLOCATEPREPARESTMT; ENDIF; END$$ DELIMITER;
上面四个存储过程执行后将可以使用
CALLpartition_maintenance('<zabbix_db_name>','<table_name>',<days_to_keep_data>,<hourly_interval>,<num_future_intervals_to_create>)
命令对想要分区的表进行表分区了。其中的参数我这里解释一下。
这是举例:
CALLpartition_maintenance(zabbix,'history_uint',31,24,14);
zabbix_db_name:库名
table_name:表名
days_to_keep_data:保存多少天的数据
hourly_interval:每隔多久生成一个分区
num_future_intervals_to_create:本次一共生成多少个分区
这个例子就是history_uint表最多保存31天的数据,每隔24小时生成一个分区,这次一共生成14个分区
这里可以将上面四个存储过程保存为一个文件,导入到数据库中,文件我稍后将会放在附件中,这里使用的命令是:mysql -uzabbix -pzabbix zabbix<partition_call.sql
然后可以将CALL统一调用也做成一个文件,统一调用的内容如下:
DELIMITER$$ CREATEPROCEDURE`partition_maintenance_all`(SCHEMA_NAMEVARCHAR(32)) BEGIN CALLpartition_maintenance(SCHEMA_NAME,'history',31,24,14); CALLpartition_maintenance(SCHEMA_NAME,'history_log',31,24,14); CALLpartition_maintenance(SCHEMA_NAME,'history_str',31,24,14); CALLpartition_maintenance(SCHEMA_NAME,'history_text',31,24,14); CALLpartition_maintenance(SCHEMA_NAME,'history_uint',31,24,14); CALLpartition_maintenance(SCHEMA_NAME,'trends',180,24,14); CALLpartition_maintenance(SCHEMA_NAME,'trends_uint',180,24,14); END$$ DELIMITER;
也将该文件导入到数据库中,使用命令:mysql -uzabbix -pzabbix zabbix<partition_all.sql
好了,到了这里之后就可以使用如下命令执行表分区了:
mysql-uzabbix-pzabbixzabbix-e"CALLpartition_maintenance_all('zabbix');" +----------------+--------------------+ |table|partitions_deleted| +----------------+--------------------+ |zabbix.history|N/A| +----------------+--------------------+ +--------------------+--------------------+ |table|partitions_deleted| +--------------------+--------------------+ |zabbix.history_log|N/A| +--------------------+--------------------+ +--------------------+--------------------+ |table|partitions_deleted| +--------------------+--------------------+ |zabbix.history_str|N/A| +--------------------+--------------------+ +---------------------+--------------------+ |table|partitions_deleted| +---------------------+--------------------+ |zabbix.history_text|N/A| +---------------------+--------------------+ +---------------------+--------------------+ |table|partitions_deleted| +---------------------+--------------------+ |zabbix.history_uint|N/A| +---------------------+--------------------+ +---------------+--------------------+ |table|partitions_deleted| +---------------+--------------------+ |zabbix.trends|N/A| +---------------+--------------------+ +--------------------+--------------------+ |table|partitions_deleted| +--------------------+--------------------+ |zabbix.trends_uint|N/A| +--------------------+--------------------+
看到如下结果证明所有7张表都进行了表分区,也可以在Mysql的数data目录下看到新生成的表分区文件。(PS:注意,最好是清空history_uint表的数据之后再执行上面这条命令,否则因为这张表数据量太大,转换时间将会好长,清空表中数据的命令为:truncate table history_uint;)
好了,这样可以进行表分区了。
将上面这条命令写入到计划任务中如下:
crontab-l|tail-1 0101***/opt/software/mysql/bin/mysql-uzabbix-pzabbixzabbix-e"CALLpartition_maintenance_all('zabbix');"
每天晚上的1点01执行一次。还有之前写的备份数据库的脚本也需要执行计划任务每天的凌晨0点01执行备份:
crontab-l|tail-2|head-1 0100***/usr/local/scripts/Zabbix_MySQLdump_per_table_v2.shmysqldump
这样就大功告成了,之后再体验一下zabbix的web页面看是不是感觉比以前快了?