#!/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_[0-9]|ga_[0-9]"
`
FAIL_BAK_DB=()
for
DB
in
${DB_SET};
do
# 如果是游戏分析
1.0
的数据库并且该数据库不是需要过滤的数据库，则开始清理
if
[[
"${DB}"
=~ ^dc_[
0
-
9
]*$ && !
" ${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
"是否清理历史数据：[Yes/No]"
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
