根據欄位條件清理MySQL資料庫資料

根據欄位條件清理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}" 來釋放磁碟空間。由於刪除和釋放過程都是會佔用相當多的時間，所以這兩個過程最好是分開來進行。

在批量運行指令碼之前，需要先制定單個庫表來驗證下指令碼的清理效果。

本文永久更新連結地址：