zabbix(proxy_history表）分区+python脚本

先对proxy_history进行基础分区

##删除表
drop table `proxy_history`;
##重新创建表，日期记得修改
CREATE TABLE `proxy_history` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `itemid` bigint unsigned NOT NULL,
  `clock` int NOT NULL DEFAULT '0',
  `timestamp` int NOT NULL DEFAULT '0',
  `source` varchar(64) COLLATE utf8mb4_bin NOT NULL DEFAULT '',
  `severity` int NOT NULL DEFAULT '0',
  `value` longtext COLLATE utf8mb4_bin NOT NULL,
  `logeventid` int NOT NULL DEFAULT '0',
  `ns` int NOT NULL DEFAULT '0',
  `state` int NOT NULL DEFAULT '0',
  `lastlogsize` bigint unsigned NOT NULL DEFAULT '0',
  `mtime` int NOT NULL DEFAULT '0',
  `flags` int NOT NULL DEFAULT '0',
  `write_clock` int NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`,`clock`),
  KEY `proxy_history_1` (`clock`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
PARTITION BY RANGE ( clock)
(PARTITION p2023_03_12 VALUES LESS THAN (UNIX_TIMESTAMP("2023-03-13 00:00:00")) ENGINE = InnoDB,
PARTITION p2023_03_14 VALUES LESS THAN (UNIX_TIMESTAMP("2023-03-15 00:00:00")) ENGINE = InnoDB)

python

安装

yum -y install python3

PIP模块

pip3 install python-dateutil
pip3 pymysql

python脚本

import pymysql
import datetime
from dateutil.relativedelta import relativedelta

### mysql信息，请更换host、user、password、db
mysql_host = '1.1.1.1'
mysql_user = 'zabbix'
mysql_password = 'password'
mysql_db = 'zabbix_proxy'

### 要分区的表信息
partition_table_name = 'proxy_history'

### 连接数据库
mysqlcon = pymysql.connect(host=mysql_host,user=mysql_user,password=mysql_password,port=3306,db=mysql_db)
cursor = mysqlcon.cursor()

### 获取当前日期
current_date = datetime.datetime.now()

### 获取要保留的分区最大日期
last_month_date = current_date - relativedelta(months=1)

### 计算要保留的分区名称
last_month_partition_name = "p" + last_month_date.strftime("%Y_%m_%d")

### 添加分区的函数
def add_partition(table_name, partition_name, partition_date):
    add_partition_query = """
        ALTER TABLE {} ADD PARTITION ( PARTITION {} VALUES LESS THAN (UNIX_TIMESTAMP('{}')) ENGINE = InnoDB )
    """.format(table_name, partition_name, partition_date)

    cursor.execute(add_partition_query)

### 删除分区的函数
def drop_partition(table_name, partition_name):
    drop_partition_query = """
        ALTER TABLE {} DROP PARTITION {}
    """.format(table_name, partition_name)

    cursor.execute(drop_partition_query)

### 获取表分区的函数
def get_partitions(table_name):
    cursor.execute('''SELECT partition_name FROM information_schema.partitions WHERE table_schema = '{}' AND table_name = '{}' '''.format(mysql_db, table_name))

    partitions = [partition[0] for partition in cursor.fetchall()]
    return partitions

### 获取当前要分区的表的分区信息
partitions = get_partitions(partition_table_name)

### 添加分区，并且每执行一次，创建30天
for daytime in range(1,31):
	### get partiton date
	max_partition_date = current_date + relativedelta(days=daytime)
	### get partition storage date
	partition_date_tmp = max_partition_date - relativedelta(days=1)
	### get partition name
	partition_name = "p" + partition_date_tmp.strftime("%Y_%m_%d")
	### if partition is not exist , add it
	if partition_name not in partitions:
		### add partition
		add_partition(partition_table_name, partition_name, max_partition_date.strftime("%Y-%m-%d"))

### 删除冗余分区，保留一个月分区数据
for partition in partitions:
    if partition < last_month_partition_name:
        drop_partition(partition_table_name, partition)

### 数据提交，游标关闭，数据库连接关闭
mysqlcon.commit()
cursor.close()
mysqlcon.close()

创建定时任务

##根据自己的时间需求进行修改
0 1 28 * * /usr/bin/python3 /apps/update_partition.py