一、需求

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

二、解决方法

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

三、脚本

读取配置文件

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

MYSQL_HOME=/usr/local/mysql/bin/mysqlINCLUDE_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_25LOCAL_FILES=(/home/yaolihong/yao/backup/bak.tar.gz)BUSINESS_TYPE=gaCUSTOM_SUB_DIR=mysql

2.backup_tools.sh脚本

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

#!/bin/sh#set-x#checkuserWHO_AM_I=`whoami`if["hadoop"==$WHO_AM_I];thenecho"Error:youshouldnotusehadoopusertobackfiles,useanotheruserinsteadplease..."exit1fishellLocation=`dirname$0`shellLocation=`cd"$shellLocation";pwd`.$shellLocation/conf/backup.confHADOOP_CMD="$shellLocation/bin/hadoop-1.0.4/bin/hadoop"#checkifemptyREQUIED_PARAMS=(JAVA_HOMELOCAL_FILESBUSINESS_TYPECUSTOM_SUB_DIR)forPARAMin${REQUIED_PARAMS[@]}doif["X${!PARAM}"="X"];thenecho"Error:${PARAM}isnotset..."exit1fidoneexportJAVA_HOMECURRENT_TIME=`date+%Y%m%d-%H_%M_%S`CURRENT_LOG_FILE=$shellLocation/logs/backup.$CURRENT_TIME.log#gettimeCURRENT_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];thenBACKUP_DAY_PATH="/backup/$BUSINESS_TYPE/$CUSTOM_SUB_DIR/$1"fi#checkifhdfsdirexistandmkdir$HADOOP_CMDfs-test-e$BACKUP_DAY_PATHif[$?-ne0];then$HADOOP_CMDfs-mkdir$BACKUP_DAY_PATH>>$CURRENT_LOG_FILE2>&1fiHOST_NAME=`/bin/hostname`IS_ANY_FAILED="N"forLOCAL_FILEin${LOCAL_FILES[@]}do#checkiflocalfileexistif[!-f$LOCAL_FILE];thenIS_ANY_FAILED="Y"echo"Error:Localfilenotexist:$LOCAL_FILE">>$CURRENT_LOG_FILEcontinuefi#putlocalfiletohdfsLOCL_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,weshouldnottouchadonefileif[$?-ne0];thenIS_ANY_FAILED="Y"echo"Error:putfiletohdfsfailed:$LOCAL_FILE">>$CURRENT_LOG_FILEcontinuefidone#deletelog10daysagofind$shellLocation/logs/-mtime+10-deleteif[$IS_ANY_FAILED="Y"];thenexit1fi#set+x

3.清除数据库脚本clean_data0.sh

#!/bin/bash#加载配置文件source./conf/mysql.confsource./conf/backup.confSHELLLOCATION=`dirname$0`SHELLLOCATION=`cd"${SHELLLOCATION}";pwd`HADOOP_CMD="${SHELLLOCATION}/bin/hadoop-1.0.4/bin/hadoop"echo"选择要清理的数据库实例："selectMYSQLin${INCLUDE_MYSQL[@]};dobreakdoneecho"您选择要清理的实例是：$MYSQL"MYSQL_HOST=`echo$MYSQL|awk-F':''{print$1}'`MYSQL_PORT=`echo$MYSQL|awk-F':''{print$2}'`read-p"数据库账号："MYSQL_USERread-s-p"数据库密码："MYSQL_PWDTODAY=`date+"%Y%m%d"`if[!-d./logs/${MYSQL_HOST}/${MYSQL_PORT}];thenmkdir-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}"]];thenDIR=data/${MYSQL_HOST}/${MYSQL_PORT}/${DB}/${TODAY}if[!-d./backup/${DIR}];thenmkdir-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}]];thenTOTAL_ROW=`query_mysql"selectcount(*)from${DB}.${TABLE}"`DELETE_ROW=0DELETE_COLUMN=""#如果是日表if[[!`query_mysql"desc${DB}.${TABLE}StatiTime"`==""]];thenDELETE_COLUMN="StatiTime"DELETE_ROW=`query_mysql"selectcount(*)from${DB}.${TABLE}whereStatiTime./backup/${DIR}/${DEL_TABLE}.sqlDEL_ROW=`query_mysql"setsql_log_bin=0;deletefrom${DB}.${DEL_TABLE}where${DEL_COLUMN}