Mysql系统变量系列之（二）Binary Logging

1. 开启二进制日志：--log-bin[=base_name]

Property	Value
Command-Line Format	--log-bin=file_name
Type	File name

    Enables binary logging. With binary logging enabled, the server logs all statements that change data to the binary log, which is used for backup and replication.

    开启二进制日志选项后，会将修改数据的statement记录在日志文件中去。

    In MySQL 5.7, the base name defaults to host_name-bin.

2. 二进制日志索引文件：--log-bin-index[=file_name]

Property	Value
Command-Line Format	--log-bin-index=file_name
System Variable	log_bin_index
Scope	Global
Dynamic	No
Type	File name

    The name for the binary log index file, which contains the names of the binary log files. By default, it has the same location and base name as the value specified for the binary log files using the --log-bin option, plus the extension .index. If you do not specify --log-bin, the default binary log index file name is binlog.index. If you omit the file name and do not specify one with --log-bin, the default binary log index file name is host_name-bin.index, using the name of the host machine.

3. 校验和：--binlog-checksum={NONE|CRC32}

Property	Value
Command-Line Format	--binlog-checksum=type
Type	String
Default Value	CRC32
Valid Values	NONE CRC32

    Enabling this option causes the master to write checksums for events written to the binary log.

    将事件的校验和写入到二进制日志文件中。

4. master 开启校验和验证：master_verify_checksum

Property	Value
Command-Line Format	--master-verify-checksum[={OFF|ON}]
System Variable	master_verify_checksum
Scope	Global
Dynamic	Yes
Type	Boolean
Default Value	OFF

    Enabling this variable causes the master to verify events read from the binary log by examining checksums, and to stop with an error in the event of a mismatch.

    master从日志文件中读取事件时，会进行校验和验证。如果校验和不匹配，则停止读取事件，抛出错误信息。

5. slave开启校验和验证：--slave-sql-verify-checksum[={OFF|ON}]

Property	Value
Command-Line Format	--slave-sql-verify-checksum[={OFF|ON}]
Type	Boolean
Default Value	ON

    When this option is enabled, the slave examines checksums read from the relay log, in the event of a mismatch, the slave stops with an error.

    slave从中继日志中读取事件时，会对校验和进行验证，匹配失败则停止SQL Thread线程的执行，抛出错误信息。

6. 是否将statement语句记录进行日志文件中：--binlog-rows-query-log-events[={OFF|ON}]

Property	Value
Command-Line Format	--binlog-rows-query-log-events[={OFF|ON}]
System Variable	binlog_rows_query_log_events
Scope	Global, Session
Dynamic	Yes
Type	Boolean
Default Value	OFF

    This system variable affects row-based logging only. When enabled, it causes the server to write informational log events such as row query log events into its binary log. 

    针对binlog_format=row的配置有效，将原始的statement语句写入到二进制文件中。

    To view them, increase the verbosity level by using mysqlbinlog's --verbose option twice, either as -vv or --verbose --verbose.

7. 日志文件的过期时间：--expire-logs-days

Property	Value
Command-Line Format	--expire-logs-days=#
System Variable	expire_logs_days
Scope	Global
Dynamic	Yes
Type	Integer
Default Value	0
Minimum Value	0
Maximum Value	99

    The number of days for automatic binary log file removal. The default is 0, which means “no automatic removal.”

    日志文件超过X天后，会被自动移除。

7. 单个日志文件的最大值：--max-binlog-size

Property	Value
Command-Line Format	--max-binlog-size=#
System Variable	max_binlog_size
Scope	Global
Dynamic	Yes
Type	Integer
Default Value	1073741824
Minimum Value	4096
Maximum Value	1073741824

    If a write to the binary log causes the current log file size to exceed the value of this variable, the server rotates the binary logs (closes the current file and opens the next one). The minimum value is 4096 bytes. The maximum and default value is 1GB.

8. 刷新二进制日志文件到磁盘的频率：--sync-binlog

Property	Value
Command-Line Format	--sync-binlog=#
System Variable	sync_binlog
Scope	Global
Dynamic	Yes
Type	Integer
Default Value (>= 5.7.7)	1
Default Value (<= 5.7.6)	0
Minimum Value	0
Maximum Value	4294967295

Controls how often the MySQL server synchronizes the binary log to disk.

• sync_binlog=0: Disables synchronization of the binary log to disk by the MySQL server. Instead, the MySQL server relies on the operating system to flush the binary log to disk from time to time as it does for any other file. 禁用mysql server刷新日志到磁盘的功能，日志刷新策略依赖操作系统的刷新机制。

• sync_binlog=1: Enables synchronization of the binary log to disk before transactions are committed. This is the safest setting but can have a negative impact on performance due to the increased number of disk writes. 在事务提交之前，刷新日志到磁盘，但是这种模式对性能有影响。

• sync_binlog=N, where N is a value other than 0 or 1: The binary log is synchronized to disk after N binary log commit groups have been collected. 在N个事务提交之后，将日志文件刷新到磁盘；

For the greatest possible durability and consistency in a replication setup that uses InnoDB with transactions, use these settings:

• sync_binlog=1.

• innodb_flush_log_at_trx_commit=1.