mysql binlog日志 可以删除 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

发现数据又回来了

参考博客：