MySQL HeatWave 数据库日常维护：别只顾扩容，先做好"家务"

-- 按表大小排序，找出 Top 20 占空间的表
SELECT
    table_schema,
    table_name,
    ROUND(data_length / 1024 / 1024, 2) AS data_mb,
    ROUND(index_length / 1024 / 1024, 2) AS index_mb,
    ROUND((data_length + index_length) / 1024 / 1024, 2) AS total_mb,
    table_rows
FROM information_schema.tables
WHERE table_schema NOT IN ('mysql', 'information_schema', 'performance_schema', 'sys')
ORDER BY (data_length + index_length) DESC
LIMIT 20;

-- 归档后删除：先备份再 DROP，避免误删
CREATE TABLE archive.operation_log_2023 AS
SELECT * FROM operation_log WHERE created_at < '2024-01-01';

-- 确认归档数据无误后再删除原表对应分区或整表
ALTER TABLE operation_log DROP PARTITION p2023;

-- 分批删除，避免长事务锁表
DELETE FROM operation_log WHERE created_at < '2024-01-01' LIMIT 10000;
-- 重复执行直到 affected_rows = 0

-- 删除完成后回收碎片
OPTIMIZE TABLE operation_log;

-- 查看 binlog 过期天数和当前文件列表
SHOW VARIABLES LIKE 'binlog_expire_logs_seconds';
SHOW VARIABLES LIKE 'expire_logs_days';  -- 8.0 之前版本

-- 列出所有 binlog 文件及大小
SHOW BINARY LOGS;

-- 设置 binlog 保留 7 天（单位为秒）
SET GLOBAL binlog_expire_logs_seconds = 604800;

-- 确认配置持久化（HeatWave 通过 OCI 配置管理持久化，也可写入 mysqld-auto.cnf）
-- 如果是用户可持久化的参数：
SET PERSIST binlog_expire_logs_seconds = 604800;

-- 清理到指定编号之前的 binlog
PURGE BINARY LOGS TO 'mysql-bin.000145';

-- 或按时间清理
PURGE BINARY LOGS BEFORE '2025-01-08 00:00:00';

-- 计算每张表的碎片率（DATA_FREE / TOTAL_SIZE）
SELECT
    table_schema,
    table_name,
    ROUND(data_free / 1024 / 1024, 2) AS free_mb,
    ROUND((data_length + index_length) / 1024 / 1024, 2) AS total_mb,
    ROUND(data_free / (data_length + index_length) * 100, 2) AS fragment_pct
FROM information_schema.tables
WHERE table_schema NOT IN ('mysql', 'information_schema', 'performance_schema', 'sys')
  AND data_free > 0
ORDER BY fragment_pct DESC
LIMIT 20;

# 使用 Percona Toolkit 在线重建表（不锁表）
# 需要在可以连接 HeatWave MySQL 的客户端主机上安装 pt-online-schema-change
pt-online-schema-change \
  --alter "ENGINE=InnoDB" \
  --user=admin --password=your_password \
  --host=heatwave-mysql-endpoint \
  --execute \
  D=production,t=order_line

-- 低峰期逐表执行
OPTIMIZE TABLE production.order_line;

-- 卸载不再需要 HeatWave 加速的表，释放内存配额
ALTER TABLE production.archive_report SECONDARY_UNLOAD;

-- 查看 HeatWave 加速引擎内存使用概况
SHOW GLOBAL STATUS LIKE 'Heatwave%';

#!/bin/bash
# heatwave_housekeeping_check.sh
# 输出存储 Top10、碎片 Top10、binlog 体积，供人工判断后续操作

MYSQL_HOST="heatwave-mysql-endpoint"
MYSQL_USER="admin"
MYSQL_PASS="your_password"

echo "===== Top 10 大表 ====="
mysql -h "$MYSQL_HOST" -u "$MYSQL_USER" -p"$MYSQL_PASS" -e "
SELECT table_schema, table_name,
       ROUND((data_length+index_length)/1024/1024,2) AS total_mb
FROM information_schema.tables
WHERE table_schema NOT IN ('mysql','information_schema','performance_schema','sys')
ORDER BY (data_length+index_length) DESC LIMIT 10;"

echo "===== 碎片率 Top 10 ====="
mysql -h "$MYSQL_HOST" -u "$MYSQL_USER" -p"$MYSQL_PASS" -e "
SELECT table_schema, table_name,
       ROUND(data_free/(data_length+index_length)*100,2) AS fragment_pct,
       ROUND(data_free/1024/1024,2) AS free_mb
FROM information_schema.tables
WHERE table_schema NOT IN ('mysql','information_schema','performance_schema','sys')
  AND data_length+index_length > 0
ORDER BY fragment_pct DESC LIMIT 10;"

echo "===== Binlog 文件数与总体积 ====="
mysql -h "$MYSQL_HOST" -u "$MYSQL_USER" -p"$MYSQL_PASS" -e "
SHOW BINARY LOGS;" | tail -n +2 | awk '{count++; size+=$4} END {print "文件数:", count, " 总体积(MB):", size/1024/1024}'

echo "===== HeatWave 列存状态 ====="
mysql -h "$MYSQL_HOST" -u "$MYSQL_USER" -p"$MYSQL_PASS" -e "
SHOW GLOBAL STATUS LIKE 'Heatwave%';"