Mysql备份脚本

 

1、脚本

#!/bin/bash#Script_Name: mysql_backup.sh#Author: 蜷缩的小蜗牛#Site:  www.along.party#Version: V0.1#Purpose: Mysql Backup #Create_Time: 2017/07/25DB_NAME=$1BASE_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 : 数据库名称！！