shell脚本大量清除数据库数据 & 清理数据库空间碎片

删除n张表数据，每张表删除部分数据，为了防止数据量大直接把mysql的临时空间沾满而导致删除失败 每次只删除5000条

#!/bin/sh
dsize=5000
pwd=xxx
hst=xxx
dbname=xxx
dport=xxx
user=xxx
for line in `cat ./tbl.txt`
do
IFS=',' arr=($line)
j=$((${arr[0]}/dsize + 1))
SQL="delete from ${arr[0]} where ${arr[1]} <= ${arr[2]} limit $dsize;"
echo $SQL
echo $j
i=0;
while [ $i -le $j ];
do
echo "del table---${arr[0]} size---$dsize times---$i"
i=$((i + 1))
QUERY=`mysql -h$hst  -u$user -p$pwd -P$dport $dbname  << EOF
$SQL
exit
EOF`
done
done

tbl.txt

每一行第一个字段是表名，第二个字段是主键或其他，执行时要指定的where条件字段名字，第三个字段是where条件小于的值

比如第二行在shell运行后会被组装成sql

delete from user where id < 247058 limit 5000

table_name1,premarykeyname1,7382
user,id,247508

删除数据后使用如下命令释放mysql碎片空间

innodb引擎

alter table xxx engine = innodb;

其他的可以使用

optimize table xxx1,xxx2

可以再执行前后用如下sql查看表空间的释放情况单位M

SELECT 
DATA_LENGTH/1024/1024 + DATA_FREE/1024/1024 + INDEX_LENGTH/1024/1024 AS totl,
DATA_LENGTH/1024/1024 , DATA_FREE/1024/1024 , INDEX_LENGTH/1024/1024 
FROM TABLES WHERE table_name='xxx'

11396total visits,1visits today