增量dump mysql的shell脚本

#! /bin/sh# sh dump.sh &DBSERVER=192.168.1.239USER=xxxxPASS=xxxxMYSQLDUMPBIN_PATH='/software/mysql-5.6/bin/mysqldump'#DBSERVER=xxxxxx#USER=xxxx#PASS=xxxx#MYSQLDUMPBIN_PATH='/usr/bin/mysqldump'OUTPUT_DIR=dataOLD_DIR=old_dataLOG_FILE=log_dump.txt#默认的数据库normal_dbs=('open_account' 'open_agent' 'open_epay' 'open_operate' 'open_web')log_message(){    echo $* >> ${LOG_FILE}    #echo $*}dump_normal_db(){DATABASE=$1FILE=${DATABASE}.`date +"%Y%m%d"`.sqlbegin_time=`date +%s`log_message "Start dumpping ${DATABASE} to ${FILE}.gz "  "`date +'%Y-%m-%d %H:%M:%S'`" ".............................."    #use this command for a database server on remote.    ${MYSQLDUMPBIN_PATH} --opt --protocol=TCP --user=${USER} --password=${PASS} --host=${DBSERVER} ${DATABASE} > ${FILE}    gzip $FILE    #log_message "${FILE}.gz was created."    #ls -al ${FILE}.gz    finish_time=`date +%s`    seconds_cost=$[ finish_time - begin_time ]    log_message "Finish dumpping "  "`date +'%Y-%m-%d %H:%M:%S'`" " total cost ${seconds_cost} seconds ......................."}dump_open_sms(){DATABASE="open_sms"FILE=${DATABASE}.`date +"%Y%m%d"`.sqllog_message "Start dumpping ${DATABASE} to ${FILE}.gz "  "`date +'%Y-%m-%d %H:%M:%S'`" ".............................."IGNORED_TABLES_STRING=""begin_time=`date +%s`for((i=-366;i<-5;i++))dodate_end_str="20170715"date_str=`date -d "${i} day" "+%Y%m%d"`#如果定义在for外面的话，会有语法错误，fuckif [ "${date_str}" \< "${date_end_str}" ] ; then#log_message "ignore date:" ${date_str}table_name="sms_log_"${date_str}#log_message ${table_name}IGNORED_TABLES_STRING+=" --ignore-table=${DATABASE}.${table_name} "table_name="flow_log_"${date_str}IGNORED_TABLES_STRING+=" --ignore-table=${DATABASE}.${table_name} "table_name="sms_to_"${date_str}IGNORED_TABLES_STRING+=" --ignore-table=${DATABASE}.${table_name} "table_name="sms_submit_"${date_str}IGNORED_TABLES_STRING+=" --ignore-table=${DATABASE}.${table_name} "table_name="push_smsstatus_"${date_str}IGNORED_TABLES_STRING+=" --ignore-table=${DATABASE}.${table_name} "        #log_message "After is :"${IGNORED_TABLES_STRING}else     breakfidoneIGNORED_TABLES_STRING+=" --ignore-table=${DATABASE}.smslog "IGNORED_TABLES_STRING+=" --ignore-table=${DATABASE}.smsto "IGNORED_TABLES_STRING+=" --ignore-table=${DATABASE}.phone139 "IGNORED_TABLES_STRING+=" --ignore-table=${DATABASE}.sms_sign_ignore139_suffix "IGNORED_TABLES_STRING+=" --ignore-table=${DATABASE}.sms_status_139_log "IGNORED_TABLES_STRING+=" --ignore-table=${DATABASE}.sms_status_139_log_bak "IGNORED_TABLES_STRING+=" --ignore-table=${DATABASE}.mo_sms_fail_log "IGNORED_TABLES_STRING+=" --ignore-table=${DATABASE}.mo_sms_fail_log_bk "IGNORED_TABLES_STRING+=" --ignore-table=${DATABASE}.mo_sms_log "#log_message "Ignore tables: "${IGNORED_TABLES_STRING}${MYSQLDUMPBIN_PATH} --opt --protocol=TCP --user=${USER} --password=${PASS} --host=${DBSERVER} ${DATABASE} ${IGNORED_TABLES_STRING} > ${FILE}gzip $FILEfinish_time=`date +%s`    seconds_cost=$[ finish_time - begin_time ]log_message "Finish dumpping "  "`date +'%Y-%m-%d %H:%M:%S'`" " total cost ${seconds_cost} seconds ......................."}dump_open_billing(){DATABASE="open_billing"IGNORED_TABLES_STRING=""FILE=${DATABASE}.`date +"%Y%m%d"`.sqllog_message "Start dumpping ${DATABASE} to ${FILE}.gz "  "`date +'%Y-%m-%d %H:%M:%S'`" ".............................."begin_time=`date +%s`for((i=-366;i<-5;i++))dodate_end_str="20170715" #如果定义在for外面的话，会有语法错误，fuckdate_str=`date -d "${i} day" "+%Y%m%d"`#echo "current date:" ${date_str} "VS" ${date_end_str}if [ "${date_str}" \< "${date_end_str}" ] ; thentable_name="billing_log_"${date_str}IGNORED_TABLES_STRING+=" --ignore-table=${DATABASE}.${table_name} "table_name="cdr_"${date_str}IGNORED_TABLES_STRING+=" --ignore-table=${DATABASE}.${table_name} "#log_message ${IGNORED_TABLES_STRING}else     breakfidoneIGNORED_TABLES_STRING+=" --ignore-table=${DATABASE}.cdr_history "IGNORED_TABLES_STRING+=" --ignore-table=${DATABASE}.cdr_daily_use_total "IGNORED_TABLES_STRING+=" --ignore-table=${DATABASE}.billing_log "IGNORED_TABLES_STRING+=" --ignore-table=${DATABASE}.ptest "IGNORED_TABLES_STRING+=" --ignore-table=${DATABASE}.callback_fail_log "IGNORED_TABLES_STRING+=" --ignore-table=${DATABASE}.audio_record "IGNORED_TABLES_STRING+=" --ignore-table=${DATABASE}.voice_verify_log "#log_message "Ignore tables: "${IGNORED_TABLES_STRING}${MYSQLDUMPBIN_PATH} --opt --protocol=TCP --user=${USER} --password=${PASS} --host=${DBSERVER} ${DATABASE} ${IGNORED_TABLES_STRING} > ${FILE}gzip $FILEfinish_time=`date +%s`    seconds_cost=$[ finish_time - begin_time ]log_message "Finish dumpping "  "`date +'%Y-%m-%d %H:%M:%S'`" " total cost ${seconds_cost} seconds ......................."}while truedo    if [ ! -d "$OLD_DIR" ]; then        mkdir ${OLD_DIR}    fi    if [ ! -d "$OUTPUT_DIR" ]; then        mkdir ${OUTPUT_DIR}    fi    rm -fr ${OLD_DIR}/*.sql.gz    mv ${OUTPUT_DIR}/*.sql.gz ${OLD_DIR}    for db in "${normal_dbs[@]}"    do        dump_normal_db $db    done    dump_open_sms    dump_open_billing    mv *.sql.gz ${OUTPUT_DIR}    sleep 86400done