mysql binlog日志优化及思路

在数据库安装完毕，对于binlog日志参数设置，有一些参数的调整，来满足业务需求或使性能最大化。mysql日志主要对io性能产生影响，本次主要关注binlog 日志。 查一下二进制日志相关的参数 mysql show variables like '%binlog%'; ---------------------------

在数据库安装完毕，对于binlog日志参数设置，有一些参数的调整，来满足业务需求或使性能最大化。mysql日志主要对io性能产生影响，本次主要关注binlog 日志。

查一下二进制日志相关的参数  

   mysql> show variables like '%binlog%';

+--------------------------------+----------------------+

| Variable_name                  | Value                |

+--------------------------------+----------------------+

| binlog_cache_size              | 1048576              |

| innodb_locks_unsafe_for_binlog | OFF                  |

| max_binlog_cache_size          | 18446744073709547520 |

| max_binlog_size                | 1073741824           |

| sync_binlog                    | 0                    |

+--------------------------------+----------------------+

5 rows in set (0.14 sec)

其中innodb_locks_unsafe_for_binlog 参数是innodb存储引擎特有的与binlog相关的参数，默认是关闭的

Binlog_cache_size:默认大小是37268即32K.我们计算一下上面binlog_cache_size参数大小，1048576/1024/1024 = 1M，根据事务需要调整大小。次参数表示在事务中容纳二进制日志sql语句的缓存大小。那么怎么理解二进制日志缓存，就是服务器支持事务存储引擎并且服务器启用了二进制日志(-log-bin选项)的前提下为每个客户端分配的内存，是每个client都可以分配设置大小的binlog cache空间。我们可以通过以下两个状态变量判断binlog_cache_size的使用情况：

1  binlog_cache_use: Thenumber of transactions that used the temporary binary log cache.（使用二进制日志缓存的事务的数量）

iWebShop开源商城系统

iWebShop是一款基于PHP语言及MYSQL数据库开发的B2B2C多用户开源免费的商城系统，系统支持自营和多商家入驻、集成微信商城、手机商城、移动端APP商城、三级分销、视频电商直播、微信小程序等于一体，它可以承载大数据量且性能优良，还可以跨平台，界面美观功能丰富是电商建站首选源码。iWebShop开源商城系统 v5.14 更新日志：新增商品编辑页面规格图片上传优化商品详情页面规格图片与主图切

0

查看详情

  2   Binlog_cache_disk_use: The number oftransactions that used the binary log cache but that exceeded the value of <span>binlog_cache_size</span> andused a temporary file to store changes from the transaction.

 使用二进制日志缓存并且值达到了binlog_cache_size设置的值，用临时文件存储来自事务的变化这样的事务数量

Max_binlog_cache_size: 默认值是18446744073709547520，这个值很大，够我们使用的了。此参数和binlog_cache_size相对应，代表binlog所能使用的cache最大使用大小。如果系统中事务过多，而此参数值设置有小，则会报错。

Max_binlog_size: 1073741824=1G  binlog的最大值，一般设置为512M或1G,一般不能超过1G。此参数不能非常严格控制binlog的大小，特别是在遇到大事务时，而binlog日志又到达了尾部，为了保证事务完整性，不切换日志，把所有sql都写到当前日志。这根oracle redo日志有点不一样（感兴趣的可以google）。Mysql记录的是mysql数据库逻辑变化信息，称之为event，实际上就是引起数据库变化的query语句。

Sync_binlog: 参数不仅影响数据库的性能，而且还影响数据库数据的完整性。Sync_binlog参数选项有如下选择

1 Sync_binlog=0,这意味着当事务提交之后，mysql不做fsync（文件系统同步）之类的磁盘同步指令刷新binlog_cache中的信息到磁盘，而让filesystem自行决定什么时候来做同步，或者cache满了之后才同步到磁盘。

2 sync_binlog=n,当每进行n次提交之后，mysql将进行一次fsync之类的磁盘同步指令来将binlog_cache中的数据强制写入磁盘。

Sync_binlog=0,这是数据库默认设置，性能也是最好的,也是最危险的。一旦系统crash，在binlog_cache中的所有binlog信息都会被丢失。当synn_binlog=1的时候，是最安全性能消耗也最大，完成一个事务，同步一次。系统crash了，也只是丢掉了未完成的事务信息。没坐过这方面的测试，设置为1和0在性能方面的差距，有做过的说在高并发系统中，两种设置性能可高达5倍甚至更多。

 这需要我们来做出选择，是要性能最好，还是最安全。

 资料：《mysql性能调优与架构设计》

 http://dev.mysql.com/doc/refman/5.0/en/server-status-variables.html

http://dev.mysql.com/doc/refman/5.5/en/server-system-variables.html