MySQL日志

Mysql数据库中存在着4种不同日志，分别是错误日志、二进制日志（binlog）、查询日志和慢查询日志。这些日志记录着mysql不同方面的踪迹，用来辅助管理员进行问题诊断和调优。

一、错误日志

1、作用：记录mysqld启动、停止以及mysql数据库在运行过程中发生的各种严重错误信息。当数据库发生任何故障导致无法重启时，可以参考错误日志进行故障诊断。

2、位置：可以使用--log-error[=file_name]参数选项来指定mysqld保存错误日志文件的位置。如果没有给定file_name文件位置，mysqld使用的错误日志名为host_name.err并默认保存在datadir指定的目录下。

二、二进制日志（binlog）

1、作用：Binlog记录了数据库所有的ddl语句和dml语句，但不包括select语句内容，语句以事件的形式保存，描述了数据的变更顺序，对于灾难恢复和备份恢复来说至关重要。相当于oracle数据库中的archivelog文件。

二进制日志文件记录了数据库的变更过程，对于数据库的安全性和完整性起着至关重要的作用。

2、位置和格式：当用--log-bin[=file_name]选项启动时，mysqld开始将数据库变更情况写入日志文件。如果没有给出file_name值，默认名为主机名-bin。如果给出了文件名，但不包含路径，则文件默认被写入datadir参数指定路径。

Mysq5.5中，二进制日志格式分为3种：statement，row和mixed，可以在启动数据库时通过参数--binlog_format进行设置，这3种格式区别如下：

1）statement：

Mysql5.1之前的版本都采用这种方式，binlog日志中记录的都是语句，每一条对数据库造成修改的语句都会记录在日志中，通过mysqlbinlog工具，可以清晰的看到每条语句的文本。主从复制时，从库的日志解析为原文本在从库中执行。优点是日志量小，清晰易懂，对io压力小，缺点是某些情况下slave的日志复制会出差。

2）row：

Mysql5.1.11之后出现的。它将每一行变更记录到日志中，而不是记录sql语句。优点是记录每一行数据变化的细节，不会出现slave模式下某些情况无法复制的情况。缺点是日志量大，对io影响较大。

3）mixed：

这是目前mysql的默认binlog日志格式，即混合了statement和row两种日志。默认情况下采用statement，但是在一些特殊情况下采用row方式记录日志。此种模式利用statement和row的优点，而尽量避开他们的缺点。

注意：可以在global和session级别对binlog日志的格式进行修改。

3、日志文件的读取

由于binlog以二进制方式存储，不能直接读取，因此需要使用mysqlbinlog工具进行日志分析，语法：mysqlbinlog log-file。

例：

1）以binlog方式启动数据库：

[root@faspdev bin]# ./mysqld_safe --user=mysql --log-bin=binlog-test

2）执行dml语句：

mysql> delete from t1;

Query OK, 2 rows affected (0.02 sec)

3）mysqlbinlog查看binlog内容：

[root@faspdev data]# ../bin/mysqlbinlog binlog-test.000001

/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;

/*!40019 SET @@session.max_insert_delayed_threads=0*/;

/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;

DELIMITER /*!*/;

# at 4

#161111 17:45:52 server id 1  end_log_pos 120 CRC32 0x5bfb8e4f Start: binlog v 4, server v 5.6.31-log created 161111 17:45:52 at startup

# Warning: this binlog is either in use or was not closed properly.

ROLLBACK/*!*/;

BINLOG '

UJMlWA8BAAAAdAAAAHgAAAABAAQANS42LjMxLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA

AAAAAAAAAAAAAAAAAABQkyVYEzgNAAgAEgAEBAQEEgAAXAAEGggAAAAICAgCAAAACgoKGRkAAU+O

+1s=

'/*!*/;

# at 120

#161111 17:47:29 server id 1  end_log_pos 199 CRC32 0x3f0abe26 Query thread_id=1 exec_time=0 error_code=0

SET TIMESTAMP=1478857649/*!*/;

SET @@session.pseudo_thread_id=1/*!*/;

SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;

SET @@session.sql_mode=1075838976/*!*/;

SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;

/*!C utf8 *//*!*/;

SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/;

SET @@session.lc_time_names=0/*!*/;

SET @@session.collation_database=DEFAULT/*!*/;

BEGIN

/*!*/;

# at 199

#161111 17:47:29 server id 1  end_log_pos 287 CRC32 0x78e57a7e Query thread_id=1 exec_time=0 error_code=0

use `test`/*!*/;

SET TIMESTAMP=1478857649/*!*/;

delete from t1

/*!*/;

# at 287

#161111 17:47:29 server id 1  end_log_pos 318 CRC32 0x0b2d7e98 Xid = 9

COMMIT/*!*/;

DELIMITER ;

# End of log file

ROLLBACK /* added by mysqlbinlog */;

/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;

/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

如果日志格式是row，mysqlbinlog解析的文件是无法读懂的字符，可以加-v或-vv进行查看。

4、日志文件的删除

1）方法1：reset master删除所有的binlog日志文件，删除后的日志文件从1开始编号。

mysql> reset master;

Query OK, 0 rows affected (0.00 sec)

2）方法二：purge master logs to ‘mysql-bin.******’命令，该命令删除‘******’编号之前的所有日志文件。

3）方法三：purge master logs before ‘yyyy-mm-dd hh24:mi:ss’命令，该命令删除‘yyyy-mm-dd hh24:mi:ss’日期之前的所有日志。

4）设置参数--expire_logs_days=#,此参数含义是设置日志过期天数， 过了指定天数后的日志将会被自动删除，这样有利于减少DBA管理日志的工作量。

5、binlog管理相关参数

1）--binlog-do-db=db_name:该参数告诉主服务器，如果当前的数据库是db_name，应该将其记录到binlog中，其他没有被指定的数据库将被忽略，可多次使用，对多个数据库进行定义。

2）--binlog-ignore-db=db_name:改参数告诉主服务器，如果当前数据库为db_name，则不记录binlog，其他没有指定的数据库变化将记录binlog中，可多次使用，对多个数据库进行定义。

3）--innodb-sage-binlog:该参数经常和--sync-binlog=N（没写N次日志同步磁盘）一起配合使用，使得事务在日志中的记录更加安全。

4）Set sql_log_bin=0：具有super权限的客户端可以通过此语句禁止将自己的语句记录二进制文件，这个选项在某些环境下是有用的，但是使用一定倍加小心，免得造成slave环境下的数据不一致。

三、查询日志

1、作用：查询日志记录客户端的所有语句，而binlog不记录select语句。

2、位置：查询日志和慢查询日志都可以保存在文件或表中，并使用参数--log-output[=value]来进行控制，value的值可以是table，file，none的一个或多个的组合，中间用逗号进行分割，分别表示日志保存在表，文件，不保存在表或文件中，这里的表指的是mysql库总的general_log（慢查询日志是slow_log）表。其中none的优先级最高，比如--log-output=table,file表示日志同时输出到表和文件中，--log-output=table,none表示日志不保存在表中。如果不显示使用此参数，则表示日志输出到文件中。一般日志输出到表中要占用更多的系统资源。

如果要启用查询日志，可以通过参数--general_log[={0|1}]和--general_log_file=file_name来进行控制。前者控制是否启用查询日志（--general_log设置为1或者不带值都可以启用查询日志，设置为0表示关闭查询日志，不指定此参数表示不启动查询日志），后者控制日志文件的路径。

如果没有指定--general_log_file=file_name值，且没有显示设置--log_output参数，那么日志默认写入datadir目录下的，默认文件名为host_name.log。这两个参数都是global类型，可以在启动时或系统运行时动态修改。如果想在session级别控制日志是否被记录，则通过在session中设置参数sql_log_off为on或off来进行控制。

3、日志内容的读取：查询日志为文本文档格式，一般直接读取就可。

4、一般情况下不建议开启查询日志功能。

四、慢查询日志

慢查询日志记录了所有执行时间超过参数long_query_time设置值并且扫描记录数不小于min_examined_row_limit的所有sql语句的日志（获得表锁定的时间不能算作执行时间）。Long_query_time默认为10秒，最小为0，精度可以到微秒。

一般两类语句不记录到慢查询日志中：管理语句和不使用索引进行查询的语句。如果要监控这两类SQL语句，可以分别通过参数--log-slow-admin-statements和log_queries_not_using_indexes进行控制。

1、文件位置和格式：慢查询日志默认是关闭的，在mysql5.1.29前，当用--log-slow-queries[=file_name]选项启动mysqld时，慢查询日志开始被记录。和前几种日志一样，如果没有指定file_name的值，日志将写入datadir路径，默认文件名为host_name-slow.log。在mysql5.1.29后，--log-slow-queries参数废弃，采用两个新的参数进行替换：--slow_query_log[={0|1}]显示指定慢查询日志状态，如果不指定值或指定值为1都会打开慢查询；使用slow_query_log_file[=file_name]来指定慢查询日志路径，另外还可以指定--log-output参数指定日志的输出格式，默认输出到文件。需要注意的是如果选择输出到表，则表中的记录的慢查询时间只能精确到秒，而日志文件中可以精确到微秒。

2、日志文件的读取：慢查询日志文件是存文本方式存储，可以直接读取。也可以通过mysqldumpslow工具（../bin/mysqldumpslow faspdev-slow.log）对慢查询日志进行汇总处理。慢查询日志通常用来定位mysql服务中sql问题，默认建议打开慢查询日志，并定期查看分析。

mysql> show variables like 'long_query_time';

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

| Variable_name   | Value     |

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

| long_query_time | 10.000000 |

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

1 row in set (0.00 sec)

mysql> set long_query_time=2;

Query OK, 0 rows affected (0.00 sec)

mysql> show variables like '%slow_query_log%';

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

| Variable_name       | Value                                      |

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

| slow_query_log      | OFF                                        |

| slow_query_log_file | /usr/local/mysql5631/data/faspdev-slow.log |

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

2 rows in set (0.00 sec)

mysql> set global slow_query_log=1;

Query OK, 0 rows affected (0.00 sec)