下载地址:https://download.youkuaiyun.com/download/qq_31408331/11247189
1.脚本
#!/bin/bash
#Program
#
#定义连接变量
host_str=172.16.16.64
port=3306
username=admin
password=admin
database=cmom_trunk_1
mysql_conf=$(cat ./config/mysql.conf)
#conn_array=$(awk '{print $1 ";" $4}' ./config/mysql.conf)
echo "数据库连接信息:---------------------------"
#echo $mysql_conf
sleep 1
conns=(${mysql_conf// /})
#echo $conns
if [ -n "${conns[0]}" ]
then
host_str=${conns[0]}
echo "数据库地址为:${host_str}"
fi
if [ -n "${conns[1]}" ]
then
port=${conns[1]}
echo "数据库端口为:${port}"
fi
if [ -n "${conns[2]}" ]
then
username=${conns[2]}
echo "用户名称为:${username}"
fi
if [ -n "${conns[3]}" ]
then
password=${conns[3]}
echo "用户密码为:*****"
fi
if [ -n "${conns[4]}" ]
then
database=${conns[4]}
echo "数据库名称为:${database}"
fi
notdel=$(cat ./config/notdel)
sleep 1
notdel_array=(${notdel// /})
sleep 1
echo "开始连接数据库........."
#checkdown data
$(mysql -h${host_str} -P${port} -u${username} -p${password} ${database} -e "SET NAMES utf8;show tables;" > table_info)
sleep 3
echo "数据连接成功..........."
tables=$(cat table_info)
tab_array=(${tables// /})
unset tab_array[0]
sleep 1
echo "加载配置文件...."
sleep 1
echo "开始解析配置文件...."
#定义表清空语句
tab_del_cmd=''
delete_cmd='delete from'
i=0
#统计表的总数
count_sum=0
#统计清空表的个数
count_clean=0
#统计跳过表的个数
count_jump=0
is_del=true
for el in ${tab_array[@]}
do
echo ----$el-----
not_del=0
let count_sum+=1
for tab in ${notdel_array[@]}
do
if [ "$tab" == "$el" ]
then
let count_jump+=1
not_del=1
break
fi
done
if [ $not_del -eq 1 ]
then
echo --跳过${tab}
continue
fi
#拼接sql
if [ $i -eq 0 ]
then
tab_del_cmd="$delete_cmd $el;"
else
tab_del_cmd="$tab_del_cmd $delete_cmd $el;"
fi
let i+=1
let count_clean+=1
done
sleep 1
echo "解析配置文件结束....."
sleep 1
echo $tab_del_cmd > ./tab_del_sql
echo "开始清空数据库表数据------"
$(mysql -h${host_str} -P${port} -u${username} -p${password} ${database} -e "$tab_del_cmd" > tab_del.log)
sleep 5
echo "查询到表总数:${count_sum},清空表的个数:${count_clean},跳过表总数:${count_jump}" >> tab_del.log
echo "执行完毕,详情查看日志文件 tab_del.log"
2.配置文件
mysql.conf 配置连接信息,运行时将后边备注删除,notdel也是
172.16.*.* //数据库地址
3306 //端口号
root //用户名
root //密码
cmom_trunk_1 //数据库名称
notdel 标注不被清空的表
bd_user //表名