1、脚本

#!/bin/bash
#Script_Name: mysql_backup.sh
#Author: 蜷缩的小蜗牛
#Site:  www.along.party
#Version: V0.1
#Purpose: Mysql Backup 
#Create_Time: 2017/07/25


DB_NAME=$1
BASE_PATH=`dirname $0`
log_file='mysql_backup.log'

LOG()
{    
    time=`date "+%Y-%m-%d %H:%M:%S"`    
    echo "[${time}] $*"    
    echo "[${time}] $*">>${BASE_PATH}/${log_file}    
}    

WARN_LOG()    
{    
    time=`date "+%Y-%m-%d %H:%M:%S"`    
    echo -e "\033[33m[${time}]WARNING: $*\033[0m"    
    echo "[${time}] $*">>${BASE_PATH}/${log_file}    
}    
    
ERR_LOG()    
{    
    time=`date "+%Y-%m-%d %H:%M:%S"`    
    echo -e "\033[31m[${time}]ERROR: $*\033[0m"    
    echo "[${time}] $*">>${BASE_PATH}/${log_file}    
}    
    
SUCC_LOG()    
{    
    time=`date "+%Y-%m-%d %H:%M:%S"`    
    echo -e "\033[32m[${time}]SUCCESS: $*\033[0m"    
    echo "[${time}] $*">>${BASE_PATH}/${log_file}    
}

HELP()
{
    echo "USE: bash ${0} ALL | DATABASE_NAME"
    echo -e "\tALL : 备份所有数据库！"
    echo -e "\tDATABASE_NAME : 数据库名称！！"
}


##全库备份
ALL_BACKUP()
{        
    startTime=`date +%s`
    LOG "全库备份开始"
    LOG "mysqldump -u${DB_USER} -p -h${DB_HOST} -P${DB_PORT}    --all-databases --single-transaction  >${BAK_PATH}/${DB_HOST}-ALL_DATABASE-${NOW_TIME}"
    
    mysqldump -u${DB_USER} -p${DB_PASS} -h${DB_HOST} -P${DB_PORT}    --all-databases --single-transaction \
    >${BAK_PATH}/${DB_HOST}-ALL_DATABASE-${NOW_TIME} 2>.warn.log
    
    if [ $? -eq 0 ];then
       SUCC_LOG
       cd ${BAK_PATH}
       tar -zcvf ${DB_HOST}-ALL_DATABASE-${NOW_TIME}.tar.gz ${DB_HOST}-ALL_DATABASE-${NOW_TIME}
       endTime=`date +%s`
       ((outTime=(${endTime}-${startTime})))
       LOG "全库备份成功,用时[$outTime]秒"
       rm -rf ALL_DATABASE-${NOW_TIME}
    else
       ERR_LOG "全库备份异常，请查看${log_file}日志文件"
       ERR_LOG `head -10 .warn.log`
       rm -rf .warn.log
    fi
    
}

##备份单个库
DB_BACKUP()
{
    startTime=`date +%s`

    LOG "数据库[${DB_NAME}]备份开始"
    LOG "mysqldump -u${DB_USER} -p -h${DB_HOST} -P${DB_PORT} ${DB_NAME}   >${BAK_PATH}/${DB_HOST}-${DB_NAME}-${NOW_TIME}"
    mysqldump -u${DB_USER} -p${DB_PASS} -h${DB_HOST} -P${DB_PORT} ${DB_NAME}   >${BAK_PATH}/${DB_HOST}-${DB_NAME}-${NOW_TIME}
    if [ $? -eq 0 ];then
       SUCC_LOG
       cd ${BAK_PATH}
       tar -zcvf ${DB_HOST}-${DB_NAME}-${NOW_TIME}.tar.gz ${DB_HOST}-${DB_NAME}-${NOW_TIME}
       endTime=`date +%s`
       ((outTime=(${endTime}-${startTime})))
       LOG "数据库[${DB_NAME}]备份成功,用时[$outTime]秒"
       rm -rf ${DB_NAME}-${NOW_TIME}
    else
       ERR_LOG "数据库[${DB_NAME}]备份失败"
       ERR_LOG `head -10 .warn.log`
       rm -rf .warn.log
    fi
    
}



main()
{
    if [ -f "${BASE_PATH}/config.ini" ];then
        source ${BASE_PATH}/config.ini
    else
        ERR_LOG "配置文件${BASE_PATH}/config.ini不存在"
        exit 1
    fi
    
    
    
    if [ -z $DB_NAME ];then
        HELP
    elif [ $DB_NAME == "ALL" ];then
        ALL_BACKUP

    else
        DB_BACKUP
    fi
}

main

 

2、配置文件

配置文件config.ini放在脚本同级目录

DB_HOST='127.0.0.1'
DB_PORT='3306'
DB_USER='backup'
DB_PASS='xxxxxx'
NOW_TIME=`date +%Y%m%d%H%M`
BAK_PATH='/www/backup'

 

3、创建备份用户

GRANT REPLICATION  SELECT, RELOAD, SHOW DATABASES, LOCK TABLES  ON *.* TO backup@'localhost' IDENTIFIED BY 'xxxxx';

 

4、使用方法

USE: bash mysql_backup.sh ALL | DATABASE_NAME
    ALL : 备份所有数据库！
    DATABASE_NAME : 数据库名称！！

 

image