mysql 脚本批量删除数据_一个批量删除大表数据的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