Mysql之利用binlog日志恢复数据操作记录

删除的数据找插入时节点进行恢复；

1.my.ini中配置

2.重启mysq服务配置生效

G:\mysql-5.7.21-winx64\ 会生成两个文件

1）二进制日志索引文件（文件名后缀为.index）用于记录所有的二进制文件 2）二进制日志文件（文件名后缀为.00000*）记录数据库所有的DDL和DML(除了数据查询语句select)语句事件。

3.登陆mysql

mysql -uy_username -py_possword

4.查看binlog日志是否开启

show variables like 'log_%';

5.查看所有binlog日志列表

show master logs;

 

6.查看master状态

即最后(最新)一个binlog日志的编号名称，及其最后一个操作事件pos结束点(Position)值

show master status;

7.flush刷新log日志

自此刻开始产生一个新编号的binlog日志文件 flush logs;

8.重置(清空)所有binlog日志

reset master;

9.查看binlog日志内容

进入日志文件存放目录 G:\mysql-5.7.21-winx64\ 下 使用 mysqlbinlog mysql-bin.000001 打开查看日志内容

可以使用 mysqlbinlog mysql-bin.000001>1234.sql 将日志文件转成 sql 文件

转成sql文件打开后

at 310 : sql开始时的pos节点

server id 1 ： 数据库主机的服务号；

end_log_pos 534 ： sql结束时的pos节点

181129 16:57:52 : sql执行时间 2018-11-29 16:57:52

10.查看binlog日志文件2

show binlog events in 'mysql-bin.000001'\G;

show binlog events in 'mysql-bin.000001';

上面这条语句可以将指定的binlog日志文件，分成有效事件行的方式返回，并可使用limit指定pos点的起始偏移，查询条数！ 如下操作示例： a）查询第一个(最早)的binlog日志： mysql> show binlog events\G;

b）指定查询 mysql-bin.000002这个文件： mysql> show binlog events in 'mysql-bin.000001'\G;

c）指定查询 mysql-bin.000002这个文件，从pos点:154开始查起： mysql> show binlog events in 'mysql-bin.000001' from 154\G;

d）指定查询 mysql-bin.000002这个文件，从pos点:154开始查起，查询10条（即10条语句） mysql> show binlog events in 'mysql-bin.000001' from 154 limit 10\G;

e）指定查询 mysql-bin.000002这个文件，从pos点:154开始查起，偏移2行（即中间跳过2个），查询10条 mysql> show binlog events in 'mysql-bin.000001' from 154 limit 2,10\G;

 

11.场景模拟：

创建数据库

CREATE TABLE `t_areainfo` (
  `id` int(11) NOT NULL,
  `level` int(11) DEFAULT '0',
  `name` varchar(255) DEFAULT '0',
  `parentId` int(11) DEFAULT '0',
  `status` int(11) DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

1. dome库会在每天凌晨4点进行一次完全备份的定时计划任务

2. 早上9点上班了，由于业务的需求会对数据库进行各种“增删改”操作。

   INSERT  INTO `t_areainfo`(`id`,`level`,`name`,`parentId`,`status`) VALUES (66,0,'中国',0,0)
   INSERT  INTO `t_areainfo`(`id`,`level`,`name`,`parentId`,`status`) VALUES (67,0,'中国',0,0)

3. 中午又执行了修改数据操作：

   UPDATE `t_areainfo` t SET t.level = 1 WHERE t.id = 67
   

4. 在下午18:00的时候，手贱执行了drop语句，直接删除了dome库

   drop database dome;
   

5. 这种时候，一定不要慌张！！！ 先备份一下最后一个binlog日志文件：

6. 接着执行一次刷新 flush logs; 日志索引操作，重新开始新的binlog日志记录文件 会生成新的 mysql-bin.000003；

7. 按理说mysql-bin.000002这个文件不会再有后续写入了，因为便于我们分析原因及查找pos节点，以后所有数据库操作都会写入到新的mysql-bin.000003日志文件。

8. 读取binlog日志， show binlog events in 'mysql-bin.000002';分析问题。

   通过分析，造成数据库破坏的pos点区间是介于 1106--1198之间（这是按照日志区间的pos节点算的），只要恢复到1106前就可。

9. 先把凌晨4点全备份的数据恢复：（先前有备份）

10. 从binlog日志恢复凌晨4后数据

    在mysql 安装目录bin 目录下执行

    

    mysqlbinlog mysql-bin.0000xx | mysql -u用户名 -p密码 数据库名

    常用参数选项解释： --start-position=1106 起始pos点 --stop-position=1198 结束pos点 --start-datetime="2018-11-29 12:35:21" 起始时间点 --stop-datetime="2018-11-29 13:35:21" 结束时间点 --database=zyyshop 指定只恢复zyyshop数据库(一台主机上往往有多个数据库，只限本地log日志)

    不常用选项： -u --user=name 连接到远程主机的用户名 -p --password[=name] 连接到远程主机的密码 -h --host=name 从远程主机上获取binlog日志 --read-from-remote-server 从某个MySQL服务器上读取binlog日志

    这些命令、文件尽量写成绝对路径；

    a）完全恢复(需要先手动vim编辑mysql-bin.000003，将那条drop语句剔除掉)

    mysqlbinlog ../mysql-bin.000002 | mysql -uroot -p123456 -v dome

    b）指定pos结束点恢复(部分恢复)：

    mysqlbinlog --stop-position=1106  ../mysql-bin.000002 | mysql -uroot -p123456

    

12.恢复误删

需要恢复误删除的某条数据则需找到写入该条数据的pos 起始节点和结束节点 进行恢复

mysqlbinlog --start-position=502004 --stop-position=502263 C:/Users/MACHENIKE/Desktop/mysql-bin.002172 | G:\mysql-5.7.21-winx64/bin/mysql -uroot -p123456

13.binlog sql乱码问题

my.ini中配置

transaction_isolation = REPEATABLE-READ binlog_format=MIXED

隔离级别太低了，我的默认隔离级别是READ-COMMITTED，所以导致binlog记录的必须都是row模式，解析出来的是乱码，强行用-v显示出来也是row模式，所以我将隔离级别升级为REPEATABLE-READ的话，就会有row格式也会有statement格式了。

14.mysql隔离级别

事务的并发问题

1、脏读：事务A读取了事务B更新的数据，然后B回滚操作，那么A读取到的数据是脏数据

2、不可重复读：事务 A 多次读取同一数据，事务 B 在事务A多次读取的过程中，对数据作了更新并提交，导致事务A多次读取同一数据时，结果 不一致。

4、幻读：系统管理员A将数据库中所有学生的成绩从具体分数改为ABCDE等级，但是系统管理员B就在这个时候插入了一条具体分数的记录，当系统管理员A改结束后发现还有一条记录没有改过来，就好像发生了幻觉一样，这就叫幻读。

小结：不可重复读的和幻读很容易混淆，不可重复读侧重于修改，幻读侧重于新增或删除。解决不可重复读的问题只需锁住满足条件的行，解决幻读需要锁表

事务隔离级别

1、读未提交：事务A可以读取事务B未提交的数据（不可重复读），事务B回滚则（脏读）

2、读已提交：事务A读不到读事务B未提交数据，事务A在事务B开启读到的数据与事务B修改数据提交后读到的数据 不同（不可重复读）

3、可重复读：事务A在事务B开启读到的数据与事务B修改数据提交后读到的数据 不变，但是更新操作却使用事务B更新后的数据

4、串行化：事务A未提交前事务B不可操作，锁表

 

补充

1、事务隔离级别为读提交时，写数据只会锁住相应的行

2、事务隔离级别为可重复读时，如果检索条件有索引（包括主键索引）的时候，默认加锁方式是next-key 锁；如果检索条件没有索引，更新数据时会锁住整张表。一个间隙被事务加了锁，其他事务是不能在这个间隙插入记录的，这样可以防止幻读。

3、事务隔离级别为串行化时，读写数据都会锁住整张表

4、隔离级别越高，越能保证数据的完整性和一致性，但是对并发性能的影响也越大。