手动删除mysql分区表数据

解决数据量占用系统资源问题 定期清理历史数据

删除正式环境2019年6月份之前的所有历史数据.

流程描述

• 确定Mysql需要清理的库:

product_tst_iot_guangzhouwanli 
product_tst_iot_guilun 
product_tst_iot_guojiluntai
product_tst_iot_hangzhoupt
product_tst_iot_jiande
product_tst_iot_qingquan
product_tst_iot_shuangqian
product_tst_iot_xinguilun
product_tst_iot_xinjiang
product_tst_iot_yousheng
product_tst_iot_zhongce

• influxdb数据库

all
jiande
xinjiang
guojiluntai
yousheng
guilun
xinguilun
guangzhouwanli
zhongce
jichi
shuangqiantyre
qingquan

• 确定mysql的表

t_machine_alert_gantt
t_machine_heart_gantt
t_machine_running_info
t_machine_step_gantt
t_tire_info
t_tire_step_info

• 确定influxdb的表

machine_alert_log
machine_analog_log
machine_heart_log
machine_heart_log_v2
machine_step_log

• 将mysql与influxdb分别设立从库

• 将mysql与influxdb从库断开不在同步

• 使用sql查询一下删除之前的总条数

• 查询一下2019年6月之前的总条数

• 编写操作语句上传至git仓库，超过2人以上审核

• 执行删除语句，在查询删除之后的总条数，是否等于 [5] - [6]

• 如果条数不对，将从库切换为主库，从新还原主库，等主库还原完成再切换回主库

• 如果条数对了将数据库主从恢复同步状态，删除流程结束

• influxdb删除语句

DELETE FROM "machine_alert_log" where time < '2021-12-15 00:00:00';
DELETE FROM "machine_analog_log" where time < '2021-12-15 00:00:00';
DELETE FROM "machine_heart_log" where time < '2021-12-15 00:00:00';
DELETE FROM "machine_heart_log_v2" where time < '2021-12-15 00:00:00';
DELETE FROM "machine_step_log" where time < '2021-12-15 00:00:00';
DELETE FROM "machine_string_log" where time < '2021-12-15 00:00:00';
DELETE FROM "machine_plc_serial_num_log" where time < '2021-12-15 00:00:00';
DELETE FROM "machine_parts_warn_log" where time < '2021-12-15 00:00:00';

• mysql删除语句

DELETE from t_machine_alert_gantt where start_time < '2019-06-01 00:00:00';
DELETE from t_machine_heart_gantt where start_time < '2019-06-01 00:00:00';
DELETE from t_machine_step_gantt where start_time < '2019-06-01 00:00:00';
DELETE from t_tire_info where time_stamp < '2019-06-01 00:00:00';
DELETE from t_tire_step_info where time_stamp < '2019-06-01 00:00:00';

• 先查mysql有多少要删除的数据

SELECT COUNT(*) from t_machine_alert_gantt where start_time < '2019-06-01 00:00:00';
SELECT COUNT(*) from t_machine_heart_gantt where start_time < '2019-06-01 00:00:00';
SELECT COUNT(*) from t_machine_step_gantt where start_time < '2019-06-01 00:00:00';
SELECT COUNT(*) from t_tire_info where time_stamp < '2019-06-01 00:00:00';
SELECT COUNT(*) from t_tire_step_info where time_stamp < '2019-06-01 00:00:00';

• 再查influxdb有多少要删的数据

SELECT COUNT(*) FROM "machine_alert_log" WHERE time < '2019-06-01 00:00:00';
SELECT COUNT(*) FROM "machine_analog_log" WHERE time < '2019-06-01 00:00:00';
SELECT COUNT(*) FROM "machine_heart_log" WHERE time < '2019-06-01 00:00:00';
SELECT COUNT(*) FROM "machine_heart_log_v2" WHERE time < '2019-06-01 00:00:00';
SELECT COUNT(*) FROM "machine_step_log" WHERE time < '2019-06-01 00:00:00';

---

2021-05-20更新
目前测试环境的硬盘已经满了,使用上面的删除语句对数据量大的表无法使用,幸好数据量大的表在规划的时候使用了时间分区,可以根据分区删除数据,步骤如下

# 查表分区 在information_scheam库中
SELECT
	TABLE_SCHEMA,
	TABLE_NAME,
	PARTITION_NAME,
	PARTITION_DESCRIPTION,
	TABLE_ROWS
FROM
	`PARTITIONS`
WHERE
	`TABLE_SCHEMA` LIKE 'product_tst_iot_%'
AND `TABLE_NAME` LIKE '%t_machine_step_gantt%'
AND `TABLE_ROWS` != 0 
LIMIT 0, 1000

查看PARTITION_DESCRIPTION时间列对应的分区pxxx,执行清空语句

mysql> ALTER TABLE product_tst_iot_zhongce.t_machine_step_gantt TRUNCATE PARTITION p11c,p121;
Query OK, 0 rows affected (0.50 sec)

下面这个语句时删除分区语句,分区删除之后无法再次创建,所以这个语句不要使用,了解就行

mysql> ALTER TABLE t_machine_step_gantt DROP PARTITION p121;
Query OK, 0 rows affected (1.69 sec)
Records: 0  Duplicates: 0  Warnings: 0

命令行快速清空分区表(需要修改密码,待清空表名,待清空表分区)

[root@tst-advance-01 ~]# for i in `mysql -uroot -p'tLI123!' -e 'SHOW DATABASES' 2>/dev/null | grep 'product_tst_iot_' | egrep -v 'admin|hangzhoupt'`;do mysql -uroot -p'tLI123!' -e "ALTER TABLE $i.t_machine_alert_gantt TRUNCATE PARTITION p11c,p121";done