线上删除mysql数据脚本
#!/bin/bash# 加载配置文件
source ./conf/mysql.conf
source ./conf/backup.conf
SHELLLOCATION=`dirname $0`
SHELLLOCATION=`cd "${SHELLLOCATION}" ; pwd`
HADOOP_CMD="${SHELLLOCATION}/bin/hadoop-1.0.4/bin/hadoop"
echo "选择要清理的数据库实例："
select MYSQL in ${INCLUDE_MYSQL[@]};
do
break
done
echo "您选择要清理的实例是：$MYSQL"
MYSQL_HOST=`echo $MYSQL | awk -F':' '{print $1}'`
MYSQL_PORT=`echo $MYSQL | awk -F':' '{print $2}'`
read -p "数据库账号：" MYSQL_USER
read -s -p "数据库密码：" MYSQL_PWD
TODAY=`date +"%Y%m%d"`
if [ ! -d ./logs/${MYSQL_HOST}/${MYSQL_PORT} ]; then
mkdir -p ./logs/${MYSQL_HOST}/${MYSQL_PORT}
fi
# 定义一个查询数据库的方法
query_mysql()
{
mysql -u${MYSQL_USER} -p${MYSQL_PWD} -h${MYSQL_HOST} -P${MYSQL_PORT} --default-character-set=utf8 -N -e "$*" | sed "s/^//;s/$//"
}
# 定义一个打印日志的方法
printlog()
{
echo -e $*
echo -e $* >> ./logs/${MYSQL_HOST}/${MYSQL_PORT}/mysql.${TODAY}.log
}
# 定义一个打印删除记录日志的方法
print_delete_log()
{
echo -e $*
echo -e $* >> ./logs/${MYSQL_HOST}/${MYSQL_PORT}/delete.${TODAY}.log
}
DB_SET=`query_mysql "show databases" | egrep -E "dc_|ga_"`
FAIL_BAK_DB=()
for DB in ${DB_SET}; do
# 如果是游戏分析1.0的数据库并且该数据库不是需要过滤的数据库，则开始清理
if [[ "${DB}" =~ ^dc_*$ && ! " ${EXCLUDE_DB_GA10[@]} " =~ " ${DB} " ]]; then
DIR=data/${MYSQL_HOST}/${MYSQL_PORT}/${DB}/${TODAY}
if [ ! -d ./backup/${DIR} ]; then
mkdir -p ./backup/${DIR}
printlog "开始扫描$DB"
WAIT_DELETE_TABLE=()
for TABLE in `query_mysql "use ${DB}; show tables"`; do
# 遍历需要清理的表
for INCLUDE_TABLE in ${DELETE_TABLE_GA10[@]}; do
# 把appid替换成正则表达式
INCLUDE_TABLE=`echo ${INCLUDE_TABLE} | sed 's/^/\^/;s/$/\$/'`
if [[ ${TABLE} =~ ${INCLUDE_TABLE} ]]; then
TOTAL_ROW=`query_mysql "select count(*) from ${DB}.${TABLE}"`
DELETE_ROW=0
DELETE_COLUMN=""
# 如果是日表
if [[ ! `query_mysql "desc ${DB}.${TABLE} StatiTime"` == "" ]]; then
DELETE_COLUMN="StatiTime"
DELETE_ROW=`query_mysql "select count(*) from ${DB}.${TABLE} where StatiTime < unix_timestamp(date_add(now(), interval -6 month))"`
#如果是周表或者月表
elif [[! `query_mysql "desc ${DB}.${TABLE} EndDate"` == "" ]]; then
DELETE_COLUMN="EndDate"
DELETE_ROW=`query_mysql "select count(*) from ${DB}.${TABLE} where EndDate < unix_timestamp(date_add(now(), interval -6 month))"`
fi
if [ ${DELETE_ROW} -gt 0 ]; then
WAIT_DELETE_TABLE+=(${TABLE}.${DELETE_COLUMN})
printlog "${DB}.${TABLE} \t ${TOTAL_ROW} \t ${DELETE_ROW}"
fi
TOTAL_ROW=0
DELETE_ROW=0
DELETE_COLUMN=""
break
fi
done
done
if [ ${#WAIT_DELETE_TABLE[@]} -gt 0 ]; then
read -p "是否清理历史数据：" SURE
if [ ${SURE} = "Yes" ]; then
print_delete_log "开始清除${DB}"
for WAIT_DELETE in ${WAIT_DELETE_TABLE[@]}; do
DEL_TABLE=`echo ${WAIT_DELETE} | awk -F'.' '{print $1}'`
DEL_COLUMN=`echo ${WAIT_DELETE} | awk -F'.' '{print $2}'`
mysqldump -u${MYSQL_USER} -p${MYSQL_PWD} -h${MYSQL_HOST} -P${MYSQL_PORT} ${DB} ${DEL_TABLE} -w"${DEL_COLUMN} < unix_timestamp(date_add(now(), interval -6 month))" > ./backup/${DIR}/${DEL_TABLE}.sql
DEL_ROW=`query_mysql "set sql_log_bin=0; delete from ${DB}.${DEL_TABLE} where ${DEL_COLUMN} < unix_timestamp(date_add(now(), interval -6 month))"`
print_delete_log "${DEL_TABLE} \t ${DEL_ROW}"
done
echo "开始打包导出的sql文件！"
tar -zcf ./backup/bak.tar.gz ./backup/${DIR}/*.sql
echo "开始发送打包文件到hdfs，可能需要几分钟，请耐心等候！"
sh backup_tools.sh "${DIR}"
if [[ $? -ne 0 || `${HADOOP_CMD} fs -ls /backup/${BUSINESS_TYPE}/${CUSTOM_SUB_DIR}/${DIR}/ | grep bak.tar.gz` = "" ]]; then
print_delete_log "==========${DB}备份失败，请手动备份！=========="
FAIL_BAK_DB+=(${MYSQL}.${DB})
fi
fi
fi
print_delete_log "==========备份失败的数据库有：${FAIL_BAK_DB[@]}=========="
unset WAIT_DELETE_TABLE
fi
done
页:
[1]