mysql数据库批量备份shell脚本

功能简介:
1.通过mysqldump导出数据库备份,记录每个数据库导出时间
2.单独将所有mysql数据库数据导出到一个文件中且被压缩
3.其他单个数据库分别命令导出
4.可结合任务计划设置实现定期备份

#!/bin/bash
 
# Mysql vars
DB_USER="root"
DB_PASS="west.123"
MYSQLDUMP_OPT="--opt --events --ignore-table=mysql.events --default-character-set=utf8"
 
# Others vars
DATE=`date +%Y%m%d_%H%M`
BIN_DIR="/application/mysql-5.6.40/bin"
BACK_DIR="/home/backup/mysql"
 
# GET DB_NAMES
DB_NAMES=`${BIN_DIR}/mysql -u${DB_USER} -p${DB_PASS} -A -e "show databases;" | egrep -v "Database|schema"`
 
# Todo mysqldump
[ -d ${BACK_DIR} ] || mkdir -p ${BACK_DIR}
# mysqldump all databases
${BIN_DIR}/mysqldump ${MYSQLDUMP_OPT} -u${DB_USER} -p${DB_PASS} --all-databases | gzip > ${BACK_DIR}/all_databases_${DATE}.sql.gz
for DB_NAME in ${DB_NAMES}; do
	sleep 1
	DATE=`date +%Y%m%d_%H%M%S`
	${BIN_DIR}/mysqldump ${MYSQLDUMP_OPT} -u${DB_USER} -p${DB_PASS} ${DB_NAME} > ${BACK_DIR}/${DB_NAME}_${DATE}.sql
	# gzip backup
	# ${BIN_DIR}/mysqldump ${MYSQLDUMP_OPT} -u${DB_USER} -p${DB_PASS} ${DB_NAME} | gzip > ${BACK_DIR}/${DB_NAME}_${DATE}.sql.gz
	[ $? == 0 ] && echo "${DATE}:mysqldump ${DB_NAME} success." >>${BACK_DIR}/../mysqldump_backup.log || echo "${DATE}:mysqldump ${DB_NAME} err,please check." >>${BACK_DIR}/../mysqldump_backup.log
done
echo "==========All databases backups over==========" >>${BACK_DIR}/../mysqldump_backup.log

转载来自：《运维辞典 » mysql数据库批量备份shell脚本》