MySQl数据库备份脚本

# 创建一个mysql用户，用来备份数据库
CREATE USER 'backupuser'@'localhost' IDENTIFIED BY 'backupuser';
GRANT SELECT, LOCK TABLES, SHOW VIEW, PROCESS  ON *.* TO 'backupuser'@'%localhost';

CREATE USER 'backupuser'@'%' IDENTIFIED BY 'backupuser';
GRANT SELECT, LOCK TABLES, SHOW VIEW, PROCESS ON *.* TO 'backupuser'@'%';

FLUSH PRIVILEGES;

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

DUMP=/usr/bin/mysqldump
IPADDR=127.0.0.1
PORT=3306
USER=backupuser
PASSWD=backupuser
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

注：

如果要INSERT语句分开，加入如下参数

--skip-extended-insert

如果备份报错
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

分表备份脚本：

如果表多建议使用分表备份。
MySQL分表备份脚本:https://www.cnblogs.com/outsrkem/p/11256950.html