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

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

需求背景

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

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

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

备份和还原的拓扑图

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

mysql备份与还原

逻辑备份的shell脚本

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

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

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

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

#!/bin/sh#--------------------------------------------------# # CreatDate: 2020/03/18 # Desc:      备份数据库脚本#--------------------------------------------------BACKUP_DIR=$1HOSTNAME=$2USERNAME=$3USERPASSWORD=$4MAIL_RECIPIENT=$5if [[ ! -d $BACKUP_DIR ]]; thenecho "存放备份文件的目录不存在！"exit 5elif [[ $HOSTNAME"x" == "x" ]]; thenecho "数据所在主机名称或IP不能为空！"exit 10elif [[ $USERNAME"x" == "x" ]]; thenecho "备份数据库的用户名称不能为空！"exit 15elif [[ $USERPASSWORD"x" == "x" ]]; thenecho "备份数据库的用户密码不能为空！"exit 20elif [[ $MAIL_RECIPIENT"x" == "x" ]]; thenecho "收件人邮箱地址不能为空！"exit 25fiLOGINCOMMAND="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) ]]; thenecho "备份日期目录不存在，需要创建该日期目录"        mkdir -p $BACKUP_DIR/$(date +%Y%m%d)fiBACKUP_LOG_FILE=$BACKUP_DIR/$(date +%Y%m%d)/db_backup_$(date +%Y%m%d).logif [[ ! -f $BACKUP_LOG_FILE ]]; thentouch $BACKUP_LOG_FILEfiecho "======================分库备份开始于：$(date '+%Y-%m-%d %H:%M:%S')=======================" >> $BACKUP_LOG_FILE# 分库备份for dbname in $DATABASE_NAME_LISTdoecho "备份$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.gzif [[ $? -ne 0 ]]; thenecho "ERROR: 备份$dbname数据库失败于：$(date '+%Y-%m-%d %H:%M:%S')" >> $BACKUP_LOG_FILEcontinueelseecho "备份$dbname数据库结束于：$(date '+%Y-%m-%d %H:%M:%S')" >> $BACKUP_LOG_FILEfidoneecho "======================分库备份结束于：$(date '+%Y-%m-%d %H:%M:%S')=======================" >> $BACKUP_LOG_FILEecho "======================分库分表备份开始于：$(date '+%Y-%m-%d %H:%M:%S')=======================" >> $BACKUP_LOG_FILE# 分库分表备份for dbname in $DATABASE_NAME_LISTdoTABLE_NAME_LIST=`$LOGINCOMMAND -e "show tables from $dbname;" | sed '1d' `if [[ ! -d $BACKUP_DIR/$(date +%Y%m%d)/$dbname/ ]]; thenecho "要备份的数据库名称目录不存在，需要创建该目录" >> $BACKUP_LOG_FILEmkdir -p $BACKUP_DIR/$(date +%Y%m%d)/$dbnamefifor tabname in $TABLE_NAME_LISTdo$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.gzif [[ $? -ne 0 ]]; thenecho "ERROR: 备份$dbname.$tabname的时候失败于：$(date '+%Y-%m-%d %H:%M:%S')" >> $BACKUP_LOG_FILEcontinuefidonedoneecho "======================分库分表备份结束于：$(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_BASENAMEMAIL_SUBJECT_INFO="INFO: DB backup successfully"MAIL_CONTENT_INFO="DB backup successfully, detail info pls refer attached log file."# Send emailecho "$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