[翻译]——Zabbix: Partitioning MySQL / MariaDB database Tables in 5 min

声明:本文转自《https://www.cnblogs.com/kerrycode/p/13875901.html》

考虑到是别人的博客,终究一天别人会删掉,所以花点时间整理到自己的博客上来,加深理解

 

前言:本文是对这篇博客Zabbix: Partitioning MySQL / MariaDB database Tables in 5 min的翻译,如有翻译不当的地方,敬请谅解,请尊重原创和翻译劳动成果,转载的时候请注明出处。谢谢!

英文地址:https://bestmonitoringtools.com/zabbix-partitioning-tables-on-mysql-database

 

你可以将本教程用于任何Zabbix 3.0之后的版本(3.2、3.4、4.0、4.2、4.4、5.0、5.2等)

 

在本教程中,我们将逐步学习如何使用分区脚本(partitioning script)在MySQL或MariaDB服务器上对Zabbix数据库(history和trends表)进行分区。

 

Zabbix从主机采集数据并将其存储在history和trends表中,Zabbix的history表中保存原始数据(Zabbix采集的每一个值),trends表中存储每小时内的合并数据,那些数据的平均值、最小值、最大值。

 

Zabbix的housekeeping进程负责删除trends表和history表中的旧数据。使用delete语句从数据库删除旧数据可能对数据库性能产生负面的性能影响。因此,我们中许多人都收到过令人讨厌的警报Zabbix housekeeper processes more than 75% busy

 

这个问题能够通过数据库分区方案轻松的解决。每小时或每一天为分区表创建一个分区,并在不需要它们的时候,删除这些分区。使用SQL删除分区比使用DELET语句删除数据更高效。

 

在开始之前,请备份你的zabbix数据库,但是如果是新安装的Zabbix Server,则无需备份。

 

步骤1:下载分区脚本进行分区

 

在数据库服务器上下载并解压SQL脚本zbx_db_partitiong.sql

 

wget https://bestmonitoringtools.com/wp-content/uploads/2019/10/zbx_db_partitiong.tar.gz
 
tar -zxvf zbx_db_partitiong.tar.gz

 

脚本"zbx_db_partitiong.sql"中配置保留hisotry表中7天的数据和trends表中365天的数据——如果你可以接受这些设置,请转到步骤2.

 

如果你想修改trends表和history表中保留数据的天数,请打开zbx_db_partitiong.sql文件,如下图所示,修改设置并保存文件。

 

 

步骤2:使用脚本创建分区的存储过程。

 

运行脚本的语法为mysql -u '<db_username>' -p'<db_password>' <zb_database_name> < zbx_db_partitiong.sql,现在用你Zabbix服务器的数据库名称、用户名和密码去创建这些存储过程。

 

mysql -u 'zabbix' -p'zabbixDBpass' zabbix < zbx_db_partitiong.sql

 

在新安装的Zabbix 服务器上,,脚本将非常快速的创建分区过程,但是在大型数据库上,此过程坑你持续数小时。

 

译者点评: 这里应该是作者的笔误或表达有误,不管zabbix数据库多大,创建这些存储过程都非常快,但是在大型的zabbix数据库上,运行这些维护分区(创建分区、删除分区)的存储过程脚本将非常慢。猜测是作者表达有误!

 

步骤3:自动运行分区存储过程

 

我们已经创建了分区的存储过程,但是在我们运行它之前,它是不会做任何事情的。

 

这一步是最重要的,因为我们必须使用分区的存储过程定期(每天)删除和创建分区。

 

不用担心,你不必手工做这些操作,我们可以使用两种工具来完成这些任务: MySQL事件调度程序或Crontab - 选择你喜欢的一种技术。

 

注意:如果没有正确设置Crontab作业来定期创建新分区,Zabbix将会停止采集数据,并且在MySQL的日志文件中出现[Z3005] query failed: [1526] Table has no partition for value .."这样的错误。

 

选项1:使用MySQL事件调度器自动管理分区(推荐这种方式)

 

默认情况下,MySQL的事件调度器(计划任务)是禁用的。你需要在配置文件的[mysqld]下设置event_scheduler=ON来启用它。

 

[mysqld] 
 
event_scheduler = ON

 

如果你不知道该文件位于哪里的话? 如果你看过我的安装和优化Zabbix教程的话,那么你应该知道MySQL的配置文件(10_my_tweaks.cnf)应该位于/etc/mysql/mariadb.conf.d/ /etc/my.cnf.d/下 ,否则尝试使用以下命令进行搜索:

 

sudo grep --include=*.cnf -irl / -e "\[mysqld\]"

 

更改配置后,请重新启动MySQL服务器,以使设置生效!

 

sudo systemctl restart mysql

 

好的,MySQL事件调度器启用后,让我们用下面命令来检查确认一下

mysql -u 'zabbix' -p'zabbixDBpass' zabbix -e "SHOW VARIABLES LIKE 'event_scheduler';"

+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| event_scheduler | ON |
+-----------------+-------+

 

现在,我们可以创建一个事件,该事件每1小时运行一次存储过程 partition_maintenance_all

mysql -u 'zabbix' -p'zabbixDBpass' zabbix -e "CREATE EVENT zbx_partitioning ON SCHEDULE EVERY 3600 SECOND DO CALL partition_maintenance_all('zabbix');"

一个小时后,请使用以下命令检查事件是否已成功执行。

mysql -u 'zabbix' -p'zabbixDBpass' zabbix -e "SELECT * FROM INFORMATION_SCHEMA.events\G"
EVENT_CATALOG: def
                   ...
CREATED: 2020-10-24 11:01:07
LAST_ALTERED: 2020-10-24 11:01:07
LAST_EXECUTED: 2020-10-24 11:43:07
                   ...

 

选项2:使用Crontab作业自动管理分区

如果您无法使用MySQL事件调度器,则Crontab作业也是一个不错的选择。 使用命令sudo crontab -e打开crontab文件,在文件中的任何位置添加以下命令行,在每天03:30 AM对Zabbix数据库进行分区维护操作:

 

30 03 * * * /usr/bin/mysql -u 'zabbix' -p'zabbixDBpass' zabbix -e "CALL partition_maintenance_all('zabbix');" > /tmp/CronDBpartitiong.log 2>&1

 

译者点评: 这种使用账号密码明文的方式,其实不太安全,应该使用mysql_config_editor技术配置账号密码,避免在作业或脚本中使用数据库账号明文

 

30 03 * * * /usr/bin/mysql --login-path=zabbixdb zabbix -e "CALL partition_maintenance_all('zabbix');" > /tmp/CronDBpartitiong.log 2>&1

 

Crontab作业会执行脚本,通过存储过程维护分区(删除掉旧分区和创建新分区),并且将这些操作记录在日志““/tmp/CronDBpartitiong.log中。

因此,如果你没有耐心等待作业运行,也可以从终端运行这个命令

 mysql -u 'zabbix' -p'zabbixDBpass' zabbix -e "CALL partition_maintenance_all('zabbix');"

+-----------------------------------------------------------+
| msg |
+-----------------------------------------------------------+
| partition_create(zabbix,history,p201910150000,1571180400) |
+-----------------------------------------------------------+
+-----------------------------------------------------------+
 ...etc.

 

然后检查分区状态,如下所示:

mysql -u 'zabbix' -p'zabbixDBpass' zabbix -e "show create table history\G"
Table: history
Create Table: CREATE TABLE history (
itemid bigint(20) unsigned NOT NULL,
clock int(11) NOT NULL DEFAULT '0',
value double(16,4) NOT NULL DEFAULT '0.0000',
ns int(11) NOT NULL DEFAULT '0',
KEY history_1 (itemid,clock)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin
/*!50100 PARTITION BY RANGE (clock)
(PARTITION p201910140000 VALUES LESS THAN (1571094000) ENGINE = InnoDB,
PARTITION p201910150000 VALUES LESS THAN (1571180400) ENGINE = InnoDB,
PARTITION p201910160000 VALUES LESS THAN (1571266800) ENGINE = InnoDB) */

 

从上面输出结果中,你可以看到我们为history和trend表创建了3个分区。

译者点评:运行这些脚本,只会创建当前日期往后的三个分区,所以存在一些弊端,如果history等表中已经有大量历史数据,那么此时最靠前的一个分区将会变得无比巨大。

步骤4: 在Zabbix前端中配置Housekeeping

 

 

如果图片没有表述清楚,请按照以下步骤在Zabbix前端上配置Housekeeping

转到Housekeeping部分:管理——>常规(或者是一般的翻译)——>Housekeeping

取消历史记录和趋势部分下启用内部整理(Enable internal housekeeping)的复选标记;

历史记录和趋势部分的替代项目趋势期(Override item trend period)上打勾;

历史记录和趋势部分下,为趋势和历史记录定义数据存储期的天数(必须与数据库分区中配置的天数相同-历史记录应为7天,趋势图应为365天,如果您未更改 脚本中的默认设置);

单击更新按钮。

备注:5.2.6在界面上已经没有这个参数了

 

步骤5: 修改分区设置(修改history和trends表中的天数)

 

有时候,你可能最初为history表和trends表设置了太长的时间,因此磁盘空间消耗的太快了,或者相反的情况,你没有为history和trends表配置足够的天数,那该怎么办呢?

 

你无需再次运行该脚本,只需创建一个新过程并将其设置为从crontab作业中运行即可。

 

a) 创建新的分区存储过程

 连接到MySQL或MariaDB服务器:

mysql -u 'zabbix' -p'zabbixDBpass'

use zabbix;

创建一个新的存储过程,但是根据你的需要修改history和trends表保留的天数。我一般会为history表保留30天的数据,trends表保留400天的数据。

DELIMITER $$
CREATE PROCEDURE partition_maintenance_all_30and400(SCHEMA_NAME VARCHAR(32))
BEGIN
CALL partition_maintenance(SCHEMA_NAME, 'history', 30, 24, 3);
CALL partition_maintenance(SCHEMA_NAME, 'history_log', 30, 24, 3);
CALL partition_maintenance(SCHEMA_NAME, 'history_str', 30, 24, 3);
CALL partition_maintenance(SCHEMA_NAME, 'history_text', 30, 24, 3);
CALL partition_maintenance(SCHEMA_NAME, 'history_uint', 30, 24, 3);
CALL partition_maintenance(SCHEMA_NAME, 'trends', 400, 24, 3);
CALL partition_maintenance(SCHEMA_NAME, 'trends_uint', 400, 24, 3);
END$$
DELIMITER ;

b)更新MySQL事件调度器或Crontab作业

在前面部分(或者翻译为上一步),我们创建了存储过程,但是该存储过程还未调用过。现在,我们必须用的新的存储过程替换旧的存储过程,该过程将定期删除和添加分区。根据您在Zabbix实例上配置的内容,选择以下两个选项之一。

选项1:更新MySQL事件调度器

如果您按照本教程创建了事件调度程序,请使用此命令将旧存储过程替换为新的存储过程。

mysql -u 'zabbix' -p'zabbixDBpass' zabbix -e "ALTER EVENT zbx_partitioning ON SCHEDULE EVERY 3600 SECOND DO CALL partition_maintenance_all_30and400('zabbix');"

选项2:更新Crontab作业

用命令sudo crontab -e"打开crontab文件,注释掉旧的作业命令,并新增一个新的作业

 

# old procedure, still exists in the database so it can be used if needed
#
# 30 03 * * * /usr/bin/mysql -u 'zabbix' -p'zabbixDBpass' zabbix -e "CALL partition_maintenance_all('zabbix');" > /tmp/CronDBpartitiong.log 2>&1
30 03 * * * /usr/bin/mysql -u 'zabbix' -p'zabbixDBpass' zabbix -e "CALL partition_maintenance_all_30and400('zabbix');" > /tmp/CronDBpartitiong.log 2>&1

步骤6:关于Zabbix分区脚本的信息

本指南中使用的Zabbix分区SQL脚本包含以下分区过程:

 

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 ;
DELIMITER $$
CREATE PROCEDURE `partition_maintenance_all`(SCHEMA_NAME VARCHAR(32))
BEGIN
                CALL partition_maintenance(SCHEMA_NAME, 'history', 7, 24, 3);
                CALL partition_maintenance(SCHEMA_NAME, 'history_log', 7, 24, 3);
                CALL partition_maintenance(SCHEMA_NAME, 'history_str', 7, 24, 3);
                CALL partition_maintenance(SCHEMA_NAME, 'history_text', 7, 24, 3);
                CALL partition_maintenance(SCHEMA_NAME, 'history_uint', 7, 24, 3);
                CALL partition_maintenance(SCHEMA_NAME, 'trends', 365, 24, 3);
                CALL partition_maintenance(SCHEMA_NAME, 'trends_uint', 365, 24, 3);
END$$
DELIMITER ;

 

你还需要更多信息吗? 请观看有关Zabbix的MySQL数据库分区的视频。

 

# 分区和存储的常用命令

 

# 查看表信息 | 查看表分区
show create table history\G

# 查看生效中的存储过程 | 查看某个存储过程的执行记录 | 某个event的信息
SELECT * FROM INFORMATION_SCHEMA.events\G

# 查看所有的存储过程
show procedure status like 'partition_maintenance%' \G

# 查看某个存储过程的创建详情
show create procedure partition_maintenance_all_30and365and24hand4p\G

# event使用新的存储过程,每个小时执行一次
ALTER EVENT zbx_partitioning ON SCHEDULE EVERY 3600 SECOND DO CALL partition_maintenance_all_30and365and24hand4p('zabbix');

# 手动执行某个存储过程
CALL partition_maintenance_all('zabbix');

# 创建一个event,每个小时执行一次存储过程
CREATE EVENT zbx_partitioning ON SCHEDULE EVERY 3600 SECOND DO CALL partition_maintenance_all_30and365and24hand4p('zabbix'); 

# 删除一个存储过程
DROP PROCEDURE ShowStuScore;

 

# 针对有数据的(200G-300G的分区实践过程)

涉及到的表:
history
history_log
history_str
history_text
history_uint
trends
trends_uint

# 操作前:创建新表
create table history_new like history;
create table history_log_new like history_log;
create table history_str_new like history_str;
create table history_text_new like history_text;
create table history_uint_new like history_uint;
create table trends_new like trends;
create table trends_uint_new like trends_uint;

操作前
停用所有的报警媒介
http://zabbix.zabbix.com/zabbix.php?action=mediatype.list
停用zabbix server机php的监控

1、停止 zabbix_server (防止有新的数据写入)
ps aux |grep zabbix_server |grep -v grep |awk '{print $2}'|xargs kill -9

2、表备份:rename (例如:rename history history_bak)
rename table history to history_bak;
rename table history_log to history_log_bak;
rename table history_str to history_str_bak;
rename table history_text to history_text_bak;
rename table history_uint to history_uint_bak;
rename table trends to trends_bak;
rename table trends_uint to trends_uint_bak;

3、使用新表:rename(例如:rename history_new history)
rename table history_new to history;
rename table history_log_new to history_log;
rename table history_str_new to history_str;
rename table history_text_new to history_text;
rename table history_uint_new to history_uint;
rename table trends_new to trends;
rename table trends_uint_new to trends_uint;
4、手动执行分区存储过程,利用定时任务执行脚本的方式(历史数据30天,趋势数据90天)
vim /data/scripts/zbx_db_partitiong.sql
source /data/scripts/zbx_db_partitiong.sql ;

DELIMITER $$
CREATE PROCEDURE partition_maintenance_all_30and365and24hand4p(SCHEMA_NAME VARCHAR(32))
BEGIN
CALL partition_maintenance(SCHEMA_NAME, 'history', 30, 24, 4);
CALL partition_maintenance(SCHEMA_NAME, 'history_log', 30, 24, 4);
CALL partition_maintenance(SCHEMA_NAME, 'history_str', 30, 24, 4);
CALL partition_maintenance(SCHEMA_NAME, 'history_text', 30, 24, 4);
CALL partition_maintenance(SCHEMA_NAME, 'history_uint', 30, 24, 4);
CALL partition_maintenance(SCHEMA_NAME, 'trends', 365, 24, 4);
CALL partition_maintenance(SCHEMA_NAME, 'trends_uint', 365, 24, 4);
END$$
DELIMITER ;

SELECT * FROM INFORMATION_SCHEMA.events\G
CALL partition_maintenance_all_30and365and24hand4p('zabbix');
CALL partition_maintenance('zabbix');
show create table history\G

5、启动zabbix_server:验证{通过页面和zabbix_server.log检查新的监控数据入库是否正常,是否正常生成告警并发送}
6、添加event自动执行存储过程
CREATE EVENT zbx_partitioning ON SCHEDULE EVERY 3600 SECOND DO CALL partition_maintenance_all('zabbix');

回滚
1、新表备份
rename table history_log to history_log_new_bak;
rename table history_str to history_str_new_bak;
rename table history_text to history_text_new_bak;
rename table history_uint to history_uint_new_bak;
rename table trends to trends_new_bak;
rename table trends_uint to trends_uint_new_bak;
2、数据表恢复
rename table history_log_bak to history_log;
rename table history_str_bak to history_str;
rename table history_text_bak to history_text;
rename table history_uint_bak to history_uint;
rename table trends_bak to trends;
rename table trends_uint_bak to trends_uint;
3、删除 存储过程,关闭 event_scheduler

DROP PROCEDURE partition_maintenance_all_30and365and24hand4p;
DROP PROCEDURE partition_maintenance;
SELECT * FROM information_schema.routines where routine_name='partition_maintenance_all_30and365and24hand4p'\G
SELECT * FROM information_schema.routines where routine_name='partition_maintenance'\G

4、启动zabbix_server:验证{通过页面和zabbix_server.log检查新的监控数据入库是否正常,是否正常生成告警并发送}
http://zabbix.zabbix.com/zabbix.php?action=mediatype.list
启用zabbix server机php的监控

 

posted @ 2021-07-30 11:08  Hello_worlds  阅读(388)  评论(0编辑  收藏  举报