免费分享mysql数据知识大全 mysql数据库备份与恢复的常用方法

• 需求背景
• 备份和还原的拓扑图
• 逻辑备份的shell脚本
• 逻辑还原的shell脚本

需求背景

数据库备份的重要性就不再赘述了。前段时间，我们的一个数据库，由于一些网络安全组的配置错误，导致被黑客攻击黑掉，数据表全部被删掉了，如果想要数据就要给黑客指定的账号充比特币，更详细的信息可以参考我之前分享的一篇文章《MySQL被黑掉的一次记录》。

试想一下，如果被黑掉的数据库没有备份，我们该怎么办？不就彻底凉凉了。所以，数据库的备份很重要。

数据库的备份和还原是数据库日常维护工作中最为常见的需求，对于MySQL数据库的备份和还原，你真正的了解了吗？接下来我们一起来看下备份和还原的一些知识点吧。

备份和还原的拓扑图

总结了一些MySQL数据库备份的方式，以及各个备份方式的具体参数，详情见下图：(如果想获取原文件，可以加”coder-home”留言即可。)

mysql备份与还原

逻辑备份的shell脚本

这里分享一个我平时用到的数据库逻辑备份脚本。采用的是MySQL原始自带的逻辑备份命令mysqldump。它的效率和第三方插件xtrabackup相比虽然差些，但是对于一些常见的备份需求，它还是可以满足大多数备份需求的。

在下面的这个脚本中，对数据库进行了两次备份。

• 一次是分库备份：即一个数据库(schema)一个备份文件。
• 另外一次是分库分表备份：即一个表一个备份文件，这样的备份方式，对于以后还原的时候只还原数据库下面的某一个表十分的方便，而不用在整个数据库的备份文件中摘出来对应的表。

大家可以根据自己的需求，自行修改，删除冗余的备份逻辑，或者增加自己的备份逻辑。

#!/bin/sh

#--------------------------------------------------
# 
# CreatDate: 2020/03/18 
# Desc:      备份数据库脚本
#--------------------------------------------------

BACKUP_DIR=$1
HOSTNAME=$2
USERNAME=$3
USERPASSWORD=$4
MAIL_RECIPIENT=$5

if [[ ! -d $BACKUP_DIR ]]; then
	echo "存放备份文件的目录不存在！"
	exit 5
elif [[ $HOSTNAME"x" == "x" ]]; then
	echo "数据所在主机名称或IP不能为空！"
	exit 10
elif [[ $USERNAME"x" == "x" ]]; then
	echo "备份数据库的用户名称不能为空！"
	exit 15
elif [[ $USERPASSWORD"x" == "x" ]]; then
	echo "备份数据库的用户密码不能为空！"
	exit 20
elif [[ $MAIL_RECIPIENT"x" == "x" ]]; then
	echo "收件人邮箱地址不能为空！"
	exit 25
fi


LOGINCOMMAND="mysql -h$HOSTNAME -u$USERNAME -p$USERPASSWORD"
DUMPCOMMAND="mysqldump -h$HOSTNAME -u$USERNAME -p$USERPASSWORD"

# 排除mysql,sys和以_schema结尾的数据库
DATABASE_NAME_LIST=`$LOGINCOMMAND -e "show databases;" |egrep -v "*_schema|mysql|sys" | sed '1d' `


if [[ ! -d $BACKUP_DIR/$(date +%Y%m%d) ]]; then
	echo "备份日期目录不存在，需要创建该日期目录"
        mkdir -p $BACKUP_DIR/$(date +%Y%m%d)
fi

BACKUP_LOG_FILE=$BACKUP_DIR/$(date +%Y%m%d)/db_backup_$(date +%Y%m%d).log
if [[ ! -f $BACKUP_LOG_FILE ]]; then
	touch $BACKUP_LOG_FILE
fi

echo "======================分库备份开始于：$(date '+%Y-%m-%d %H:%M:%S')=======================" >> $BACKUP_LOG_FILE

# 分库备份
for dbname in $DATABASE_NAME_LIST
do
	echo "备份$dbname数据库开始于：$(date '+%Y-%m-%d %H:%M:%S')" >> $BACKUP_LOG_FILE
	
	$DUMPCOMMAND --flush-privileges 
			--master-data=2 
			--flush-logs 
			--single-transaction 
			--triggers 
			--routines 
			--events 
			--databases $dbname | gzip > $BACKUP_DIR/$(date +%Y%m%d)/${dbname}.$(date +%Y%m%d%H%M%S).sql.gz
	
	if [[ $# -ne 0 ]]; then
		echo "ERROR: 备份$dbname数据库失败于：$(date '+%Y-%m-%d %H:%M:%S')" >> $BACKUP_LOG_FILE
		continue
	else
		echo "备份$dbname数据库结束于：$(date '+%Y-%m-%d %H:%M:%S')" >> $BACKUP_LOG_FILE
	fi
done
echo "======================分库备份结束于：$(date '+%Y-%m-%d %H:%M:%S')=======================" >> $BACKUP_LOG_FILE


echo "======================分库分表备份开始于：$(date '+%Y-%m-%d %H:%M:%S')=======================" >> $BACKUP_LOG_FILE
# 分库分表备份
for dbname in $DATABASE_NAME_LIST
do
	TABLE_NAME_LIST=`$LOGINCOMMAND -e "show tables from $dbname;" | sed '1d' `
	if [[ ! -d $BACKUP_DIR/$(date +%Y%m%d)/$dbname/ ]]; then
		echo "要备份的数据库名称目录不存在，需要创建该目录" >> $BACKUP_LOG_FILE
		mkdir -p $BACKUP_DIR/$(date +%Y%m%d)/$dbname
	fi
	for tabname in $TABLE_NAME_LIST
	do
		$DUMPCOMMAND --flush-privileges 
                        --master-data=2 
                        --single-transaction 
                        --triggers 
                        --routines 
                        --events 
                        $dbname $tabname | gzip > $BACKUP_DIR/$(date +%Y%m%d)/$dbname/${dbname}.${tabname}.$(date +%Y%m%d%H%M%S).sql.gz
		
		if [[ $# -ne 0 ]]; then
			echo "ERROR: 备份$dbname.$tabname的时候失败于：$(date '+%Y-%m-%d %H:%M:%S')" >> $BACKUP_LOG_FILE
			continue
		fi
	done
done
echo "======================分库分表备份结束于：$(date '+%Y-%m-%d %H:%M:%S')=======================" >> $BACKUP_LOG_FILE


# 获取日志文件的名称和目录
BACKUP_LOG_FILE_BASENAME=`echo $(basename ${BACKUP_LOG_FILE})`
BACKUP_LOG_FILE_DIRNAME=`echo $(dirname ${BACKUP_LOG_FILE})`

# 对生成的日志文件打包压缩，便于以附件的方式发送邮件。
tar -zcvf ${BACKUP_LOG_FILE_BASENAME}.tar.gz -C $BACKUP_LOG_FILE_DIRNAME $BACKUP_LOG_FILE_BASENAME


MAIL_SUBJECT_INFO="INFO: DB backup successfully"
MAIL_CONTENT_INFO="DB backup successfully, detail info pls refer attached log file."

# Send email
echo "$MAIL_CONTENT_INFO" | mail -s "$MAIL_SUBJECT_INFO" -a "${BACKUP_LOG_FILE_BASENAME}.tar.gz" "$MAIL_RECIPIENT"

exit 0

脚本在调用的时候，是在crontab中配置的定时任务，示例如下：

30 1 * * * /root/backup_mysql_db.sh /data/mysql_backup_dir 192.168.5.100 root root

上述定时任务的含义是：每天的凌晨1:30分执行/root目录下面的数据库的脚本backup_mysql_db.sh。这个shell脚本在调用的时候，后面需要传入5个参数，分别为：

1. BACKUP_DIR：生成的备份文件存放的目录在哪里
2. HOSTNAME：要备份的数据库所在的服务器IP地址
3. USERNAME：执行备份的时候，连接到MySQL数据库的用户名称
4. USERPASSWORD：执行备份的时候，连接到MySQL数据库的用户对应的密码
5. MAIL_RECIPIENT：数据库备份完成后，接受通知邮件的收件人邮箱地址。

备份任务执行完成后，会有对应的邮件发送给配置的邮箱。

逻辑还原的shell脚本

数据库的还原脚本就比较简单，只要选择对应需要还原的数据库或表就可以了，选择好对应的待还原的SQL文件。然后执行类似于如下的命令即可。

mysql -uroot -p -D xxxx < xxxx_backup.sql