背景

線上某個數據庫有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}" 來釋放磁盤空間。由于刪除和釋放過程都是會占用相當多的時間，所以這兩個過程最好是分開來進行。

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

總結

以上是生活随笔為你收集整理的mysql 清洗字段数据,根据字段条件清理MySQL数据库数据的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。