MySQL数据库，表的增量备份与恢复

1. 从物理与逻辑的角度

数据库备份可以分为物理备份和逻辑备份。物理备份是对数据库操作系统的物理文件（如数据文件，日志文件等）的备份。这种类型的备份适用于在出现问题时需要快速恢复的大型重要数据库。

物理备份又可以分为冷备份（脱机备份）、热备份（联机备份）和温备份。

冷备份：在数据库关闭状态下进行备份操作。

热备份：在数据库处于运行状态时进行备份操作，该备份方法依赖数据库的日志文件。

温备份：数据库锁定表格（不可写入但可读）的状态下进行备份操作。

逻辑备份是对数据库逻辑组件（如表等数据库对象）的备份，表示为逻辑数据库结构（CREATE DATABASE，CREATE TABLE语句）和内容（INSERT语句或分隔文本文件）的信息。这种类型的备份适用于可以编辑数据值或表结构较小的数据量，或者在不同的机器体系结构上重新创建数据。

2. 从数据库的备份策略角度

从数据库的备份策略角度，数据库的备份可分为完全备份、差异备份和增量备份。

完全备份：每次对数据进行完整的备份，即对整个数据库的备份，数据库结构和文件结构的备份，保存的是备份完成时刻的数据库，是差异备份与增量备份的基础，完全备份的备份与恢复操作都非常简单方便，但是数据存在大量的重复，并且会占用大量的磁盘空间，备份的时间也很长。 差异备份：备份那些自从上次完全备份之后被修改过的所有文件，备份的时间节点是从上次完整备份起，备份数据量会越来越大。恢复数据时，只需恢复上次的完全备份与最近的一次差异备份。

增量备份：只有那些在上次完全备份或者增量备份后被修改的文件才会被备份，以上次完整备份或上次的增量备份的时间为时间点，仅备份这之间的数据变化，因而备份的数据量小，占用空间小，备份速度快。但恢复时，需要从上一次的完整备份开始到最后一次增量备份之间的所有增量依次恢复，如中间某次的备份数据损坏，将导致数据的丢失。

3.常见的备份方法

MySQL数据库的备份可以采用很多种方式，如直接打包数据库文件（物理冷备份）、专用备份工具（mysqldump）、二进制日志增量备份、第三方工具备份等。

实验环境

某学校近期在进行期中考试，要求数据库管理员负责一班，二班学生的考试成绩录入，为保证数据的可靠性，数据库管理员在录入学生成绩后均要做数据库备份，并且为了测试备份数据是否可用，模拟数据丢失故障，进行数据还原。

需求描述

在数据库表中，分三次录入学生考试成绩，具体的考试成绩信息分别参见表4-2~表4-4。

数据库在上一章已经创建好了

创建表一班学生成绩表

mysql> create table yiban (姓名 char(10) not null,班级 char(10) not null, 学号 char(10) not null,语文 char(10) not null,数学 char(50) not null,英语 char(10) not null,理综 char(10) not null);

录入数据

mysql> insert into yiban(姓名,班级,学号,语文,数学,英语,理综)
    -> values('张三','一班','20170822','110','105','92','235'),
    ->       ('李四','一班','20170820','95','115','110','260'),
    ->       ('王五','一班','20170818','95','103','108','270'),
    ->       ('赵六','一班','20170816','100','109','112','265');

创建二班学生成绩表

mysql> create table erban (姓名 char(10) not null,班级 char(10) not null, 学号 char(100) not null,语文 char(10) not null,数学 char(50) not null,英语 char(10) not null,理综  char(10) not null);

录入数据

mysql> insert into erban(姓名,班级,学号,语文,数学,英语,理综)
    -> values('李宁','二班','20170824','92','98','105','235'),
    ->       ('陈铭','二班','20170826','111','107','96','204');

创建三班学生成绩表

mysql> create table sanban (姓名 char(10) not null,班级 char(10) not null, 学号 char(100) not null,语文 char(10) not null,数学 char(50) not null,英语 char(10) not null,理综  char(10) not null);

录入数据

mysql> insert into sanban(姓名,班级,学号,语文,数学,英语,理综)
    -> values('付杰','三班','20170828','115','118','116','268'),
    ->       ('郭尚','三班','20170830','111','99','80','259');

> 首次录入成绩后，做该表的完全备份，后两次成绩的录入之后，分别做增量备份。

> 模拟数据丢失，并使用增量备份分别基于位置和时间点恢复二班和三班同学的成绩。

1）先进行一次完全备份为方便验证二进制日志的增量恢复功能，我们在插入三条用户数据后先对bdqn数据库的 yiban 表进行一次完全备份。然后在Linux 系统命令行下执行“mysqladmin -u root -p flush-logs”命令或在“mysql>”命令提示符下执行“flush logs；”生成新的二进制日志。

要进行MySQL的增量备份，首先要开启二进制日志功能，按图中命令操作

[root@node01 ~]# mkdir /mysql_bak //创建文件夹

[root@node01 ~]# mysqldump -u root -p bdqn yiban >/mysql_bak/bdqn_yiban-$(date +%F).sql //将bdqn数据库中的一班表备份

[root@node01 ~]# ls /mysql_bak/  //查看备份好的

[root@node01 ~]# mysqladmin -u root -p flush-logs //生成新的二进制日志

[root@node01 ~]# ls -l /usr/local/mysql/mysql-bin.*

2）继续录入新的数据并进行增量备份继续录入两个用户的数据，并执行‘mysqladmin -u root -p flush-logs”命令刷新二进制日志，进行增量备份，如此，二进制日志文件mysql-bin.000005中仅保留插入两个用户数据的操作。

mysql> use bdqn;

mysql> insert into yiban(姓名,班级,学号,语文,数学,英语,理综)   //将二班和三班的同学录入一班
    -> values('李宁','二班','20170824','92','98','105','235'),
    ->       ('陈铭','二班','20170826','111','107','96','204'),
    ->       ('付杰','三班','20170828','115','118','116','268'),
    ->       ('郭尚','三班','20170830','111','99','80','259');

mysql> select * from yiban;

[root@node01 ~]# mysqladmin -u root -p flush-logs

[root@node01 ~]# ls -l /usr/local/mysql/mysql-bin.*

[root@node01 ~]# cp /usr/local/mysql/mysql-bin.000004 /mysql_bak/

3) 模拟误操作删除yiban表

[root@node01 ~]# mysql -u root -p

mysql> use bdqn; //使用bdqn数据库

mysql> drop table yiban; //模拟删除yiban表

mysql> select * from yiban; 查看一班表是否被删除

yiban表已被删除

4）恢复操作（基于位置恢复）

再执行恢复操作时，需要先恢复完全备份，然后恢复增量备份。

root@node01 ~]# mysql -u root -p bdqn < /mysql_bak/bdqn_yiban-2023-12-14.sql //恢复完全备份

想要实现基于位置或时间点恢复数据，必须先通过查看二进制日志文件确定恢复的位置或时间点，使用“mysqlbinlog --no-defaults 二进制日志文件”可以查看二进制日志文件的具体内容。

[root@node01 ~]# mysqlbinlog --no-defaults /mysql_bak/mysql-bin.000004 //查看二进制日志文件的具体内容

通过查看日志文件的具体内容可以发现，在每进行一个操作之前都会有一个独特的编号，如“# at 617”.此编号随着操作数增多而变大，我们称之为操作ID。在操作ID下面紧跟着的是时间标记. 要实现基于位置或时间点恢复数据，需要分别依赖二进制日志文件中的操作ID或者时间标记.例如，通过二进制日志文件得知，在操作ID为“617”的时候，user_info表中插入了“除一班外同学”的用户数据。因此执行以下命令可以实现仅恢复到操作ID为“617”之前的数据，即不恢复“除一班外同学”的信息。这时所恢复的数据是从二进制日志文件的开始位置直到指定位置。

[root@node01 ~]# mysqlbinlog --no-defaults --stop-position='617' /mysql_bak/mysql-bin.000004 | mysql -u root -p

[root@node01 ~]# mysql -u root -p -e 'select * from bdqn.yiban;'

恢复成功

上述操作命令中，‘--stop-position”指定的是停止的位置，而“--start-position”选项指定开始恢复数据的位置。这时所恢复的数据是从指定位置开始直到二进制日志文件的最后。

基于时间点恢复

基于时间点恢复数据所使用的选项是“--start-datetime”，指定的时间同样也是查询二进制日志所得。执行以下操作可以实现仅恢复到10：47：42之前的数据

模拟将二班和三班的同学删除后再进行恢复（如整个表都删除了，也需先恢复完全备份，再进行增量恢复）

增量备份基于时间点恢复

上图红框可看到时间

[root@node01 ~]# mysqlbinlog --no-defaults --start-datetime='2023-12-14 17:49:17' /mysql_bak/mysql-bin.000004 | mysql -u root -p

[root@node01 ~]# mysql -u root -p -e 'select * from bdqn.yiban;'

恢复成功