IT码农

二进制日志

2016/03/20 mysql

#mysql二进制日志 The Binary Log

二进制日志包含描述更改数据库的”事件”,如创建或更改表的操作。它还可能包含会更改的数据库的语句事件(如一个没有匹配到行的delete语句)，除非行级日志被开启。通常还包含了语句的执行时间长度。

##二进制日志主要有2个目的

1.用于主从复制 主服务器master上的二进制日志提供了将要发送到从服务器slave上的数据更改记录。使得从服务器对数据的更改和主服务器是一致的。 2.某些数据恢复操作需要使用二进制日志。在备份已还原后，重新执行备份后记录的二进制日志中的事件。这些事件将数据库从备份的点更新到最新

二进制日志不会记录如(SELECT,SHOW)等不会改变数据的操作。

运行具有二进制日志记录功能的服务器的性能会稍微慢一点，然而它能够使你对数据库的操作，进行复制和恢复，对于一点点的性能损失是可以接受的。

为了安全，对于二进制中的密码，不会原本的显示，会被服务器重写。

##二进制日志的格式 Replication Formats 有2种日志格式，分别是基于语句(statement-based)SBR 和 基于行(row-based)RBR 2种格式。

基于语句 MASTER主服务器会记录SQL语句到二制日志，SLAVE通过执行二进制中的日志来复制，通常简称为SBR，在5.1.4及之前的版本使用该唯一的方式。 优点:写入二进制日志中的数据较少，当更新或删除多行数据的时候，写入二进制日志的数据将很大程度的减少，这将减少存储空间的需求，也使得恢复变得更快。 任何修改数据库的操作都将被记录到二进制日志中。

缺点：对于SBR并不是很安全，并非所有的修改数据的语句都可以用来复制，一些不确定的行为，是很难用来复制的，如：

1.依赖于UDF或者是存储过程的语句，是不确定的 2.DELETE,UPDATE语句使用了LIMIT但没有使用ORDER BY是不确定的。 3.以下的一些SQL行为是不确定的

LOAD_FILE()
UUID(), UUID_SHORT()
USER()
FOUND_ROWS()
SYSDATE() (unless both the master and the slave are started with the --sysdate-is-now option)
GET_LOCK()
IS_FREE_LOCK()
IS_USED_LOCK()
MASTER_POS_WAIT()
RAND()
RELEASE_LOCK()
SLEEP()
VERSION()

对于无法被正确复制的SQL语句，将会输出一个警告信息到日志文件。 [Warning] Statement is not safe to log in statement format.

客户端可以使用 SHOW WARNINGS显示警告信息。

4.INSERT … SELECT 需要多个行级锁 5.UPDATE语句需要扫描表(如果where子句没有使用索引),会锁住多条记录。 6.对于Innodb，使用INSERT的AUTO_INCREMENT会阻塞其他没有冲突的INSERT语句 7.对于一些复杂的语句，在从服务器上必需先执行update或者inser完成后才能执行其他语句，如果使用用行级日志，只要修改爱影响的行即可，而不必要执行全部的语句。 8.如果在执行复杂语句时，出现了错误，可能会缓慢的增加受影响行的误差幅度。 9.如果存储过程包含了NOW(),会出现错误 10.Deterministic UDFs must be applied on the slaves. 11.表的定义，在MASTER和SLAVE之间必需完全一致。

基于行 MASTER主服务器记录改变表中的行(rows)数据的事件到二进制日志中。SALVE通过拷备二进制中的事件来复制主服务器。该方式简称为RBR

优点：所有的改变均可被复制。 对以下的一些语句，只需要少数的行级锁，这样可以实现更高的并发

INSERT ... SELECT
INSERT 使用自增 AUTO_INCREMENT
UPDATE ,DELETE语使用WHERE条件,且没有使用索引

对于SLAVE服务器，执行INSERT,UPDATE,DELETE只需要少数的行级锁。

缺点：日志文件较大。对于一些DML操作(如UPDATE,DELETE)，会写入每行的修改到日志文件，而基于语句的只写一行或很少。这意味着复制数据可能花费更多的时间。另外二进制文件可能会锁住比较长的时间，用来写入数据，可能产生并发问题。可以使用binlog_row_image=minimal来减少因此产生的对并发的不利因素。

Deterministic UDFs that generate large BLOB values take longer to replicate with row-based replication than with statement-based replication. This is because the BLOB column value is logged, rather than the statement generating the data.

2.你不能从SLAVE服务器查看，接收到了和执行哪些SQL语句。可以使用mysqlbinlog工具使用–base64-output=DECODE-ROWS 选项查看。或者使用binlog_rows_query_log_events 变量。

3.对于MyISAM表，需要一个全局的锁来执行INSERT语句，这意味着使用行级二进制日志不支持MYISAM的并发写入。

混合格式Mixed Binary Logging Format

你可以同时指定这2种格式的日志，简称mixed-format。如果使用mixed-format，默认使用基于语句的日志，当特定的语句，存储引擎被使用时，让日志自动切换到基于行。

每一个二进制日志记录格式都有优点和缺点。对于大多数用户来说，混合复制格式应该提供数据完整性和性能的最佳组合

！注：在5.7.7之前使用基于语句的二进制日志，之后的版本使用基于行的二进制日志。 binlog_format=[STATEMENT| ROW | MIXED] 变量用来控制日志格式。必需要有超级权限。

##配置二进制日志 以下配置选项是mysqld的配置参数，是二进制相关配置

–binlog-row-event-max-size=N 该值指定基于行row-based的日志的最大值(字节)，是256的倍数，默认8192

Command-Line Format –binlog-row-event-max-size=# Permitted Values (32-bit platforms) Type integer Default 8192 Min Value 256 Max Value 4294967295 Permitted Values (64-bit platforms) Type integer Default 8192 Min Value 256 Max Value 18446744073709551615

–log-bin[=base_name] 启用二进制。mysql服务器会记录所有对数据库的更改操作到指定的文件,用来备份和恢复数据。 如果没有指定该值，默认是pid-file的值加上-bin(通常是主机名-bin)推荐设置一个固定的值，而不使用默认值。如果设置了该值，会往该文件写入相应的内容 如果该值设置了扩展名如base_name.extension，则扩展名将自动被忽略 mysqld会给二进制日志文件名，加上一个连续的数字作为文件名，每次创建一个新的日志文件(每次启动或flush log，或者文件大小到达max_binlog_size)，都会增加数字的值。 mysqld会创建一个二进制日志索引文件，包含所有二进制文件的名称。该文件默认值是二进制文件名称的前缀是一样的，默认以’.index’结尾。可以使用 –log-bin-index[=file_name] 修改该值。注意不要在运行时修改该值。否则可能导致混乱。 如果命令行设置了该值，会将系统变量log-bin设置为1或ON，同时log_bin_basename也将使用该值。 在5.7.3以后，如果设置了该选项，而没有设置 –server-id则服务将不允许启动。

SET sql_log_bin=0可以禁用日志。

可以使用 binlog_checksum 对日志进行校验，默认是CRC32，也可以设置为NONE(按长度来校验) master_verify_checksum slave_sql_verify_checksum **expire_logs_days **日志几天过期

使用RESET MASTER 或 PURGE BINARY LOGS删除所有二进制日志 RESET MASTER更倾向于在主服务器刚启动时 它和PURGE BINARY LOGS的不同，主要表现在以下2点： 1.RESET MASTER清除所有在二进制索引文件中列出的二进制日志文件，只留下一个空的以数字编号.000001结尾的二进制日志文件。而 PURGE BINARY LOGS不会重置数字编号。

2.RESET MASTER不应用于有从服务器在运行的情况。在从服务器运行时使用RESET MASTER的行为是未知的(也不被支持)，然而PURGE_BINARY_LOGS可以安全的用于有从服务器运行的情况。

可使用mysqladmin flush-logs 在主服务器上删除，推荐使用PURGE BINARY LOGS,因为它更加安全，同时还会更新二进制索引文件，还可以设置日期参数。

使用mysqlbinlog可以显示二进制日志文件内容，下面操作可以通过二进制日志用来更新mysql服务 shell> mysqlbinlog log_file | mysql -h server_name

mysqlbinlog还可以用来显示relay log file，因为它和binary log files文件的格式 是一样的。

Command-Line Format –log-bin System Variable Name log_bin Variable Scope Global Dynamic Variable No Permitted Values Type file name

–log-bin-index[=file_name] The index file for binary log file names.如果忽略该选项，且没有指定–log-bin,则mysql将使用host_name-bin.index作为文件名称。

Command-Line Format –log-bin-index=file_name Permitted Values Type file name

http://dev.mysql.com/doc/refman/5.7/en/replication-options-binary-log.html http://dev.mysql.com/doc/refman/5.7/en/replication-formats.html http://dev.mysql.com/doc/refman/5.7/en/binary-log-mixed.html http://dev.mysql.com/doc/refman/5.7/en/replication-sbr-rbr.html http://dev.mysql.com/doc/refman/5.7/en/replication-rbr-safe-unsafe.html http://dev.mysql.com/doc/refman/5.7/en/replication-features-auto-increment.html http://dev.mysql.com/doc/refman/5.7/en/binary-log.html http://dev.mysql.com/doc/refman/5.7/en/reset-master.html http://dev.mysql.com/doc/refman/5.7/en/purge-binary-logs.html

Search

    Table of Contents