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

需求背景备份和还原的拓扑图逻辑备份的shell脚本逻辑还原的shell脚本需求背景数据库备份的重要性在此不再赘述。前段时间我们的一个数据库因为一些网络安全组的配置错误被黑

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

数据库备份的重要性在此不再赘述。前段时间我们的一个数据库因为一些网络安全组的配置错误被黑了，所有的数据表都被删除了。想要数据，就得把比特币充到黑客指定的账户里。更详细的信息可以参考我之前分享的一篇文章，MySQL被黑的记录。

试想一下，如果被黑的数据库没有备份怎么办？不会完全凉。因此，数据库的备份非常重要。

数据库的备份和恢复是数据库日常维护中最常见的需求。你真的了解MySQL数据库的备份和恢复吗？接下来，我们来看看备份和恢复的一些知识点。

备份和恢复拓扑图

总结了一些MySQL数据库备份方法以及每种备份方法的具体参数。具体见下图:(如果想获取原文件，可以添加”编码器-主页”请留言。)

Mysql备份和恢复

下面是我常用的一个数据库逻辑备份脚本。使用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执行/根目录下数据库的脚本backup_mysql_db.sh。当调用这个shell脚本时，后面需要传入五个参数，它们是:

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

备份任务完成后，相应的邮件会发送到配置好的邮箱。

逻辑还原外壳脚本

数据库恢复脚本比较简单，只需选择对应的要恢复的数据库或表，选择对应的要恢复的SQL文件即可。然后执行类似下面的命令。

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