Mysql配置文件 binlog和慢日志

[TOC]

## binlog
### binlog_format
binlog日志格式，mysql默认采用statement，建议使用mixed

STATEMENT模式（SBR）
每一条会修改数据的sql语句会记录到binlog中。优点是并不需要记录每一条sql语句和每一行的数据变化，减少了binlog日志量，节约IO，提高性能。缺点是在某些情况下会导致master-slave中的数据不一致(如sleep()函数， last_insert_id()，以及user-defined functions(udf)等会出现问题)

ROW模式（RBR）
不记录每条sql语句的上下文信息，仅需记录哪条数据被修改了，修改成什么样了。而且不会出现某些特定情况下的存储过程、或function、或trigger的调用和触发无法被正确复制的问题。缺点是会产生大量的日志，尤其是alter table的时候会让日志暴涨。

MIXED模式（MBR）
以上两种模式的混合使用，一般的复制使用STATEMENT模式保存binlog，对于STATEMENT模式无法复制的操作使用ROW模式保存binlog，MySQL会根据执行的SQL语句选择日志保存方式。

查询：
在线配置：
配置文件：`binlog_format = MIXED`

### log_slave_updates
把relay-log里的日志内容再记录到slave本地的binlog里，默认关闭，主主就要开启。

如果关闭当主主复制的时候会报错，导致主B写入数据时主A无法同步，5.7版本及以后不用开启，用一张gtid_executed系统表记录同步复制的信息（UUID:事务号），减轻了压力。

查询：`show variables like 'log_slave_updates';`
在线配置：
配置文件：`log_slave_updates = 0`

### log-bin|log-bin-index
log-bin将开启binlog日志，mysql-bin 是日志的基本名或前缀名，可以更换。
log-bin-index指定的是binlog文件的索引文件，这个文件管理了所有的binlog文件的目录，不用进行更改，默认即可。

查询：
在线配置：
配置文件：`log-bin=mysql-bin` | `log-bin-index=mysql-bin.index`

### expire_logs_days
超过指定天数的binlog将被删除

查询：
在线配置：
配置文件：`expire_logs_days = 7`

### relay-log|relay-log-index
relay-log在从上指定从主那里同步过来的binlog文件存放位置。
relay-log-index定义relay-log-index的文件名，类似binlog的log-bin-index。

查询：`show variables like '%relay%';`
在线配置：
配置文件：`relay-log=mysql.relay-log` | `relay-log-index=mysql.relay-log.index`

### relay-log-info-file
设置relay-log.info的位置和名称（relay-log.info记录MASTER的binary_log的恢复位置和relay_log的位置）

查询：`show variables like '%relay%';`
在线配置：
配置文件：`relay-log=mysql.relay-log` | `relay-log-index=mysql.relay-log.index`

### max_relay_log_size
从服务器在主服务器上复制的binlog日志，如果超过这个值，将形成一个新的日志。用于控制文件大小。

查询：
在线配置：
配置文件：`max_relay_log_size = 512M`

### binlog-ignore-db|replicate-ignore-db
binlog-ignore-db无需开启二进制日志文件的数据库，多个数据库则要重复设置
replicate-ignore-db来设置不需要同步的库
bin-do-db,bin-ignore-db 为互斥关系，只需设置其中一项即可

查询：
在线配置：
配置文件：
`binlog-ignore-db = mysql`
`binlog-ignore-db = test`
`binlog-ignore-db = information_schema`
`binlog-ignore-db = performance_schema`
|
`replicate-ignore-db = mysql`
`replicate-ignore-db = test`
`replicate-ignore-db = information_schema`
`replicate-ignore-db = performance_schema`

### binlog_cache_size
二进制日志缓冲区吗，默认是32k。该参数是基于会话的，不要设置过大。

当事务的记录大于设定的**binlog_cache_size**时，mysql会把缓冲区中的日志信息写入一个临时文件中，所以该值也不能设置过小。

使用命令`show global status like 'binlog_cache_%';`查看**Binlog_cache_disk_use**（记录了使用临时文件写二进制日志的次数）和**Binlog_cache_use**（用缓冲的次数），可以看到**binlog_cache_size**值设置小了。

+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| Binlog_cache_disk_use | 1008  |
| Binlog_cache_use      | 5721  |
+-----------------------+-------+

查询：`show variables like 'binlog_cache_size';`
在线配置：
配置文件：`binlog_cache_size = 64M`

### max_binlog_size
超过这个数值，将会把日志写到一个新的文件中。最大值1GB，最小值1M，默认1GB。

查询：
在线配置：
配置文件：`max_binlog_size = 512M`

### max_binlog_cache_size
表示的是binlog 能够使用的最大cache 内存大小,默认是1M。

查询：`show global status like 'bin%';`
在线配置：`set global max_binlog_cache_size=1048576*100`
配置文件：`max_binlog_cache_size = 100M`

## 慢查询
### slow-query-log
是否开启慢查询日志，默认OFF关闭。

查询：`show variables like '%slow%';`
在线配置：`set global slow_query_log=1;`
配置文件：`slow_query_log=1`

### long_query_time
指定多少秒返回查询的结果为慢查询，单位秒。默认为10秒，正好等于数值的也不会记录，要超过。

查询：`show global status like 'bin%';`
在线配置：`set global long_query_time=2;`
配置文件：`long_query_time=2`

### slow_query_log_file
指定保存路径及文件名，默认为数据文件目录，hostname-slow.log

查询：`show variables like "slow%";`
在线配置：
配置文件：`slow_query_log_file=/data/hostname-slow.log`

### log-queries-not-using-indexes
记录所有没有使用到索引的查询语句，但可能会导致日志激增。

查询：
在线配置：
配置文件：`log-queries-not-using-indexes = TRUE`

### log_throttle_queries_not_using_indexes
表示每分钟允许记录到slow log的且未使用索引的sql语句次数，配合log-queries-not-using-indexes使用。限制条目数后，日志将不会激增。

查询：
在线配置：
配置文件：`log_throttle_queries_not_using_indexes=1000`

### min_examined_row_limit
记录那些由于查找了多余1000次而引发的慢查询

查询：
在线配置：
配置文件：`min_examined_row_limit=1000`

### log_slow_admin_statements
记录optimize table，analyze table和alter table等语句引发的慢查询

查询：
在线配置：
配置文件：`log-slow-admin-statements = TRUE`

### log-slow-admin-statements
记录从服务器产生的慢查询

查询：
在线配置：
配置文件：`log-slow-admin-statements = TRUE`

### log_slow_filter
慢查询日志过滤类型

查询：
在线配置：
配置文件：`log_slow_filter = full_scan,full_join,tmp_table,tmp_table_on_disk,filesort,filesort_on_disk`

### log_slow_verbosity
是否记录详细格式的日志信息

查询：
在线配置：
配置文件：`log_slow_verbosity = full`