线上删除mysql数据脚本

一、需求

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

二、解决方法

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

三、脚本

读取配置文件

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

<code>MYSQL_HOME=/usr/local/mysql/bin/mysql</code>

<code>INCLUDE_MYSQL=(mysql4:</code><code>5507</code> <code>mysql5:</code><code>5508</code> <code>mysql6:</code><code>5509</code> <code>mysql7:</code><code>5510</code> <code>mysql7:</code><code>5511</code><code>)</code>

<code>EXCLUDE_DB_GA10=(dc_15 dc_17 dc_44 dc_49 dc_88 dc_183 dc_279 dc_490 dc_624 dc_643 dc_903 dc_906 dc_908 dc_954 dc_1099 dc_1100 dc_1167 dc_1214 dc_1463 dc_1464 dc_2444 dc_2445 dc_2695)</code>

<code>APPID_REG=[</code><code>0</code><code>-9A-Za-z]{</code><code>32</code><code>,</code><code>33</code><code>}</code>

<code>DELETE_TABLE_GA10=(dc_datacenter_cache dc_day_report_cache_basic dc_day_report_cache_income dc_day_report_cache_cumu dc_day_report_cache_channel ${APPID_REG_REG}_dc_distributed_everyday front_${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_day front_${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_30dayvalue front_${APPID_REG}_dc_player_30dayvalue ${APPID_REG}_dc_player_30day_arpu front_${APPID_REG}_dc_player_30day_arpu ${APPID_REG}_dc_whale_player_day ${APPID_REG}_dc_uid_retain_by_day front_${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_retain front_${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_month front_${APPID_REG}_dc_custom_retain_by_week front_${APPID_REG}_dc_custom_retain_by_month)</code>

<code>EXPIRE_LOGS_DAYS=</code><code>21</code>

1.2 backup.conf

<code>LOCAL_FILES=(/home/yaolihong/yao/backup/bak.tar.gz)</code>

<code>BUSINESS_TYPE=ga</code>

<code>CUSTOM_SUB_DIR=mysql</code>

2.backup_tools.sh脚本

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

<code>#check user</code>

<code>WHO_AM_I=`whoami`</code>

<code>if</code> <code>[ </code><code>"hadoop"</code> <code>== $WHO_AM_I ]; then</code>

<code> </code><code>echo </code><code>"Error: you should not use hadoop user to back files, use another user instead please..."</code>

<code>shellLocation=`dirname $</code><code>0</code><code>`</code>

<code>shellLocation=`cd </code><code>"$shellLocation"</code> <code>; pwd`</code>

<code>. $shellLocation/conf/backup.conf</code>

<code>HADOOP_CMD=</code><code>"$shellLocation/bin/hadoop-1.0.4/bin/hadoop"</code>

<code>#check </code><code>if</code> <code>empty</code>

<code>REQUIED_PARAMS=(JAVA_HOME LOCAL_FILES BUSINESS_TYPE CUSTOM_SUB_DIR)</code>

<code>for</code> <code>PARAM </code><code>in</code> <code>${REQUIED_PARAMS[@]}</code>

<code> </code><code>if</code> <code>[ </code><code>"X${!PARAM}"</code> <code>= </code><code>"X"</code> <code>]; then</code>

<code> </code><code>echo </code><code>"Error: ${PARAM} is not set..."</code>

<code>export JAVA_HOME</code>

<code>CURRENT_TIME=`date +%Y%m%d-%H_%M_%S`</code>

<code>CURRENT_LOG_FILE=$shellLocation/logs/backup.$CURRENT_TIME.log</code>

<code>CURRENT_YEAR=`date +%Y -d </code><code>'1 hours ago'</code><code>`</code>

<code>CURRENT_MONTH=`date +%m -d </code><code>'1 hours ago'</code><code>`</code>

<code>CURRENT_DAY=`date +%d -d </code><code>'1 hours ago'</code><code>`</code>

<code>LAST_HOUR=`date +%H -d </code><code>'1 hours ago'</code><code>`</code>

<code>HOUR_STR_FOR_HDFS=</code><code>"$CURRENT_YEAR/$CURRENT_MONTH/$CURRENT_DAY/$LAST_HOUR"</code>

<code>BACKUP_BASIC_PATH=</code><code>"/backup/$BUSINESS_TYPE/$CUSTOM_SUB_DIR"</code>

<code>BACKUP_DAY_PATH=</code><code>"$BACKUP_BASIC_PATH/$CURRENT_YEAR/$CURRENT_MONTH/$CURRENT_DAY"</code>

<code># 判断是否有传入路径，有则以传入的路径为准</code>

<code> </code><code>BACKUP_DAY_PATH=</code><code>"/backup/$BUSINESS_TYPE/$CUSTOM_SUB_DIR/$1"</code>

<code>#check </code><code>if</code> <code>hdfs dir exist and mkdir</code>

<code>$HADOOP_CMD fs -test -e $BACKUP_DAY_PATH</code>

<code> </code><code>$HADOOP_CMD fs -mkdir $BACKUP_DAY_PATH >> $CURRENT_LOG_FILE </code><code>2</code><code>>&</code><code>1</code>

<code>HOST_NAME=`/bin/hostname`</code>

<code>IS_ANY_FAILED=</code><code>"N"</code>

<code>for</code> <code>LOCAL_FILE </code><code>in</code> <code>${LOCAL_FILES[@]}</code>

<code> </code><code>#check </code><code>if</code> <code>local file exist</code>

<code> </code><code>if</code> <code>[ ! -f $LOCAL_FILE ]; then</code>

<code> </code><code>IS_ANY_FAILED=</code><code>"Y"</code>

<code> </code><code>echo </code><code>"Error: Local file not exist : $LOCAL_FILE"</code> <code>>> $CURRENT_LOG_FILE</code>

<code> </code><code>continue</code>

<code> </code><code>#put local file to hdfs</code>

<code> </code><code>LOCL_FILE_NAME=`/bin/basename $LOCAL_FILE`</code>

<code> </code><code>HDFS_FILE_NAME=${LOCL_FILE_NAME}_${HOST_NAME}_${CURRENT_TIME}</code>

<code> </code><code>$HADOOP_CMD fs -put $LOCAL_FILE $BACKUP_DAY_PATH/$HDFS_FILE_NAME >> $CURRENT_LOG_FILE </code><code>2</code><code>>&</code><code>1</code>

<code> </code><code>#</code><code>if</code> <code>put failed, we should not touch a done file</code>

<code> </code><code>echo </code><code>"Error: put file to hdfs failed : $LOCAL_FILE"</code> <code>>> $CURRENT_LOG_FILE</code>

<code>#</code><code>delete</code> <code>log </code><code>10</code> <code>days ago</code>

<code>find $shellLocation/logs/ -mtime +</code><code>10</code> <code>-</code><code>delete</code>

<code>if</code> <code>[ $IS_ANY_FAILED = </code><code>"Y"</code> <code>]; then</code>

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

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

<code># 加载配置文件</code>

<code>source ./conf/mysql.conf</code>

<code>source ./conf/backup.conf</code>

<code>SHELLLOCATION=`dirname $</code><code>0</code><code>`</code>

<code>SHELLLOCATION=`cd </code><code>"${SHELLLOCATION}"</code> <code>; pwd`</code>

<code>HADOOP_CMD=</code><code>"${SHELLLOCATION}/bin/hadoop-1.0.4/bin/hadoop"</code>

<code>echo </code><code>"选择要清理的数据库实例："</code>

<code>select MYSQL </code><code>in</code> <code>${INCLUDE_MYSQL[@]};</code>

<code> </code><code>break</code>

<code>echo </code><code>"您选择要清理的实例是：$MYSQL"</code>

<code>MYSQL_HOST=`echo $MYSQL | awk -F</code><code>':'</code> <code>'{print $1}'</code><code>`</code>

<code>MYSQL_PORT=`echo $MYSQL | awk -F</code><code>':'</code> <code>'{print $2}'</code><code>`</code>

<code>read -p </code><code>"数据库账号："</code> <code>MYSQL_USER</code>

<code>read -s -p </code><code>"数据库密码："</code> <code>MYSQL_PWD</code>

<code>TODAY=`date +</code><code>"%Y%m%d"</code><code>`</code>

<code>if</code> <code>[ ! -d ./logs/${MYSQL_HOST}/${MYSQL_PORT} ]; then</code>

<code> </code><code>mkdir -p ./logs/${MYSQL_HOST}/${MYSQL_PORT}</code>

<code># 定义一个查询数据库的方法</code>

<code>query_mysql()</code>

<code> </code><code>mysql -u${MYSQL_USER} -p${MYSQL_PWD} -h${MYSQL_HOST} -P${MYSQL_PORT} --</code><code>default</code><code>-character-</code><code>set</code><code>=utf8 -N -e </code><code>"$*"</code> <code>| sed </code><code>"s/^//;s/$//"</code>

<code># 定义一个打印日志的方法</code>

<code>printlog()</code>

<code> </code><code>echo -e $* >> ./logs/${MYSQL_HOST}/${MYSQL_PORT}/mysql.${TODAY}.log</code>

<code># 定义一个打印删除记录日志的方法</code>

<code>print_delete_log()</code>

<code> </code><code>echo -e $* >> ./logs/${MYSQL_HOST}/${MYSQL_PORT}/</code><code>delete</code><code>.${TODAY}.log</code>

<code>DB_SET=`query_mysql </code><code>"show databases"</code> <code>| egrep -E </code><code>"dc_[0-9]|ga_[0-9]"</code><code>`</code>

<code> </code><code># 如果是游戏分析</code><code>1.0</code><code>的数据库并且该数据库不是需要过滤的数据库，则开始清理</code>

<code> </code><code>if</code> <code>[[ </code><code>"${DB}"</code> <code>=~ ^dc_[</code><code>0</code><code>-</code><code>9</code><code>]*$ && ! </code><code>" ${EXCLUDE_DB_GA10[@]} "</code> <code>=~ </code><code>" ${DB} "</code> <code>]]; then</code>

<code> </code><code>DIR=data/${MYSQL_HOST}/${MYSQL_PORT}/${DB}/${TODAY}</code>

<code> </code><code>if</code> <code>[ ! -d ./backup/${DIR} ]; then</code>

<code> </code><code>mkdir -p ./backup/${DIR}</code>

<code> </code><code>printlog </code><code>"开始扫描$DB"</code>

<code> </code><code>WAIT_DELETE_TABLE=()</code>

<code> </code><code>for</code> <code>TABLE </code><code>in</code> <code>`query_mysql </code><code>"use ${DB}; show tables"</code><code>`; </code><code>do</code>

<code> </code><code># 遍历需要清理的表</code>

<code> </code><code>for</code> <code>INCLUDE_TABLE </code><code>in</code> <code>${DELETE_TABLE_GA10[@]}; </code><code>do</code>

<code> </code><code># 把appid替换成正则表达式</code>

<code> </code><code>INCLUDE_TABLE=`echo ${INCLUDE_TABLE} | sed </code><code>'s/^/\^/;s/$/\$/'</code><code>`</code>

<code> </code><code>if</code> <code>[[ ${TABLE} =~ ${INCLUDE_TABLE} ]]; then</code>

<code> </code><code>TOTAL_ROW=`query_mysql </code><code>"select count(*) from ${DB}.${TABLE}"</code><code>`</code>

<code> </code><code>DELETE_ROW=</code><code>0</code>

<code> </code><code>DELETE_COLUMN=</code><code>""</code>

<code> </code><code># 如果是日表</code>

<code> </code><code>if</code> <code>[[ ! `query_mysql </code><code>"desc ${DB}.${TABLE} StatiTime"</code><code>` == </code><code>""</code> <code>]]; then</code>

<code> </code><code>DELETE_COLUMN=</code><code>"StatiTime"</code>

<code> </code><code>DELETE_ROW=`query_mysql </code><code>"select count(*) from ${DB}.${TABLE} where StatiTime < unix_timestamp(date_add(now(), interval -6 month))"</code><code>`</code>

<code> </code><code>#如果是周表或者月表</code>

<code> </code><code>elif [[ ! `query_mysql </code><code>"desc ${DB}.${TABLE} EndDate"</code><code>` == </code><code>""</code> <code>]]; then</code>

<code> </code><code>DELETE_COLUMN=</code><code>"EndDate"</code>

<code> </code><code>DELETE_ROW=`query_mysql </code><code>"select count(*) from ${DB}.${TABLE} where EndDate < unix_timestamp(date_add(now(), interval -6 month))"</code><code>`</code>

<code> </code><code>if</code> <code>[ ${DELETE_ROW} -gt </code><code>0</code> <code>]; then</code>

<code> </code><code>WAIT_DELETE_TABLE+=(${TABLE}.${DELETE_COLUMN})</code>

<code> </code><code>printlog </code><code>"${DB}.${TABLE} \t ${TOTAL_ROW} \t ${DELETE_ROW}"</code>

<code> </code><code>TOTAL_ROW=</code><code>0</code>

<code> </code><code>break</code>

<code> </code><code>if</code> <code>[ ${#WAIT_DELETE_TABLE[@]} -gt </code><code>0</code> <code>]; then</code>

<code> </code><code>read -p </code><code>"是否清理历史数据：[Yes/No]"</code> <code>SURE</code>

<code> </code><code>print_delete_log </code><code>"开始清除${DB}"</code>

<code> </code><code>for</code> <code>WAIT_DELETE </code><code>in</code> <code>${WAIT_DELETE_TABLE[@]}; </code><code>do</code>

<code> </code><code>DEL_TABLE=`echo ${WAIT_DELETE} | awk -F</code><code>'.'</code> <code>'{print $1}'</code><code>`</code>

<code> </code><code>DEL_COLUMN=`echo ${WAIT_DELETE} | awk -F</code><code>'.'</code> <code>'{print $2}'</code><code>`</code>

<code> </code><code>mysqldump -u${MYSQL_USER} -p${MYSQL_PWD} -h${MYSQL_HOST} -P${MYSQL_PORT} ${DB} ${DEL_TABLE} -w</code><code>"${DEL_COLUMN} < unix_timestamp(date_add(now(), interval -6 month))"</code> <code>> ./backup/${DIR}/${DEL_TABLE}.sql</code>

<code> </code><code>DEL_ROW=`query_mysql </code><code>"set sql_log_bin=0; delete from ${DB}.${DEL_TABLE} where ${DEL_COLUMN} < unix_timestamp(date_add(now(), interval -6 month))"</code><code>`</code>

<code> </code><code>print_delete_log </code><code>"${DEL_TABLE} \t ${DEL_ROW}"</code>

<code> </code><code>echo </code><code>"开始打包导出的sql文件！"</code>

<code> </code><code>tar -zcf ./backup/bak.tar.gz ./backup/${DIR}/*.sql</code>

<code> </code><code>echo </code><code>"开始发送打包文件到hdfs，可能需要几分钟，请耐心等候！"</code>

<code> </code><code>sh backup_tools.sh </code><code>"${DIR}"</code>

<code> </code><code>if</code> <code>[[ $? -ne </code><code>0</code> <code>|| `${HADOOP_CMD} fs -ls /backup/${BUSINESS_TYPE}/${CUSTOM_SUB_DIR}/${DIR}/ | grep bak.tar.gz` = </code><code>""</code> <code>]]; then</code>

<code> </code><code>print_delete_log </code><code>"==========${DB}备份失败，请手动备份！=========="</code>

<code> </code><code>FAIL_BAK_DB+=(${MYSQL}.${DB})</code>

<code> </code><code>print_delete_log </code><code>"==========备份失败的数据库有：${FAIL_BAK_DB[@]}=========="</code>

<code> </code><code>unset WAIT_DELETE_TABLE</code>

3.清除日志脚本clean_data1.sh

<code>echo </code><code>"您选择要清理的实例是：${MYSQL}"</code>

<code>MYSQL_HOST=`echo ${MYSQL} | awk -F</code><code>':'</code> <code>'{print $1}'</code><code>`</code>

<code>MYSQL_PORT=`echo ${MYSQL} | awk -F</code><code>':'</code> <code>'{print $2}'</code><code>`</code>

<code>TARGET_DAY=`date -d</code><code>"-${EXPIRE_LOGS_DAYS} day"</code> <code>+%Y-%m-%d`</code>

<code>MTIME=`expr ${EXPIRE_LOGS_DAYS} - </code><code>1</code><code>`</code>

<code>TODAY=`date +%Y%m%d`</code>

<code>DIR=binlog/${MYSQL_HOST}/${MYSQL_PORT}/${TODAY}</code>

<code>if</code> <code>[ ! -d ./backup/${DIR} ]; then</code>

<code> </code><code>mkdir -p ./backup/${DIR}</code>

<code>rm -f ~/.ssh/known_hosts</code>

<code>for</code> <code>LOG </code><code>in</code> <code>`expect << EOF</code>

<code> </code><code>spawn ssh ${USER}@${MYSQL_HOST} </code><code>"find /data1/mysql/log${MYSQL_PORT}/mysql-bin.* -mtime +${MTIME} | xargs ls --full-time | grep -v ${TARGET_DAY}"</code>

<code> </code><code>expect </code><code>"no)?"</code>

<code> </code><code>expect </code><code>"password:"</code>

<code> </code><code>set</code> <code>timeout -</code><code>1</code>

<code> </code><code>expect eof</code>

<code>if</code> <code>[[ `echo ${LOG} | egrep -E </code><code>"mysql-bin\.[0-9]{1,}"</code><code>` != </code><code>""</code> <code>]]; then</code>

<code>expect << EOF</code>

<code> </code><code>spawn scp ${USER}@${MYSQL_HOST}:${LOG} backup/${DIR}</code>

<code>echo </code><code>"开始打包导出的sql文件！"</code>

<code>tar -zcf ./backup/bak.tar.gz ./backup/${DIR}/mysql-bin.*</code>

<code>echo </code><code>"开始发送打包文件到hdfs，可能需要几分钟，请耐心等候！"</code>

<code>sh backup_tools.sh </code><code>"${DIR}"</code>

<code>if</code> <code>[[ $? -ne </code><code>0</code> <code>|| `${HADOOP_CMD} fs -ls /backup/${BUSINESS_TYPE}/${CUSTOM_SUB_DIR}/${DIR}/ | grep bak.tar.gz` = </code><code>""</code> <code>]]; then</code>

<code> </code><code>echo </code><code>"==========备份失败，请手动备份！=========="</code>

<code> </code><code>read -p </code><code>"数据库账号："</code> <code>MYSQL_USER</code>

<code> </code><code>read -s -p </code><code>"数据库密码："</code> <code>MYSQL_PWD</code>

<code> </code><code>mysql -u${MYSQL_USER} -p${MYSQL_PWD} -h${MYSQL_HOST} -P${MYSQL_PORT} -e </code><code>"PURGE BINARY LOGS BEFORE CURRENT_DATE -INTERVAL ${EXPIRE_LOGS_DAYS} DAY"</code>

本文转自 zouqingyun 51CTO博客，原文链接：http://blog.51cto.com/zouqingyun/1739972，如需转载请自行联系原作者

线上删除mysql数据脚本

继续阅读

Linxu常用命令技巧汇总

ERROR 1 (HY000): Can't create/write to file '/tmp/#sql_4188_1.MYI' (Errcode: 28)

艰难安装LDAP,SSL认证

《Linux命令行与Shell脚本编程大全第2版.布卢姆》pdf

MySQL的4种隔离级别？出现问题

User Defined Hadoop DataType

XX系统实施过程问题总结

无组件上传图片到数据库中，最完整解决方案

【MySQL数据库】数据库索引事务1.索引2.事务

neo4j之cypher使用文档

Ambari介绍和架构原理

NOSQL安全攻击

mybatis_入门程序Mybatis入门

mysql使用source命令导入.sql文件

登录plsql 报错 the account is locked --用户被锁

SequoiaDB巨杉数据库C++驱动概述