推荐下载
-
2020-05-12 浏览：740
-
2020-05-06 浏览：521
-
2020-05-08 浏览：510
-
2020-04-04 浏览：504
-
2020-05-12 浏览：502
MySQl数据库备份脚本
发布时间:2021-05-03
#!/bin/bash
#====================================
# MySQL定时备份
# MySQL timed backup
#====================================
DUMP=http://www.weixuecn.cn/usr/bin/mysqldump
IPADDR=127.0.0.1
PORT=3306
USER=typecho
PASSWD=569da4de2139687044bb
DATABASE=typecho
ROOT_DIR=http://www.weixuecn.cn/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、创建相关用户
2、在命令中加上 --skip-lock-tables 这个参数
${DUMP} -h${IPADDR} -P${PORT} -u${USER} -p${PASSWD} --databases --skip-lock-tables ${DATABASE} > ${OUT_DIR}/${DATE}/${DATABASE}-${DATE}.sql 分表备份脚本：如果表多建议使用分表备份。
MySQL分表备份脚本:https://www.cnblogs.com/outsrkem/p/11256950.html
