背景
线上某个数据库有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}" 来释放磁盘空间。由于删除和释放过程都是会占用相当多的时间,所以这两个过程最好是分开来进行。
在批量运行脚本之前,需要先制定单个库表来验证下脚本的清理效果。