Mysql 日志总结

Mysql 日志总结

 

主要包含：错误日志、查询日志、慢查询日志、事务日志、二进制日志

1. 错误日志

记录MySQL的启动、停止信息以及在MySQL运行过程中的错误信息。

（默认开启）

参数：

 

log_error（默认开启）

    log_error=[path/[file_name]],如果不指定文件名，则默认hostname.err.

修改后重启生效

 

log_warnings（在5.7.2之后被不建议使用，取而代之的是log_error_verbosity）

0：数据库启动时候加载的内容+数据库启动后的错误+从库停止，

会有记录（从库）

1 （默认）：Aborted connection(5.6半同步，如果从库超时，则日志会记录)

 

log_error_verbosity(5.7.2被引入)

Verbosity Value Message Types Logged

1 Errors only

2 Errors and warnings

3（默认） Errors, warnings, and notes

 

查看错误日志的位置：

show variables like 'log_error';

 

删除错误日志 —Error log

<5.5.7版本时，flush logs会将filename.err命名为filename.err_old

>5.5.7版本则只是会重建错误日志，也就是除非错误日志被删除了会重建一个；否则不会对线上错误日志有影响。

 

注意：删除错误日志后，不会自动重建。需要flush logs或者重启数

据库。

mysqladmin -u root -p flush-logs

或者

mysql>flush logs;

 

2. 查询日志

默认情况下查询日志是关闭的。由于查询日志会记录用户的所有操作，其中还包含增删查改等信息，在并发操作大的环境下会产生大量的信息从而导致不必要的磁盘IO，会影响mysql的性能的。

 

参数：

general_log（默认关闭）{0-关闭,1-开启} 可以在线打开和关闭；

set global general_log='ON';

general_log_file=file_name,不指定文件名只指定路径路径则为hostname.log

 

关于表mysql.general_log表：

• select * from mysql.general_log;

• 表mysql.general_log不支持insert，update和delete；

• 表是不同步的（复制），不写binlog；

dump的时候会被忽略掉，5.6.6以后只能dump表结构，内容不会dump出来.

 

log_output=｛TABLE | FILE | NONE｝   --查询日志的记录方式

TABLE：便于统计，写入一个CSV引擎的表中。

FILE：默认。

TABLE，FILE：便于统计，写入一个CSV引擎的表中。

NONE：任何地方都不写。

注意：如果log_output=NONE但是general_log为1，则默认不写常规日志。

 

set sql_log_off=’ON’ 当前操作不写常规日志。

 

删除、备份general 日志：

[root@mytest02 mysql]# mv mytest02.log mytest02.log.bak

[root@mytest02 mysql]# mysqladmin -uroot -proot flush-logs

一定记住flush logs；或者mysqladmin flush-logs

 

3. 慢查询日志

MySQL的慢查询日志是MySQL提供的一种日志记录，它用来记录在MySQL中响应时间超过阀值（long_query_time、min_examined_row_limit、log_slow_admin_statements andlog_queries_not_using_indexes）的语句。（默认关闭）

 

注意：慢查询日志不会计算语句在开始执行之前等待锁的时间（initial slow query），mysqld只会从语句等待的锁释放掉，并开始执行开始计算时间（执行开始后的锁定时间会记录）。所以慢查询日志中的语句顺序和语句的发出顺序或者general log的顺序不同。

 

参数：

slow_query_log（默认关闭）{0-关闭,1-开启} 可以在线打开和关闭；

set global slow_query_log='ON';

slow_query_log_file=file_name,不指定路径则为hostname-slow.log

long_query_time：SQL执行的阀值

log_output=｛TABLE | FILE | NONE｝

TABLE：便于统计，写入一个CSV引擎的表mysql.slow_log中。

FILE：默认。

TABLE，FILE：便于统计，写入一个CSV引擎的表mysql.slow_log中。

NONE：任何地方都不写。

注意：如果log_output=NONE但是slow_query_log为1，则默认不写常规日志。

 

log_queries_not_using_indexes：记录没有用索引的查询

log_throttle_queries_not_using_indexes：记录没有使用索引查询的语句条数在一分钟内记录的条数

*如何消除上线后未使用索引查询的隐患。线上开启5秒即可。

 

min_examined_row_limit：要检查的行数大于等于N时才记录为慢查询，前提是必须满足long_query_time和log-queries-not-using-indexes约束。

 

log_slow_admin_statements：管理语句是否记录ALTER TABLE, ANALYZE TABLE, CHECK TABLE,

CREATE INDEX, DROP INDEX, OPTIMIZE TABLE,and REPAIR TABLE.

log_slow_slave_statements：从库的语句执行是否记录

 

分析慢查询日志：

mysqldumpslow -s r -t 10 /database/mysql/slow-log

 得到返回记录集最多的10个查询。

mysqldumpslow -s t -t 10 -g  “left join”  /database/mysql/slow-log

 得到按照时间排序的前10条里面含有左连接的查询语句。

输出结果分析

 

 

4. 二进制日志

以“事件”的形式保存，描述数据的更改，包含关于每个更新数据库的语句的执行时间。

注意：binlog只记录完整事务。

目的：最大可能地恢复数据库

启用该选项数据库性能降低1%，但保障数据库完整性，对于重要数据库值得以性能换完整。

参数：

 

log_bin（默认关闭）{0-关闭,1-开启} 不可以在线打开和关闭；

修改my.cnf中的参数！

log_bin_basename，不指定路径则为hostname-bin.00000N

log_bin_index，不置顶路径则为hostname-bin.index

 

注意：

所有的日志都建议自己起名字，不要用默认！

 

binlog_cache_size：内存中缓存二进制的缓冲区大小。

注意：对于大的事务，二进制日志会超过max_binlog_size设定的值。也

即是事务仅仅写入一个二进制日志。

 

 

二进制日志记录在语句或事务完成之后立即执行（而不是在任何锁释放或任何提交完成之前写入），这确保日志以提交顺序登录。对于不支持事务的表，执行之后马上会被记录到二进制日志中去。对于未提交的事务中，更改事务表（例如InnoDB表）的所有更新（UPDATE，DELETE或INSERT）将被缓存，直到服务器收到COMMIT语句。 在这一点上，mysqld在执行COMMIT之前将整个事务写入二进制日志。对非事务性表的修改无法回滚。 如果回滚的事务包括对非事务性表的修改，则会在最后使⽤用ROLLBACK语句记录整个事务，以确保对这些表的修改进行复制。

 

当处理事务的线程开始时，它将缓冲区语句分配一个binlog_cache_size缓冲区。 如果一个语句大于这个值，线程将打开一个临时文件来存储事务。 当线程结束时，临时文件被删除。

binlog_cache_use状态变量显示用于存储语句的此缓冲区（可能还有一个临时文件）的事务数。

binlog_cache_disk_use状态变量显示有多少这些事务实际上必须使用临时文件。

这两个变量可用于将binlog_cache_size调整到足够大的值，以避免使用临时文件。

max_binlog_cache_size系统变量（默认为4GB，也是最大值）可用于限制用于缓存多语

句事务的总大小。 如果一个事务大于这个多个字节，它将失败并回滚。 最小值为4096。

 

sync_binlog：{0,1}二进制内容从缓冲区落入磁盘的时机。

5.7.7以后，sync_binlog参数被默认设置为1，减少了binlog中的

不一致可能性。

 

expire_logs_days：日志过期时间，一定要和备份相结合。

binlog_format：binlog模式。

• 基于语句句：当时语句句的原样写⼊入。

• 基于⾏行行：只记录受影响的⾏行行。

• 混合模式：默认⽤用语句句模式，遇到下⾯面情况⽤用基于⾏行行：

• uuid()

• UDF

• temporary

• rows_found/row_count

• user()/current_user()

• LOAD_FILE()

 

二进制日志的维护：

• 切换二进制日志：

• flush logs;

• 清理所有二进制日志，并从新开始记录二进制日志：

• reset master;

• 清理指定编号之前的二进制日志：

• purge binary logs to ‘3306-bin.000005’;

• 清理指定时间前的二进制日志：

• purge binary logs before '2017-02-25 19:30:57';

 

不建议手动rm删除二进制日志，会造成index文件不一致。

 

 

5. 事务日志

事务日志（InnoDB特有的日志）可以帮助提高事务的效率。使用事务日志，存储引擎在修改表的数据时只需要修改其内存拷贝，再把改修改行为记录到持久在硬盘上的事务日志中，而不用每次都将修改的数据本身持久到磁盘。事务日志采用追加的方式，因此写日志的操作是磁盘上一小块区域内的顺序I/O，而不像随机I/O需要在磁盘的多个地方移动磁头，所以采用事务日志的方式相对来说要快得多。事务日志持久以后，内存中被修改的数据在后台可以慢慢的刷回到磁盘。目前大多数的存储引擎都是这样实现的，我们通常称之为预写式日志，修改数据需要写两次磁盘。

 

如果数据的修改已经记录到事务日志并持久化，但数据本身还没有写回磁盘，此时系统崩溃，存储引擎在重启时能够自动恢复这部分修改的数据。具有的恢复方式则视存储引擎而定。

 

参数：

innodb_log_group_home_dir

InnoDB重做日志文件的目录路径。 如果没有指定任何InnoDB日志变量，则默认是在

MySQL数据目录中创建两个名为ib_logfile0和ib_logfile1的文件。

 

innodb_log_files_in_group

日志组中的日志文件数。 InnoDB以循环方式写入文件。 默认（和推荐）值为2.文件的位置由innodb_log_group_home_dir指定。

 

innodb_log_file_size

日志组中每个日志文件的大小（以字节为单位）。 日志文件（innodb_log_file_size *innodb_log_files_in_group）的组合大小不能超过略小于512GB的最大值。默认值为48M。最小值为4M（5.7.11）

 

innodb_file_per_table

可以修改InnoDB为独立表空间模式，每个数据库的每个表都会生成一个数据空间。

独立表空间优点：

1． 每个表都有自已独立的表空间。

2． 每个表的数据和索引都会存在自已的表空间中。

3． 可以实现单表在不同的数据库中移动。

4． 空间可以回收（除drop table操作处，表空不能自已回收）

 

支持在线修改：

SET GLOBAL innodb_file_per_table=1;

如何将表从系统表空间中分离出来

SET GLOBAL innodb_file_per_table=1;

ALTER TABLE table_name ENGINE=InnoDB;

 

innodb_flush_log_at_trx_commit           

0：log buffer将每秒一次地写入log file中，并且log file的flush(刷到磁盘)操作同时进行。该模式下在事务提交的时候，不会主动触发写入磁盘的操作。

1：每次事务提交时MySQL都会把log buffer的数据写入log file，并且flush(刷到磁盘)中去，该模式为系统默认。

2：每次事务提交时MySQL都会把log buffer的数据写入log file，但是flush(刷到磁盘)操作并不会同时进行。该模式下，MySQL会每秒执行一次 flush(刷到磁盘)操作。