MySQl数据库备份脚本

#!/bin/bash #==================================== # MySQL定时备份 # MySQL timed backup #==================================== DUMP=/usr/bin/mysqldump IPADDR=127.0.0.1 PORT=3306...
#!/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
  • 发表于 2019-06-23 10:40
  • 阅读 ( 116 )
  • 分类:网络文章

你可能感兴趣的文章

相关问题

条评论

请先 登录 后评论
不写代码的码农
小编

篇文章

作家榜 »

  1. 小编 文章
返回顶部
部分文章转自于网络,若有侵权请联系我们删除