MySQL的binlog恢复（Windows下）

MySQL的binlog恢复（Windows下）

前⾔

在最近的⼯作中，由于⾃⼰粗（zuo）⼼（si）误update操作导致⼏百⾏的数据出现错误，在⼼急如焚的同时（那时候我竟然不知道除了备份之后还有binlog⽇志恢复）⽴马查资料学习binlog的恢复，随后⽴马进⾏了恢复。虽然可以假装⾃⼰没出错（emmmmm......最后还是得承认的！），但下班之后⼼情不能久久平复，⽴马打开电脑进⾏⼀次实验记录才能对得起⾃⼰犯的错误。

注：此次实验是在Wnidows下进⾏的（⽹上Linux挺多，但是Windows的甚少，加上本⾝我的电脑也是Win7就简单做⼀次实验吧！

1、初识binlog

（1）MySQL的binlog就是⼤家经常所说的Binary Log，即bin-log，是MySQL存储对数据库改动的⼆进制⽂件，也就是记录了所有DDL与DML（select除外）语句，利⽤它主要可以做两件事：

数据恢复：通过mysqlbinog⼯具进⾏恢复；

数据复制：MySQL Replication在Master端开启binlog，Mster把它的⼆进制⽇志传递给slaves来达到master-slave数据⼀致的⽬的。

（2）如何查看MySQL的⽇志情况（是否开启等），当MySQL还没开启时候，通过命令：show variables like ‘log_bin%'查看

mysql> show variables like'log_bin%';

+---------------------------------+-------+

| Variable_name | Value |

+---------------------------------+-------+

| log_bin |OFF|

| log_bin_basename ||

| log_bin_index ||

| log_bin_trust_function_creators |OFF|

| log_bin_use_v1_row_events |OFF|

+---------------------------------+-------+

5 rows in set

（3）如何在Wnidows在修改log_bin状态为ON开启呢？

找到C:\ProgramData\MySQL\MySQL Server 5.7\my.ini⽂件（注意：是C盘下隐藏⽂件夹ProgramData，⽽不是Program Files下）

增加/修改常⽤的配置属性

（4）常⽤简单属性说明：除了以上log_bin、binlog_format两个简单的配置外，还可以有其他的属性配置

log-bin = /xxx/xxx/mysql_bin #binlog⽇志⽂件,以mysql_bin开头，六个数字结尾的⽂件：mysql_bin.000001，并且会将⽂件存储在相应的xxx/xxx路径下，如果只配置mysql_bin 的话默认在C:\ProgramData\MySQL\MySQL Server 5.7\Data下；

binlog_format = ROW #binlog⽇志格式，默认为STATEMENT：每⼀条SQL语句都会被记录；ROW：仅记录哪条数据被修改并且修改成什么样⼦，是binlog开启并且能恢复数据的关键；

expire_logs_days= 7 #binlog过期清理时间；

max_binlog_size = 100m #binlog每个⽇志⽂件⼤⼩；

binlog_cache_size = 4m #binlog缓存⼤⼩；

max_binlog_cache_size = 512m #最⼤binlog缓存⼤⼩。

2、恢复数据测试

（1）准备表user

mysql>select*from user;

+----+----------+----------------------------------+

| id | name | password |

+----+----------+----------------------------------+

|1| Zhangsan | 2d7284808e5111e8af74201a060059ce |

|2| Lisi | 2d73641c8e5111e8af74201a060059ce |

|3| Wangwu | 2d73670c8e5111e8af74201a060059ce |

+----+----------+----------------------------------+

3 rows in set

（2）误update恢复，⽐如我在update user set name = 'Lijian' where id = 1;的时候忘写where id = 1重要条件，结果导致全部数据被更新

mysql>update user set name ='Lijian';

Query OK, 3 rows affected

Rows matched: 3 Changed: 3 Warnings: 0

mysql>select*from user;

+----+--------+----------------------------------+

| id | name | password |

+----+--------+----------------------------------+

|1| Lijian | 2d7284808e5111e8af74201a060059ce |

|2| Lijian | 2d73641c8e5111e8af74201a060059ce |

|3| Lijian | 2d73670c8e5111e8af74201a060059ce |

+----+--------+----------------------------------+

3 rows in set

这个时候你肯定很慌，但是先不要慌（实际上慌也没⽤），先看没有备份，如果没有再看是否开启binlog（show variables like ‘log_bin%'），如果两者都没有（我相信⼤家都会定时备份+binlog）从数据库这个层⾯是⽆法恢复的了，如果binlog开启的话，⼀切都好说。就开始执⾏下⾯⼏步恢复吧！

第⼀步：找到当前mysql记录的binlog⽂件，执⾏show master status;

第⼆步：查看binlog，定位误操作的pos或者时间段。执⾏show binlog events in 'mysql_bin.000001';

mysql> show binlog events in'mysql_bin.000001';

+------------------+-----+----------------+-----------+-------------+---------------------------------------+

| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |

+------------------+-----+----------------+-----------+-------------+---------------------------------------+

| mysql_bin.000001|4| Format_desc |1|123| Server ver: 5.7.12-log, Binlog ver: 4|

| mysql_bin.000001|123| Previous_gtids |1|154||

| mysql_bin.000001|154| Anonymous_Gtid |1|219|SET@@SESSION.GTID_NEXT='ANONYMOUS'|

| mysql_bin.000001|219| Query |1|291|BEGIN|

| mysql_bin.000001|291| Table_map |1|344| table_id: 108 (er) |

| mysql_bin.000001|344| Update_rows |1|650| table_id: 108 flags: STMT_END_F |

| mysql_bin.000001|650| Xid |1|681|COMMIT/* xid=22 */|

+------------------+-----+----------------+-----------+-------------+---------------------------------------+

7 rows in set

第三步：进⼊C:\ProgramData\MySQL\MySQL Server 5.7\Data执⾏mysqlbinlog --start-position=219 --stop-position=681 mysql-bin.000001 > e:\\update.sql将update部分单独备份出来到E盘下为update.sql

第四步：登录mysql（mysql -uroot -p123）；

第五步：执⾏source e:update.sql恢复数据，部分截图如下：

第六步：查看结果

mysql>select*from user;

+----+----------+----------------------------------+

| id | name | password |

+----+----------+----------------------------------+

|1| Zhangsan | 2d7284808e5111e8af74201a060059ce |

|2| Lisi | 2d73641c8e5111e8af74201a060059ce |

|3| Wangwu | 2d73670c8e5111e8af74201a060059ce |

+----+----------+----------------------------------+

3 rows in set

3、总结

（1）使⽤binlog只能针对针对数据量不是很多的情况，真正的⽣产环境每个⼏个G的⽇志⽂件，不⽌是光靠binlog恢复的，还有更多的办法，在此只是做⼀个简单的学习记录！ （2）判断时间binlog⽇志的时间阶段与pos位置很重要，但是需要知道关于binlog的很多参数！

（3）之前天真的以为开发⼈员就不需要太了解数据库相关的运维，但是今天经历过才知道数据库的相关知识也是开发⼈员必须了解的！