Mysql删除分区，增加分区，分区数据清理 |
您所在的位置：网站首页 › linux清空分区数据 › Mysql删除分区，增加分区，分区数据清理 |
Mysql删除分区，增加分区，分区数据清理
|
Mysql删除分区，增加分区，分区数据清理
最近线上的分区表占用空间较大，需要进行分区数据的删除，记录如下，顺便把分区维护的其他命令也一并记录，方便后续进行查询使用。 之前建表的语句大概如下，省掉了其他一些字段 CREATE TABLE `bm_scenes_data_reminder` ( `id` bigint(11) NOT NULL AUTO_INCREMENT, `trace_id` varchar(50) DEFAULT NULL COMMENT '数据traceId', `bc_url` varchar(100) DEFAULT NULL COMMENT '数据bc.url', `data_source` varchar(50) DEFAULT NULL COMMENT '数据来源（topic）', `bz_resultCode` varchar(10) DEFAULT NULL COMMENT '错误码', `start_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '开始时间', PRIMARY KEY (`id`,`start_time`) USING BTREE ) ENGINE=InnoDB AUTO_INCREMENT=19771343 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC /*!50100 PARTITION BY RANGE (to_days(start_time)) (PARTITION p20210103 VALUES LESS THAN (738158) ENGINE = InnoDB, PARTITION p20210104 VALUES LESS THAN (738159) ENGINE = InnoDB, PARTITION p20210105 VALUES LESS THAN (738160) ENGINE = InnoDB, PARTITION p20210106 VALUES LESS THAN (738161) ENGINE = InnoDB, PARTITION p20210107 VALUES LESS THAN (738162) ENGINE = InnoDB, PARTITION p20210108 VALUES LESS THAN (738163) ENGINE = InnoDB, PARTITION p20210109 VALUES LESS THAN (738164) ENGINE = InnoDB, PARTITION p20210110 VALUES LESS THAN (738165) ENGINE = InnoDB, PARTITION future VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */ 查看每个分区的记录行数和占用大小 SELECT PARTITION_NAME,TABLE_ROWS,DATA_LENGTH FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME = 'bm_scenes_data_reminder'; +----------------+------------+-------------+ | PARTITION_NAME | TABLE_ROWS | DATA_LENGTH | +----------------+------------+-------------+ | p20210103 | 0 | 16384 | | p20210104 | 0 | 16384 | | p20210105 | 0 | 16384 | | p20210106 | 0 | 16384 | | p20210107 | 0 | 16384 | | p20210108 | 0 | 16384 | | p20210109 | 0 | 16384 | | p20210110 | 0 | 16384 | | future | 0 | 16384 | +----------------+------------+-------------+ 9 rows in set (0.00 sec) 清理分区数据为空，保留分区不删除，仅仅是清理数据，命令如下 alter table bm_scenes_data_reminder truncate partition p20210104; 删除分区 alter table bm_scenes_data_reminder drop partition p20210104;删除后执行查看建表语句，可以看到p20210104分区没有了 结果如下，说明是不可行的。 mysql> ALTER TABLE bm_scenes_data_reminder ADD PARTITION (PARTITION p20210104 VALUES LESS THAN (738159) ENGINE = InnoDB); ERROR 1481 (HY000): MAXVALUE can only be used in last partition definition mysql> ##1步骤中不可行，提示必须是在最后一个分区的后面才可以这样增加。 因此如果一定要加回p20210104这个分区（即需要在中间部分增加分区）,只能将p20210104 后面的分区先全删除，再增加p20210104分区，再后p20210104 后面的分区重新加回去。操作如下： ##先删除p20210104分区后面的所有分区 ALTER TABLE bm_scenes_data_reminder drop PARTITION p20210105; ALTER TABLE bm_scenes_data_reminder drop PARTITION p20210106; ALTER TABLE bm_scenes_data_reminder drop PARTITION p20210107; ALTER TABLE bm_scenes_data_reminder drop PARTITION p20210108; ALTER TABLE bm_scenes_data_reminder drop PARTITION p20210109; ALTER TABLE bm_scenes_data_reminder drop PARTITION p20210110; ALTER TABLE bm_scenes_data_reminder drop PARTITION future; ##增加p20210104分区 ALTER TABLE bm_scenes_data_reminder ADD PARTITION (PARTITION p20210104 VALUES LESS THAN (738159) ENGINE = InnoDB); ##把p20210104分区后面的所有分区重新加回去 ALTER TABLE bm_scenes_data_reminder ADD PARTITION (PARTITION p20210105 VALUES LESS THAN (738160) ENGINE = InnoDB); ALTER TABLE bm_scenes_data_reminder ADD PARTITION (PARTITION p20210106 VALUES LESS THAN (738161) ENGINE = InnoDB); ALTER TABLE bm_scenes_data_reminder ADD PARTITION (PARTITION p20210107 VALUES LESS THAN (738162) ENGINE = InnoDB); ALTER TABLE bm_scenes_data_reminder ADD PARTITION (PARTITION p20210108 VALUES LESS THAN (738163) ENGINE = InnoDB); ALTER TABLE bm_scenes_data_reminder ADD PARTITION (PARTITION p20210109 VALUES LESS THAN (738164) ENGINE = InnoDB); ALTER TABLE bm_scenes_data_reminder ADD PARTITION (PARTITION p20210110 VALUES LESS THAN (738165) ENGINE = InnoDB); ALTER TABLE bm_scenes_data_reminder ADD PARTITION (PARTITION future VALUES LESS THAN MAXVALUE ENGINE = InnoDB);最后再查看一下ddl发现分区加回去了，但这种操作方式会把p20210104分区后面的所有分区数据删除，在正式线上环境中请慎重 系统有操作系统表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 ##修改成分区表，只执行一次，留一个当前时间的分区和将来的future分区 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) |
【本文地址】
今日新闻 |
推荐新闻 |