Mysql 数据恢复 - binlog

基本上每个跟数据库打交道的程序员都会碰一个问题，MySQL误操作后如何快速回滚？

比如，delete一张表，忘加限制条件，整张表没了。假如这还是线上环境核心业务数据，那这事就闹大了。误操作后，能快速回滚数据是非常重要的。

使用binlog二进制文件恢复数据（Mysql在5.7以前是默认关闭log_bin的，8以后默认开启），本文以ubuntu18.04系统下的5.7版本的mysql为例子

1. 开启mysql的log-bin参数记录binlog日志功能

首先，查看一下log_bin日志的开启状况

mysql> show variables like '%log_bin%';

我们可以看到log_bin对应的值是OFF，即处于关闭的状态的。
我们通过更改my.cnf配置文件来开启log_bin
my.cnf默认放在/etc/mysql/目录下，添加如下内容:

[mysqld]
server_id = 1
log_bin=/var/lib/mysql/mysql-bin

然后重启mysql

services mysql restart

 

这时候查看log_bin，可以发现结果已经变成了ON，成功开启，同时在对应的文件夹也生成了对应的日志文件。

 

 

 

3.建立测试环境和数据
a.建立数据

//mysql命令行执行
//创建数据库
create database Demo;
//创建表
create table student(sno int(10) NOT NULL COMMENT '学号',sname varchar(16) NOT NULL COMMENT '姓名',ssex char(2) NOT NULL COMMENT '性别', sage tinyint(2) NOT NULL default '0' COMMENT '学生年龄',sdept varchar(16) default NULL COMMENT '学生所在系别',PRIMARY KEY (sno)) ENGINE=Innodb AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
//插入测试数据
insert into student values(0001,'路亚','男','24','计算机网络');
insert into student values(0002,'elain','男','26','computer');
insert into student values(0003,'zhangxiao','男','28','物流');
insert into student values(0004,'jeacen','男','28','compter');
insert into student values(0005,'张扬','男','29','计算机科学');

 

b. 进行备份

mysqldump -F -uroot -proot --lock-all-tables -B student> /tmp/student.sql //加了-F参数，备份后binlog会生成新文件 /终端执行

 

c.模拟更新数据

insert into student values(0006,'jason','男','20','compter');
insert into student values(0007,'tomsion','男','27','人力资源');
drop database Demo;

3.恢复数据

mysqladmin -uroot -p flush-logs //回车输入密码

刷新并备份出需要的binlog日志文件，找到对应的logbin文件
把二进制文件转成sql文件

mysqlbinlog /var/lib/mysql/mysql-bin.000008 > /tmp/binlog.sql

然后删除文件里面无关的内容，把需要用到的语句重新运行一遍就可以了

4.完成数据恢复