背景
将Zabbix的数据库迁移至OceanBase后，以其中的几个大表作为案例，本文将分享如何利用ODC（OceanBase 开发者工具），来进行自动管理OB数据库中的表分区的方案。
因为原始表里已经有大量的数据，如果贸然对原始表去创建表分区的话，可能会造成长时间的缩影，所以，当前的整体思路是：
- 为每个要操作的表创建一个 [tb_name]---->临时表[tb_name_tmp]，表结构语原始表保持一致；
- 通过代码为每个临时表 [tb_name_tmp]生成表分区创建语句；
- 为每个临时表 [tb_name_tmp]创建表分区；
- 将操作日前一天的数据通过 insert into select 写入到临时表 [tb_name_tmp]中，并校验
- 停止应用，并将剩余数据写入到临时表 [tb_name_tmp]中
- 将原始表[tb_name]重名为[tb_name_bak]，并将临时表 [tb_name_tmp]重命名为[tb_name]
- 启动服务，并检查服务是否正常，如果服务异常，检查数据是否正确；
- 如果服务正常，在odc中为相关表创建表分区维护计划；
- 一段时间后，将原始表[tb_name_bak]进行归档或者移除；
操作对象
首先，明确需要操作的表对象是哪些：
| 表名 | 作用 | 保留时长 | 清理间隔 | 数据类型 |
|---|---|---|---|---|
| history | 存储原始的历史数据 | 90天 | 每天 | 数字（浮点数） |
| history_uint | 存储原始的历史数据 | 90天 | 每天 | 数字（无符号） |
| history_str | 存储原始的短字符串数据 | 90天 | 每天 | 字符型 |
| history_text | 存储原始的长字符串数据 | 90天 | 每天 | 文本 |
| history_log | 存储原始的日志字符串数据 | 90天 | 每天 | 日志 |
| trends | 存储每小时统计数据（趋势） | 12个月 | 每月 | 数字（浮点数） |
| trends_uint | 保持每小时统计数据（趋势） | 12个月 | 每月 | 数字（无符号） |
| auditlog | 审计日志表 |
准备操作
查找各个表中的最早记录
如果记录最早时间早于保留时间，那就从保留日开始导入，如果晚于保留开始日期，则用最早记录的日期开始建立分区
SELECT FROM_UNIXTIME(MIN(clock)) FROM history;SELECT FROM_UNIXTIME(MIN(clock)) FROM history_uint;SELECT FROM_UNIXTIME(MIN(clock)) FROM history_str;SELECT FROM_UNIXTIME(MIN(clock)) FROM history_text;SELECT FROM_UNIXTIME(MIN(clock)) FROM history_log;SELECT FROM_UNIXTIME(MIN(clock)) FROM trends;SELECT FROM_UNIXTIME(MIN(clock)) FROM trends_uint;创建分区表结构
SHOW CREATE TABLE history;
SHOW CREATE TABLE history_uint;
SHOW CREATE TABLE history_str;
SHOW CREATE TABLE history_text;
SHOW CREATE TABLE history_log;
SHOW CREATE TABLE trends;
SHOW CREATE TABLE trends_uint;使用以下脚本来生成表分区
使用python生成创建表分区的SQL语句
by day
# -*- coding: utf-8 -*-
# @Time : 2023/6/8 10:17
# @Author : YoKing Ma
# @FileName : create_tp_day.py
# @Software : PyCharmimport datetimetb_name = input("Please input data table name:")print(f"""
Please get the first day, use like this command:
SELECT FROM_UNIXTIME(MIN(clock)) FROM {tb_name}
""")
start_day = input("Please input the first day (2023-03-03): ")# tb_name="history"
# start_day="2023-03-03"try:first_date = datetime.datetime.strptime(start_day, "%Y-%m-%d")print(f"Your input date is [{first_date}]")
except ValueError:print("date format is error!")pt_create=[]period = 0
while period <= 90:curr_date = first_date + datetime.timedelta(days=period)curr_date_title = curr_date.strftime("%Y_%m_%d")tommow_date = curr_date + datetime.timedelta(days=1)period += 1sql_str=f'PARTITION p{curr_date_title} VALUES LESS THAN (UNIX_TIMESTAMP("{tommow_date}"))'pt_create.append(sql_str)pt_sql_str = f"""ALTER TABLE {tb_name} PARTITION BY RANGE (clock)
("""
for sql in pt_create:pt_sql_str += '\n'pt_sql_str += sqlpt_sql_str += ','pt_sql_str += "\b\n);"print(pt_sql_str)by mouth
# -*- coding: utf-8 -*-
# @Time : 2023/6/8 11:27
# @Author : YoKing Ma
# @FileName : create_tp_month.py
# @Software : PyCharm## pip install python-dateutilimport datetime
from dateutil.relativedelta import relativedeltatb_name = input("Please input data table name:")print(f"""
Please get the first day, use like this command:
SELECT FROM_UNIXTIME(MIN(clock)) FROM {tb_name}
""")
start_month = input("Please input the first day (2023-03): ")# tb_name="history"
# start_month="2022-02"try:tmp_date = start_month.split('-')fyear = int(tmp_date[0])fmonth = int(tmp_date[1])fdate = datetime.datetime(fyear, fmonth, 1)
except ValueError:print("date format is error!")pt_create=[]period = 12
cdate = fdate
for i in range(0,13):title = f"{cdate.year}_{cdate.month}"ndate = cdate + relativedelta(months=+1)sql = f'PARTITION p{title} VALUES LESS THAN (UNIX_TIMESTAMP("{ndate}"))'pt_create.append(sql)cdate = ndatept_sql_str = f"""ALTER TABLE {tb_name} PARTITION BY RANGE (clock)
("""
for sql in pt_create:pt_sql_str += '\n'pt_sql_str += sqlpt_sql_str += ','pt_sql_str += "\b\n);"print(pt_sql_str)案例演示
如生成了 `history` 表的临时表 `history_tmp`
CREATE TABLE `history_tmp` (`itemid` bigint(20) unsigned NOT NULL,`clock` int(11) NOT NULL DEFAULT '0',`value` double NOT NULL DEFAULT '0',`ns` int(11) NOT NULL DEFAULT '0',PRIMARY KEY (`itemid`, `clock`, `ns`)
) DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_bin;用上面的脚本，为`history_tmp`表创建表分区语句
ALTER TABLE history_tmp PARTITION BY RANGE ( clock)
(
PARTITION p2024_04_04 VALUES LESS THAN (UNIX_TIMESTAMP("2024-04-05 00:00:00")) ENGINE = InnoDB,
PARTITION p2024_04_05 VALUES LESS THAN (UNIX_TIMESTAMP("2024-04-06 00:00:00")) ENGINE = InnoDB,
……
PARTITION p2024_07_01 VALUES LESS THAN (UNIX_TIMESTAMP("2024-07-02 00:00:00")) ENGINE = InnoDB,
PARTITION p2024_07_02 VALUES LESS THAN (UNIX_TIMESTAMP("2024-07-03 00:00:00")) ENGINE = InnoDB,
PARTITION p2024_07_03 VALUES LESS THAN (UNIX_TIMESTAMP("2024-07-04 00:00:00")) ENGINE = InnoDB
);迁移数据
分2次迁移数据，这样可以减少业务停机的时长
数据导入
第一次迁移可以把变更日前的数据全部导入，比如变更日为2024年的7月3日，那将2024年7月3日 00:00:00之前90天的数据全部导入到分区表中
INSERT /*+ append enable_parallel_dml parallel(8) */ INTO `history_tmp`(`itemid`, `clock`, `value`, `ns`)
SELECT `itemid`, `clock`, `value`, `ns`
FROM `history`
WHERE `clock` >= 1712160000 and `clock` < 1719936000;数据校验
对比临时表中的最早和最晚的记录是否和原表一致，并检查行数是否一致。
因为zabbix有housekeeper服务，这个服务会不定时的删除表中过期的数据，所以，在导入数据的过程中会造成数据不一致的情况。可以先把zabbix的管家服务停止；
SELECT FROM_UNIXTIME(MIN(clock)) FROM history_tmp;
SELECT FROM_UNIXTIME(MAX(clock)) FROM history_tmp;
SELECT COUNT(1) FROM history WHERE `clock` >= 1712160000 and `clock` < 1719936000; -- 402789803
SELECT COUNT(1) FROM history_tmp; -- 402789803校验通过后，可以停止zabbix服务，并进行剩余数据的导入。操作与上文类似就不再赘述。
修改表名
rename table history to history_bak;
rename table history_tmp to history;修改完表名后，就启动zabbix服务，看服务日志是否有报错，报错的话需要解决报错，直至服务正常。
在ODC中创建表分区管理计划
为已经分区表创建分区计划（在odc中每个数据库只允许有1个分区计划，新的分区计划生效后，旧的就失效了）
我这边history相关表是每日一个表分区，trends相关表是1个月做一次表分区的维护。
所以【分区策略】我就以2中类型展示：【日】和【月】
分区策略--日
这里产生的SQL语句类似于：
ALTER TABLE `zabbix`.`history` DROP PARTITION (`p2024_04_04`);ALTER TABLE `zabbix`.`history` ADD PARTITION (PARTITION `p2024_07_04` VALUES LESS THAN (1720108800));策略中用到的表达式
分区创建表达式：unix_timestamp(DATE(NOW() - INTERVAL DAY(NOW())
表分区命名表达式：DATE_FORMAT(DATE(NOW() - INTERVAL DAY(NOW()) DAY + INTERVAL 1 DAY + INTERVAL 1 MONTH), 'p%Y_%m')分区策略-月
ALTER TABLE `zabbix`.`trends` DROP PARTITION (`p2023_7`);ALTER TABLE `zabbix`.`trends` ADD PARTITION (PARTITION `p2024_08` VALUES LESS THAN (1725120000));策略中用到的表达式
分区创建表达式：unix_timestamp(DATE(NOW() - INTERVAL DAY(NOW())
表分区命名表达式：DATE_FORMAT(DATE(NOW() - INTERVAL DAY(NOW()) DAY + INTERVAL 1 DAY + INTERVAL 1 MONTH), 'p%Y_%m')
