霜天部落 | 专注PHP研发,研究LAMP高性能架构部署与优化

MySQL二进制日志相关参数及优化策略

MySQL二进制日志在MySQL主从复制方面有着关键性的作用,本文深入探讨一下有关二进制日志的设置参数以及优化策略。

我们先查看mysql二进制日志的相关参数:

mysql> show variables like ‘%binlog%';
+——————————–+————+
| Variable_name | Value |
+——————————–+————+
| binlog_cache_size | 32768 |
| binlog_format | STATEMENT |
| innodb_locks_unsafe_for_binlog | OFF |
| max_binlog_cache_size | 4294963200 |
| max_binlog_size | 104857600 |
| sync_binlog | 0 |
+——————————–+————+
6 rows in set (0.00 sec)

相关参数介绍如下:

binlog_cache_size:

代表在事务过程中容纳二进制日志SQL语句的缓存大小。(当系统中经常会出现多语句事务,可以常识增加该值大小,以获得更优的性能)

max_binlog_cache_size:

Binlog能够使用的最大cache内存大小。当执行多语句事务时,max_binlog_cache_size 如果不够大,系统可能会报出“Multi-statement transaction required more than ‘max_binlog_cache_size’ bytes of storage”的错误。

max_binlog_size:

Binlog最大值,一般设置为512M或1GB,但不能超过1GB。该设置并不能严格控制Binlog的大小,尤其是Binlog比较靠近为不而又遇到一根比较大事务时,
为了保证事务的完整性,不可能做切换日志的动作,只能将该事务的所有SQL都记录进当前日志,直到事务结束。

sync_binlog:

这个参数对于Mysql系统来说是至关重要的,它不仅影响到Binlog对Mysql所带来的性能损耗,而且还影响到Mysql中数据的完整性。

sync_binlog=0,当事务提交后,Mysql仅仅是将binlog_cache中的数据写入Binlog文件,但不执行fsync之类的磁盘同步指令通知文件系统将缓存刷新到磁盘,而让Filesystem自行决定什么时候来做同步。

sync_binlog=n,在进行n次事务提交以后,Mysql将执行一次fsync之类的磁盘同步指令,同志文件系统将Binlog文件缓存刷新到磁盘。

(Mysql中默认的设置是sync_binlog=0,即不作任何强制性的磁盘刷新指令,这时性能是最好的,但风险也是最大的。一旦系统绷坤(Crash),在文件系统缓存中的所有Binlog信息都会丢失)

binlog的格式:

从 MySQL 5.1.12 开始,可以用以下三种模式来实现:

基于SQL语句的复制(statement-based replication, SBR),
基于行的复制(row-based replication, RBR),
混合模式复制(mixed-based replication, MBR)。
相应地,binlog的格式也有三种:STATEMENT,ROW,MIXED。MBR 模式中,SBR 模式是默认的。

当binlog_format格式为STATEMENT时,事务级别为read committed时,会导导致插入不了数据,因为read committed可能会导致不可重复读,也就是说可以读取到后面进入并提交的数据,如果基于STATEMENT格式的话,会导致主从数据不一样,因为 STATEMENT是基于SQL语句的复制模式。另外设置innodb_locks_unsafe_for_binlog=1 ,binlog也要设为row格式。

Mysql Binlog复制控制

Binlog_Do_Db:设定哪些数据库(Schema)需要记录Binlog;
Binlog_Ignore_Db:设定哪些数据库(Schema)不需要记录Binlog;
Replicate_Do_Db:设定需要复制的数据库(Schema),多个DB用“,”分割;
Replicate_Ignore_Db:设定可以忽略的数据库(Schema);
Replicate_Do_Table:设定需要复制的Table;
Replicate_Ignore_Table:设定可以忽略的Table;
Replicate_Wild_Do_Table:功能同Replicate_Do_Table,但可可以带通配符来设置
Replicate_Wild_Ignore_Table:功能同Replicate_Ignore_Table,可以带通配符设置;

前面2个设置在Master,后面6个设置在Slave端,前2个参数设置可减少Binlog记录所带来的I/O量,还会让Master端的I/O线程减少Binlog的读取量,这样,传递给Slave端的线程的Binlog量自然就会减少。

后6个参数,在性能方面不及前面2个,但是在Slave端设置复制过滤机制,可保证不会出现因为默认Schema的问题而造成Slave和Master数据不一致或复制出错的问题。