根据字段条件清理MySQL数据库数据

背景
线上某个数据库有1000个分库的DB，磁盘告警，每个库的大小都不是很大但是加起来就非常大了。手动根据时间字段来清理数据不太现实，于是决定写脚本来删除指定时间以前的数据。脚本：#/bin/bash
##auth by qunyingliu
## files in xxx

HOST=$1
DBPORT=3306
USER="xxxx"
PASSWORD="xxxx"
DB_SKIP_CLEAN="mysql|performance_schema|information_schema"
DBBAK_DIR="/data/backup/databases/$HOST"

MySQL="mysql -h ${HOST} -u${USER} -p${PASSWORD} -P${DBPORT}"
MySQL_show="mysqlshow -h ${HOST} -u${USER} -p${PASSWORD} -P${DBPORT} "
MySQL_Dump="mysqldump -h ${HOST} -u${USER} -p${PASSWORD} -P${DBPORT}"

function ExistsColumn
{
local DB=$1
local TABLE=$2
local COLUMN=$3

SEARCH_RESULT=$（ ${MySQL_show} ${DB} ${TABLE} ${COLUMN} | awk "{ if （ NR == 5） print $2 }"）
if [ "${COLUMN}" = "${SEARCH_RESULT}" ];
then
echo "true";
else
echo "false";
fi
}

function DataBasesBackup
{
local DB=$1
local TABLE=$2
local DBBAK_DIR=$3
local count=0

[ ！ -d ${DBBAK_DIR}/$DB ] && mkdir -p ${DBBAK_DIR}/$DB

#count=$（${MySQL} -e "select count（Fdate） from ${DB}.${TABLE} where Fdate < "2014-01-01" order by Fdate"|awk "{if （NR == 2） print $0}"）
count=$（${MySQL} -e "use ${DB};show table status like "${TABLE}";"|awk "{if（NR==2） print $5}"）

if [ $count -gt 0 ];then
echo "start backup ${DB} ${TABLE}"
echo "count:$count"
${MySQL_Dump} $DB $TABLE |gzip -c >${DBBAK_DIR}/$DB/$DB.$TABLE.sql.gz
else
echo "Fdate older than 2014-01-01 count : $count,skip backup "
#${MySQL} -e "select Fdate from ${DB}.${TABLE} limit 1;"
fi
#sleep 10
#exit 1
}

function DataBasesClean
{
local DB=$1
local TABLE=$2
local count=0
#count=$（${MySQL} -e "select count（Fdate） from ${DB}.${TABLE} where Fdate < "2014-01-01" order by Fdate"|awk "{if （NR == 2） print $0}"）
count=$（${MySQL} -e "use ${DB};show table status like "${TABLE}";"|awk "{if（NR==2） print $5}"）
Engine=$（${MySQL} -e "use ${DB};show table status like "${TABLE}";"|awk "{if（NR==2） print $2}"）

while [ $count -gt 0 ]
do
if [ $count -le 500000 ];then
${MySQL} -e "delete from ${DB}.${TABLE} where Fdate < "2014-01-01";"
echo "clean ${DB}.${TABLE} ok"
sleep 1

#sleep 10
#exit 1
else
${MySQL} -e "delete from ${DB}.${TABLE} where Fdate < "2014-01-01" limit 500000;"
fi
count=$（（$count - 500000））
done

#${MySQL} -e "optimize table ${DB}.${TABLE}"

}

#########main###############
if [ ！ $HOST ]
then
echo "输入错误，请检查！"
echo "usage: $0 IP"
exit 1
fi

ALL_DATABASES="$（${MySQL_show} |awk "{++n;if（n>3&&NF>=3&&$2！~"（"${DB_SKIP_CLEAN}"）"）print$2}"）"
mkdir -p /data/logs/mysqlclean
echo ${ALL_DATABASES} >/data/logs/mysqlclean/databases.txt
COLUMN="Fdate"

for DB in ${ALL_DATABASES};
do
echo $DB
#${MySQL_show} $DB

ALL_TABLES=$（${MySQL_show} $DB |awk "{ if （NR >4 ） print $_}" |sed -e "s/[|+-]//g; /^$/d "|xargs ）
echo ${ALL_TABLES} >/data/logs/mysqlclean/$DB.txt

for TABLE in ${ALL_TABLES};
do
if [ "true" = "$（ExistsColumn $DB $TABLE $COLUMN）" ];
then
echo $DB $TABLE
DataBasesBackup $DB $TABLE ${DBBAK_DIR}
DataBasesClean $DB $TABLE
fi
done
done上面的脚本只是备份和删除指定条件的数据，删除完数据后还需要运行 #${MySQL} -e "optimize table ${DB}.${TABLE}" 来释放磁盘空间。由于删除和释放过程都是会占用相当多的时间，所以这两个过程最好是分开来进行。在批量运行脚本之前，需要先制定单个库表来验证下脚本的清理效果。本文永久更新链接地址