MySQL 日志

在MySQL 中，有4 种不同的日志，分别是错误日志、二进制日志（BINLOG 日志）、查询日志和慢查询日志，这些日志记录着数据库在不同方面的踪迹。1、 错误日志错误日志是MySQL 中最重要的日志之一，它记录了当mysqld 启动和停止时，以及服务器在运行过程中发生任何严重错误时的相关信息。当数据库出现任何故障导致无法正常使用时，可以首先查看此日志。可以用--log-error[=file_name]选项来指定mysqld（MySQL 服务器）保存错误日志文件的位置。如果没有给定file_name 值，mysqld 使用错误日志名host_name.err（host_name 为主机名）并默认在参数DATADIR（数据目录）指定的目录中写入日志文件。2、 二进制日志二进制日志（BINLOG）记录了所有的DDL（数据定义语言）语句和DML（数据操纵语言）语句，但是不包括数据查询语句。语句以“事件”的形式保存，它描述了数据的更改过程。此日志对于灾难时的数据恢复起着极其重要的作用。[root@CentOS 3306]# ll CentOS.err -rw-r-----. 1 mysql root 59442 12月  2 17:43 CentOS.err##日志的位置当用--log-bin[=file_name]选项启动时，mysqld 将包含所有更新数据的SQL 命令写入日志文件。如果没有给出file_name 值，默认名为主机名后面跟“-bin”。如果给出了文件名，但没有包含路径，则文件默认被写入参数DATADIR（数据目录）指定的目录。[mysqld]log-bin=mysql_redo[root@CentOS 3306]# ll mysql_redo.*-rw-rw----. 1 mysql mysql 433 12月  2 17:45 mysql_redo.000001-rw-rw----. 1 mysql mysql  20 12月  2 17:43 mysql_redo.index##日志的格式mysql5.5中，二进制日志的格式分为3种：STATEMENT、ROW、MIXED，可以在[mysqld]中通过参数binlog-format进行设置，这3种格式的区别如下。STATEMENT：日志中记录的都是SQL语句，通过mysqlbinlog工具（# mysqlbinlog mysql_redo.000001），可以清洗的看到每条语句的文本。这种格式的优点是日志记录清晰易读、日志量少，对I/O影响较小。ROW：它将每一行的变更记录到日志中，而不是记录SQL语句。比如一个简单的更新SQL：update emp set name='ABC'，如果是STATEMENT格式，日志中会记录一行SQL文本；如果是ROW，由于是全表进行更新，也就是每一行记录都会发生变更，如果是一个100万的大表，则日志中会记录100万条记录的变化情况。对I/O影响较大。MIXED：混合了STATEMENT和ROW两种日志。默认情况下采用STATEMENT，但在一些特殊情况下采用ROW来进行记录。##日志的读取由于日志以二进制方式存储，不能直接读取，需要用mysqlbinlog 工具来查看，语法如下：shell> mysqlbinlog log-file;##日志的删除执行“RESET MASTER;”命令，该命令将删除所有BINLOG 日志，新日志编号从“000001”开始。mysql> reset master;执行“PURGE MASTER LOGS TO 'mysql-bin.******'”命令，该命令将删除“******”编号之前的所有日志。mysql> purge master logs to 'mysql_redo.000003';执行“PURGE MASTER LOGS BEFORE 'yyyy-mm-dd hh24:mi:ss'”命令，该命令将删除日期为“yyyy-mm-dd hh24:mi:ss”之前产生的所有日志。mysql> purge master logs before '2007-08-10 04:07:00';设置参数--expire_logs_days=#，此参数的含义是设置日志的过期天数，过了指定的天数后日志将会被自动删除，这样将将有利于减少DBA 管理日志的工作量。[mysqld]expire-logs-days=7##用“flush logs”触发日志文件更新mysql> flush logs;##其他选项? --binlog-do-db=db_name该选项告诉主服务器，如果当前的数据库(即USE 选定的数据库)是db_name，应将更新记录到二进制日志中。其他所有没有显式指定的数据库更新将被忽略，不记录在日志中。? --binlog-ignore-db=db_name该选项告诉主服务器，如果当前的数据库(即USE 选定的数据库)是db_name，不应将更新保存到二进制日志中，其他没有显式忽略的数据库都将进行记录。如果想记录或忽略多个数据库，可以对上面两个选项分别使用多次，即对每个数据库指定相应的选项。3、 查询日志查询日志记录了客户端的所有语句，而二进制日志不包含只查询数据的语句。查询日志和慢查询日志都可以选择保存在文件或者表中，通过参数log-output控制：[mysqld]log-output=FILE,TABLE  ##日志保存在表和文件中general-log=TRUE       ##启用查询日志#general-log-file=/var/lib/mysql/CentOS.log  ##控制日志文件的路径表指的是mysql库中的general_log（慢查询日志是slow_log表）表。如果没有指定general-log-file=file_name的值，那么日志将写入参数datadir指定的路径下，默认文件名是host_name.log。[root@CentOS 3306]# ll *.log-rw-rw----. 1 mysql mysql 598 12月  2 18:40 CentOS.log[root@CentOS 3306]# cat CentOS.log /usr/local/mysql/bin/mysqld, Version: 5.6.27-log (MySQL Community Server (GPL)). started with:Tcp port: 3306  Unix socket: /mydata/data/3306/mysql_3306.sockTime                 Id Command    Argument151202 18:40:21    1 Connectroot@localhost on     1 Queryselect @@version_comment limit 1151202 18:40:24    1 QuerySELECT DATABASE()    1 Init DBtest    1 Queryshow databases    1 Queryshow tables    1 Field Listt1 151202 18:40:32    1 Queryshow databases151202 18:40:37    1 QuerySELECT DATABASE()    1 Init DBtest151202 18:40:40    1 Queryselect * from t1注意：log 日志中记录了所有数据库的操作，对于访问频繁的系统，此日志对系统性能的影响较大，建议一般情况下关。4、 慢查询日志和前面几种日志一样，如果没有给定file_name 的值，日志将写入参数DATADIR（数据目录）指定的路径下，默认文件名是host_name-slow.log。[mysqld]log-output=FILE,TABLE  ##日志保存在表和文件中（慢查询日志是slow_log表）long-query-time=2  ##查询超过2秒的SQL都会被记录min-examined-row-limit=10000  ##查询记录超过10000行的SQL都会被记录（long-query-time和min-examined-row-limit时“并且”关系）log-slow-admin-statements=TRUE  ##记录管理SQL，默认不记录。log-queries-not-using-indexes=TRUE  ##记录没有使用索引的SQL，默认不记录。slow-query-log=TRUE         ##启用慢查询日志slow-query-log-file=/var/lib/mysql/CentOS-slow.log  ##日志保存路劲##查看慢查询日志。[root@CentOS 3306]# cat CentOS-slow.log /usr/local/mysql/bin/mysqld, Version: 5.6.27-log (MySQL Community Server (GPL)). started with:Tcp port: 3306  Unix socket: /mydata/data/3306/mysql_3306.sockTime                 Id Command    Argument# Time: 151202 19:15:52# User@Host: root[root] @ localhost [127.0.0.1]  Id:     2# Query_time: 48.333541  Lock_time: 0.000000 Rows_sent: 0  Rows_examined: 0SET timestamp=1449054952;KILL QUERY 1;# Time: 151202 19:15:53# User@Host: root[root] @ localhost [127.0.0.1]  Id:     1# Query_time: 159.371774  Lock_time: 0.000622 Rows_sent: 0  Rows_examined: 768use test;SET timestamp=1449054953;select * from t1 a,t1 b,t1 c order by 1,2,3;/usr/local/mysql/bin/mysqld, Version: 5.6.27-log (MySQL Community Server (GPL)). started with:Tcp port: 3306  Unix socket: /mydata/data/3306/mysql_3306.sockTime                 Id Command    Argument# Time: 151202 19:23:04# User@Host: root[root] @ localhost [127.0.0.1]  Id:     1# Query_time: 1.539581  Lock_time: 1.531698 Rows_sent: 768  Rows_examined: 768SET timestamp=1449055384;select * from test.t1;##如果慢查询日志中记录内容很多，可以使用mysqldumpslow 工具（MySQL 客户端安装自带）来对慢查询日志进行分类汇总。[root@CentOS 3306]# mysqldumpslow CentOS-slow.log Reading mysql slow query log from CentOS-slow.logCount: 1  Time=159.37s (159s)  Lock=0.00s (0s)  Rows=0.0 (0), root[root]@localhost  select * from t1 a,t1 b,t1 c order by N,N,NCount: 1  Time=48.33s (48s)  Lock=0.00s (0s)  Rows=0.0 (0), root[root]@localhost  KILL QUERY NCount: 1  Time=0.01s (0s)  Lock=1.53s (1s)  Rows=768.0 (768), root[root]@localhost  select * from test.t1对于SQL 文本完全一致，只是变量不同的语句，mysqldumpslow 将会自动视为同一个语句进行统计，变量值用N 来代替。这个统计结果将大大增加用户阅读慢查询日志的效率，并迅速定位系统的SQL 瓶颈。注意：慢查询日志对于我们发现应用中有性能问题的SQL 很有帮助，建议正常情况下，打开此日志并经常查看分析。总结：MySQL自带了一些工具可以对日志进行分析，但这些工具相对功能较单一，而且对查询日志没有提供分析工具。有很多第三方工具，而mysqlsla是其中较广泛的一个。除了mysqlsla，还有一些常用日志分析工具，比如myprofi、mysql-explain-slow-log、mysqllogfilter等。