zabbix7.0.4 + oceanbase mysql模式_zabbix oceanbase-优快云博客

本文链接：https://blog.youkuaiyun.com/wuqiayan/article/details/143156852

1.oceanbase集群部署

参考官方部署(略)

建立zabbix租户

2.zabbix 7.0.4 采用docker-compose方式部署

version: '3.0'
services:

 xxxzbx:
   hostname: xxxzbx
   image: zabbix/zabbix-server-mysql:7.0-alpine-latest
   container_name: xxxzbx
   ports:
     - 10051:10051
   networks:
     - zbxnet
   restart: always
   environment:
     - DB_SERVER_HOST=10.x.x.x
     - DB_SERVER_PORT=2883
     - MYSQL_DATABASE=zabbix
     - MYSQL_USER=root@zabbix
     - MYSQL_PASSWORD={替换密码}
     - ZBX_SERVER_NAME=xxxzbx
     - ZBX_LISTENIP=0.0.0.0
     - ZBX_LISTENPORT=10051
     - ZBX_ENABLE_SNMP_TRAPS=false
     - ZBX_AUTONODEADDRESS=xxxzbx
     - ZBX_NODEADDRESSPORT=10051
     - ZBX_NODEADDRESS=xxxzbx
   volumes:
     - /etc/localtime:/etc/localtime:ro
     - /etc/timezone:/etc/timezone:ro

 zbx-web:
   hostname: zbx-web
   image: zabbix/zabbix-web-nginx-mysql:7.0-alpine-latest
   container_name: zbx-web
   ports:
     - 80:8080
     - 443:8443
   networks:
     - zbxnet
   restart: always
   environment:
     - DB_SERVER_HOST=10.x.x.x
     - DB_SERVER_PORT=2883
     - MYSQL_DATABASE=zabbix
     - MYSQL_USER=root@zabbix
     - MYSQL_PASSWORD={替换密码}
     - ZBX_SERVER_NAME=xxxzbx
     - TZ=Asia/Shanghai
   links:
     - xxxzbx

 agent:
    hostname: xxxzbx-agent
    image: zabbix/zabbix-agent2:7.0-alpine-latest
    container_name: xxxzbx-agent
    ports:
      - 11050:10050
    networks:
      - zbxnet
    restart: always
    #environment:
    #  - ZBX_SERVER_ACTIVE=  #禁用主动模式
    #  - ZBX_SERVER_HOST=xxxzbx,192.168.240.1  # Zabbix服务器的IP地址
    #  - ZBX_SERVER_PORT=10051  # Zabbix服务器的端口，默认是10051
    #  - ZBX_HOSTNAME=xxxzbx  # 主机名
    volumes:
      - /etc/localtime:/etc/localtime
      - ./zabbix_agent2.conf:/etc/zabbix/zabbix_agent2.conf:ro
    links:
      - xxxzbx:xxxzbx
    depends_on:
      - zbx-web


networks:
  zbxnet:
    external: true

3.针对历史表以及趋势表的表分区处理

>类似mysql的zabbix历史表处理过程

3.1 zabbix库相关的存储过程

CREATE DEFINER = `root`@`%` PROCEDURE `zabbix`.`partition_create`(
    IN SCHEMANAME varchar(64),
    IN TABLENAME varchar(64),
    IN PARTITIONNAME varchar(64),
    IN CLOCK int
)
    READS SQL DATA
BEGIN
    -- 声明变量用于存储查询结果
    DECLARE RETROWS INT DEFAULT 0;

    -- 检查分区是否已存在
    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
        -- 构造并执行创建分区的SQL语句
        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

CREATE DEFINER = `root`@`%` PROCEDURE `zabbix`.`partition_drop`(
    IN SCHEMANAME VARCHAR(64),
    IN TABLENAME VARCHAR(64),
    IN DELETE_BELOW_PARTITION_DATE BIGINT
)
    READS SQL DATA
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(64); -- 增加长度以适应分区名

    /*
       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, 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 = "", 
                                  CONCAT("`", drop_part_name, "`"), 
                                  CONCAT(@drop_partitions, ", `", drop_part_name, "`"));
    END LOOP;
    CLOSE myCursor;

    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

CREATE DEFINER = `root`@`%` PROCEDURE `zabbix`.`partition_verify`(
    IN schema_name VARCHAR(64), 
    IN table_name VARCHAR(64), 
    IN hourly_interval INT(11)
)
    READS SQL DATA
BEGIN
    DECLARE partition_name VARCHAR(16);
    DECLARE ret_rows INT;
    DECLARE future_timestamp TIMESTAMP;

    /*
     * Check if any partitions exist for the given schema_name.table_name.
     */
    SELECT COUNT(1) INTO ret_rows
    FROM information_schema.partitions
    WHERE table_schema = schema_name AND table_name = table_name AND partition_name IS NULL;

    /*
     * If partitions do not exist, go ahead and partition the table
     */
    IF ret_rows = 1 THEN
        /*
         * Take the current date at 00:00:00 and add hourly_interval 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 (i.e., 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, hourly_interval, CONCAT(CURDATE(), ' 00:00:00'));
        SET partition_name = CONCAT('p', DATE_FORMAT(future_timestamp, '%Y%m%d%H00'));

        -- Create the partitioning query
        SET @__partition_sql = CONCAT('ALTER TABLE ', schema_name, '.', table_name, ' PARTITION BY RANGE(`clock`) (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

CREATE DEFINER = `root`@`%` PROCEDURE `zabbix`.`partition_maintenance`(
    IN schema_name VARCHAR(32), 
    IN table_name VARCHAR(32), 
    IN keep_data_days INT, 
    IN hourly_interval INT, 
    IN 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;
    DECLARE __interval INT DEFAULT 1;

    CALL partition_verify(schema_name, table_name, hourly_interval);
    SET cur_time = UNIX_TIMESTAMP(DATE_FORMAT(NOW(), '%Y-%m-%d 00:00:00'));

    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

CREATE DEFINER = `root`@`%` PROCEDURE `zabbix`.`partition_maintenance_all`(IN schema_name VARCHAR(32))
BEGIN
    -- 对history表执行分区维护
    CALL partition_maintenance(schema_name, 'history', 30, 24, 14);
       -- 对history_bin表执行分区维护
    CALL partition_maintenance(schema_name, 'history_bin', 30, 24, 14);
    -- 对history_log表执行分区维护
    CALL partition_maintenance(schema_name, 'history_log', 30, 24, 14);
    -- 对history_str表执行分区维护
    CALL partition_maintenance(schema_name, 'history_str', 30, 24, 14);
    -- 对history_text表执行分区维护
    CALL partition_maintenance(schema_name, 'history_text', 30, 24, 14);
    -- 对history_uint表执行分区维护
    CALL partition_maintenance(schema_name, 'history_uint', 30, 24, 14);
    -- 对trends表执行分区维护
    CALL partition_maintenance(schema_name, 'trends', 400, 24, 14);
    -- 对trends_uint表执行分区维护
    CALL partition_maintenance(schema_name, 'trends_uint', 400, 24, 14);
END

> 采用nifi 执行每天执行分区维护

5. 分区表trends_uint DDL

-- zabbix.trends_uint definition

CREATE TABLE `trends_uint` (
  `itemid` bigint(20) unsigned NOT NULL,
  `clock` int(11) NOT NULL DEFAULT '0',
  `num` int(11) NOT NULL DEFAULT '0',
  `value_min` bigint(20) unsigned NOT NULL DEFAULT '0',
  `value_avg` bigint(20) unsigned NOT NULL DEFAULT '0',
  `value_max` bigint(20) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`itemid`, `clock`)
) DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_bin ROW_FORMAT = DYNAMIC COMPRESSION = 'zstd_1.3.8' REPLICA_NUM = 3 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0
 partition by range(`clock`)
(partition `p202410100000` values less than (1728604800),
partition `p202410110000` values less than (1728691200),
partition `p202410120000` values less than (1728777600),
partition `p202410130000` values less than (1728864000),
partition `p202410140000` values less than (1728950400),
partition `p202410150000` values less than (1729036800),
partition `p202410160000` values less than (1729094400),
partition `p202410170000` values less than (1729180800),
partition `p202410180000` values less than (1729267200),
partition `p202410190000` values less than (1729353600),
partition `p202410200000` values less than (1729440000),
partition `p202410210000` values less than (1729526400),
partition `p202410220000` values less than (1729612800),
partition `p202410230000` values less than (1729699200),
partition `p202410240000` values less than (1729785600),
partition `p202410250000` values less than (1729872000),
partition `p202410260000` values less than (1729958400),
partition `p202410270000` values less than (1730044800),
partition `p202410280000` values less than (1730131200),
partition `p202410290000` values less than (1730217600),
partition `p202410300000` values less than (1730304000),
partition `p202410310000` values less than (1730390400),
partition `p202411010000` values less than (1730476800),
partition `p202411020000` values less than (1730563200),
partition `p202411030000` values less than (1730649600),
partition `p202411040000` values less than (1730736000));

附: oceanbase版本

SHOW VARIABLES LIKE '%version%';

group_replication_allow_local_lower_version_join    OFF
innodb_version    5.7.38
ob_compatibility_version    4.3.2.0
ob_last_schema_version    0
ob_security_version    4.3.2.0
protocol_version    10
slave_type_conversions    ALL_LOSSY
tls_version    
version    5.7.25-OceanBase_CE-v4.3.2.0
version_comment    OceanBase_CE 4.3.2.0 (r1000000920240729127f239820d2067e8d0983ce6d0f13b5e0491571e3) (Built Jul 29 2024 22:56:28)
version_compile_machine    
version_compile_os