一个批量删除大表数据的shell小脚本

    通常我们在删除线上的大表数据的时候，如果超过一定数目就不能直接删除，否则可能会造成数据库长时间锁定或者直接导致服务器挂掉。合理的做法应该是分多批次删除，比如下面脚本中是每次删除1000行，然后休息10s种后继续删除。

点击(此处)折叠或打开

#Purpose: 批量删除大表的数据，减少对线上数据的影响,每次删除1000行数据，然后sleep 10
#Author: Carl Zhang
#Date: 2012-04-09
#Useage: ./delete_bigtable_data.sh 'DELETE FROM test.test WHERE test.datetime <="2012-04-09 00:00:00"'
MYSQL='/usr/local/mysql/bin/mysql'
USER='root'
HOST='localhost'
PASSWORD='zhang@123'
if [ $# -ne 1 ];then
        echo "Usage: ./delete_bigtable_data.sh 'DELETE_SQL'"
        echo "For example: ./delete_bigtable_data.sh 'DELETE FROM test.test WHERE test.datetime <="2012-04-09 00:00:00"'"
        echo "Please try again"
        exit 1
fi
DELETE_SQL=$1
echo "DELETE_SQL is $DELETE_SQL"
SELECT_SQL=`echo $DELETE_SQL|sed 's/;$//;s/^\w*/SELECT */;s/$/ LIMIT 1/;s/.*/select count(1) from (&) a/'`
echo "SELECT_SQL is $SELECT_SQL"
LAST_SQL=`echo $DELETE_SQL |sed 's/;$//;s/$/ LIMIT 1000/'`
echo "LAST_SQL is $LAST_SQL"
while [ `$MYSQL -u$USER -p$PASSWORD -h$HOST  -ss -e "$SELECT_SQL"` -gt 0 ];do
        echo "deleting 1000 rows,please wait"
        $MYSQL -u$USER -p$PASSWORD -h$HOST  -ss -e "$LAST_SQL"
        echo "sleep 10"
sleep 10
done