线上删除mysql数据脚本

页面导航:首页 > 数据库 > Mysql > 线上删除mysql数据脚本

线上删除mysql数据脚本

来源: 作者: 时间:2016-02-17 10:07 【

一、需求 线上有几个实例,经常磁盘告警,之前每次人工删除,效率实在不高。二、解决方法 登录跳板机执行脚本删除,不过在删除前,需要导出binary log日志和库表的的数据,最后把

一、需求

线上有几个实例,经常磁盘告警,之前每次人工删除,效率实在不高。

二、解决方法

登录跳板机执行脚本删除,不过在删除前,需要导出binary log日志和库表的的数据,最后把备份的传数据传到hdfs。

三、脚本

读取配置文件

1.1 mysql.conf,哪些实例,哪些表名

MYSQL_HOME=/usr/local/mysql/bin/mysql
INCLUDE_MYSQL=(mysql4:5507mysql5:5508mysql6:5509mysql7:5510mysql7:5511)
EXCLUDE_DB_GA10=(dc_15dc_17dc_44dc_49dc_88dc_183dc_279dc_490dc_624dc_643dc_903dc_906dc_908dc_954dc_1099dc_1100dc_1167dc_1214dc_1463dc_1464dc_2444dc_2445dc_2695)
APPID_REG=[0-9A-Za-z]{32,33}
DELETE_TABLE_GA10=(dc_datacenter_cachedc_day_report_cache_basicdc_day_report_cache_incomedc_day_report_cache_cumudc_day_report_cache_channel${APPID_REG_REG}_dc_distributed_everydayfront_${APPID_REG}_dc_everyday2${APPID_REG}_dc_everyhour${APPID_REG}_dc_everyday${APPID_REG}_dc_distributed_everyday2${APPID_REG}_dc_distributedweek_everyweek2${APPID_REG}_dc_distributedmonth_everymonth2${APPID_REG}_dc_pay_distributed_everyday${APPID_REG}_dc_custom_retain_by_dayfront_${APPID_REG}_dc_custom_retain_by_day${APPID_REG}_dc_distributedmonth_everymonth2${APPID_REG}_dc_equipment_distributed_by_day${APPID_REG}_dc_fp_distributed_everyday${APPID_REG}_dc_event_by_day${APPID_REG}_dc_event_attr_by_day${APPID_REG}_dc_error_report_detail${APPID_REG}_dc_error_report_dist_hour${APPID_REG}_dc_player_30dayvaluefront_${APPID_REG}_dc_player_30dayvalue${APPID_REG}_dc_player_30day_arpufront_${APPID_REG}_dc_player_30day_arpu${APPID_REG}_dc_whale_player_day${APPID_REG}_dc_uid_retain_by_dayfront_${APPID_REG}_dc_uid_retain_by_day${APPID_REG}_dc_task_everyday${APPID_REG}_dc_level_everyday${APPID_REG}_dc_app_ring${APPID_REG}_dc_error_report_dist_sys${APPID_REG}_dc_error_report_detail_sys${APPID_REG}_dc_error_report_dist_user${APPID_REG}_dc_error_report_detail_user${APPID_REG}_dc_rollserver_player_by_day${APPID_REG}_dc_rollserver_income_by_day${APPID_REG}_dc_rollserver_retainfront_${APPID_REG}_dc_rollserver_retain${APPID_REG}_dc_tag_everyday${APPID_REG}_dc_tag_level_outflow${APPID_REG}_dc_tag_retain_by_day${APPID_REG}_dc_everyweek${APPID_REG}_dc_everymonth${APPID_REG}_dc_distributed_everyweek${APPID_REG}_dc_distributed_everyweek2${APPID_REG}_dc_distributed_everymonth${APPID_REG}_dc_distributed_everymonth2${APPID_REG}_dc_custom_retain_by_week${APPID_REG}_dc_custom_retain_by_monthfront_${APPID_REG}_dc_custom_retain_by_weekfront_${APPID_REG}_dc_custom_retain_by_month)

EXPIRE_LOGS_DAYS=21

1.2 backup.conf

JAVA_HOME=/usr/java/jdk1.7.0_25
LOCAL_FILES=(/home/yaolihong/yao/backup/bak.tar.gz)
BUSINESS_TYPE=ga
CUSTOM_SUB_DIR=mysql

2.backup_tools.sh脚本

主要将备份压缩好的数据发往到hdfs,这个会在clean_data0.sh和clean_data1.sh最后调用到

#!/bin/sh
#set-x

#checkuser
WHO_AM_I=`whoami`
if["hadoop"==$WHO_AM_I];then
echo"Error:youshouldnotusehadoopusertobackfiles,useanotheruserinsteadplease..."
exit1
fi

shellLocation=`dirname$0`
shellLocation=`cd"$shellLocation";pwd`
.$shellLocation/conf/backup.conf
HADOOP_CMD="$shellLocation/bin/hadoop-1.0.4/bin/hadoop"

#checkifempty
REQUIED_PARAMS=(JAVA_HOMELOCAL_FILESBUSINESS_TYPECUSTOM_SUB_DIR)
forPARAMin${REQUIED_PARAMS[@]}
do
if["X${!PARAM}"="X"];then
echo"Error:${PARAM}isnotset..."
exit1
fi
done
exportJAVA_HOME

CURRENT_TIME=`date+%Y%m%d-%H_%M_%S`
CURRENT_LOG_FILE=$shellLocation/logs/backup.$CURRENT_TIME.log

#gettime
CURRENT_YEAR=`date+%Y-d'1hoursago'`
CURRENT_MONTH=`date+%m-d'1hoursago'`
CURRENT_DAY=`date+%d-d'1hoursago'`
LAST_HOUR=`date+%H-d'1hoursago'`
HOUR_STR_FOR_HDFS="$CURRENT_YEAR/$CURRENT_MONTH/$CURRENT_DAY/$LAST_HOUR"

BACKUP_BASIC_PATH="/backup/$BUSINESS_TYPE/$CUSTOM_SUB_DIR"
BACKUP_DAY_PATH="$BACKUP_BASIC_PATH/$CURRENT_YEAR/$CURRENT_MONTH/$CURRENT_DAY"

#判断是否有传入路径,有则以传入的路径为准
if[$#=1];then
BACKUP_DAY_PATH="/backup/$BUSINESS_TYPE/$CUSTOM_SUB_DIR/$1"
fi

#checkifhdfsdirexistandmkdir
$HADOOP_CMDfs-test-e$BACKUP_DAY_PATH
if[$?-ne0];then
$HADOOP_CMDfs-mkdir$BACKUP_DAY_PATH>>$CURRENT_LOG_FILE2>&1
fi
HOST_NAME=`/bin/hostname`

IS_ANY_FAILED="N"
forLOCAL_FILEin${LOCAL_FILES[@]}
do
#checkiflocalfileexist
if[!-f$LOCAL_FILE];then
IS_ANY_FAILED="Y"
echo"Error:Localfilenotexist:$LOCAL_FILE">>$CURRENT_LOG_FILE
continue
fi

#putlocalfiletohdfs
LOCL_FILE_NAME=`/bin/basename$LOCAL_FILE`
HDFS_FILE_NAME=${LOCL_FILE_NAME}_${HOST_NAME}_${CURRENT_TIME}
$HADOOP_CMDfs-put$LOCAL_FILE$BACKUP_DAY_PATH/$HDFS_FILE_NAME>>$CURRENT_LOG_FILE2>&1

#ifputfailed,weshouldnottouchadonefile
if[$?-ne0];then
IS_ANY_FAILED="Y"
echo"Error:putfiletohdfsfailed:$LOCAL_FILE">>$CURRENT_LOG_FILE
continue
fi
done

#deletelog10daysago
find$shellLocation/logs/-mtime+10-delete

if[$IS_ANY_FAILED="Y"];then
exit1
fi
#set+x

 

 

3.清除脚本clean_data0.sh

#!/bin/bash

#加载配置文件
source./conf/.conf
source./conf/backup.conf
SHELLLOCATION=`dirname$0`
SHELLLOCATION=`cd"${SHELLLOCATION}";pwd`
HADOOP_CMD="${SHELLLOCATION}/bin/hadoop-1.0.4/bin/hadoop"


echo"选择要清理的数据库实例:"
selectMYSQLin${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"showdatabases"|egrep-E"dc_[0-9]|ga_[0-9]"`
FAIL_BAK_DB=()
forDBin${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=()
forTABLEin`query_mysql"use${DB};showtables"`;do
#遍历需要清理的表
forINCLUDE_TABLEin${DELETE_TABLE_GA10[@]};do
#把appid替换成正则表达式
INCLUDE_TABLE=`echo${INCLUDE_TABLE}|sed's/^/\^/;s/$/\$/'`
if[[${TABLE}=~${INCLUDE_TABLE}]];then
TOTAL_ROW=`query_mysql"selectcount(*)from${DB}.${TABLE}"`
DELETE_ROW=0
DELETE_COLUMN=""
#如果是日表
if[[!`query_mysql"desc${DB}.${TABLE}StatiTime"`==""]];then
DELETE_COLUMN="StatiTime"
DELETE_ROW=`query_mysql"selectcount(*)from${DB}.${TABLE}whereStatiTime./backup/${DIR}/${DEL_TABLE}.sql
DEL_ROW=`query_mysql"setsql_log_bin=0;deletefrom${DB}.${DEL_TABLE}where${DEL_COLUMN}
    Tags:

    文章评论

    最 近 更 新
    热 点 排 行
    Js与CSS工具
    代码转换工具
    
    <