mysql 5.0.26 log文档_26.mysql日志

26.mysql日志

mysql日志包括：错误日志、二进制日志、查询日志、慢查询日志。

26.1 错误日志

错误日志记录了mysqld启动到停止之间发生的任何严重错误的相关信息。

mysql故障时应首先查看错误日志。

可以用--log-error= 选项来指定mysqld起动后错误日志保存得路径和文件名。

不指定时默认路径为数据目录(datadir参数指定)，默认文件名为host_name.err。

26.2 二进制日志binlog

二进制日志记录所有的DDL操作和DML操作，不包括DQL(select)操作，用于mysql复制和灾难恢复。

26.2.1 日志的位置和格式

参数log-bin指定了binlog的路径和文件名，默认路径为数据目录(datadir参数指定)，默认文件名为host_name-bin.000001。

binlog格式分为statement、row、mixed 3种，由参数binlog-format指定，默认使用mixed。

1.statement：记录每条DDL和DML语句，从库依次执行该语句以达到主从同步的目的。

优点：日志清晰易读，日志量少，对IO影响小。

缺点：数据一致性差。

2.row：记录DDL和DML语句影响到的每行变前、变后数据，从库更新变后数据以达到主从同步的目的。

优点：记录数据变化细节，数据一致性高。

缺点：日志量大，对IO影响大。

3.mixed：对不确定SQL使用row格式记录binlog,对确定SQL使用statement格式记录binlog。

不确定SQL指SQL适用的行数与数据有关是不确定的，例如update和delete语句不使用主键列作为where条件，或使用数据库函数now()等。

优点：中和了statement和row的优缺点。

26.2.2 日志的读取

二进制日志binlog不能直接读取，需要使用mysqlbinlog工具读取。

语法：

# mysqlbinlog log_file

statement格式的日志内容：

连接数据库，设置时间戳，设置唯一、外键、非空约束，设置SQL模式，设置字符集和排序规则，记录SQL语句，设置时间戳，记录SQL语句...

row格式的日志内容：需要增加-v或者-vv选项。

26.2.3 日志的删除

binlog日志占用大量磁盘空间，需要定期删除。

删除方法如下：

1.reset master 将原有binlog全部删除，新的binlog从000001重新开始。

2.purge master logs to 'hostname-bin.00000x' 将00000x之前的日志全部删除。

3.purge master logs before 'yyyy-mm-dd hh24:mi:ss' 将'yyyy-mm-dd hh24:mi:ss'时间之前的日志全部删除。

4.设置日志过期天数参数expire_logs_days=1 将1天之前的日志全部删除。

26.2.4 其他选项(参数)

binlog-do-db=db_name 指定记录binlog的数据库，多个数据库时写多条语句

binlog-ignore-db=db_name 指定不记录binlog的数据库，多个数据库时写多条语句

sync-binlog=N 每写N次日志时将binlog从内存缓冲区刷新到磁盘上

innodb-safe-binlog

sql_log_bin=0 不记录binlog

26.3 查询日志

查询日志记录了执行的所有语句,日志内容为文本格式。

26.3.1 日志的位置和格式

参数：

general_log=0｜1 是否启用查询日志，general_log=默认为1启用查询日志；不设置general_log指不启用查询日志。

general_log_file=file_name 设置查询日志的路径和文件名，默认路径为DATADIR，默认文件名host_name.log

log_output=NONE|FILE|TABLE|FILE,TABLE

NONE指不保存查询日志；

FILE指将查询日志保存在文件host_name.log中(默认FILE)；

TABLE指将查询日志保存在表中，保存在表中比保存在文件中要占用更多的资源；

FILE,TABLE指将查询日志同时保存在文件中和表中。

sql_log_off=on|off 指在session级别控制本条SQL是否记录查询日志。

设置参数：

1.在my.cnf文件中设置，并重启mysql

2.在global级别设置

mysql> set global general_log=0;

3.在session级别设置

mysql> set sql_log_off=on

26.3.2 日志的读取

查询日志内容是文本格式，可直接读取。

注意：查询日志记录了所有数据库操作，对于访问频繁的系统，日志对系统性能影响较大时建议关闭查询日志。

26.4 慢查询日志

慢查询日志记录了所有执行时间超过参数long_query_time设置值，并且扫描记录不小于参数min_examined_row_limit的所有SQL语句的日志。

注意：

参数long_query_time 单位：秒，默认值10秒，精度可以到微秒，如：2.999999秒；

获得表锁的时间不算作执行时间；

默认情况下管理语句和不使用索引的查询语句不记录慢查询日志；

管理语句包括：alter table,analyze table,check table,create index,drop index,optimize table,repair table等；

可以通过参数--log-slow-admin-statements启用对管理语句记录慢查询日志；

可以通过参数--log-queries-not-using-indexes启用对不使用索引的查询语句记录慢查询日志。

参数汇总：

slow_query_log=0｜1 ： 是否开启慢查询日志，不设置默认关闭，slow_query_log=默认开启。

log_slow_queries ： 指定是否开启慢查询日志(该参数要被slow_query_log取代，做兼容性保留)

slow_query_log_file ： 指定慢查询日志的路径和文件名，默认路径为DATADIR，默认文件名host_name-slow.log。

long_query_time ： 设定慢查询的阀值，超出次设定值的SQL即被记录到慢查询日志，缺省值为10s。

min_examined_row_limit： 查询检查返回少于该参数指定行的SQL不被记录到慢查询日志

og-slow-admin-statements： 是否启用对管理语句记录慢查询日志

log_queries_not_using_indexes: 是否启用对不使用索引的查询语句记录慢查询日志

26.4.1 日志的位置和格式

慢查询日志默认路径为DATADIR，默认文件名host_name-slow.log。

log_output=NONE|FILE|TABLE|FILE,TABLE 也控制慢查询日志输出格式。

26.4.2 日志的读取

错误日志、查询日志、慢查询日志内容都是纯文本格式，可以直接读取。

set long_query_time=2;

set long_query_time=0.01;

set log_output=FILE,TABLE;

从表中查看慢查询日志

select * from slow_log where sql_text='?';

用mysqldumpslow工具可以对慢查询日志进行分类汇总(对同一语句进行统计)：

# mysqldumpslow host_name-slow.log

慢查询日志可以发现有性能问题的SQL，建议开启慢查询日志。

26.5 mysqlsla简介

mysqlsla(Mysql statement log Analyzer)是用于日志分析的第三方工具，

可以分析查询日志、慢查询日志、二进制日志和自定义日志。

下载地址http://hackmysql.com/mysqlsla

man mysqlsla 帮助命令，用于查看mysqlsla的参数

mysqlsla --log-type general host_name.log 解析查询日志

mysqlsla --log-type msl host_name.log 解析带微妙的查询日志

mysqlsla --log-type slow host_name-slow.log 解析慢查询日志

mysqlbinlog binlog-00000.x | mysqlsla --log-type binary - 解析binlog日志

mysqlsla --log-type udl --udl-format udl.log 解析自定义格式的日志

注意：除二进制日志外，可以省略--log-type选项，即mysqlsla file_name.log即可。

mysqlsla --statement-filter|-sf +update file_name.log 仅显示update语句的日志

mysqlsla --statement-filter|-sf -delete file_name.log 不显示delete语句的日志

mysqlsla --explain|-ex file_name.log 在报表中显示执行计划

mysqlsla --sort file_name.log 对分析结果进行排序，慢查询日志和微妙日志按总执行时间排序，其它日志按总执行次数排序

mysqlsla --grep PATTERN 用PATTERN匹配SQL语句中的字符串，如--grep 'count' 只显示带count的SQL语句的分析结果。

分析结果第一部分是对被分析日志的总体介绍，如：

Auto-detected logs as general logs 自动探测到日志的类型

109 queries total 总的查询次数

36 unique 对SQL语句去重(将实际值抽象并过滤空格后distinct)后的查询次数

sorted by 'c_sum'|'t_sum' 结果的排序方式是按总执行次数排序，还是按总执行时间排序

分析结果第二部分是报告的主题，按照执行次数由大到小的顺序输出前10条SQL的统计信息，如：

Count :16(14.68%) 该SQL的执行次数及占比

Connection ID:2 该SQL的连接次数

Database：test 执行该SQL的数据库

Users： 执行该SQL的用户，如果多个用户时，按每个用户的执行次数比例显示

Query abstract:xxx 该SQL抽象后的语句

Query sample：xxx 该SQL带入实际参数值的样例

可以将mysqlsla的常用配置选项写入mysqlsla配置文件~/.mysqlsla文件。

除mysqlsla外，还有其它日志分析工具，如：myprofi、mysql-explain-slow-log、mysqllogfilter等。

26.6 小结