线上mysql数据库备份

#!/bin/bash
#=============================================================================
# FileName: MySQLBackup.sh
# Desc: MySQL Databases backup tool.
# Author: chenzm
# Email:969113439@qq.com
# HomePage: http://chenzm0592.blog.51cto.com/
# Version: 0.0.1
# LastChange: 2013-11-15 09:16:39
# History:
#=============================================================================

cd /opt/MySQLBackup
. /etc/profile

USERNAME='root'
PASSWORD='password'

U_ID=0
G_ID=

# 设置mysqldump参数
MYSQLDUMP="mysqldump --opt --default-character-set=utf8 --skip-lock-tables -C"

# 设置压缩参数 从stdin读取
COMPRESS="gzip -9"

# 设置压缩文件的后缀名, 请与压缩参数相对应
COMPRESS_EXT="gz"

DATE=$(date +%Y-%m-%d)
mkdir -p logs/

# 定义日志输入
function log() {
    #echo -ne ${1}
    echo -ne ${1} >>logs/backup-${DATE}.log
}

# 计算字符时，分， 秒
function charDate() {
    DATE=$1
    if [ ${DATE} -lt 60  ]; then
        echo "${DATE}秒"
    elif [ ${DATE} -ge 60 ] && [ ${DATE} -lt 3600 ]; then
        echo "$((DATE/60))分$((DATE%60))秒"
    else
        M=$((DATE%3600))
        echo "$((DATE/3600))小时$((M/60))分$((M%60))秒"
    fi
}

TIME_HOST_START=$(date +%s)
retry=0
while :
do
    data=$(mysql -u${USERNAME} -p${PASSWORD} -h IP -e 'SHOW DATABASES' 2>&1)
    if [ $? == 0 ]; then
        break
    elif [ ${retry} -ge 5 ]; then
        log "连接到数据库失败...退出.\n"
        exit 1
    fi
    ((retry++))
done

DBS=$(echo ${data} | awk 'BEGIN{split("test|information_schema",arr,"|"); for (i in arr) b[arr[i]]} END{for(i=2;i<=NF;i++) if($i in b==0) print $i}')
DB_TOTAL=$(echo ${DBS} | awk '{print NF}')

log "总计数据库数量: ${DB_TOTAL}\n"
log "正在备份,请稍后....\n"

mkdir -p /databak/IP/db/${DATE}
for DB in ${DBS}
do
    TIME_DB_START=$(date +%s)
    log "正在导出: ${DB}..."
    ${MYSQLDUMP} -u${USERNAME} -p${PASSWORD} -h IP ${DB} 2>>logs/error.log | ${COMPRESS} > /databak/IP/db/${DATE}/${DB}.sql.${COMPRESS_EXT}

    # 使用$PIPESTATUS获取管道之前的exit status
    if [ ${PIPESTATUS[0]} != 0  ]; then
        log "失败"
    else
        ((OK++))
        log "成功"
    fi
    TIME_DB_END=$(date +%s)
    ELAPSED=$((TIME_DB_END - TIME_DB_START))
    log " - 耗时: $(charDate ${ELAPSED})\n"
done
#chown -R ${U_ID}:${G_ID} /db/ip/${DATE}

TIME_HOST_END=$(date +%s)
ELAPSED=$((TIME_HOST_END - TIME_HOST_START))
log "总计: ${DB_TOTAL} 成功: ${OK} 失败: $((DB_TOTAL-OK)) - 耗时: $(charDate ${ELAPSED})\n"
log "$(printf '%30s\n' | tr ' ' -) $(date "+%Y-%m-%d %H:%M:%S") $(printf '%30s\n' | tr ' ' -)\n"