MySQL如何传输二进制日志（二）

1、二进制日志简介

MySQL中的二进制日志主要有两个功能：数据恢复和数据复制。
第一：数据恢复

例如我们可以每天午夜12:00进行数据备份。但是，此类备份功能并不是对数据库的实时备份，如果数据库在下午17:00出现故障无法恢复，那么从前一天午夜12:00到当天下午17:00的数据库内容将丢失。通过二进制日志可以解决这个问题。可以通过前一天午夜12:00的数据库备份文件恢复数据库，然后使用二进制日志恢复从前一天午夜12:00到当天下午17:00的数据库内容。
第二：数据复制
MySQL支持主从服务器间的数据复制功能，并通过该功能实现数据库的冗余机制以保证数据库的可用性和提高数据库的性能。MySQL正是通过主服务器的二进制日志来实现数据的传递。主服务器上的二进制日志内容会被发送到各个从服务器，并在每个从服务器上执行，从而保证了主从服务器之间数据的一致性。在默认配置下，MySQL不记录二进制日志。可以通过设置参数--log-bin=[base_name]启用二进制日志功能，MySQL会将修改数据库内容的语句记录到以base_name-bin.00000X为名字的日志文件中。其中bin代表binary，后缀00000X为二进制日志文件顺序。每次MySQL启动时或者调用命令FLUSH LOGS强制轮换二进制日志，日志文件顺序会自动加1。

我们可以从前面介绍的二进制日志的功能(数据恢复和数据复制)看出，二进制日志主要是供MySQL内部使用的，并不是为了数据库管理员阅读使用。因此二进制日志文件的格式不是文本格式，其内容不能通过记事本直接查看。但是，为了管理员的方便，MySQL也提供了一个名为mysqlbinlog的工具，用于查看和处理二进制日志文件的内容。mysqlbinlog的用法为直接在命令后面添加二进制日志文件作为参数，比如：$mysqlbinlog mysql-bin.000001，其中mysql-bin.000001为二进制日志文件。

2、二进制日志的两种方式

第一种：基于语句的复制

基于语句的复制把修改数据的语句写到二进制日志中，然后复制到slave服务器，或者用来做PITR还原。在这里我们来看看MySQL日志是怎么记录“不常见”的查询，如事件，函数，存储过程，局部变量等等。我们将得知我们会遇到的问题以及如何解决他们。

(1). 关于触发器的处理：

当一个查询触发了一个触发器，记录的只有本查询语句，触发器里的语句不会被记录。如果你想要维护数据的一致性，那么就必须保证主从服务器的触发器定义是一样的。

例如:

对应生成的binlog可能如下：

我们可以发现，触发器中对t_copy表的操作并没有记录到二进制日志中。这个特性可能是一个要考虑的问题，或在别的情况下在基础架构方面帮助我们。如果你需要相同的语句在不同的复制服务器之间产生不同的操作，定义不同的触发器就可以做到。

(2). 关于函数的处理：

调用函数的语句直接记录到二进制日志中。所以如果你没有在所有的服务器上创建全部函数，你将会中断复制，slave服务器上面的SQL线程会停止并报出一个错误。

例如：

对应生成的binlog可能如下：

如果你忘记在slave服务器创建函数了，复制将会中断。执行SHOW SLAVE STATUS你将看到如下信息：

Last_Error: Error 'FUNCTION test.this_year does not exist' on query. Default database: 'test'. Query: 'insert into t VALUES(this_year())'

(3). 关于存储过程的处理：

存储过程和函数的特性完全不同。如果利用存储过程写数据到表里，存储过程里面的SQL语句被记录到日志，而不是记录CALL语句本身。所以在这个情况下你不需要复制存储过程到slave服务器。

例如：

对应生成的binlog可能如下：

我们对它做加强一点难度。我们的存储过程修改一个表，这个表有一个INSERT触发器，我们用前面的函数去插入当前年份。根据前面的分析的规则，很容易想象到：存储过程里面的显式调用语句被记录到日志，触发器里执行的没有被记录。我们有一个触发器调用一个存储过程，在这个情况下，不管是触发器还是存储过程里面的查询语句都被记录了。在这个情况下你需要在slave服务器手动创建触发器和存储过程。

第一种：基于行的复制

在基于行的复制模式下，Binnary Log可以不记录执行的SQL语句的上下文相关信息，只要记录哪一行修改了，修改成什么样子。基于行的复制模式会详细的记录下每一行数据的修改细节，而且不会出现某个特定情况下的存储过程，或Function，以及Trigger的调用和触发无法被正确复制问题。

3、二进制日志管理

第一、二进制日志包含的内容及作用

1. 包含了所有更新了数据或者已经潜在更新了数据(比如没有匹配任何行的一个DELETE)。
2. 包含了关于每个更新数据库(DML)的语句的执行时间信息。
3. 不包含没有修改任何数据的语句(例如纯粹的Select语句)，如果需要启用该选项，需要开启通用日志功能。
4. 主要目的是尽可能的将数据库恢复到数据库故障点，因为二进制日志包含备份后进行的所有更新。
5. 用于在主服务器上记录所有将发送给从服务器的语句。
6. 启用该选项数据库性能降低1%，但保障数据库完整性，对于重要数据库值得以性能换完整。

第二、开启二进制日志的方法及属性

1. 在配置文件中指定log-bin，启动时mysqld写入包含所有更新数据的SQL命令到二进制日志文件中。
2. 若当前的日志大小达到max_binlog_size，则自动创建新的二进制日志文件。或者是主服务器重启的时候，也会自动的创建新的二进制日志文件。又或者是运行FLUSH LOGS命令，强制轮换二进制日志文件。
3. 二进制日志文件会有一个对应二进制日志索引文件，该文件包含所有的二进制日志，其文件名与二进制日志相同，扩展名为.index。

4、和二进制日志相关的配置参数

max_binlog_size、binlog_cache_size、sync_binlog、binlog-do-db、binlog-ignore-db、log-slave-update、binlog_format
第一、max_binlog_size

该参数指定了单个二进制日志文件的最大值，如果超过该值，则产生新的二进制日志文件，后缀名加1，并记录到.index文件，从Mysql 5.0开始的默认值为1073741824，代表1G。
Mysql5.5的默认值如下：

当使用的存储引擎是InnoDB时，所有未提交的事务会记录到一个缓存中，等待事务提交时，直接将缓冲中的二进制日志写入二进制日志文件，而该缓冲的大小由binlog_cache_size决定，默认大小为32KB。此外，binlog_cache_size是基于session的，也就是说当一个线程开始一个事务时，mysql会自动分配一个大小为binlog_cache_size的缓存，因此该值得设置需要相当小心，可以通过show global status 查看binlog_cache_use、binlog_cache_disk_use的状态，可以判断当前binlog_cache_size的设置是否合适。

第二：Binlog_cache_size的默认大小为32KB

第三、参数sync_binlog=[N]表示每写缓存多少次就同步到磁盘。如果将N设置为1，则表示采用同步写磁盘的方式来写二进制日志，该参数很重要，这个以后还会提到。值得注意的是，在将该参数设置为1时，也应该将innodb_support_xa设为1来解决，这可以确保二进制日志和InnoDB存储引擎数据文件的同步。
第四、参数binlog-do-db和binlog-ignore-db表示需要写入或者忽略写入哪些库的日志，默认值为空，则表示将所有库的日志同步到二进制日志。
第五、Log-slave-update

该参数在搭建master=>slave=>slave的架构时，需要配置。

第六、Binlog_format参数也特别重要。从mysql5.1版本开始引入这个参数，该参数可以设置的值有STATEMENT、ROW、MIXED；
（1）STATEMENT格式和之前的mysql版本一样，二进制日志文件记录的是日志的逻辑SQL语句。
（2）在ROW格式下，二进制日志记录的不再是简单的SQL语句了，而是记录表的行更改情况，此时可以将InnoDB的事务隔离基本设为READ COMMITTED，以获得更好的并发性。
（3）MIXED格式下，mysql默认采用的STATEMENT格式进行二进制日志文件的记录，但是在一些情况下会使用ROW格式，可能的情况包括：
1）表的存储引擎为NDB，这时对于表的DML操作都会以ROW格式记录
2）使用了UUID()、USER()、CURRENT_USER()、FOUND_ROWS()、ROW_COUNT()等不确定函数
3）使用了INSERT DELAY语句
4）使用了用户定义函数
5）使用了临时表

5、如何传输二进制日志

MySQL Replication可以很方便的用来做应用的读扩展，也可以帮MySQL实现一定程度的HA方案。MySQL通过向slave库传送二进制日志来实现Replication，本文将通过二进制日志相关源代码的主要接口来解释：“MySQL如何传输二进制日志，是主库推，还是备库拉？MySQL日志传输的实时性如何？”。

在MySQL Replication结构中，slave库端初次通过CHANGE MASTER TO完成Replication配置，再使用start slave命令开始复制。更细致的，slave库通过IO Thread向主库发起读取binlog的请求（COM_BINLOG_DUMP命令），主库收到COM_BINLOG_DUMP请求后，在主库中使用单独线程（dump thread）不断向slave库IO Thread发送Binlog。示意图如下：

在主库端一旦有新的日志产生后，立刻会发送一次广播，dump线程在收到广播后，则会读取二进制日志并通过网络向slave库传输日志，所以这是一个主库向备库不断推送的过程；新日志在产生后，只需一次广播和网络就会立刻（<1ms）发送到slave库。如果主从之间网络较好的话（例如RTT<1ms），slave库端的日志也就小于2ms了。所以，一般的（依赖于RTT），slave库的实时性都非常好。

参考文献：

1. MySQL如何传输二进制日志

2. 基于语句复制下的存储程序，触发器和事件

3. MySQl Study学习之--MySQl二进制日志管理