mysql数据恢复日志路径_mysql的binlog日志记录和数据恢复

1、binlog开启

1)、检查是否开启 binlog

注意：开启binlog后mysqld服务需要重启，请提前做好准备工作！

show variables like '%log_bin%';

2)、开启 binlog：

编辑 my.cnf 文件，在[mysqld]下面添加下面三个配置：

server-id = 1 # (单个节点 id)

log-bin= /var/lib/mysql/mysql-bin # 位置一般和 mysql 库文件所在位置一样

expire_logs_days = 10 #表示此日志保存时间为 10 天,重启 mysqld

# 再次查看 binlog 日志开启状态为 ON

3)、参数说明：

Binlog 日志包括两类文件；第一个是二进制索引文件(后缀名为.index),第二个为日志文件(后缀名为.00000*),记录数据库所有的 DDL 和 DML(除了查询语句 select)语句事件

4)、查看所有 binlog 日志文件列表:show master logs; 或者 SHOW BINARY LOGS;

5)、查看最后一个 binlog 日志的编号名称及其最后一个操作事件 pos 结束点的值：show master status;

6)、Flush logs 刷新日志，此刻开始产生一个新编号的 binlog 文件，例如：

我们看到 mysql-bin.000004 这个是刚刚生成的。

注意：每当 mysqld 服务重启时，会自动执行刷新 binlog 日志命令，mysqldump 备份数据时加-F 选项也会刷新 binlog 日志

7)、清空所有 binlog 日志命令:reset master; (慎用！！！)

8)、查看 biblog 内容：

使用 mysqlbinlog binlog_files

该方式不好观察，使用 show binlog events in 'ysql-bin.000004'; 这种形式可以方便的观察内容。从指定索引位置开始查看： show binlog events in 'mysql-bin.000004' from 3183;

show binlog events in 'mysql-bin.000004' from 3183 limit 2,4; 分页查询

2、使用 binlog 恢复数据

mysqlbinlog 的使用说明：

https://dev.mysql.com/doc/refman/5.7/en/mysqlbinlog.html

先使用 flush logs;新创建 binlog 文件，然后创建表，新增数据。

测试数据为：

CREATE TABLE `emp` (

`eid` int(20) NOT NULL,

`ename` varchar(10) DEFAULT NULL,

`birth` date DEFAULT NULL,

`sal` double DEFAULT NULL,

`did` int(20) DEFAULT NULL,

`passwd` varchar(20) DEFAULT NULL,

`degree` varchar(255) DEFAULT NULL,

`sex` int(11) NOT NULL,

PRIMARY KEY (`eid`),

KEY `emp_name_sal` (`ename`,`sal`) USING BTREE,

KEY `idx_birth` (`birth`) USING BTREE

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `bart`.`emp` (`eid`, `ename`, `birth`, `sal`, `did`, `passwd`, `degree`, `sex`) VALUES

('3', 'bart', '2017-07-03', '10000', '2', '123', '\'本科\'', '1');

INSERT INTO `bart`.`emp` (`eid`, `ename`, `birth`, `sal`, `did`, `passwd`, `degree`, `sex`) VALUES

('4', 'homer', '2017-07-20', '9000', '2', '123', '硕士', '1');

INSERT INTO `bart`.`emp` (`eid`, `ename`, `birth`, `sal`, `did`, `passwd`, `degree`, `sex`) VALUES

('5', 'marge', '2017-07-22', '8000', '3', '123', '本科', '1');

INSERT INTO `bart`.`emp` (`eid`, `ename`, `birth`, `sal`, `did`, `passwd`, `degree`, `sex`) VALUES

('6', 'xg', NULL, '8500', '1', NULL, '博士', '0');

INSERT INTO `bart`.`emp` (`eid`, `ename`, `birth`, `sal`, `did`, `passwd`, `degree`, `sex`) VALUES

('7', 'xg', NULL, '8500', '1', NULL, '硕士', '1');

INSERT INTO `bart`.`emp` (`eid`, `ename`, `birth`, `sal`, `did`, `passwd`, `degree`, `sex`) VALUES

('10', 'aaa', '2017-09-09', '123.456', '1', NULL, '本科', '1');

INSERT INTO `bart`.`emp` (`eid`, `ename`, `birth`, `sal`, `did`, `passwd`, `degree`, `sex`) VALUES

('11', 'bbb', NULL, '9000', '1', NULL, '博士', '1');

INSERT INTO `bart`.`emp` (`eid`, `ename`, `birth`, `sal`, `did`, `passwd`, `degree`, `sex`) VALUES

('12', 'ccc', NULL, '10000', '2', '123', '硕士', '0');

INSERT INTO `bart`.`emp` (`eid`, `ename`, `birth`, `sal`, `did`, `passwd`, `degree`, `sex`) VALUES

('13', 'ddd', NULL, '12000', '3', '123', '本科', '1');

将测试数据导入库中后，查看日志：show master logs;

此时有两个日志，第1个是我们创建表和插入数据之前的，第2 个是记录我们刚刚的操作的。

我们现在故意制造一些“生产事故”，比如：现在不小心把所有的 ename 字段全部更新成了 NULL update emp set ename = NULL;因为已经 commit 所以此时使用 rollback 已经无效。 次数数据已经被损坏，我们需要修复到修改之前的状态。

恢复

通过 pos 恢复

1、 出错之后先 flush logs; 保存当前的 log，生成新的 log 是为了将旧的 log 隔离，方便我们恢复，因为接下来的操作会进入新的 log 中。

其中标红的就是记录我我们刚刚操作的那个 log。

2、查询日志： show binlog events in 'mysql-bin.000001' ;

可以看到红圈标注的是 update 操作的 pos，在他之前有个 begin 事物开始点(pos 是 3745)之后有个 commit 事务提交(pos 是 4501)我们需要恢复没有被更新之前的状态，所以我们的使用 pos 恢复的 stop-postion 就是事务的开始索引位置即 3745，恢复命令为：

/usr/bin/mysqlbinlog --stop-position=3745 --database=bart /var/lib/mysql/mysql-bin.000001 | /usr/bin/mysql -uroot -p123456 -v

成功后，查看 emp 发现被置位 NULL 的 ename 字段又恢复了。

通过时间恢复

人工制造一个“事故”，比如：不小心执行了命令：drop database bart; 直接把库删除了(这个命令生产中慎用！)

1、先保护现场，flush logs;

2、查看日志文件: show binlog events;

先创建被删除的数据库 createdatabase bart;、查看日志show binlog events in 'mysql-bin.000001' ; 找到创建表的日志开始位置：

3、在找到修改表之前的位置：

4、恢复语句：

(1、position 方式)

/usr/bin/mysqlbinlog --start-position=400 --stop-position=3745 --database=bart /var/lib/mysql/mysql-bin.000001 | mysql -uroot -p123456 -v

(2、time 方式)

首先我们使用 mysqlbinlog [binlog_files] 查看日志，找到需要恢复的时间段：

找到创建表的位置：

找到更新表之前的位置：

使用命令为：

/usr/bin/mysqlbinlog --start-datetime='2019-10-25 17:00:35' --stop-datetime='2019-10-25 17:01:42' --database=bart /var/lib/mysql/mysql-bin.000001 | /usr/bin/mysql -uroot -p123456

-v

发现数据又回来了