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
发现数据又回来了