六、MySQL增量备份与恢复
6.1 MySQL增量备份介绍
使用mysqldump进行完全备份存在的问题
- 备份数据中有重复数据
- 备份时间与恢复时间过长
增量备份是什么：
- 是自上一次备份后增加/变化的文件或者内容
增量备份的特点
- 没有重复数据，备份量不大，时间短
- 恢复需要上次完全备份及完全备份之后所有的增量备份才 能恢复，而且要对所有增量备份进行逐个反推恢复
6.2 MySQL增量备份的方法
- MySQL没有提供直接的增量备份方法
- 可通过MySQL提供的二进制日志间接实现增量备份
- MySQL二进制日志对备份的意义
- 二进制日志保存了所有更新或者可能更新数据库的操作
- 二进制日志在启动MySQL服务器后开始记录，并在文件达到 max_binlog_size所设置的大小或者接收到flush logs命令后重新 创建新的日志文件
- 只需定时执行flush logs方法重新创建新的日志，生成二进制文 件序列，并及时把这些日志保存到安全的地方就完成了一个时间 段的增量备份
6.3 MySQL数据库增量恢复
- 一般恢复
- 将所有备份的二进制日志内容全部恢复
- 基于位置恢复
- 数据库在某一时间点可能既有错误的操作也有正确的操作
- 可以基于精准的位置跳过错误的操作
- 基于时间点恢复
- 跳过某个发生错误的时间点实现数据恢复
七、增量备份与恢复 操作演示
7.1 增量备份
1、开启二进制日志功能
vim /etc/my.cnf [mysqld] log-bin=mysql-bin #开启二进制日志。如果使用相对路径，则保存在/usr/local/mysql/data/目录下 binlog_format = MIXED #可选，指定二进制日志(binlog)的记录格式为MIXED server-id = 1 systemctl restart mysqld ls -l /usr/local/mysql/data/mysql-bin.* -------------- 以下是注释 ----------------- #二进制日志(binlog)有3种不同的记录格式: STATEMENT (基于SQL语句)、ROW(基于行)、MIXED(混合模式)，默认格式是STATEMENT STATEMENT (基于SQL语句)：记录修改的sql语句。高并发的情况下，记录操作的sql语句时可能顺序会有错误，导致恢复数据时，数据丢失或有误差。效率高，但数据可能有误差。 ROW(基于行)：记录每一行数据，准确，但恢复时效率低。 MIXED(混合模式)：正常情况下使用STATEMENT，高并发的情况下会智能地切换到ROW。 复制代码
网络异常，图片无法展示
|
网络异常，图片无法展示
|
2、可每周对数据库或表进行完全备份
mysqldump -u root -p yuji class > /bak/yuji_class_$(date +%F).sql mysqldump -u root -p --databases yuji > /bak/yuji_$(date +%F).sql 复制代码
网络异常，图片无法展示
|
网络异常，图片无法展示
|
3、可每天进行增量备份操作，生成新的二进制日志文件（例如mysql-bin.000002）
mysqladmin -u root -p flush-logs 复制代码
网络异常，图片无法展示
|
4、插入新数据，以模拟数据的增加或变更
use yuji; insert into class values(3, '吉他') ; insert into class values(4, '二胡') ; 复制代码
网络异常，图片无法展示
|
5、再次生成新的二进制日志文件（例如mysql -bin.000003）
mysqladmin -u root -p flush-logs #之前的步骤4的数据库操作会保存到mysql-bin.000002文件中，之后数据库数据再发生变化则保存在mysql-bin.00003文件中 复制代码
网络异常，图片无法展示
|
6、查看二进制日志文件的内容
cp /usr/local/mysql/data/mysql-bin.000002 /bak/ mysqlbinlog --no-defaults --base64-output=decode-rows -v /bak/mysql-bin.000002 #--base64-output=decode-rows：使用64位编码机制去解码并按行读取 #-v：显示详细内容 复制代码
网络异常，图片无法展示
|
7.2 增量恢复
- 一般恢复
- 将所有备份的二进制日志内容全部恢复
- 基于位置恢复
- 数据库在某一时间点可能既有错误的操作也有正确的操作
- 可以基于精准的位置跳过错误的操作
- 基于时间点恢复
- 跳过某个发生错误的时间点实现数据恢复
7.2.1 一般恢复
将所有备份的二进制日志内容全部恢复。
1、模拟丢失更改的数据的恢复步骤。
use yuji; delete from class where cid=3; #删除今天新增加的两条数据 delete from class where cid=4; #增量恢复（今天新增加的两条数据记录保存在mysql-bin.000002日志中） mysqlbinlog --no-defaults /bak/mysql-bin.000002 | mysql -u root -p mysql -u root -p -e 'select * from yuji.class;' #查看表中数据 复制代码
网络异常，图片无法展示
|
网络异常，图片无法展示
|
网络异常，图片无法展示
|
2、模拟丢失表中所有数据的恢复步骤。
mysql -u root -p -e 'drop table yuji.class;' #删除整个class表 mysql -u root -p -e 'show tables from yuji;' #查看yuji库中的表 #先完全恢复历史数据 mysql -u root -p yuji < /bak/yuji_class_2022-06-05.sql #再进行增量恢复今天新增的2条数据（mysql-bin.000002日志中保存了今天新增加的两条数据记录） mysqlbinlog --no-defaults /bak/mysql-bin.000002 | mysql -u root -p mysql -u root -p -e 'select * from yuji.class;' #查看表中数据 复制代码
网络异常，图片无法展示
|
网络异常，图片无法展示
|
7.2.2 断点恢复
向class表中插入4条数据，之后刷新二进制日志，移动前一个日志：
use yuji; insert into class values(5,'竹笛'); insert into class values(6,'古琴'); insert into class values(7,'小号'); insert into class values(8,'长号'); mysqladmin -u root -p flush-logs #刷新日志，生成新的二进制日志 cp /usr/local/mysql/data/mysql-bin.000003 /bak/ #将前一个日志复制到/bak/目录下 mysqlbinlog --no-defaults --base64-output=decode-rows -v /bak/mysql-bin.000003 > /bak/binlog.txt #将二进制文件内容重定向到binlog.txt文件中 复制代码
网络异常，图片无法展示
|
网络异常，图片无法展示
|
网络异常，图片无法展示
|
网络异常，图片无法展示
|
1）基于位置的断点恢复
mysqlbinlog --no-defaults --start-position='位置点' 文件名 | mysql -u root -p #从某一个位置点开始恢复，一直到日志结尾 mysqlbinlog --no-defaults --stop-position='位置点' 文件名 | mysql -u root -p #从日志开头，一直恢复到某一个位置点前结束 mysqlbinlog --no-defaults --start-position='xxx'--stop-position='位置点' 文件名 | mysql -u root -p #从某一个位置点开始恢复，一直到某一个位置点前结束 复制代码
