（4.6）mysql备份还原——深入解析二进制日志（2）binlog参数配置解析

关键词：binlog配置，binlog参数，二进制日志配置，二进制文件参数配置

关键词：binlog缓存，binlog 刷新

 

0、bin写入流程

写binlog流程如下：
# 数据操作buffer pool > binlog buffer > file system buffer > commit > binlog file

1、配置

【1.1】查看
show variables like '%log_bin%';
show variables like '%binlog%';
show variables like '%server%';

【1.2】必要参数配置
[mysqld]
server_id=3306  #服务ID，主从必须不一致。(建议数字：ip+端口)  5.7.3以后版本，必须加
#目录必须存在且授权好，binlog为索引文件，实际文件会自动根据索引文件建立如binlog.0000001
log_bin=/mysql/log/3306/binlog    
log_bin_index=/mysql/log/3306/binlog.index 　   #不设置的话，会根据log_bin值名称自动生成binlog.index
binlog_format='row' #(row,statement,mixed)     #3种模式
binlog_rows_query_log_events=on #打开才能查看详细记录
expire_logs_days=10 #超过10天的数据会被认定为过期，且会被清理
max_binlog_size=100M #表示单个二进制文件的最大值为100M，如果有大事务可能会超出最大值
binlog_row_image=full #（full,minimal,noblob），分别表示binlog中内容全记录，只记录被操作的，和不记录二进制

binlog-do-db=db_name #该选项告诉服务器，binlog只记录db_name数据库操作（可写多行实现多个数据库）
binlog-ignore-db=db_name #该选项告诉服务器，binlog不记录db_name数据库操作（可写多行实现多个数据库）

【1.3】文件大小
show variables like '%binlog%';
（1）max_binlog_size:范围4K-1G，默认1G、最大也是1G一个。但如果一个SQL事务太大，比如这个SQL产生2G日志，那么binlog也会达到2G。

【1.4】缓存大小（如果事务巨大则会超出定义值）
（1）binlog_cache_size（默认32K）：二进制日志写缓存区大小。写binlog流程如下：
# 数据操作buffer pool > binlog buffer > file system buffer > commit > binlog file
监控使用：show status like 'binlog_cache';
=>Binlog_cache_use 表示用到binlog缓存的次数
=>Binlog_cache_disk_use 表示使用临时文件来存放binlog缓存的次数。

（2）max_binlog_cache_size：binlog_cache_size缓存最大值，默认无限大，超出报错
（3）binlog_stmt_cache_size（默认32K）:非事务语句文件缓存大小
（4）max_binlog_stmt_cache_size：binlog_stmt_cache_size缓存最大值，默认无限大,超出报错
（5）binlog_row_image：针对binlog_format='row'格式来设置记录的日志范围（默认full，minimal,noblob）
（6）binlog_gtid_simple_recovery：在主从复制时有影响，默认为on，如果主从复制计算gtid出错，改为off.
　　#在重启/清理binlog的时候，只需要打开最早的和最晚的binlog来根据gtid对比，不需要打开所有文件。
   #好处：可以通过这2个文件来计算当前的gtid来进行复制。

【1.5】flush disk相关的

写binlog流程如下：
# 数据操作buffer pool > binlog buffer > file system buffer > commit > binlog file
在写binlog file之前，commit有3种模式，分别是：0,1，N
sync_binlog=0:mysql不会主动同步Binlog内容到磁盘文件中，而是依赖操作系统刷新文件的机会刷binlog file.一般是1S/次
sync_binlog=1:默认值，mysql主动刷新file system buffer到磁盘上的binlog file中，每1次commit，就主动fsync一次。
sync_binlog=N:非0非1，mysql主动刷新file system buffer到磁盘上的binlog file中，每N次commit，就主动fsync一次。

【1.6】数据库先写redo log还是先写binlog?
答案：先写redo LOG，再写binlog。如果2个有任一失败，就会回滚。
sync_binlog配合另一个参数innodb_flush_log_at_trx_commit;
如果都是1，数据库挂了以后，最多只丢一条一句或一个事务的数据；
show variables like 'innodb_flush_log_at_trx_commit';
但会影响性能，只能说在数据要求非常高的场景下使用。