实战案例：恢复误删除的表

案例说明：每天2：30做完全备份，早上10：00误删除了表students，10：10才发现故障，现需要将数 据库还原到10：10的状态，且恢复被删除的students表

首先确认二进制是否开启

select @@log_bin;
select @@sql_log_bin;

查看当前二进制文件

show master logs;

完全备份（必须开启二进制）

[root@localhost data]# mysqldump -uroot -p123456 -A -F --single-transaction --source-data=2 > /backup/full_`date +%F`.sql

查看备份的文件，新日志从这里开始记录

模拟数据更新

insert students (name,age,gender) values('rose',20,'f');
insert students (name,age,gender) values('jack',22,'M');

查看当前二进制文件，发现二进制文件位置发生变化

show master logs;

假设二进制文件越来越大，模拟新的二进制文件

flush logs;
show master logs;

再次模拟数据更新

insert students (name,age,gender) values('haha',23,'f');
insert students (name,age,gender) values('xixi',21,'f');

破坏数据

drop table students;
insert teachers (name,age,gender)values('wang',30,'M');
insert teachers (name,age,gender)values('mage',28,'M');

然后按正常顺序可以进行数据还原，但为了保障操作无误应先找一个测试数据库进行还原

查看二进制记录的信息

[root@localhost ~]# ll /var/lib/mysql

查看备份的文件，新日志从这里开始记录，所以只需要导出这之后的信息导入一个新文件中

[root@localhost ~]# mysqlbinlog --start-position=157 /var/lib/mysql/binlog.000003 > /backup/inc.sql
[root@localhost ~]# mysqlbinlog /var/lib/mysql/binlog.000004 >> /backup/inc.sql

找到误删除的语句，从备份中删除此语句

[root@localhost ~]# grep -i drop /backup/inc.sql
DROP TABLE `students` /* generated by server */
[root@localhost ~]# sed -i '/DROP TABLE/d' /backup/inc.sql
[root@localhost ~]# sed -n '/DROP TABLE/p' /backup/inc.sql

将完全备份和修改过的二进制日志拷贝到测试数据库上先进行还原测试

不希望还原过程生成新的二进制日志，所以先暂时停了二进制，然后进行还原，还原成功后再开启。

set sql_log_bin=0;
source /data/full_2023-08-03.sql
source /data/inc.sql
set sql_log_bin=1;

数据已成功还原

在测试数据库上还原成功，只需要在原来的数据库上进行一遍数据还原即可。