你值得收藏的binlog设置参数详解

概述

binlog即binary log，中文翻译一般为二进制日志。这里详细整理了常用的相关配置和解释，包括binlog_format,max_binlog_size,binlog_cache_size,max_binlog_cache_size,
binlog_do_db,binlog_ignore_db,sync_binlog等。
binary log包含那些可能会引起数据库或表数据变化的events或者statements，还包括更新语句的耗时信息，查询则不会不记录在binary log。这个日志有两个主要用途，主从复制和数据恢复。要激活使用 binary log, 可以在mysql启动的时候带上参数 --log-bin=base_name，或配置log_bin=ON。要注意二进制日志和事务日志不是一回事儿。
另外，注意有个sql_log_bin是一个会话级别的变量，可以单独设置某个会话的操作不记录到binlog。

查看binlog

默认情况下，binlog日志是二进制格式的，不能使用查看文本工具的命令（比如，cat，vi等）查看，而使用mysqlbinlog解析查看

mysql> mysqlbinlog --base64-output=decode-rows -v /data/mysql/log/mysql-bin.000001

简单的查看可以通过命令行来

# 显示服务器现有的二进制日志信息
mysql> SHOW BINARY LOGS;
+---------------	+-----------	+-----------	+
| Log_name      	| File_size 	| Encrypted 	|
+---------------	+-----------	+-----------	+
| binlog.000015 	|    724935 	|       Yes 	|
| binlog.000016 	|    733481 	|       Yes 	|
+---------------	+-----------	+-----------	+
# 显示主库现在的二进制信息，在主从备份的时候还可以看slave status
mysql> SHOW MASTER STATUS\G
*************************** 1. row 	***************************
             File	: master-bin.000002
         Position	: 1307
     Binlog_Do_DB	: test
 Binlog_Ignore_DB	: manual, mysql
Executed_Gtid_Set	: 3E11FA47-71CA-11E1-9E33-C80AA9429562	:1-5
1 row in set (0.00 sec)

mysql> show binlog events [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count];
# 选项解析：
#  IN 'log_name' 指定要查询的binlog文件名(不指定就是第一个binlog文件)
#  FROM pos 指定从哪个pos起始点开始查起(不指定就是从整个文件首个pos点开始算)
#  LIMIT [offset,] 偏移量(不指定就是0)
#  row_count 查询总条数(不指定就是所有行)

log_bin

log_bin=ON # 是否激活二进制日志
In earlier MySQL versions, binary logging was disabled by default, and was enabled if you specified the --log-bin option. From MySQL 8.0, binary logging is enabled by default, with the log_bin system variable set to ON, whether or not you specify the --log-bin option.
在8.0版本之前默认是不开启binlog的，需要通过–log-bin配置来开启，8.0之后binlog默认是开启的。

log_bin_basename

log_bin_basename=/data/mysql/log/mysql-bin
Holds the base name and path for the binary log files, which can be set with the --log-bin server option.
指定了binlog的基础命名和存储路径也可以通过–log-bin设置。也就是说像上面的配置binlog文件会存在/data/mysql/log目录下，文件命名方式为mysql-bin.0000XX.

log_bin_index

log_bin_index= /data/mysql/log/mysql-bin.index
Holds the base name and path for the binary log index file, which can be set with the --log-bin-index server option.
指定了binlog索引文件的基础命名和存储路径，也可以通过–log-bin-index来指定，index文件存储了启用的binglog文件名

binlog_error_action

binlog_error_action=ABORT_SERVER
Controls what happens when the server encounters an error such as not being able to write to, flush or synchronize the binary log, which can cause the source’s binary log to become inconsistent and replicas to lose synchronization.
当发生不可以写、刷或同步binary log的时候，可能会导致数据一致性遭到破坏或者复制操作停止，这时候服务器要做什么操作。有两个选项ABORT_SERVER 服务关闭（默认值）, IGNORE_ERROR 忽略错误。

binlog_format

binlog_format=ROW
定义binlog的日志写入格式。有三个选项可选：

STATEMENT
每一条被修改数据的sql都会记录到master的bin-log中，slave在复制的时候sql进程会解析成和原来master端执行过的相同的sql再次执行。
优点：不需要记录每一行的数据变化，减少bin-log日志量，节约磁盘IO，提高新能；缺点：容易出现主从复制不一致
ROW
日志中会记录每一行数据被修改后的情况，然后在slave端对相同的数据进行修改。
优点：能清楚的记录每一行数据修改的细节；
缺点：数据量太大
MIXED
以上两种模式的混合使用，一般的复制使用STATEMENT模式保存binlog，对于STATEMENT模式无法复制的操作使用ROW模式保存binlog，MySQL会根据执行的SQL语句选择日志保存方式。
这种模式会导致binglog可读性变差。

max_binlog_size

max_binlog_size=536870912
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. A transaction is written in one chunk to the binary log, so it is never split between several binary logs.
定义了binlog的文件大小，超过这个大小会关闭当前文件然后打开下一个，最大值是1GB。不会严格按照这个大小来切割，因为会优先保证一个事务不会分割到多个binlog中。

binlog_cache_size

binlog_cache_size=131072
The size of the cache to hold changes to the binary log during a transaction. The value must be a multiple of 4096. A binary log cache is allocated for each client if the server supports any transactional storage engines and if the server has the binary log enabled (–log-bin option). If you often use large transactions, you can increase this cache size to get better performance.
为每个session 分配的内存，在事务过程中用来存储二进制日志的缓存。 默认值：32768，即32K。如果经常有大事务操作可以将这个值调大一点点。

max_binlog_cache_size

max_binlog_cache_size=4294967296
If a transaction requires more than this many bytes of memory, the server generates a Multi-statement transaction required more than ‘max_binlog_cache_size’ bytes of storage error. The minimum value is 4096. The maximum possible value is 16EB (exabytes). The maximum recommended value is 4GB; this is due to the fact that MySQL currently cannot work with binary log positions greater than 4GB. The value must be a multiple of 4096.
一个事务可用的最大cache，否则会报错。该变量的默认值是16EB，非常非常大，但是官方文档说的是最大值推荐4GB。还请有经验的人留下看法。

relay_log_recovery

relay_log_recovery=OFF # 中继日志，主从复制用，以后再说

binlog_do_db和binlog_ignore_db

binlog_do_db = db1 # 此参数表示只记录指定数据库的二进制日志，默认全部记录
binlog_do_db = db2
binlog_ignore_db = db3 # 此参数表示不记录指定的数据库的二进制日志
binlog_ignore_db = db4

sync_binlog

sync_binlog=0
Controls how often the MySQL server synchronizes the binary log to disk.
定义binlog日志同步到硬盘的规则。取值范围为0~N

0
当事务提交之后，MySQL不做fsync之类的磁盘同步指令刷新binlog_cache中的信息到磁盘，而让操作系统的Filesystem自行决定什么时候来做同步，或者cache满了之后才同步到磁盘。这个是性能最好的
1或者N
当每进行n次事务提交之后，MySQL将进行一次fsync之类的磁盘同步指令来将binlog_cache中的数据强制写入磁盘。

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

• sync_binlog=1 # (bin-log)
• innodb_flush_log_at_trx_commit=1 #(redo-log).