mysql开启二进制日志,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%’;查看
![%title插图%num 345832-20160519160402419-1628420057](https://blog-1251118125.cos.ap-guangzhou.myqcloud.com/2019/07/345832-20160519160402419-1628420057.png)
3.创建库和表
create database mytest;
use mytest;
create table t(a int PRIMARY key)ENGINE = INNODB DEFAULT CHARSET=utf8;
flush logs;
flush logs,刷新二进制日志后会多出来一个二进制日志
![%title插图%num 345832-20160519160439138-122508735](https://blog-1251118125.cos.ap-guangzhou.myqcloud.com/2019/07/345832-20160519160439138-122508735.png)
使用命令查看二进制日志内容
![%title插图%num 345832-20160519160459623-1488776862](https://blog-1251118125.cos.ap-guangzhou.myqcloud.com/2019/07/345832-20160519160459623-1488776862.png)
默认会读取配置文件,检测到no–beep会报错。
推荐使用命令:mysqlbinlog –no-defaults E:\Mysql57BinLog\binlog.000001
![%title插图%num 345832-20160519160520404-1737319888](https://blog-1251118125.cos.ap-guangzhou.myqcloud.com/2019/07/345832-20160519160520404-1737319888.png)
4.插入数据
use mytest;
insert into t select 1 union all select 2 union all select 3;
insert into t select 1 union all select 2 union all select 3;
flush logs;
![%title插图%num 345832-20160519160559748-1921486361](https://blog-1251118125.cos.ap-guangzhou.myqcloud.com/2019/07/345832-20160519160559748-1921486361.png)
5.删除数据库
drop database mytest;
flush logs;
![%title插图%num 345832-20160519160623748-439208554](https://blog-1251118125.cos.ap-guangzhou.myqcloud.com/2019/07/345832-20160519160623748-439208554.png)
6.恢复数据
mysqlbinlog –no-defaults E:\Mysql57BinLog\binlog.000001 E:\Mysql57BinLog\binlog.000002 E:\Mysql57BinLog\binlog.000003 | mysql -u root -p
![%title插图%num 345832-20160519160646669-317579787](https://blog-1251118125.cos.ap-guangzhou.myqcloud.com/2019/07/345832-20160519160646669-317579787.png)
![%title插图%num 345832-20160519160709279-1419495778](https://blog-1251118125.cos.ap-guangzhou.myqcloud.com/2019/07/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);
>mysqlbinlog –no-defaults E:\Mysql57BinLog\binlog.000006
![%title插图%num 345832-20160519160738216-444684527](https://blog-1251118125.cos.ap-guangzhou.myqcloud.com/2019/07/345832-20160519160738216-444684527.png)
删除数据
delete from t2 where a < 4;
![%title插图%num 345832-20160519160800451-1496388961](https://blog-1251118125.cos.ap-guangzhou.myqcloud.com/2019/07/345832-20160519160800451-1496388961.png)
恢复数据
![%title插图%num 345832-20160519160818685-1153468423](https://blog-1251118125.cos.ap-guangzhou.myqcloud.com/2019/07/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
![%title插图%num 345832-20160519160843560-1994185604](https://blog-1251118125.cos.ap-guangzhou.myqcloud.com/2019/07/345832-20160519160843560-1994185604.png)
![%title插图%num 345832-20160519160859591-617077743](https://blog-1251118125.cos.ap-guangzhou.myqcloud.com/2019/07/345832-20160519160859591-617077743.png)
数据恢复成功。