增量dump mysql的shell脚本

#! /bin/sh
# sh dump.sh &

DBSERVER=192.168.1.239
USER=xxxx
PASS=xxxx
MYSQLDUMPBIN_PATH='/software/mysql-5.6/bin/mysqldump'


#DBSERVER=xxxxxx
#USER=xxxx
#PASS=xxxx
#MYSQLDUMPBIN_PATH='/usr/bin/mysqldump'


OUTPUT_DIR=data
OLD_DIR=old_data
LOG_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=$1
	FILE=${DATABASE}.`date +"%Y%m%d"`.sql
	begin_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"`.sql
	log_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++))
	do
		date_end_str="20170715"
		date_str=`date -d "${i} day" "+%Y%m%d"`
		#如果定义在for外面的话，会有语法错误，fuck
		if [ "${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
		     break	
		fi
	done

	
	IGNORED_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 $FILE
	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_billing()
{
	DATABASE="open_billing"
	IGNORED_TABLES_STRING=""
	FILE=${DATABASE}.`date +"%Y%m%d"`.sql
	log_message "Start dumpping ${DATABASE} to ${FILE}.gz "  "`date +'%Y-%m-%d %H:%M:%S'`" ".............................."
	begin_time=`date +%s`

	for((i=-366;i<-5;i++))
	do
		date_end_str="20170715" #如果定义在for外面的话，会有语法错误，fuck
		date_str=`date -d "${i} day" "+%Y%m%d"`
		#echo "current date:" ${date_str} "VS" ${date_end_str}

		if [ "${date_str}" \< "${date_end_str}" ] ; then
			table_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
		     break	
		fi
	done

	IGNORED_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 $FILE
	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 ......................."
}

while true
do
    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 86400
done