3. 二进制日志(Binary Log)

　　二进制日志中记录对数据库的修改事件，比如建表操作，数据修改操作等，另外，不是说一定有数据被修改才会被记入二进制日志，比如像DELETE语句，即使未匹配删除任何数据，也有可能被记录(视事件记录格式而定)，同时，二进制日志还将包含事件执行花费的时间。

　　通过二进制日志，能够实现两个重要的功能：

• 用于复制。Master端将二进制日志发送至slave端，slave端即可根据二进制日志中记录的语句，在本地重做，以达到主从同步的目的。
• 用于恢复。二进制日志可用于数据恢复，当使用备份恢复了数据库后，通过应用二进制日志文件，能够实现将数据库恢复到故障发生前的状态。

　　启用二进制日志可能会对性能有一定影响，不过，考虑到二进制日志在数据安全和数据恢复方面的巨大作用，DBA需要在效率和安全之间做个选择。

　　二进制日志文件有一堆的系统环境变量控制，这部分信息参考官方文档：16.1.3.4

　　对于SELECT/SHOW等完全不涉及数据修改的语句，不会被记录到二进制日志，要记录所有数据库执行的语句，可以使用普通查询日志。这部分内容参考本单其它小节的内容。

　　二进制日志应该被保护，因为其记录的日志中可能包含密码相关的操作。

　　二进制日志中记录事件的格式依赖于二进制日志格式的设置，当前支持三种格式：row-based logging, statement-based logging, mixed-based logging。

　　启用二进制日志，在启动MySQL服务时指定--log-bin[=base_name]选项，如果不指定参数值，则默认文件名为pid-file.bin，保存在data路径下。

　　mysqld自动附加日志序号到生成的二进制日志文件，序号会以递增方式生成。当启动服务或者刷新日志时都会创建新的日志文件，同时当日志文件大小达到max_binlog_size时也会创建新的二进制日志文件，不过，生成的日志文件仍有可能超出max_binlog_size参数指定的值，比如当执行一个超大事务的时候，由于事务自动特性必须连续，因此这种情况下，该事件会写到同一日志文件，可能造成日志文件超出指定参数值的现象。

　　为了能够跟踪哪些二进制日志被用过，mysqld同时创建一个同名(但扩展名为.index)二进制日志索引文件(注意不要联机手动编辑该文件)，它将包含所有使用的二进制日志文件，可以通过--log-bin-index[=file_name]参数指定该文件的名称和存储路径。

　　术语：

• 二进制日志文件(binary log file)：表示包含数据库事件的文件。
• 二进制日志(binary log)：表示包含记录数据库事件的文件的文件集。

　　服务器提供了--binlog-do-db和--binlog-ignore-db两个选项可以指定某库记录(或不记录)二进制日志，类似--replicate-do-db和--replicate-ignore-db选项。具体可以参考初始化参数章节。

　　复制的slave端默认情况下，不会将接收到的master端的修改事件写到自己的binary log中(默认这部分日志是写在relay-log)，要记录这些修改的话，启动slave时附加--log-slave-updates选项。这种设置可用于数据的级联复制，即slave被当做其它服务器的master。

　　删除所有二进制日志可以通过RESET MASTER语句，或PURGE BINARY LOGS命令删除指定日志。

　　对于复制环境，删除二进制日志文件需要确保该文件已发送至slave，否则必将导致slave与master不一致的情况，从而造成slave应用出错。

　　拥有super权限的用户，可以通过set sql_log_bin=0的方式，禁用其执行的语句生成二进制日志。

　　DBA可以通过mysqlbinlog命令查看二进制日志中记录的内容，对于恢复操作，这个工具将非常有用。例如，可以通过二进制日志更新mysql服务器，执行语句如下：

　　语句执行完后即被写入二进制日志，哪怕此时锁未释放，或未完成提交。这将能够确保日志记录是按照执行顺序。

　　对于非事务表语句执行完后马上写入二进制日志。进行5.1版本后，在5.1.22版本之前，UPDATE语句使用存储函数修改非事务表，如果执行出错的话该语句不会写入二进制日志，并且对于[insert ... on duplicate key update]语句遇到复制键约束，不过没有实际修改任何数据时，也不会写入二进制日志，从5.1.23版本开始，上述两种情况的操作也会写入二进制日志。

　　对于未提交的事务，修改操作(insert/update/delete)修改事务表(比如InnoDB引擎的表)会被缓存，直到commit，在此期间，mysqld会将事务完整写到二进制日志中。

　　非事务的表修改操作不能回滚，如果一个事务回滚中包含非事务包，那么事务主体记录里也会包含ROLLBACK语句，以确保这些表的修改能被复制。

　　当一个线程接手了一个事务，那么它将按照binlog_cache_size指定的值分配内存空间，缓存SQL语句，如果语句所需要的空间比分配的缓存区要大，那么该线程将打开一个临时文件保存这个事务，直到事务结束时再自动删除临时文件。

　　　Binlog_cache_use状态变量显示了使用binlog_cache_size系统变量的事务数(含临时文件)，binlog_cache_disk_use状态变量则显示了使用临时文件的事务数，这两个参数组合起来可用于binlog_cache_size系统变量设置的调整和优化，以尽可能避免使用磁盘临时文件。

　　　Max_binlog_cache_size系统变量(默认为4G，也是最大值)用来限制事务能够使用的最大缓存区，如果某个事务超出了这个限制，则执行将出错，事务会回滚。该变量最小值为4096。

　　如果二进制日志以基于行格式记录，并发插入(如create select/insert select)会改为普通插入，以确保操作可被重现。如果是使用基于语句格式记录，则二进制日志中记录原始语句。

　　默认情况下，二进制日志不是及时写向磁盘，因此如果操作系统崩溃或者机器故障，存在数据丢失的机率，要防止这种情况的发生，需要考虑的因素比较多，仅从mysql的二进制日志同步来说，可以设置二进制日志同步到磁盘的频率，mysql也提供了相应的系统变量：sync_binlog。该参数的详细说明参见初始化参数设置章节，这里仅简要描述。

　　　Sync_binlog值设为1(秒)安全级别最高，同时也是最慢的设置，不过即使设置为1，同样有可能存在丢失数据的可能。举例来说，使用InnoDB引擎的表通过事务向表中写数据，操作已写入二进制日志，但还未来的将提交语句写入日志，这时系统崩溃，那么当数据库服务重新启动时，InnoDB引擎肯定会将未提交的事务回滚，那么这种情况下，必然可能造成数据丢失。

　　要解决这种问题，mysql另外又提供了初始化参数--innodb_support_xa，设置该参数值为1，启用分布式事务的支持，确保二进制日志与InnoDB数据文件的同步。这种选项提供了深度的安全性，MySQL应被配置为以事务为单位同步二进制日志和InnoDB日志到磁盘。InnoDB日志默认即是同步状态，sync_binlog=1可以同步二进制日志。这样当mysql服务从崩溃中恢复时，为事务执行回滚后，MySQL服务中断二进制日志中InnoDB事务的回滚，以这种方式确保二进制日志能够考虑InnoDB表中的实际数据，同样，slave端也会保持同步状态(因为没有收到回滚的语句)。

　　　当MySQL服务执行崩溃恢复时发现，二进制日志比期望中要少，比如InnoDB事务缺少commit(当sync_binlog=1时不可能出现这种情况)，服务器抛出错误消息：The binary log file_name is shorter than its expected size，这种情况下，说明二进制日志文件有误，复制环境有必须从新刷新。

　　　从5.1.20版本开始，下列会话级设置的系统变量也会写入二进制日志，并被slave端解析：