MySQL增量备份与恢复

实验环境

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

需求描述

在数据库表中，分三次录入学生考试成绩，具体的考试成绩信息分别参见表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；”生成新的二进制日志。

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

[root@node01 ~]# mysqldump -u root -p bdqn yiban >/mysql_bak/bdqn_yiban-$(date +%F).sql

[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;'

恢复成功