mysql日志基本概念和启用

1. mysql日志

目录

• 1. mysql日志
  • 1.1. 概述
  • 1.2. MySQL日志文件分类
    • 1.2.1. 错误日志
    • 1.2.2. 二进制日志
    • 1.2.3. 通用查询日志
      • 1.2.3.1. 定位查询慢的sql
      • 1.2.3.2. 慢查询日志的启动和参数说明；
    • 1.2.4. 慢查询日志
    • 1.2.5. Innodb的在线redo日志
    • 1.2.6. 更新日志
  • 1.3. 日志启动
  • 1.4. 总结
    • 1.4.1. binglog日志应用场景

1.1. 概述

日志文件是MySQL数据库的重要组成部分。MySQL有几种不同的日志文件，通常包括错误日志文件，二进制日志，通用日志，慢查询日志，等等。
这些日志可以帮助我们定位mysqld内部发生的事件，数据库性能故障，记录数据的变更历史，用户恢复数据库等等。本文主要描述MySQL的各种日志文件。

1.2. MySQL日志文件分类

1. 错误日志(Error Log)
2. 二进制日志(Binary Log & Binary Log Index)
3. 通用查询日志(query log)
4. 慢查询日志(slow query log)
5. Innodb的在线 redo 日志(innodb redo log)
6. 更新日志(update log)

1.2.1. 错误日志

错误日志记录了MyQL Server运行过程中所有较为严重的警告和错误信息,以及MySQLServer 每次启动和关闭的详细信息。在默认情况下,系统记录错误日志的功能是关闭的, 错误信息被输出到标准错误输出(stderr) ,如果要开启系统记录错误日志的功能,需要在启动时开启-log-error 选项。错误日志的默认存放位置在数据目录下,以hostname.err 命 名。但是可以使用命令:
—log-error[=file_name], 修改其存放目录和文件名。
为了方便维护需要, 有时候会希望将错误日志中的内容做备份并重新开始记录, 这候时 就可以利用 MySQL 的** FLUSH LOGS** 命令来告诉 MySQL 备份旧日志文件并生成新的日志文件。 备份文件名以“.old”结尾。

1.2.2. 二进制日志

二进制日志,也就是我们常说的 binlog,也是 MySQL Server 中最为重要的日志之一。
当我们通过“—log-bin[=file_name]”打开了记录的功能之后,MySQL 会将所有修改数据库数据的 query 以二进制形式记录到日志文件中。当然, 日志中并不仅限于query 语句这么 简单, 还包括每一条query 所执行的时间, 所消耗的资源, 以及相关的事务信息,所以binlog 是事务安全的。
和错误日志一样,binlog 记录功能同样需要“—log-bin[=file_name]”参数的显式指 定才能开启, 如果未指定file_name, 则会在数据目录下记录为mysql-bin.** (*代表0~ 9 之间的某一个数字,来表示该日志的序号)。

binlog 还有其他一些附加选项参数:
—max_binlog_size
设置 binlog 的最大存储上限,当日志达到该上限时,MySQL 会 重新创建一个日志开始继续记录。不过偶尔也有超出该设置的binlog 产生,一般都是因为 在即将达到上限时,产生了一个较大的事务,为了保证事务安全,MySQL 不会将同一个事务 分开记录到两个 binlog 中。

—binlog-do-db=db_name
参数明确告诉 MySQL,需要对某个(db_name)数据库记 录 binlog,如果有了“—binlog-do-db=db_name”参数的显式指定,MySQL会忽略针对其他 数据库执行的 query,而仅仅记录针对指定数据库执行的query。

—binlog-ignore-db=db_name
与“—binlog-do-db=db_name”完全相反, 它显式指 定忽略某个(db_name)数据库的binlog 记录,当指定了这个参数之后,MySQL 会记录指定 数据库以外所有的数据库的 binlog。
“—binlog-ignore-db=db_name”与“—binlog-do-db=db_name”两个参数有一个共同的概念需要大家理解清楚,参数中的db_name不是指 query 语句更新的数据所在的数据库, 而是执行 query 的时候当前所处的数据库。 不论更新哪个数据库的数据,
MySQL 仅仅比较当前连接所处的数据库(通过use db_name切换后所在的数据库)与参数设置的数据库名,而 不会分析 query 语句所更新数据所在的数据库。

类似
use a;
update b.tab set name=xxx;
只会比较当前数据库a是否被忽略或者记录，而不管你的update语句时更新的哪一个数据库。
结局方案：严格控制用户权限，一个用户只能给一个库的操作权限，避免要跨库操作，需要切换到对应的库之下操作。

**mysql-bin.index **
文件(binary log index)的功能是记录所有Binary Log的绝对路径,保证 MySQL 各种线程能够顺利的根据它找到所有需要的Binary Log 文件。

二进制日志记录所有DML和DDL（select语句除外）的语句，语句以事件形式保存，描述数据的更改。

1.2.3. 通用查询日志

查询日志记录 MySQL 中所有的 query,通过“—log[=fina_name]”来打开该功能。由于记录了所有的 query,包括所有的 select,体积比较大,开启后对性能也有较大的影响, 所以请大家慎用该功能。 一般只用于跟踪某些特殊的sql 性能问题才会短暂打开该功能。默认的查询日志文件名为 hostname.log。

1.2.3.1. 定位查询慢的sql

1、注意：通过慢查询日志可以定位那些执行效率慢的sql语句，会记录所有sql查询时间超过慢查询日志设置的时间的sql语句。
但是：慢查询日志是在查询结束以后才记录，所以在应用反应执行效率很低的时候使用并不能直接找出执行慢的sql，
所以可以使用show processlist 命令查看当前mysql正在执行的线程，包括线程的状态是否锁表等，可以实时的查看sql的执行情况，同时对一些锁表操作进行优化。

1.2.3.2. 慢查询日志的启动和参数说明；

慢查询日志是MySQL提供的重要功能，当SQL的执行时间超过阈值 long_query_time时，通常会将这条SQL记录到慢查询日志中。

log_query_time = 2 #查询时间超过 2 秒，记录到 log 里
log_queries_not_using_indexes = ON #没有走索引的语句，记录到 log 里
log-slow-queries = /data/3306/slow.log  #启用慢查询
slow-query-log=On

对于慢查询日志，比较重要的几个参数如下：

slow_query_log=1
long_query_time=0.5
slow_query_log_file=/mysql/data/mysql_slow.log

slow_query_log表示是否开启慢查询
long_query_time表示慢查询阈值，SQL执行时间超过该值，则会记录到慢查询日志中。SQL的执行耗时不包含锁等待时间。
slow_query_log_file表示慢日志所在的路径。

除了以上这几个参数外，还有一些参数与慢查询日志有关，如下：

log_queries_not_using_indexes=1
log_throttle_queries_not_using_indexes=20
min_examined_row_limit=100
log_slow_admin_statements=1
log_slow_slave_statements=1


log_queries_not_using_indexes: 没有使用索引的SQL也将被记录到慢查询日志中；

log_throttle_queries_not_using_indexes: 如果log_queries_not_using_indexes打开，没有使用索引的sql将会写入到慢查询日志中，该参数将限制每分钟写入的SQL数量；

min_examined_row_limit: 对于查询扫描行数小于此参数的SQL，将不会记录到慢查询日志中；

log_slow_admin_statements: 管理语句执行时间大于阈值也将写入到慢查询日志中，管理语句包括alter table, check table等等；

log_slow_slave_statements: 从库应用binlog，如果binlog格式是statement，执行时间超过阈值时，将写入从库的慢查询日志， 对于ROW格式binlog，不管执行时间有没有超过阈值，都不会写入到从库的慢查询日志。

其中min_examined_row_limit参数表示SQL执行过程中扫描的行数阈值，如果扫描的行数小于该参数设置的值，即使SQL执行耗时大于慢查询阈值，也不会记录到慢查询日志中，该参数默认值为0。

关于日志切割脚本

mv /data/3306/slow.log /opt/$(date +%F)_slow.log
mysqladmin –uroot –ppcwangjixuan –S /data/3306/mysql.sock flush-logs

1.2.4. 慢查询日志

顾名思义,慢查询日志中记录的是执行时间较长的 query,也就是我们常说的 slow query,通过设 —log-slow-queries[=file_name]来打开该功能并设置记录位置和文件名, 默认文件名为 hostname-slow.log,默认目录也是数据目录。慢查询日志采用的是简单的文本格式, 可以通过各种文本编辑器查看其中的内容。其中记录了语句执行的时刻,执行所消耗的时间,执行用户,连接主机等相关息。
MySQL 还提 供了专门用来分析满查询日志的工具程序mysqlslowdump, 用来帮助数据库管理人员解决可 能存在的性能问题。

1.2.5. Innodb的在线redo日志

Innodb 是一个事务安全的存储引擎, 其事务安全性主要就是通过在线redo日志和记录在表空间中的 undo 信息来保证的。redo日志中记录了Innodb 所做的所有物理变更和事务信息,通过redo 日志和 undo 信息, Innodb 保证了在任何情况下的事务安全性。Innodb 的redo 日志同样默认存放在数据目录下, 可以通过innodb_log_group_home_dir来更改设置日志的存放位置,
通过 innodb_log_files_in_group 设置日志的数量。

1.2.6. 更新日志

更新日志是 MySQL 在较老的版本上使用的,其功能和 binlog 基本类似,只不过不是以
二进制格式来记录而是以简单的文本格式记录内容。自从 MySQL增加了binlog功能之后, 就很少使用更新日志了。从版本5.0 开始,MySQL 已经不再支持更新日志了。

1.3. 日志启动

#错误日志
log-error=/var/lib/mysql/error.log

#二进制日志
log-bin = /data/mysql/logs/binary/mybinlog
log-bin-index = /data/mysql/logs/binary/mybinlog.index
binlog_format = ROW
binlog_checksum = NONE
binlog_cache_size = 4M
max_binlog_cache_size = 4G
max_binlog_size = 1G
expire_logs_days = 1
sync_binlog = 1
innodb_flush_log_at_trx_commit = 1

#通用日志
#log=/var/lib/mysql/mysql.log
general_log_file='/tmp/general.log';
general_log=1;

## Relay log
relay_log = /data/mysql/logs/relay/mysqlrelay
relay_log_index = /data/mysql/logs/relay/mysqlrelay.index
relay_log_recovery = 1
relay-log-purge = 1
默认情况下系统如下参数是打开的，表示系统应用完中继日志之后会自动清理掉已经应用的中继日志
relay_log_purge
如果中继日志没有清理，可以通过flush logs 清理;
flush logs；
flush master losg;
flush bing logs;


#慢查询日志
slow_query_log = 1
slow_query_log_file = /data/mysql/logs/slow3306.log
long_query_time = 0.1
log_queries_not_using_indexes =1
#log_throttle_queries_not_using_indexes = 60
#min_examined_row_limit = 100
#log_slow_admin_statements = 1
#log_slow_slave_statements = 1

1.4. 总结

1、系统故障时，建议首先查看错误日志，以帮助用户迅速定位故障原因。
2、如果要记录数据的变更、数据的备份、数据的复制等操作时，二进制日志必须打开，以帮助用户进行数据恢复等操作，默认不记录该日志
3、如果希望记录数据库发生的任何操作，包括select,则需要使用--log打开查询日志，此日志默认是关闭的，一般情况下建议不打开此日志、以免影响系统的性能。
4、如果系统查看系统性能的问题，希望找到有性能问题的sql语句，则需要使用--slow_query_log打开慢查询日志对于大量的慢查询日志，建议使用mysqldumpslow工具进行汇总查看。

1.4.1. binglog日志应用场景

场景一：基于binlog的主从复制，通过它可以将Master上的数据复制到一个或多个slave上，实现灾难恢复、水平扩展、统计分析、远程数据分发等功能。
场景二：读写分离，通过binlog复制来实现横向扩展，来实现读写分离。
场景三：数据恢复，当发生误删除数据库记录，或者因为误操作导致数据库存在大量脏数据的情况时，可以通过反解binlog来完成。
场景四：数据最终一致性，保证数据库与其他任何组件(es、mq、redis)的数据一致性。
场景五：异地多活，跨数据中心之间的数据同步。