3. 二进制日志(Binary Log)

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

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

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

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

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

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

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

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

    提示:

    二进制日志同样可以用来跟踪重大DDL事件,MySQL Enterprise Monitor工具就是通过分析二进制日志获取相关信息。

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

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

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

    提示:

    启用了log-bin参数后,如果想取消,只需注掉该参数和binlog_format参数(如果指定了log-slave-updates,也需要注释掉)即可,而后重启mysqld服务。

  术语:

  • 二进制日志文件(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服务器,执行语句如下:

    mysqlbinlog log_file | mysql -h server_name
    提示:Mysqlbinlog也可以查看slave端的relay log,其日志记录的格式都是相同的。

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

  对于非事务表语句执行完后马上写入二进制日志。进行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。

    提示:

    查看系统变量:show variables;

    查看状态变量:show status;

  如果二进制日志以基于行格式记录,并发插入(如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端解析:

    l sql_mode (except that the NO_DIR_IN_CREATE mode is not replicated; see Section 16.4.1.32, “Replication and Variables”) 

    l foreign_key_checks 

    l unique_checks 

    l character_set_client 

    l collation_connection 

    l collation_database 

    l collation_server 

    l sql_auto_is_null