MySQL 二进制日志(binary log)总结

lonelyxmas | | 访问(70)

  什么是二进制日志?

  用来记录操作MySQL数据库中的写入性操作(增删改,但不包括查询),相当于sqlserver中的完整恢复模式下的事务日志文件。

  二进制日志的作用?

  1、用于复制,配置了主从复制的时候,主服务器会将其产生的二进制日志发送到slave端,slave端会利用这个二进制日志的信息在本地重做,实现主从同步

  2、用户恢复,MySQL可以在全备和差异备份的基础上,利用二进制日志进行基于时间点或者事务Id的恢复操作。原理雷同于主从复制的日志重做。

  基础操作(CentOS MySQL)

mysql -u root -p; # 登录
show variables like 'binlog_format'; # 查看binlog文件格式
set session binlog_format=statement; # 改成statement格式
show binary logs; # 获取二进制日志相关信息
flush logs; # 刷新logs
exit; # 退出 MySQL(退出前,执行 创建数据库,创建表,新增数据,修改数据等测试操作)
find / -name mysql; # 查看MySQL安装目录
cd /var/lib/mysql;
mysqlbinlog -vv binlog.000002; # 查看日志文件

  MySQL 之binlog_row_image参数

  在MySQL 5.7以后binlog的格式默认就是ROW了,由于记录的是每一行的变更,会带来磁盘IO上的开销,同时由于binlog日志变大,网络开销也变大了。MySQL 就引入了新的参数binlog_row_image,用来配置日志的记录范围,这个参数默认值是FULL。

  参数binlog_row_image有如下配置:

  full:在“before”和“after”影像中,记录所有的列值;

  minimal:在“before”和“after”影像中,仅仅记录被更改的以及能够唯一识别数据行的列值;

  noblob:在“before”和“after”影像中,记录所有的列值,但是BLOB 与 TEXT列除外(如未更改)。

mysql -u root -p; # 登录
set session binlog_format=row; # 改成row格式
flush logs; # 刷新logs
show binary logs; # 获取二进制日志相关信息
show variables like 'binlog_row_image';# 查看当前设置参数
set session binlog_row_image=minimal;# 设置参数为minimal
exit; # 退出 MySQL(退出前,执行 创建数据库,创建表,新增数据,修改数据等测试操作)
find / -name mysql; # 查看MySQL安装目录
cd /var/lib/mysql;
mysqlbinlog -vv binlog.000003; # 查看日志文件

  二进制日志(binary log)的相关参数信息

  1、开启二进制日志

  开启二进制日志,需要指定一个log-bin参数的路径,比如:log_bin=/var/lib/mysql/mysql-bin

  开始二进制日志之后会自动生成一个管理二进制日志的log_bin_index文件。log_bin选项也显示为on,也即开启了二进制日志。

  2、二进制日志文件的格式

  二进制日志的格式由参数binlog_format控制,二进制日志有三种模式,基于语句(statement)的,基于行的(row),以及结合前两者的混合模式(mixed)。

  基于语句(statement)(SBR):二进制功能有一些硬伤(个人认为),比如同样的update语句中的利用当前时间的now更新操作,在主服务器和从服务器上同样都是获取当前时间,主从复制得到的结果是不一样的。

  基于行的(row)(RBR):二进制日志模式解决了基于语句的一些缺陷,但是某些情况下会产生大量的日志,比如一个update操作更新了100W行数据,如果是基于行的二进制日志,结果就是产生了100W条日志

  混合模式(mixed):基于混合模式的,结合上述两种方式的优点,可以在配置文件中设置:binlog_format=MIXED。

  3、二进制日志的记录时机

  二进制日志记录可以使同步的,也即事务提交之后就写入二进制日志,也可以是异步的,由操作系统的磁盘缓存觉得什么时候写入磁盘。

  由参数sync_binlog=n来控制,设置sync_binlog=1的话,表示最高安全级别的写入(但也不能保证不丢失任何事务日志),相当于是一种安全写入模式,不过对性能有一定的影响。

  个人觉得,如果是事务性的引擎,本身就是为了保证事务安全的,没理由不把sync_binlog 设置为1。据说设置为sync_binlog设置为1也会潜在丢失一个事务日志的可能性,但是还没想明白为什么会丢失,因为既然是事务性引擎,还有一层undo或者redo日志在做后盾啊?后面想想,因为有redo和undo日志的存在,事务的一致性可以在主服务器上得到保证,应该是主从复制的时候,可能丢失的一条事务可能无法传递到从服务器。

  4、二进制日志的单个文件大小

  二进制日志的大下就是单个日志文件的最大限制,正常情况下都不会超过设置的最大文件的大小限制,超过设置的最大限制之后,会发生日志滚动,也即重新生成一个二进制日志文件。

  max_binlog_size=100M,这里显示的104857600单位是字节,也即104857600/1024/1024=100M

  5、二进制日志的清理

  二进制日志滚动之后会生成新的文件来存储日志,日志文件逾期之后会自动删除,否则会产生源源不断的日志文件,比如可以设置过期时间为2,可配值为:expire_logs_days=2,超过两天的二进制日志会被自动删除,可以通过命令show master logs 查看当前的二进制日志文件个数。

  6、二进制日志文件的滚动

  1)正常情况下,记录满之后,自动滚动,后缀名+1

  2)重启mysql服务之后,自动滚动,不管日志文件是否按照指定的最大容量写满

  3)手动滚动,执行flush logs命令,如下执行flush logs之后,重新生成了一个二进制日志文件

  4)手动删除二进制日志

  可以通过命令purge binary logs to fileName删除指定fileName之前的文件

  可以通过命令purge binary logs before '2018-07-15 10:10:00'删除指定时间之前的文件

  删除指定日志

  purge binary logs before date_sub( now( ), interval 7 day);

  purge master logs before date_sub( now( ), interval 7 day;

  7、二进制日志的绑定(或者排除)的数据库

  可以设置某些数据库开启二进制日志,或者某些数据库不开启二进制日志

  # binlog_do_db:设置master-slave时使用;

  # binlog-ignore-db:设置哪个数据库不记录日志;

  MySQL5.7.18中设置了(my.cnf中配置了),但是查询的时候好像没用?

  8、二进制日志的缓存以及缓存大小配置

  binlog_cache_size的大小,一开始提到的问题,当事务开始的时候,会按照binlog_cache_size系统变量指定的值分配内容空间,如果指定的binlog_cache_size缓存空间不够则会报错并回滚事务

  这里显示的记录的单位同样是字节,除以两个1024之后就是以MB为单位的容量了,这里的20971520/1024/1024就相当于20MB了。

  如果有较大的事务性操作,比如在测试的时候,必须要将此缓存设置的相对较大一些,否则语句无法成功执行

  max_binlog_cache_size语binlog_cache_size的区别在于前者是实例级别的cache,后者是Session级别的cache,如果并发量很大,就需要考虑将max_binlog_cache_size设置的稍微大一些。

  max_binlog_cache_size默认是是4GB,最大值也是4GB,这里为了测试设置的是100MB(104857600/1024.0/1024.0)

  max_binlog_cache_size设置的最大内存大小为4GB,如果服务器内容较大,比如128GB或者更大,max_binlog_cache_size默认为设置为最大也无伤大雅,因为要保证并发成功写入。

  至于对于Session级别的binlog_cache_size大小,可以根据业务情况自行调整,个人觉得设置的稍微大一点也问题不大,毕竟,除了正常的OLTP操作,有一些定时作业之类的数据提取或者merge数据之类的操作可能会产生大量的日志。

  据说是可以通过查看binlog_cache_disk_use 与 binlog_cache_use来判断binlog_cache_size是否需要调整。

  但是在MySQL5.7.18中并没有发现这个参数

  9、二进制日志其他参数

  max_binlog_stmt_cache_size针对非事务语句,非事务性的参数暂不关心它了

  记得某次看到过某大师说过,innodb引擎优势不仅仅在事务性的支持上,与非事务引起的myisam引擎相比,读取性能上差距越来越小,MySQL因此将innodb设置为默认引擎。

  放弃myisam,投奔innodb是正道。

  binlog_checksum 用作复制的主从校检。暂时没有研究过这个参数,暂不论

  总结:

  MySQL二进制日志不仅仅作用于功能性(master-slave复制)的,还作用于安全性(二进制日志)以及开启了二进制日志情况下的事务性操作,因此对于生产环境,可以认为是一个必不可少的配置。

  同时,其各种参数又会影响到某些操作,因此二进制日志的参数要格外的重视,确保数据库在使用时在功能性和可用性上得到保证。