一、清理mysql库
删除binlog里比较老的文件,保留近期几个就可以(binlog用来恢复数据库的)
二、下线ck库(可恢复上线)
ck库: 要连接客户端,不能直接删除,使用sql语句删除
1.查询分区并复制
select partition from system.parts where table = ‘CK_THREAT_LOCAL’ and partition like ‘%2021%’ order by modification_time limit 100 ;
2.下线分区
ALTER TABLE default.CK_THREAT_LOCAL DETACH PARTITION (202109,30000,31000);
3.上线分区
ALTER TABLE default.CK_THREAT_LOCAL ATTACH PARTITION (202101,40000);
三、删除ck数据
1查询
SELECT COUNT(*) FROM default.CK_THREAT_LOCAL WHERE CREATE_TIME > ‘2023-06-06 12:00:00’ AND CREATE_TIME < ‘2023-08-16 00:00:00’
2备份数据
clickhouse-client -hCK0 --port 9002 -u用户 --password XXXX密码 --query “SELECT * FROM 表名 WHERE CREATE_TIME < toDateTime(‘2021-07-31 00:00:00’)” > 20210701.csv
3删除数据
ALTER TABLE default.CK_THREAT_LOCAL DELETE WHERE CREATE_TIME > ‘2022-12-01 12:00:00’ AND CREATE_TIME < ‘2023-03-30 20:00:00’