MySQl資料庫備份腳本

#!/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