mysql使用二进制日志恢复数据
一、恢复到某个二进制文件
1.开启二进制日志
在mysqld的配置节点下添加如下配置
log-bin="E:/Mysql57BinLog/binlog"(windows下的路径,linux下自行修改路径)
expire_logs_days=10
max_binlog_size=100M
expire_logs_days=10
max_binlog_size=100M
2.重启mysql服务
使用命令show VARIABLES like '%log_bin%';查看
![](https://images2015.cnblogs.com/blog/345832/201605/345832-20160519160402419-1628420057.png)
3.创建库和表
create database mytest;
use mytest;
create table t(a int PRIMARY key)ENGINE = INNODB DEFAULT CHARSET=utf8;
flush logs;
use mytest;
create table t(a int PRIMARY key)ENGINE = INNODB DEFAULT CHARSET=utf8;
flush logs;
flush logs,刷新二进制日志后会多出来一个二进制日志
![](https://images2015.cnblogs.com/blog/345832/201605/345832-20160519160439138-122508735.png)
使用命令查看二进制日志内容
![](https://images2015.cnblogs.com/blog/345832/201605/345832-20160519160459623-1488776862.png)
默认会读取配置文件,检测到no--beep会报错。
推荐使用命令:mysqlbinlog --no-defaults E:\Mysql57BinLog\binlog.000001
![](https://images2015.cnblogs.com/blog/345832/201605/345832-20160519160520404-1737319888.png)
4.插入数据
use mytest;
insert into t select 1 union all select 2 union all select 3;
flush logs;
insert into t select 1 union all select 2 union all select 3;
flush logs;
![](https://images2015.cnblogs.com/blog/345832/201605/345832-20160519160559748-1921486361.png)
5.删除数据库
drop database mytest;
flush logs;
flush logs;
![](https://images2015.cnblogs.com/blog/345832/201605/345832-20160519160623748-439208554.png)
6.恢复数据
mysqlbinlog --no-defaults E:\Mysql57BinLog\binlog.000001 E:\Mysql57BinLog\binlog.000002 E:\Mysql57BinLog\binlog.000003 | mysql -u root -p
![](https://images2015.cnblogs.com/blog/345832/201605/345832-20160519160646669-317579787.png)
![](https://images2015.cnblogs.com/blog/345832/201605/345832-20160519160709279-1419495778.png)
数据已还原。
-----------------------------------华丽的分割线--------------------------------------------------------------
二、恢复到某一时间点的数据
create table t2(a int PRIMARY key)ENGINE=INNODB default CHARSET=utf8;
insert into t2 values(1),(2),(3),(4),(5);
insert into t2 values(1),(2),(3),(4),(5);
>mysqlbinlog --no-defaults E:\Mysql57BinLog\binlog.000006
![](https://images2015.cnblogs.com/blog/345832/201605/345832-20160519160738216-444684527.png)
删除数据
delete from t2 where a < 4;
![](https://images2015.cnblogs.com/blog/345832/201605/345832-20160519160800451-1496388961.png)
恢复数据
![](https://images2015.cnblogs.com/blog/345832/201605/345832-20160519160818685-1153468423.png)
drop database mytest;
删除库mytest,回到最原始的地方
mysqlbinlog --no-defaults --start-position="4" --stop-position="1285" E:\Mysql57BinLog\binlog.000006 | mysql -u root -p
![](https://images2015.cnblogs.com/blog/345832/201605/345832-20160519160843560-1994185604.png)
![](https://images2015.cnblogs.com/blog/345832/201605/345832-20160519160859591-617077743.png)
数据恢复成功。
欢迎大家拍砖~~