MySQL日志管理

MySQL日志是MySQL管理的重要组成部分，其中主要包括：错误日志、一般查询日志、慢查询日志、二进制日志、中继日志和事物日志！

查看日志的相关配置

mysql> show global variables like '%log%';
+-----------------------------------------+----------------------------------------+
| Variable_name                           | Value                                  |
+-----------------------------------------+----------------------------------------+
| back_log                                | 50                                     |
| binlog_cache_size                       | 32768                                  |
| binlog_direct_non_transactional_updates | OFF                                    |
| binlog_format                           | MIXED                                  |
| binlog_stmt_cache_size                  | 32768                                  |
| expire_logs_days                        | 0                                      |
| general_log                             | OFF                                    |
| general_log_file                        | /application/mysql/data/host1.log      |
| innodb_flush_log_at_trx_commit          | 1                                      |
| innodb_locks_unsafe_for_binlog          | OFF                                    |
| innodb_log_buffer_size                  | 8388608                                |
| innodb_log_file_size                    | 5242880                                |
| innodb_log_files_in_group               | 2                                      |
| innodb_log_group_home_dir               | ./                                     |
| innodb_mirrored_log_groups              | 1                                      |
| log                                     | OFF                                    |
| log_bin                                 | ON                                     |
| log_bin_trust_function_creators         | OFF                                    |
| log_error                               | /application/mysql/data/host1.err      |
| log_output                              | FILE                                   |
| log_queries_not_using_indexes           | OFF                                    |
| log_slave_updates                       | OFF                                    |
| log_slow_queries                        | OFF                                    |
| log_warnings                            | 1                                      |
| max_binlog_cache_size                   | 18446744073709547520                   |
| max_binlog_size                         | 1073741824                             |
| max_binlog_stmt_cache_size              | 18446744073709547520                   |
| max_relay_log_size                      | 0                                      |
| relay_log                               |                                        |
| relay_log_index                         |                                        |
| relay_log_info_file                     | relay-log.info                         |
| relay_log_purge                         | ON                                     |
| relay_log_recovery                      | OFF                                    |
| relay_log_space_limit                   | 0                                      |
| slow_query_log                          | OFF                                    |
| slow_query_log_file                     | /application/mysql/data/host1-slow.log |
| sql_log_bin                             | ON                                     |
| sql_log_off                             | OFF                                    |
| sync_binlog                             | 0                                      |
| sync_relay_log                          | 0                                      |
| sync_relay_log_info                     | 0                                      |
+-----------------------------------------+----------------------------------------+

一、错误日志

在mysql数据库中，错误日志功能是默认开启的。并且，错误日志无法被禁止。

错误日志主要记录以下信息：

1. 服务器启动和关闭过程中的信息

2. 服务器运行过程中的错误信息

3. 事件调度器运行一个事件时产生的信息

4. 在服务器上启动从服务器进程时产生的信息

错误日志默认的地址：/mysql_data_dir/hostname.err

配置错误日志：

vi /etc/my.cnf
[mysqld]
log-error=/var/log/mysqld.err

默认情况下，mysql只记录错误日志，不会记录服务器的警告信息，如果需要同时记录警告信息需要在[mysqld]下将log_warning打开

log-warnings=1

二、一般查询日志

一般查询日志的相关配置指令有 general_log 、general_log_file，默认情况下一般查询日志是关闭的，因为打开一般查询日志后，数据库的每次查询都会写进日志里，这样会有大量的IO操作，大大降低数据服务器性能

默认地址：/mysql_data_dir/hostname.log

配置：

general=[0|1]              #开关
general-log-file=/path/    #日志路径

三、慢查询日志

记录msyql服务器查询时间超过指定值的记录，这里的语句执行时间为实际执行时间，而非在cpu上的执行时间，其他进程（如表锁）会影响语句的执行时间，因此负载较重的服务器上更容易产生慢查询。

日志默认路径：/mysql_data_dir/hostname-slow.log

配置参数：

slow-query-log=[0|1]        #开关
slow-query-log-file=/path/  #日志路径
long-query-time=1.000000    #查询时间,支持毫秒级解析度，最小0，默认10，单位秒

        注意：MySQL日志配置有一个参数：log_output={TABLE|FILE|NONE}，该参数定义一般查询日志和慢查询日志的保存方式，可以是TABLE、FILE、NONE，也可以是TABLE及FILE的组合(用逗号隔开)，默认为TABLE。如果组合中出现了NONE，那么其它设定都将失效，同时，无论是否启用日志功能，也不会记录任何相关的日志信息。作用范围为全局级别，可用于配置文件，属动态变量。

四、二进制日志（重点）

记录任何引起或可能引起数据库变化的操作，他是我们恢复数据，或即时点恢复的重要数据

1. 打开二进制日志

log-bin=1

2. 二进制日志的格式

二进制日志一般有3种记录格式：基于语句statement、基于行row、混合方式mixed

mixed格式服务器会根据不同的操作选择最优的记录方式

其中基于行的定义格式数据量会大一些但是可以保证数据的精确性。

binlog-format=[statement|row|mixed]

3. 二进制日志的事件

产生的时间

相对位置

4. 二进制日志文件

二进制日志的默认路径为MySQL的数据目录，日志文件包括2类

索引文件：msyql-bin.index（文本文件，可查看）

日志文件：mysql-bin.xxxxxx

5. 二进制日志缓存大小

mysql> show global variables like '%log%';
+-----------------------------------------+----------------------------------------+
| Variable_name                           | Value                                  |
+-----------------------------------------+----------------------------------------+
| binlog_cache_size                       | 32768                                  |
| binlog_stmt_cache_size                  | 32768                                  |
| sync_binlog                             | 10                                  |
+-----------------------------------------+----------------------------------------+

MySQL产生日志的时候，MySQL会先在内存中完成日志记录，然后写入磁盘，但是写磁盘会有大量IO操作，导致msyql性能降低，因此，我们可先将日志写入到缓存当中，当缓存满了以后再将日志输出到磁盘，或定时同步，从而提高性能，但同时增大了数据丢失的风险，因此缓存的并不是越大越好。

binlog_cache_size的大小是随着binlog_stmt_cache_size的大小而变化的，所以我们通常配置的时候只配置binlog_stmt_cache_size即可

其中：sync_binlog表示设定多久同步一次二进制日志至磁盘文件中，0表示不同步，任何正数值都表示对二进制每多少次写操作之后同步一次。当autocommit的值为1时，每条语句的执行都会引起二进制日志同步，否则，每个事务的提交会引起二进制日志同步。

6. 常用操作命令

mysql> show master status;  查看当前正使用的二进制文件，包括上一个时间结束的位置
mysql> show binlog events in 'mysql-bin.000001' [from position];查看binlog详情，可跟from位置参数
mysql> flush logs;  滚动binlog日志，从服务器之滚动中继日志，错误日志不滚动 
mysql> purge binary logs to 'mysql-bin.000003' 删除mysql-bin.000003之前的日志
mysql> purge binary logs before '2016-03-31 15:42:38'; 删除2016-03-31 15:42:38时间点之前的日志
mysql> show binary logs; 查看所有的binlog日志文件

7. mysqlbinlog命令用法

按照位置截取：

mysqlbinlog mysqlbin.000020 --start-position=365 --stop-position=456 -r pos.sql

按照时间截取

mysqlbinlog mysqlbin.000020 --start-datetime='2015-01-01 15:30:28' --stop-datetime='2015-01-01 20:30:28' -r time.sql

-d参数可指定要截取的数据库

二进制日志文件的读写IO很有可能成为数据库性能的一个瓶颈，因此我们建议将二进制日志存放在一个IO性能较高的设备上，如ssd磁盘，同时也可以方式MySQL服务器崩溃而导致所有数据丢失

五、中继日志

从主服务器的二进制日志文件中复制而来的事件，并保存为的日志文件

六、事物日志

事务性存储引擎用于保证原子性、一致性、隔离性和持久性；

参数：

innodb_flush_log_at_trx_commit:
0: 每秒同步，并执行磁盘flush操作；
1：每事务同步，并执行磁盘flush操作；
2: 每事务同步，但不执行磁盘flush操作；