MySQl数据库备份脚本

发布时间:2019-06-22 22:00:06发布者:Mr.Zhang阅读(437)

#!/bin/bash
#====================================
#   MySQL定时备份
#   MySQL timed backup
#====================================

DUMP=/usr/bin/mysqldump
IPADDR=127.0.0.1
PORT=3306
USER=typecho
PASSWD=569da4de2139687044bb
DATABASE=typecho
ROOT_DIR=/data


#----------------------
set -u
[ -x $DUMP ] || exit 100
OUT_DIR=${ROOT_DIR}/mysql/mysql-new
TAR_DIR=${ROOT_DIR}/mysql/mysql-bak-list
LOG_DIR=${ROOT_DIR}/mysql/mysql-log
LOG_DIR_file=$LOG_DIR/mysql-log
DATE=`date +%Y-%m-%d`
TAR_BAK="${DATABASE}-bak-$DATE.tar.gz"
BAK_NAME=${DATABASE}-bak-$(date -d '-5 days' "+%Y-%m-%d").tar.gz
[ -d $OUT_DIR ] || mkdir -p $OUT_DIR
[ -d $TAR_DIR ] || mkdir -p $TAR_DIR
[ -d $LOG_DIR ] || mkdir -p $LOG_DIR
cd ${OUT_DIR} ||exit 200
[[ -n "${OUT_DIR}" ]] && rm -rf ${OUT_DIR}/* || echo "Failed to delete buffer file" >> ${LOG_DIR_file}
mkdir -p ${OUT_DIR}/${DATE}
${DUMP} -h${IPADDR} -P${PORT} -u${USER} -p${PASSWD} --databases ${DATABASE} |gzip > ${OUT_DIR}/${DATE}/${DATABASE}-${DATE}.sql.gz
if [ $? -eq 0 ];then
    echo -n "[$(date +%Y-%m-%d\ %H:%M:%S)] The backup successful," >> $LOG_DIR_file
    
#压缩
    cd ${OUT_DIR}
    tar -zcf ${TAR_BAK} ${DATE} &>/dev/null && echo -n "Backup file packed successfully," >> ${LOG_DIR_file} || echo -n " Packaging failure," >> ${LOG_DIR_file}
    mv ${OUT_DIR}/${TAR_BAK} ${TAR_DIR} && echo -n "Backup file packaging successfully moved to data directory successfully," >> ${LOG_DIR_file} || echo -n "Move failure," >> ${LOG_DIR_file}

#删除之前的备份
    rm -rf ${TAR_DIR}/${BAK_NAME} && echo "Backup succeeded 5 days before deletion!" >> ${LOG_DIR_file} || echo "Backup failed 5 days before deletion。" >> ${LOG_DIR_file}
else
    echo "[$(date +%Y-%m-%d_%H:%M:%S)] Backup failure!" >> ${LOG_DIR_file}
fi

注:

如果备份报错
mysqldump: Got error: 1449: "The user specified as a definer ('abc'@'%') does not exist" when using LOCK TABLES
因为mysqldump命令默认在导出时是要锁定表的,所以解决方式有两个。
1、创建相关用户

grant all on *.* to abc@'%' identified by '123456';

2、在命令中加上 --skip-lock-tables 这个参数

${DUMP} -h${IPADDR} -P${PORT} -u${USER} -p${PASSWD} --databases --skip-lock-tables  ${DATABASE} > ${OUT_DIR}/${DATE}/${DATABASE}-${DATE}.sql




本文转自博客园,原文地址:https://www.cnblogs.com/outsrkem/p/11068672.html