MySQL基础（十三）：备份与恢复

下面是小凰凰的简介，看下吧！ 💗人生态度：珍惜时间，渴望学习，热爱音乐，把握命运，享受生活 💗学习技能：网络 -> 云计算运维 -> python全栈( 当前正在学习中) 💗您的点赞、收藏、关注是对博主创作的最大鼓励，在此谢过！ 有相关技能问题可以写在下方评论区，我们一起学习，一起进步。 后期会不断更新python全栈学习笔记，秉着质量博文为原则，写好每一篇博文。

文章目录

一、备份类型二、备份方式及工具介绍1、备份工具2、逻辑备份和物理备份的比较（1）mysqldump (MDP)（2）xtrabackup(XBK) 3、备份策略 三、mysqldump的使用1、连接参数2、备份专用参数（1）-A 全备参数（2）-B 备份多个单库（3）备份一个库下多个表 3、高级参数应用（1）--master-data=2（2）--single-transaction（3）-R、-E、--triggers（4）--max-allowed-packet（5）--set-gtid-purged=auto 4、模拟故障案例并恢复 四、xtrabackup的使用1、xtrabackup的安装（1）安装依赖包（2）下载软件并安装 2、备份方式——物理备份3、面试题：XBK在innodb表备份恢复的流程4、innobackupex使用--全备（1）如何做全备？（2）全备的恢复（3）binlog日志恢复 5、innobackupex使用--增量备份（1）增量备份须知（2）增量备份命令（3）数据恢复 五、作业训练1、脚本实现数据库自动备份2、从mysqldump 全备中获取单库或单表的备份

一、备份类型

1. 热备 在数据库正常业务时,备份数据,并且能够一致性恢复（只能是innodb）对业务影响非常小 2. 温备 锁表备份,只能查询不能修改（myisam）影响到写入操作 3. 冷备 关闭数据库业务,数据库没有任何变更的情况下,进行备份数据.业务停止

二、备份方式及工具介绍

不管哪种备份方式，都需要开启binlog日志！开启方式见： https://blog.csdn.net/weixin_44571270/article/details/103148755

1、备份工具

1. 逻辑备份工具 基于SQL语句进行备份 mysqldump mysqlbinlog 2. 物理备份工具 基于磁盘数据文件备份 xtrabackup(XBK) ：percona 第三方 MySQL Enterprise Backup（MEB）

下面讲解我们主要学习，逻辑备份工具mysqldump、物理备份工具xtrabackup以及无论那个必用的mysqlbinlog增量恢复！

2、逻辑备份和物理备份的比较

（1）mysqldump (MDP)

优点： (1)不需要下载安装 (2)备份出来的是SQL，文本格式，可读性高,便于备份处理 (3)压缩比较高，节省备份的磁盘空间 缺点： 依赖于数据库引擎，需要从磁盘把数据读出,然后转换成SQL进行转储，比较耗费资源，数据量大的话效率较低 建议： (1)100G以内的数据量级，可以使用mysqldump (2)超过TB以上，我们也可能选择的是mysqldump，配合分布式的系统 1EB =1024 PB =1000000 TB

（2）xtrabackup(XBK)

优点： (1)类似于直接cp数据文件，不需要管逻辑结构，相对来说性能较高 缺点： (2)可读性差 (3)压缩比低，需要更多磁盘空间 建议： >100G<TB

3、备份策略

备份方式： 全备:全库备份，备份所有数据 增量:备份变化的数据,只在物理备份中有 备份周期: 根据数据量设计备份周期 比如：周日全备，周1-周6增量 逻辑备份=mysqldump+mysqlbinlog 物理备份=xtrabackup_full+xtrabackup_incr+binlog 或者 xtrabackup_full+binlog

三、mysqldump的使用

首先学一个命令，我们先要研究它参数的意义！mysqldump命令分为连接参数与备份专用参数两部分！

1、连接参数

-u -p -S -h -P 本地备份: mysqldump -uroot -p -S /tmp/mysql.sock 远程备份: mysqldump -uroot -p -h 10.0.0.51 -P3306 -S /tmp/mysql.sock

2、备份专用参数

（1）-A 全备参数

注：备份所有库！

mysqldump -uroot -p -A >/data/backup/full.sql # 补充: # 1.常规备份是要加 --set-gtid-purged=OFF,解决备份时的警告 # [root@db01 ~]# mysqldump -uroot -p123 -A --set-gtid-purged=OFF >/backup/full.sql # 2.构建主从时,做的备份,不需要加这个参数 # [root@db01 ~]# mysqldump -uroot -p123 -A --set-gtid-purged=ON >/backup/full.sql

（2）-B 备份多个单库

例2 备份db01、db02两个库： mysqldump -uroot -pmysql -B db01 db02>/data/backup/db.sql

（3）备份一个库下多个表

例3 world数据库下的city,country表 mysqldump -uroot -p world city country >/backup/bak1.sql 以上备份恢复时:必须库事先存在,并且use才能source恢复

3、高级参数应用

（1）–master-data=2

以注释的形式,保存备份开始时间点的binlog的状态信息 mysqldump -uroot -p -A -R --triggers --master-data=2 >/back/world.sql [root@db01 ~]# grep 'CHANGE' /backup/world.sql -- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000035', MASTER_LOG_POS=194; 功能： （1）在备份时，会自动记录，二进制日志文件名和位置号 0 默认值 1 以change master to命令形式，可以用作主从复制 2 以注释的形式记录，备份时刻的文件名+postion号 （2） 自动锁表 （3）如果配合--single-transaction，只对非InnoDB表进行锁表备份，InnoDB表进行“热“”备，实际上是实现快照备份。

（2）–single-transaction

innodb 存储引擎开启热备(快照备份)功能 master-data可以自动加锁 （1）在不加--single-transaction ，启动所有表的温备份，所有表都锁定 （1）加上--single-transaction ,对innodb进行快照备份,对非innodb表可以实现自动锁表功能 例子6: 备份必加参数 mysqldump -uroot -p -A -R -E --triggers --master-data=2 --single-transaction --set-gtid-purged=OFF >/data/backup/full.sql

（3）-R、-E、–triggers

-R 备份存储过程及函数 --triggers 备份触发器 -E 备份事件 例子4: [root@db01 backup]# mysqldump -uroot -p -A -R -E --triggers >/data/backup/full.sql

（4）–max-allowed-packet

mysqldump -uroot -p -A -R -E --triggers --master-data=2 --single-transaction --set-gtid-purged=OFF --max-allowed-packet=256M >/data/backup/full.sql

（5）–set-gtid-purged=auto

auto , on off 使用场景: 1. --set-gtid-purged=OFF,可以使用在日常备份参数中. mysqldump -uroot -p -A -R -E --triggers --master-data=2 --single-transaction --set-gtid-purged=OFF >/data/backup/full.sql 2. auto , on:在构建主从复制环境时需要的参数配置 mysqldump -uroot -p -A -R -E --triggers --master-data=2 --single-transaction --set-gtid-purged=ON >/data/backup/full.sql

4、模拟故障案例并恢复

（1）每天全备 （2）binlog日志是完整 （3）模拟白天的数据变化 （4）模拟误删除数据库 需求： 利用全备+binlog回复数据库误删除之前。 故障模拟及恢复： 1. 模拟周一23:00的全备 mysqldump -uroot -p -A --master-data=2 --single-transaction -R -E --triggers --set-gtid-purged=OFF >/data/backup/full.sql 2. 模拟白天的数据变化 Master [(none)]>create database day1 charset utf8; Master [(none)]>use day1 Master [day1]>create table t1(id int); Master [day1]>insert into t1 values(1),(2),(3); Master [world]>update city set countrycode='CHN'; 3.模拟磁盘损坏： rm -rf /data/mysql/data/* 4. 恢复思路 (1)检查备份可用性 (2)从备份中获取二进制日志位置 (3)根据日志pos开始结束位置截取需要的二进制日志 # 详细使用请见bin-log日志恢复 (4)初始化数据库,并启动 (5)恢复全备 (6)恢复二进制日志

四、xtrabackup的使用

1、xtrabackup的安装

（1）安装依赖包

wget -O /etc/yum.repos.d/epel.repo http://mirrors.aliyun.com/repo/epel-7.repo yum -y install perl perl-devel libaio libaio-devel perl-Time-HiRes perl-DBD-MySQL libev

（2）下载软件并安装

wget https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.4.12/binary/redhat/7/x86_64/percona-xtrabackup-24-2.4.12-1.el7.x86_64.rpm or yum -y install percona-xtrabackup-24-2.4.4-1.el7.x86_64.rpm

2、备份方式——物理备份

（1）对于非Innodb表（比如 myisam），锁表后，再cp数据文件，属于一种温备份。 （2）对于Innodb的表（支持事务的），不锁表，拷贝数据页，最终以数据文件的方式保存下来，把一部分redo和undo一并备走，属于热备方式。

3、面试题：XBK在innodb表备份恢复的流程

0、xbk备份执行的瞬间,立即触发ckpt,已提交的数据脏页,从内存刷写到磁盘,并记录此时的LSN号 1、备份时，拷贝磁盘数据页，并且记录备份过程中产生的redo和undo一起拷贝走,也就是checkpoint LSN之后的日志 2、在恢复之前，模拟Innodb“自动故障恢复”的过程，将redo（前滚）与undo（回滚）进行应用 3、恢复过程是cp 备份到原来数据目录下

4、innobackupex使用–全备

注意：xtrabackup只是一个软件名，真正工作的是命令innobackupex！ 全备的恢复：上次全备+binlog日志

（1）如何做全备？

# 两句话做全备 mkdir -pv /data/backup # 创建物理备份的数据存放目录 innobackupex --user=root --password=123 --no-timestamp /data/backup/full # 其他连接参数 1. 当然如果你mysql的sock文件不在默认位置，也需要指定其--socket= 2. 如果是远程备份到本地，你需要指定--host= 3. 如果你数据库不是默认的3306端口，则需要指定--port=

下面是对全备的一些介绍，和一些理论知识！

innobackupex --user=root --password=123 /data/backup # 注意：这条命令生成的存储备份数据的文件夹的名称是以当前时间来命名的，例：2020-07-02_13-24-41 # 上面这样不好看，因此我们一般采用下面这条命令，指定文件夹名称： innobackupex --user=root --password=123 --no-timestamp /data/backup/full -rw-r----- 1 root root 24 Jun 29 09:59 xtrabackup_binlog_info -rw-r----- 1 root root 119 Jun 29 09:59 xtrabackup_checkpoints -rw-r----- 1 root root 489 Jun 29 09:59 xtrabackup_info -rw-r----- 1 root root 2560 Jun 29 09:59 xtrabackup_logfile 1. xtrabackup_binlog_info ：（备份时刻的binlog位置） [root@db01 full]# cat xtrabackup_binlog_info mysql-bin.000003 536749 79de40d3-5ff3-11e9-804a-000c2928f5dd:1-7 记录的是备份时刻，binlog的文件名字和当时的结束的position，可以用来作为截取binlog时的起点。 2. xtrabackup_checkpoints ： backup_type = full-backuped from_lsn = 0 上次所到达的LSN号(对于全备就是从0开始,对于增量有别的显示方法) to_lsn = 160683027 备份开始时间(ckpt)点数据页的LSN last_lsn = 160683036 备份结束后，redo日志最终的LSN compact = 0 recover_binlog_info = 0 （1）备份时刻，立即将已经commit过的，内存中的数据页刷新到磁盘(CKPT).开始备份数据，数据文件的LSN会停留在to_lsn位置。 （2）备份时刻有可能会有其他的数据写入，已备走的数据文件就不会再发生变化了。 （3）在备份过程中，备份软件会一直监控着redo的undo，如果一旦有变化会将日志也一并备走，并记录LSN到last_lsn。 从to_lsn ----》last_lsn 就是，备份过程中产生的数据变化. # 注意其他两个参数不需要了解

注：如果没有binlog_info文件，说明你没开启binlog日志！

（2）全备的恢复

准备备份 # 将redo进行重做，已提交的写到数据文件，未提交的使用undo回滚掉。模拟了CSR的过程 innobackupex --apply-log /backup/full 恢复备份 前提： 1、被恢复的目录是空 2、被恢复的数据库的实例是关闭 systemctl stop mysqld 创建新目录 mkdir /data/mysql1 恢复备份 cp -a /backup/full/* /data/mysql1/ 启动数据库 vim /etc/my.cnf datadir=/data/mysql1 chown -R mysql.mysql /data/mysql1 systemctl start mysqld

好！现在全备已经恢复了，还需要恢复binlog日志！

（3）binlog日志恢复

确定全备时binlog的pos点 cat /data/backup/full_2020-07-02/xtrabackup_binlog_info

pos点为245！

确定删库前的pos点

原来删库的binlog文件在/var/lib/mysql/下：

mysqlbinlog /var/lib/mysql/mysql-bin.000001

pos点为807

mysqlbinlog进行恢复

首先说，binlog日志无法改变其位置，因此，我们在恢复时需要避免恢复的sql语句再次写入原来的bin-log日志文件中：

set sql_log_bin=0; '不改也可以'，因为全备时，连你当时的binlog也会备份 指定datadir,启动mysql之后，你会生成一个mysql-bin就在/data/mysql/下，你可以指定logbin文件的位置为它的路径 然后通过恢复的操作，把binlog日志也一起同步了！ mysqlbinlog --start-position=245 --stop-position=807 /var/lib/mysql/mysql-bin.000001 |mysql -uroot -pmysql

5、innobackupex使用–增量备份

一般一周做一次全备，每天凌晨进行增量备份！ 增量的恢复：上周的全备+每天增量+binlog

（1）增量备份须知

1. 增量备份的方式，是基于上一次备份进行增量。 2. 增量备份无法单独恢复。必须基于全备进行恢复。 3. 所有增量必须要按顺序合并到全备中。

（2）增量备份命令

1. 删掉原来备份 略. 2. 全备（周日） innobackupex --user=root --password --no-timestamp /backup/full >&/tmp/xbk_full.log 3. 模拟周一数据变化 db01 [(none)]>create database cs charset utf8; db01 [(none)]>use cs db01 [cs]>create table t1 (id int); db01 [cs]>insert into t1 values(1),(2),(3); db01 [cs]>commit; 4. 第一次增量备份（周一） innobackupex --user=root --password=123 --no-timestamp --incremental --incremental-basedir=/backup/full /backup/inc1 &>/tmp/inc1.log 5. 模拟周二数据 db01 [cs]>create table t2 (id int); db01 [cs]>insert into t2 values(1),(2),(3); db01 [cs]>commit; 6. 周二增量 innobackupex --user=root --password=123 --no-timestamp --incremental --incremental-basedir=/backup/inc1 /backup/inc2 &>/tmp/inc2.log 7. 模拟周三数据变化 db01 [cs]>create table t3 (id int); db01 [cs]>insert into t3 values(1),(2),(3); db01 [cs]>commit; db01 [cs]>drop database cs;

现在我们要恢复到周三误drop之前的数据状态!

（3）数据恢复

恢复思路： 1. 挂出维护页，停止当天的自动备份脚本 2. 检查备份：周日full+周一inc1+周二inc2，周三的完整二进制日志 3. 进行备份整理（细节），截取关键的二进制日志（从备份——误删除之前） 4. 测试库进行备份恢复及日志恢复 5. 应用进行测试无误，开启业务 6. 此次工作的总结

恢复过程

1. 备份整理（apply-log）+合并备份（full+inc1+inc2） (1) 全备的整理 innobackupex --apply-log --redo-only /data/backup/full (2) 合并inc1到full中 innobackupex --apply-log --redo-only --incremental-dir=/data/backup/inc1 /data/backup/full (3) 合并inc2到full中 innobackupex --apply-log --incremental-dir=/data/backup/inc2 /data/backup/full # 注意：最后一次合并不要加--redo-only！ (4) 最后一次整理全备 innobackupex --apply-log /data/backup/full 2. 截取周二 23:00 到drop 之前的 binlog mysqlbinlog --start-position=807 --stop-position=1500 /data/binlog/mysql-bin.000009 >/data/backup/binlog.sql 3. 进行恢复 mkdir /data/mysql/data2 -p cp -a * /data/mysql/data2 chown -R mysql. /data/* systemctl stop mysqld vim /etc/my.cnf datadir=/data/mysql/data2 systemctl start mysqld Master [(none)]>set sql_log_bin=0; Master [(none)]>source /data/backup/binlog.sql

五、作业训练

1、脚本实现数据库自动备份

#!/bin/bash CONN_USER=root CONN_PWD=mysql CONN_SERVER_IP=127.0.0.1 CONN_PORT=3306 CONN_SOCK=/var/lib/mysql/mysql.sock #-------------- 全备 -------------- MDB_DUMP(){ echo -e '\033[31m ----------------- 开始进行数据库全备... ----------------- \033[0m' mysqldump -u${CONN_USER} -p${CONN_PWD} -h${CONN_SERVER_IP} -P${CONN_PORT} -S${CONN_SOCK} -A --master-data=2 --single-transaction -R -E --triggers --max-allowed-packet=64M > /data/MDB_backup/full_`date +%F`.sql ls /data/MDB_backup/|grep "full_`date +%F`.log" if [ $? -ne 0 ];then echo '`date '+%F %T %p'` 数据库全备出错!' >>/var/log/MDB_back.log exit 1 fi BACK_NUM = ls /data/MDB_backup/|wc -l # /data/MDB_backup/中的全备文件不能超过五个，一旦超过，便删除最旧的那个全备文件 if [ ${BACK_NUM} -g 5 ];then DELETE_FILE=find /data/MDB_backup/ -type f -print0|xargs -0 ls -lt -c|tail -1|awk -F' ' '{print $9}' rm -f DELETE_FILE if [ $? -ne 0 ];then echo '`date '+%F %T %p'` 删除全备文件出错!' >>/var/log/MDB_back.log exit 2 fi fi } MDB_DUMP

2、从mysqldump 全备中获取单库或单表的备份

1、获取单表备份 （1）获得表结构 sed -e'/./{H;$!d;}' -e 'x;/CREATE TABLE 'city'/!d;q' full.sql>createtable.sql （2）获得INSERT INTO 语句，用于数据的恢复 grep -i 'INSERT INTO 'city'' full.sql >data.sql & # 注意：恢复的时候需要先恢复表表结构再恢复表数据 3.获取单库的备份 sed -n '/^-- Current Database: 'world'/,/^-- Current Database: /p' all.sql >world.sql