mysql日志管理

一、innodb redo日志

innodb_log_group_home_dir:指定innodb的redo日志的保存路径，默认在datadir目录下

innodb_log_file_size:指定日志文件大小

innodb_log_files_in_group:指定日志组的数量

Innodb_fast_shutdown:指定Innodb的关闭模式

                                     1表示快速关闭类似与oracle shutdown immediate（默认）

                                     0类似于oracle的shutdown normal

                                     2类似于oracle的shutdown abort

如果要修改Innodb的日志文件的配置，需要将Innodb_fast_shutdown指定为1.

例：修改Innodb日志文件的大小为100M:

1.停止mysql服务

2.将旧的日志文件移动到备份目录下

3.修改my.cnf中的参数即：innodb_log_file_size=100M

4.重启mysql

二、慢日志

慢日志将所有查询语句时间超过系统变量long_query_time（默认10s），

而且访问记录数超过min_examined_row_limit（默认0）的语句记录下来：

(root@localhost)[(none)]> show variables like 'long_query_time';
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time | 10.000000 |

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

(root@localhost)[(none)]> show variables like 'min_exam%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| min_examined_row_limit | 0     |

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

默认情况下慢日志功能是禁用的，由参数slow_query_log控制，slow_query_log_file 指定目录：

(root@localhost)[(none)]> show variables like 'slow_query%';
+---------------------+------------------------------+
| Variable_name       | Value                        |
+---------------------+------------------------------+
| slow_query_log      | OFF                          |
| slow_query_log_file | /mydata/data/mysql1-slow.log |

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

禁用和开启慢日志功能（可以动态修改）：

set global slow_query_log='OFF';

set global slow_query_log='ON';

其它几个慢日志相关的参数：

log_short_format：控制输出到慢日志文件的信息，设置后会减少输出信息

log_slow_admin_statements：控制是否将执行时间长的管理语句记录，例如analyze table、alter table等

log_queries_not_using_indexes：控制是否将未使用索引的语句记录到慢日志中

log_throttle_queries_not_using_indexes：与log_queries_not_using_indexes一起使用，控制每分钟输入到慢日志的记录数，0表示不限制

log_slow_slave_statements：复制专用，控制是否将复制的查询语句输出到慢日志中。

注意：sql语句执行完成之后，mysqld进程将符合的sql语句写入到慢日志中，因此慢日志中的语句并不一定和执行语句顺序相同，因为语句执行时间不同。

如果慢日志很大，可以使用mysqldumpslow命令查看。

三、普通查询日志

普通查询日志会记录mysql的几乎所有操作，一般用来做审计

由两个参数控制：

general_log：控制是否启用，0关闭，1启用.可以动态修改

general_log_file：设置文件名，默认在data目录下的hostname.log

(root@localhost)[(none)]> show global variables like 'general%';
+------------------+-------------------------+
| Variable_name    | Value                   |
+------------------+-------------------------+
| general_log      | OFF                     |
| general_log_file | /mydata/data/mysql1.log |

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

启用和关闭：

set global general_log='ON';

set global general_log='OFF';  

如果在会话层禁用和启用普通日志查询，在会话级别设置sql_log_off参数：

启用：set sql_log_off='OFF';

禁用：set sql_log_off='ON';

对于普通查询日志和慢日志可以写入到日志中，也可以写入到表中。但是写到表中的资源消耗相对较大，使用log_output参数控制（默认是写到文件中）：

(root@localhost)[(none)]> show variables like 'log_out%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_output    | FILE  |

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

此参数有三个值：

TABLE：输入到表中，对应general_log和slow_log 

FILE：到文件中，默认

NONE：不输出日志

这些参数可以同时指定，以,分割，即可以让他即输入到文件中又输入到表中。当指定为none时，不论是否开启了慢日志和查询日志都不会有输出。

slow_log和general_log表默认情况下是使用csv引擎，可以truncate，但是如果要执行alter和drop需要先禁用日志功能后修改，也可以将表改为myisam引擎。

使用mysqldump导出表的时候只导出结构不会导出数据。

四、二进制日志

二进制日志两个重要功能：

1.用于复制：mysql master端发送到slave端，slave端在本地重做，达到主从复制的目的

2.用于恢复：类似于rman的恢复

启用二进制日志由参数log_bin控制，默认是不启用的

这个参数就是用来指定二进制日志的路径和文件名，如果不指定，那么默认在data目录下的hostname-bin.xxxx

(root@localhost)[(none)]> show variables like 'log_bin';
+---------------+-------+
| Variable_name | Value |

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

| log_bin          |  OFF   |

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

与biglog相关的一些变量和参数：

max_binlog_size：控制binlog的最大大小，但是生成的日志也有可能会大于此值，例如当有大事务的时候 ，如果超过该值，则产生新的二进制日志文件，后缀名+1，并记录到.index文件。

log_bin_index：=【filename】指定binlog的索引，当中包含了使用的binlog信息，类似于v$archived_log、这是一个文本格式文件

binlog_cache_size：指定了binlog的分配内存空间，binlog_cache_size是基于回话的，也就是，当一个线程开始一个事务时，mysql会自动分配一个大小为binlog_cache_size的缓存，因此该值得设置需要相当小心

binlog_cache_use：状态变量显示了binlog_cache_size 使用的事务数

binlog_cache_disk：状态变量显示了使用的临时文件事务数  这两个参数组合起来可以进行调整和优化

(root@localhost)[(none)]> show global status like 'binlog_cache%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| Binlog_cache_disk_use | 0     |
| Binlog_cache_use      | 0     |

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

max_binlog_cache_size：限制事务能够是哦那个的最大缓存区（最大4G，也是默认值）

sync_binlog：设置二进制日志同步到磁盘的频率， 表示每写缓存多少次就同步到磁盘，如果将N设置为1，则表示采用同步写磁盘的方式来写二进制日志，该参数很重要，这个以后还会提到。值得注意的是，在将该参数设置为1时，也应该将innodb_support_xa设为1来解决，这可以确保二进制日志和InnoDB存储引擎数据文件的同步

binlog_format：指定binlog的格式，默认是以语句为单位

binlog-do-db、binlog-ignore-db：表示需要写入或者忽略写入哪些库的日志，默认值为空，则表示将所有库的日志同步到二进制日志。

删除binlog的方法：

1.RESET MASTER

(root@localhost)[(none)]> reset master；

此命令将binlog的序号重置为1，并且删除以前全部的binlog

2.PURGE MASTER LOGS TO & PURGE MASTER LOGS BEFORE

执行PURGE MASTER LOGS TO 'mysql-bin.******'命令，是将'******'编号之前的所有日志进行删除

(root@localhost)[(none)]> PURGE MASTER LOGS TO 'mysql-bin.000002';

Query OK, 0 rows affected (0.00 sec)

执行PURGE MASTER LOGS BEFORE 'yyyy-mm-dd hh:mm:ss'命令，是将在'yyyy-mm-dd hh:mm:ss'时间之前的所有日志进行删除

(root@localhost)[(none)]> purge master logs before '2015-10-18 17:08:34';

Query OK, 0 rows affected (0.21 sec)

3.设置EXPIRE_LOGS_DAYS

此参数是设置日志的过期天数，过期的日志将会被自动删除，这有利于减少我们管理日志的工作量，需要修改my.cnf

(root@localhost)[(none)]> show variables like 'expir%' ;

+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| expire_logs_days | 2     |
+------------------+-------+

1 row in set (0.00 sec)