线上mysql备份_线上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"