通常我们在删除线上的大表数据的时候,如果超过一定数目就不能直接删除,否则可能会造成数据库长时间锁定或者直接导致服务器挂掉。合理的做法应该是分多批次删除,比如下面脚本中是每次删除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
阅读(6533) | 评论(2) | 转发(5) |