MySql binlog 日志

binlog想必大家都不陌生，在主从复制或者某些情况下的数据恢复会用到。由于binlog是二进制数据，要查看一般都借助mysqlbinlog工具。

binlog 的用途

1. binlog是二进制日志文件，用于记录mysql的数据更新或者潜在更新(比如DELETE语句执行删除而实际并没有符合条件的数据)

2. 在mysql主从复制中就是依靠的binlog。

binlog 与redo/undo log的区别

1. 层次不同。redo/undo log是innodb层维护的，而binlog是mysql server层维护的，跟采用何种引擎没有关系，记录的是所有引擎的更新操作的日志记录。

2. 记录内容不同。redo/undo日志记录的是每个页的修改情况，属于物理日志+逻辑日志结合的方式（redo log物理到页，页内采用逻辑日志，undo log采用的是逻辑日志），目的是保证数据的一致性。binlog记录的都是事务操作内容，比如一条语句DELETE FROM TABLE WHERE i > 1之类的，不管采用的是什么引擎。

3. 记录时机不同。redo/undo日志在事务执行过程中会不断的写入。binlog是在事务最终commit前写入的。当然，binlog什么时候刷新到磁盘跟参数sync_binlog相关。
4. 执行SELECT等不涉及数据更新的语句是不会记binlog的，而涉及到数据更新则会记录。要注意的是，对支持事务的引擎如innodb而言，必须要提交了事务才会记录binlog。

binlog刷新到磁盘的时机跟sync_binlog参数相关，如果设置为0，则表示MySQL不控制binlog的刷新，由文件系统去控制它缓存的刷新，而如果设置为不为0的值则表示每sync_binlog次事务，MySQL调用文件系统的刷新操作刷新binlog到磁盘中。设为1是最安全的，在系统故障时最多丢失一个事务的更新，但是会对性能有所影响，一般情况下会设置为100或者0，牺牲一定的一致性来获取更好的性能。

binlog 文件内容

mysql> show binlog events in ‘mysql-bin.000002’;

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 |       195 |         106 | Server ver: 5.1.73-log, Binlog ver: 4                     |
| mysql-bin.000001 |  106 | Query       |       195 |         198 | use `hadoop`; delete from user where id=3                 |
| mysql-bin.000001 |  198 | Intvar      |       195 |         226 | INSERT_ID=4                                               |
| mysql-bin.000001 |  226 | Query       |       195 |         332 | use `hadoop`; INSERT INTO user (id,name)VALUES (NULL,1)   |
| mysql-bin.000001 |  332 | Query       |       195 |         424 | use `hadoop`; delete from user where id=3                 |
| mysql-bin.000001 |  424 | Intvar      |       195 |         452 | INSERT_ID=5                                               |
| mysql-bin.000001 |  452 | Query       |       195 |         560 | use `hadoop`; INSERT INTO user (id,name)VALUES (NULL,222) |
| mysql-bin.000001 |  560 | Query       |       195 |         660 | use `hadoop`; DELETE FROM `user` WHERE (`id`='1')         |
| mysql-bin.000001 |  660 | Intvar      |       195 |         688 | INSERT_ID=6                                               |
| mysql-bin.000001 |  688 | Query       |       195 |         795 | use `hadoop`; INSERT INTO `user` (`name`) VALUES ('555')  |
| mysql-bin.000001 |  795 | Intvar      |       195 |         823 | INSERT_ID=7                                               |
| mysql-bin.000001 |  823 | Query       |       195 |         930 | use `hadoop`; INSERT INTO `user` (`name`) VALUES ('555')  |
| mysql-bin.000001 |  930 | Intvar      |       195 |         958 | INSERT_ID=8                                               |
| mysql-bin.000001 |  958 | Query       |       195 |        1065 | use `hadoop`; INSERT INTO `user` (`name`) VALUES ('555')  |
| mysql-bin.000001 | 1065 | Intvar      |       195 |        1093 | INSERT_ID=9                                               |
| mysql-bin.000001 | 1093 | Query       |       195 |        1200 | use `hadoop`; INSERT INTO `user` (`name`) VALUES ('555')  |
| mysql-bin.000001 | 1200 | Query       |       195 |        1300 | use `hadoop`; DELETE FROM `user` WHERE (`id`='9')         |
| mysql-bin.000001 | 1300 | Query       |       195 |        1400 | use `hadoop`; DELETE FROM `user` WHERE (`id`='8')         |
| mysql-bin.000001 | 1400 | Query       |       195 |        1500 | use `hadoop`; DELETE FROM `user` WHERE (`id`='7')         |
| mysql-bin.000001 | 1500 | Query       |       195 |        1600 | use `hadoop`; DELETE FROM `user` WHERE (`id`='4')         |
| mysql-bin.000001 | 1600 | Query       |       195 |        1700 | use `hadoop`; DELETE FROM `user` WHERE (`id`='5')         |
| mysql-bin.000001 | 1700 | Query       |       195 |        1800 | use `hadoop`; DELETE FROM `user` WHERE (`id`='6')         |
| mysql-bin.000001 | 1800 | Intvar      |       195 |        1828 | INSERT_ID=10                                              |
| mysql-bin.000001 | 1828 | Query       |       195 |        1935 | use `hadoop`; INSERT INTO `user` (`name`) VALUES ('555')  |
| mysql-bin.000001 | 1935 | Intvar      |       195 |        1963 | INSERT_ID=11                                              |
| mysql-bin.000001 | 1963 | Query       |       195 |        2070 | use `hadoop`; INSERT INTO `user` (`name`) VALUES ('666')  |
| mysql-bin.000001 | 2070 | Intvar      |       195 |        2098 | INSERT_ID=12                                              |
| mysql-bin.000001 | 2098 | Query       |       195 |        2205 | use `hadoop`; INSERT INTO `user` (`name`) VALUES ('777')  |
+------------------+------+-------------+-----------+-------------+-----------------------------------------------------------+

参考资源：

链接：https://www.jianshu.com/p/c16686b35807