MySQL清理千万级表数据脚本

一、脚本

cat clean_data.sh

#!/bin/bash

#定义数据库连接信息
username="xxxxx"
password="xxxxx"
host="xxxxx"
port="xxxxx"
database="xxxxx"

#定义要删除的历史数据周期
d=`date -d "30 days ago" + "%Y-%m-%d"`
table="${1}"
start_num=${2}
end_num=${3}
limit=${4}

#删除历史数据
for i in $(seq -w $start_num $end_num)
do
        t=${database}.${tables}${i}
		echo ${t}
		row_count=1
		sql_str="set session ob_query_timeout=10000000000;delete from ${t} where gmt_create<'${d}' limit ${limit};select row_count()"
        echo "sql_str: ${sql_str}"
		while [ $row_count -gt 0 ];do
        s_time=$(date +%s)
        row_count=$(mysql -u${username} -h${host} -p${password} -P${port} -Nse "${sql_str}")
		e_time=$(date +%s)
        elapsed=$(( e_time - s_time ))
            if [ $? -ne 0 ];then
			    echo "row_count:${row_count},time:${elapsed}s"
				exit
		    fi
		    echo "delete:${row_count},time:${elapsed}s"
		    sleep 1
		done
done

二、用例

#可以开启多个进程，并行删除（生产环境千万不能打爆CPU，内存）
sh clean_data.sh test 001 029 200000
sh clean_data.sh test 030 059 200000
sh clean_data.sh test 060 089 200000
sh clean_data.sh test 090 099 200000