日志分类

MySql日志类型 解析说明
错误日志（error log） 当数据库启动、运行、停止时产生该日志
普通查询日志（general query log） 客户端连接数据库执行语句时产生该日志
二进制日志（binary log） 当数据库内容发生改变时产生该日志，也被用来实现主从复制功能
中继日志（relay log） 从库上收到主库的数据更新时产生该日志
慢查询日志（show query log） SQL语句在数据库查询超过指定时间时产生该日志
DDL日志（metadata log） 执行DDL语句操作元数据时产生该日志

默认情况下，以上所有的日志都处于非激活状态（Linux环境）。当激活日志时，所有的日志都默认配置在数据文件的目录下。

普通查询日志

普通查询日志的作用是记录客户端的连接信息及执行的SQL语句信息。
普通查询日志的重要性很低，默认情况下该日志是关闭的。

  1. mysql> show variables like '%general_log%';
  2. +------------------+--------------------------------------------+
  3. | Variable_name    | Value                                      |
  4. +------------------+--------------------------------------------+
  5. | general_log      | OFF                                        |
  6. | general_log_file | /var/log//mysql-query.log                  |
  7. +------------------+--------------------------------------------+
  8. 2 rows in set (0.00 sec)

如果希望永久开启，在my.cnf的[mysqld]模块下配置：

  1. [mysqld]
  2. general_log=on
  3. general_log_file=/var/log//mysql-query.log

在高并发数据库的场景下，建议关闭普通查询日志（默认为关闭），因为查询日志的信息量很大，容易导致磁盘I/O性能问题。当访问量不是很大，企业又有审计执行的SQL语句的需求时，可以考虑开启。

错误日志

Mysql的错误日志用于记录mysql服务进程mysqld在启动、关闭或运行过程中遇到的错误信息。
通常由mysqld或mydql_safe程序产生。

  1. [mysqld]
  2. log-error=/var/log/mysqld.log

查看Errorr日志信息

  1. mysql> show variables like '%log_error%';
  2. +---------------------+---------------------+
  3. | Variable_name       | Value               |
  4. +---------------------+---------------------+
  5. | binlog_error_action | ABORT_SERVER        |
  6. | log_error           | /var/log/mysqld.log |
  7. | log_error_verbosity | 3                   |
  8. +---------------------+---------------------+
  9. 3 rows in set (0.00 sec)
Desired Log Filtering log_error_verbosity Value
Error messages 1
Error and warning messages 2
Error, warning, and note messages 3

慢查询日志

慢查询日志（slow query log）是记录执行时间超出指定值（long_query_time）或其它地址条件（如：没有使用索引，结果集大于10000行）的SQL语句。

慢查询日志的参数说明

慢查询参数 解释说明
slow_query_log 慢查询开启开关，默认为”OFF“
slow-query-log-file 记录慢查询语句的文件，文件名如”hostname-slow.log”
long_query_time 记录大于指定N秒的SQL语句，默认为10s，也可以使用微秒单位
log_queries_not_using_indexes 记录没有使用到索引的SQL语句，默认为”OFF”
min_examined_row_limit 记录结果大于N行的SQL语句，默认为0行
log_slow_admin_statements 记录管理的慢SQL语句，如：alter table, analyze table, check table, create table, create index, drop index, optimize table, repair table
log_throttle_queries_not_using_indexes 限制每分钟写入记录的慢SQL语句的数量，默认为”0”,表示没有限制

企业中常见的慢查询配置参数，在my.cnf的[mysqld]模块下配置。

  1. [mysqld]
  2. slow-query-log = ON                        #<==慢查询开启开关
  3. long_query_time = 2                        #<==记录大于2秒的SQL语句。
  4. log_queries_not_using_indexes = ON         #<==没有使用到索引的SQL语句。
  5. slow-query-log-file = /var/log/slow.log    #<==记录SQL语句的文件。
  6. min_examined_row_limit = 800               #<==记录结果集大于800行的SQL语句。

测试检查

  1. mysql> show variables like 'slow_query%';
  2. +---------------------+-----------------------------+
  3. | Variable_name       | Value                       |
  4. +---------------------+-----------------------------+
  5. | slow_query_log      | ON                          | #开启慢查询日志
  6. | slow_query_log_file | /var/log/slow.log | #文件路径
  7. +---------------------+-----------------------------+
  8. 2 rows in set (0.00 sec)

【慢日志分析】

二进制日志（Binary log ）

Binary log 用来记录数据库中发生的修改情况，比如数据的修改、表格的创建及修改等，它既可以记录涉及修改的SQL，也可以记录数据修改的行变化记录，同时也记录了执行时间。

Binlog日志的两个最重要的使用场景：

  • MySQL主从复制：MySQL Replication在Master端开启binlog，Master把它的二进制日志传递给slaves来达到master-slave数据一致的目的
    * 数据恢复：通过使用 mysqlbinlog工具来使恢复数据

开启二进制日志：

  1. [mysqld]
  2. log-bin=/var/log/mysql-binlog.log

常用的Binlog操作命令：

# 是否启用binlog日志

  1. show variables like 'log_bin';

# 查看详细的日志配置信息

  1. show global variables like '%log%';

# mysql数据存储目录

  1. show variables like '%dir%';

# 查看binlog的目录

  1. show global variables like "%log_bin%";

# 查看当前服务器使用的biglog文件及大小

  1. show binary logs;

# 查看主服务器使用的biglog文件及大小

# 查看最新一个binlog日志文件名称和Position

  1. show master status;

# 事件查询命令

# IN ‘log_name’ ：指定要查询的binlog文件名(不指定就是第一个binlog文件)

# FROM pos ：指定从哪个pos起始点开始查起(不指定就是从整个文件首个pos点开始算)

# LIMIT [offset,] ：偏移量(不指定就是0)

# row_count ：查询总条数(不指定就是所有行)

  1. show binlog events [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count];

# 查看 binlog 内容

  1. show binlog events;

# 查看具体一个binlog文件的内容 （in 后面为binlog的文件名）

  1. show binlog events in 'master.000003';

# 设置binlog文件保存事件，过期删除，单位天

  1. set global expire_log_days=3;

# 删除当前的binlog文件

  1. reset master;

# 删除slave的中继日志

  1. reset slave;

# 删除指定日期前的日志索引中binlog日志文件

  1. purge master logs before '2019-03-09 14:00:00';

# 删除指定日志文件

  1. purge master logs to 'master.000003';

写 Binlog 的时机：

  1. 对支持事务的引擎如InnoDB而言，必须要提交了事务才会记录binlog。binlog 什么时候刷新到磁盘跟参数 sync_binlog 相关。
  2. * 如果设置为0，则表示MySQL不控制binlog的刷新，由文件系统去控制它缓存的刷新；
  3. * 如果设置为不为0的值，则表示每 sync_binlog 次事务，MySQL调用文件系统的刷新操作刷新binlog到磁盘中。
  4. * 设为1是最安全的，在系统故障时最多丢失一个事务的更新，但是会对性能有所影响。
  5. 如果 sync_binlog=0 或 sync_binlog大于1，当发生电源故障或操作系统崩溃时，可能有一部分已提交但其binlog未被同步到磁盘的事务会被丢失，恢复程序将无法恢复这部分事务。

Binlog 文件以及扩展，（binlog日志）包括两类文件：

  1. * 二进制日志索引文件（文件名后缀为.index）用于记录所有有效的的二进制文件
  2. * 二进制日志文件（文件名后缀为.00000*）记录数据库所有的DDL和DML语句事件

binlog是一个二进制文件集合，每个binlog文件以一个4字节的魔数开头，接着是一组Events：

  1. * 魔数：0xfe62696e对应的是0xfebin；
  2. * Event：每个Event包含header和data两个部分；header提供了Event的创建时间，哪个服务器等信息，data部分提供的是针对该Event的具体信息，如具体数据的修改；
  3. * 第一个Event用于描述binlog文件的格式版本，这个格式就是event写入binlog文件的格式；
  4. * 其余的Event按照第一个Event的格式版本写入；
  5. * 最后一个Event用于说明下一个binlog文件；
  6. * binlog的索引文件是一个文本文件，其中内容为当前的binlog文件列表

当遇到以下3种情况时，MySQL会重新生成一个新的日志文件，文件序号递增：

  1. * MySQL服务器停止或重启时
  2. * 使用 flush logs 命令；
  3. * 当 binlog 文件大小超过 max_binlog_size 变量的值时；

Binlog 的日志格式：

  1. * STATEMENT：基于SQL语句的复制（statement-based replication, SBR）
  2. * ROW：基于行的复制（row-based replication, RBR）
  3. * MIXED：混合模式复制（mixed-based replication, MBR）
  4. 在 MySQL 5.7.7 之前，默认的格式是 STATEMENT；MySQL 5.7.7 及更高版本中，默认值是 ROW。日志格式通过 binlog-format 指定，如 binlog-format=STATEMENT、binlog-format=ROW、binlog-format=MIXED。

Statement
每一条会修改数据的sql都会记录在binlog中。
优点：不需要记录每一行的变化，减少了binlog日志量，节约了IO, 提高了性能。
缺点：由于记录的只是执行语句，为了这些语句能在slave上正确运行，因此还必须记录每条语句在执行的时候的一些相关信息，以保证所有语句能在slave得到和在master端执行的时候相同的结果。另外mysql的复制，像一些特定函数的功能，slave与master要保持一致会有很多相关问题。
Row
5.1.5版本的MySQL才开始支持 row level 的复制,它不记录sql语句上下文相关信息，仅保存哪条记录被修改。
优点： binlog中可以不记录执行的sql语句的上下文相关的信息，仅需要记录那一条记录被修改成什么了。所以row的日志内容会非常清楚的记录下每一行数据修改的细节。而且不会出现某些特定情况下的存储过程，或function，以及trigger的调用和触发无法被正确复制的问题.
缺点:所有的执行的语句当记录到日志中的时候，都将以每行记录的修改来记录，这样可能会产生大量的日志内容。

注：将二进制日志格式设置为ROW时，有些更改仍然使用基于语句的格式，包括所有DDL语句，例如CREATE TABLE， ALTER TABLE，或 DROP TABLE。

Mixed
从5.1.8版本开始，MySQL提供了Mixed格式，实际上就是Statement与Row的结合。 在Mixed模式下，一般的语句修改使用statment格式保存binlog，如一些函数，statement无法完成主从复制的操作，则采用row格式保存binlog，MySQL会根据执行的每一条具体的sql语句来区分对待记录的日志形式，也就是在Statement和Row之间选择一种。
https://juejin.im/post/5c85cecb6fb9a049fb447c82

中继日志

从服务器上面也启动一个 I/O thread，通过配置好的用户名和密码, 连接到主服务器上面请求读取二进制日志，然后把读取到的二进制日志写到本地的一个Realy log（中继日志）里面。