mysqlserver优化之mysql日志配置

一、binlog

查看binlog的相关参数：show variables like "%binlog%"

binlog_cache_size

Binlog Cache 用于在打开了二进制日志（binlog）记录功能的环境，是 MySQL 用来提高binlog的记录效率而设计的一个用于短时间内临时缓存binlog数据的内存区域。一般来说，如果我们的数据库中没有什么大事务，写入也不是特别频繁，2MB～4MB是一个合适的选择。但是如果我们的数据库大事务较多，写入量比较大，可与适当调高binlog_cache_size。同时，我们可以通过binlog_cache_use 以及 binlog_cache_disk_use来分析设置的binlog_cache_size是否足够，是否有大量的binlog_cache由于内存大小不够而使用临时文件（binlog_cache_disk_use）来缓存了。

Binlog_cache_use记录了使用缓冲写二进制日志的次数,binlog_cache_disk_use记录了使用临时文件写二进制日志的次数。

max_binlog_cache_size

通过max_binlog_cache_size 限制binlog_cache_size最大大小（当单个事务过大的时候MySQL 会申请更多的内存）。当所需内存大于max_binlog_cache_size 参数设置的时候，MySQL 会报错：“Multi-statement transaction required more than ‘max_binlog_cache_size’bytes of storage”。

max_binlog_size binlog日志最大值，一般来说设置为512M或者1G，但不能超过1G。

sync_binlog

这个参数是对于MySQL系统来说是至关重要的，他不仅影响到Binlog对MySQL所带来的性能损耗，而且还影响到MySQL中数据的完整性。对于“sync_binlog”参数的各种设置的说明如下：

sync_binlog=0，当事务提交之后，MySQL不做fsync之类的磁盘同步指令刷新binlog_cache中的信息到磁盘，而让Filesystem自行决定什么时候来做同步，或者cache满了之后才同步到磁盘。

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

在MySQL中系统默认的设置是sync_binlog=0，也就是不做任何强制性的磁盘刷新指令，这时候的性能是最好的，但是风险也是最大的。因为一旦系统Crash，在binlog_cache中的所有binlog信息都会被丢失。而当设置为“1”的时候，是最安全但是性能损耗最大的设置。因为当设置为1的时候，即使系统Crash，也最多丢失binlog_cache中未完成的一个事务，对实际数据没有任何实质性影响。从以往经验和相关测试来看，对于高并发事务的系统来说，“sync_binlog”设置为0和设置为1的系统写入性能差距可能高达5倍甚至更多。

大家都知道，MySQL的复制（Replication），实际上就是通过将Master端的Binlog通过利用IO线程通过网络复制到Slave端，然后再通过SQL线程解析Binlog中的日志再应用到数据库中来实现的。所以，Binlog量的大小对IO线程以及Msater和Slave端之间的网络都会产生直接的影响。

MySQL中Binlog的产生量是没办法改变的，只要我们的Query改变了数据库中的数据，那么就必须将该Query所对应的Event记录到Binlog中。那我们是不是就没有办法优化复制了呢？当然不是，在MySQL复制环境中，实际上是是有8个参数可以让我们控制需要复制或者需要忽略而不进行复制的DB或者Table的，分别为：

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，可带通配符设置；

二、Slow Query Log

我们为了定位系统中效率比较地下的Query语句，则需要打开慢查询日志，也就是SlowQueryLog。

slow_query_log=on 

log-slow-queries=/data/mysqldata/slowquery.log (指定日志文件存放位置，可以为空，系统会给一个缺省的文件host_name-slow.log)/data/mysqldata/slowquery.log为MySQL慢查询日志存放的位置，一般这个目录要有MySQL的运行帐号的可写权限，一般都将这个目录设置为MySQL的数据存放目录；
long_query_time=2 (记录超过的时间，默认为10s)
log-queries-not-using-indexes (log下来没有使用索引的query,可以根据情况决定是否开启)

log-long-format (如果设置了，所有没有使用索引的查询也将被记录)