由于表分区用完了,程序开始报错
解决方法:增加表分区
ALTER TABLE customeventlabelsum ADD PARTITION (
......
PARTITION kvs_s20241209 VALUES LESS THAN (20241209),
PARTITION kvs_s20241216 VALUES LESS THAN (20241216),
PARTITION kvs_s20241223 VALUES LESS THAN (20241223),
PARTITION kvs_s20241230 VALUES LESS THAN (20241230)
);
mysql分区表在定义分区时,有时会定义一个maxvalue的分区,相当于一个默认分区,保证所有数据都能被分区表存储。
对于这种情况需要使用一下sql来添加分区
把MAXVALUE分区拆分为多个分区
alter table enterprise.userprofile_bak1 REORGANIZE PARTITION pmax INTO(
PARTITION p101 VALUES LESS THAN (1646064000) ENGINE = InnoDB,
PARTITION pmax VALUES LESS THAN (MAXVALUE) ENGINE = InnoDB
);
执行后:
查看每个分区的记录行数和占用大小
SELECT PARTITION_NAME,TABLE_ROWS,DATA_LENGTH FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME = 'userprofile_bak1';
清理分区数据为空,保留分区不删除,仅仅是清理数据,命令如下
alter table bm_scenes_data_reminder truncate partition p20210104;
删除分区
alter table bm_scenes_data_reminder drop partition p20210104;
查看所有表的可用分区数量
SELECT
table_name,
count(*)
FROM
information_schema.`PARTITIONS`
WHERE
table_name NOT IN ( 'information_schema', 'performance_schema', 'sys' )
AND partition_name IS NOT NULL
AND partition_method IS NOT NULL
AND table_rows = 0
GROUP BY
table_name;
系统有操作系统表sys_log,实现每天删除90天前的分区并同时建一个4天后的分区(即每天将4天后的分区创建表),步骤如下:
CREATE TABLE `sys_log` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`begin_time` datetime DEFAULT NULL COMMENT '开始时间',
`end_time` datetime DEFAULT NULL COMMENT '结束时间',
`spend_mills` int(11) DEFAULT NULL COMMENT '运行时长,单位ms',
`username` varchar(100) DEFAULT NULL COMMENT '用户id',
`log_status` int(11) NOT NULL DEFAULT '0' COMMENT '运行状态,[0]成功[1]失败',
`code` int(11) NOT NULL DEFAULT '0' COMMENT '错误码',
`remote_addr` varchar(50) DEFAULT '' COMMENT '远程地址',
`request_uri` varchar(255) DEFAULT NULL COMMENT '请求路径',
`user_agent` text COMMENT '用户代理',
`req_data` text NOT NULL COMMENT '请求参数',
`resp_data` longtext NOT NULL COMMENT '返回结果',
PRIMARY KEY (`id`) USING BTREE,
KEY `idx_log_begintime` (`begin_time`) USING BTREE COMMENT '系统日志的beginTime字段索引'
) ENGINE=Innodb DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC
ALTER TABLE `sys_log` DROP PRIMARY KEY, ADD PRIMARY KEY(`id`,`begin_time`);
alter table sys_log partition by RANGE (to_days(begin_time)) (
PARTITION p20210816 VALUES LESS THAN (738383),
PARTITION future VALUES LESS THAN MAXVALUE
);
ALTER TABLE sys_log drop PARTITION future;
ALTER TABLE sys_log ADD PARTITION (PARTITION p20210817 VALUES LESS THAN (738384) ENGINE = InnoDB);
ALTER TABLE sys_log ADD PARTITION (PARTITION p20210818 VALUES LESS THAN (738385) ENGINE = InnoDB);
ALTER TABLE sys_log ADD PARTITION (PARTITION p20210819 VALUES LESS THAN (738386) ENGINE = InnoDB);
ALTER TABLE sys_log ADD PARTITION (PARTITION future VALUES LESS THAN MAXVALUE ENGINE = InnoDB);
30 4 * * * /bin/python /home/testuser/SyslogPartitionClear.py >/dev/null 2>&1
脚本:
#!/usr/bin/python
# -*- coding: utf8 -*-
import mysql.connector
import datetime
class SyslogPartitionClear(object):
'SyslogPartitionClear clearData'
def clearData(self,deleteTime,afterTime):
db = None
try:
db = mysql.connector.connect(host='127.0.0.1',port=3306,user='webuser', password='123456', database='icnoclx_webdb',cha
rset="utf8", use_unicode=True)
cursor = db.cursor()
cursor.execute("select to_days(DATE_ADD(NOW(),INTERVAL 4 DAY))")
results = cursor.fetchall()
##
partitionIntegerValue = 0;
for row in results:
partitionIntegerValue = row[0]
try:
print "ALTER TABLE sys_log drop PARTITION p"+deleteTime+";"
cursor.execute("ALTER TABLE sys_log drop PARTITION p"+deleteTime+";")
except Exception, e:
print e
try:
print "ALTER TABLE sys_log drop PARTITION future;"
cursor.execute("ALTER TABLE sys_log drop PARTITION future;")
except Exception, e:
print e
try:
print "ALTER TABLE sys_log ADD PARTITION (PARTITION p"+afterTime+" VALUES LESS THAN ("+str(partitionIntegerValue)+"
) ENGINE = InnoDB);"
cursor.execute("ALTER TABLE sys_log ADD PARTITION (PARTITION p"+afterTime+" VALUES LESS THAN ("+str(partitionIntege
rValue)+") ENGINE = InnoDB);")
except Exception, e:
print e
try:
print "ALTER TABLE sys_log ADD PARTITION (PARTITION future VALUES LESS THAN (MAXVALUE) ENGINE = InnoDB);"
cursor.execute("ALTER TABLE sys_log ADD PARTITION (PARTITION future VALUES LESS THAN (MAXVALUE) ENGINE = InnoDB);")
except Exception, e:
print e
db.commit()
except Exception, e:
db.rollback()
print e
finally:
db.close()
if __name__ == '__main__':
now=datetime.datetime.now()
delta=datetime.timedelta(days=-90)
before_day = now + delta
deleteTime = before_day.strftime('%Y%m%d')
after_day = now + datetime.timedelta(days=4)
afterTime = after_day.strftime('%Y%m%d')
obj = SyslogPartitionClear()
obj.clearData(deleteTime,afterTime)
页面更新:2024-04-28
本站资料均由网友自行发布提供,仅用于学习交流。如有版权问题,请与我联系,QQ:4156828
© CopyRight 2020-2024 All Rights Reserved. Powered By 71396.com 闽ICP备11008920号-4
闽公网安备35020302034903号