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 : 数据库名称!!