问题

一个mysql数据库，由于数据不断积累，目前ibdata1文件已经达到100GB大小，接近磁盘存储空间上限。

使用mysqlshow命令查看各个表占用的空间大小，发现dap_app_usage表占用的空间最大。其中data_length是数据大小，index_length是索引大小，data_free是未被使用的空间（通常是删除数据后留下的，这些空间不会被文件系统回收，但未来新插入的数据可以使用）。

mysqlshow --status apptimer

由于dap_app_usage表里保存了2014～2018年的数据，其中2015年及以前的数据属于冷数据，因此可以备份这些数据并从数据库里删除。

备份数据

我们按月备份数据以方便未来使用这些数据，每个月预估压缩后的文件大小为100MB，写一个脚本循环调用mysqldump命令以节约手工输入命令的时间并避免出错：

#!/bin/sh
#Dump dap_app_usage table to files by month
for i in {201401..201412}
do
    echo [date] Backing up $i ...
    mysqldump -uroot apptimer dap_app_usage -t --where="date between '${i}01' and '${i}31'"|gzip>~/dap_app_usage_${i}_data.sql.gz
done

删除数据

一次删除大量（如一亿条）数据效率很低，编写一个脚本小批量多次删除：

#!/bin/sh
# Delete specified range data from dap_app_usage table
# Make sure you have backup of these data

st=20140101
ed=20141231

if [[ ${st} -lt 19700101 ]]; then
    echo 'Wrong start date'
    exit
fi

if [[ ${ed} -lt ${st} ]]; then
        echo 'End date must be greater than start date'
        exit
fi

if [[ $((${ed}-${st})) -gt 1130 ]]; then
    echo 'Date range too large (1 year max)'
    exit
fi

echo "Delete data from ${st} to ${ed}"
for i in {1..99999}
do
    echo [date] delete iteration ${i}
    mysql -uroot apptimer -e "select count(*) from dap_app_usage where date between '${st}' and '${ed}'"
    mysql -uroot apptimer -e "delete from dap_app_usage where date between '${st}' and '${ed}' limit 1000"
    sleep 1
done

后续

为避免以后再出现类似问题，应定期执行上述清理操作，保证在dap_app_usage表里只保存近期一段时间的数据，这样可以维持数据库文件大小在一个固定值。

参考资料：

How to shrink/purge ibdata1 file in MySQL