MySQL增量备份与恢复

目录

前言

一、MySQL增量备份

1.增量备份的概念

2.增量备份的优缺点

3.二进制日志备份的意义

4.实现增量备份

二、增量恢复

1.增量恢复的方式

2.丢失完全备份之后更改的数据恢复

3.完全备份之后丢失所有数据恢复

4.基于时间点与位置的恢复

总结

---

前言

MySQL增量备份和恢复的方法，它可以在完全备份的基础上，减少备份文件的大小，加快备份和恢复速度。

一、MySQL增量备份

1.增量备份的概念

完全备份的问题很容易看到，每次都是把所有的数据内容进行备份，备份的数据中有大量的重复数据，并且完全备份的时间与恢复的时间很长。解决完全备份存在的问题就是使用增量备份的方式，增量备份自上一次备份之后增加或改变的文件或者内容。

2.增量备份的优缺点

增量备份的优点：没有重复数据，备份量不大，时间短。

增量备份的缺点：需要上一次完全备份及完全备份之后所有的增量备份才能恢复，而且对所有增量备份进行逐个反推恢复，操作较为繁琐。

3.二进制日志备份的意义

二进制日志保存了所有更新或者可能更新数据库的操作。

二进制日志在启动MySQL服务器开启后开始记录，并在文件达到 max_binlog_size 所设置的大小或者接收到 flush logs 命令后重新创建的日志文件。

只需要定时执行flush logs 方法重新创建新的日志，生成二进制的文件序列，并及时把这些日志保存起来到安全的地方就完成了一个时间段的增量备份。

4.实现增量备份

要进行MySQL增量备份，首先要开启二进制日志功能。开启二进制日志功能。

[root@localhost ~]# vim /etc/my.cnf
...
log-bin=mysql-bin
binlog_format = MIXED    #可选，指定二进制日志(binlog)的记录格式为MIXED（混合输入）

[root@localhost ~]# systemctl restart mysqld.service 

创建备份文件夹/opt/binlog，备份文件都保存在这个文件夹中。

[root@localhost ~]# mkdir /opt/binlog
[root@localhost ~]# cd /usr/local/mysql/data/
[root@localhost ~]# ls               #查看一下二进制的文件夹
[root@localhost data]# cp mysql-bin.000001 /opt/binlog/
[root@localhost data]# mysqlbinlog --no-defaults  /opt/mysql-bin.000001  #查看二进制日志的文件
#--base64-output=decode-rows：使用64位编码机制去解码（decode）并按行读取（rows）
#-v: 显示详细内容
#--no-defaults : 默认字符集（不加会报UTF-8的错误）
PS: 可以将解码后的文件导出为txt格式，方便查阅
mysqlbinlog --no-defaults --base64-output=decode-rows -v /opt/mysql-bin.000001 > /opt/mysql-bin.000001.txt

每天进行的增量备份操作，使用mysqladmin 的选项flush_logs 生成新的二进制文件，这样在插入新的数据后，新的二进制文件对应的就是数据库变化的内容。

[root@localhost opt]# mysqladmin -uroot -p123456 flush-logs
[root@localhost opt]# ls /usr/local/mysql/data/
mysql-bin.000001
mysql-bin.000002     #新生成的二进制文件

我们备份info下面的member表的时候最大编号是.000002，之后的数据库操作会保存到.000003下面。

注：生成之前mysql-bin文件最大编号是2，生成后是3，之后的数据库操作会保存到编号为3的二进制文件中。

二、增量恢复

增量恢复比完全恢复操作更为繁琐，每个增量备份都是单独个体，数据不重复，需要控制的更加精确。

1.增量恢复的方式

一般恢复：将所有备份的二进制日志内容全部恢复。

基于位置恢复：数据库在某一时间点可能既有错误的操作也有正确的操作，可以基于精准的位置跳过错误的操作，发生错误节点之前的一个节点，上一次正确操作的位置点停。

基于时间点恢复：跳过某个发生错误的时间点实现数据恢复，在错误时间点停止，在下一个正确时间点开始。

2.丢失完全备份之后更改的数据恢复

模拟删除插入的两条数据，也就是假设完全备份后的数据丢失了。

使用二进制文件进行恢复操作，需要注意的是恢复的顺序，要先恢复最先生成的二进制文件，然后依次执行。

3.完全备份之后丢失所有数据恢复

当完全备份和增量备份之后，所有数据丢失，需要把完全备份和所有增量备份文件逐个恢复。

模拟删除完全备份之前的数据，也就是完全备份之前的member表的数据和完全备份后的数据都丢失了。

先使用mysql命令进行完全备份操作。

再使用二进制文件进行增量恢复，需注意恢复的顺序，要先恢复最先生成的二进制文件，然后依次执行。

4.基于时间点与位置的恢复

利用二进制日志可实现基于时间点与位置的恢复，例如由于误删一张表，这时完全恢复是没有用的，因为日志里面还存在误操作的语句，我们需要的是恢复到误操作前的状态，然后跳过误操作语句，再恢复后面操作的语句。 

向数据库插入两条数据，但由于误操作，两条插入语句中误删除了一条数据，需要恢复这条数据。

[root@localhost ~]# mysqlbinlog --no-defaults /usr/local/mysql/data/mysql-bin.000001

(1)基于时间点恢复

就是将某个起始时间的二进制日志导入数据库中，从而跳过某个发生错误的时间点来实现数据的恢复。使用mysqlbinlog加上--stop-datemine选项，表示在哪个时间点结束，后面误操作的语句不执行，--start-datetime选项表示执行后面的语句，结合使用它们就可以跳过误操作的语句，完成恢复工作。

[root@localhost data]# mysqlbinlog --no-defaults --start-datetime='2021-10-27 17:26:03' --stop-datetime='2021-10-27 18:26:21' /usr/local/mysql/data/mysql-bin.000001 | mysql -uroot -p123456

(2)基于位置恢复

首先要确定要跳过误操作删除的语句，达到恢复的目的。要先找到误操作的位置。

[root@localhost data]# mysqlbinlog --no-defaults --stop-position='3078' /usr/local/mysql/data/mysql-bin.000002 | mysql -uroot -p123456   #仅恢复到操作ID为“3078"之前的数据
[root@localhost data]# mysqlbinlog --no-defaults --start-position='3190' /usr/local/mysql/data/mysql-bin.000002 | mysql -uroot -p123456    #仅恢复到操作ID为“3190"之前的数据

总结

MySQL没有直接提供增量备份的工具，需借助二进制日志文件进行操作。

使用分割日志的方式来进行增量备份。

增量恢复需要根据日志文件的时间先后，逐个恢复。

使用基于时间和位置的恢复，能更加精确的恢复数据。