[大数据]【进阶篇】MySQL日志

一、错误日志

??错误日志记录了当 mysqld 启动和停止时，以及服务器在运行过程中发生任何严重错误时的相关信息。当数据库出现任何故障导致无法正常使用时，可以首先查看此日志。

??在 MySQL 数据库中，默认开启错误日志功能。一般情况下，错误日志存储在 MySQL 数据库的数据文件夹下，通常名称为 hostname.err。其中，hostname 表示 MySQL 服务器的主机名。

1、错误日志的配置

??在 MySQL 配置文件中，错误日志所记录的信息可以通过 log-error 和 log-warnings 来定义，其中，log-err 定义是否启用错误日志功能和错误日志的存储位置，log-warnings 定义是否将警告信息也记录到错误日志中。

??将 log_error 选项加入到 MySQL 配置文件的 [mysqld] 组中，形式如下：

[mysqld]
log-error=dir/{filename}

dir 参数指定错误日志的存储路径
filename 参数指定错误日志的文件名

省略参数时文件名默认为主机名，存放在 Data 目录中。

??配置完后，需要重启MySQL 服务后，才开始生效。

2、错误日志的查看

??在 MySQL 中，通过 SHOW 命令可以查看错误日志文件所在的目录及文件名信息。例如：

SHOW VARIABLES LIKE 'log_error';

+---------------+----------------------------------------------------------------+
| Variable_name | Value                                                          |
+---------------+----------------------------------------------------------------+
| log_error     | C:\ProgramData\MySQL\MySQL Server 5.7\Data\SK-20210813JBQG.err |
+---------------+----------------------------------------------------------------+
1 row in set, 1 warning (0.04 sec)

??通过上面的文件路径和文件名，找到相应的文件打开查看即可。错误日志是一个文本文件。

3、删除错误日志

??在 MySQL 中，可以使用 mysqladmin 命令来开启新的错误日志，以保证 MySQL 服务器上的硬盘空间。mysqladmin 命令的语法如下：

mysqladmin -uroot -p flush-logs

??执行该命令后，MySQL 服务器首先会自动创建一个新的错误日志，然后将旧的错误日志更名为 filename.err-old，当确定不在需要时，手动删除该文件即可。

二、二进制日志

??二进制日志（BINLOG）记录了所有的 DDL（数据定义语言）语句和 DML（数据操纵语言）语句，但是不包括数据查询语句。语句以“事件”的形式保存，它描述了数据的更改过程。此日志对于灾难时的数据恢复起着极其重要的作用。

1、日志的位置和格式

??默认情况下，二进制日志功能是关闭的。可以通过以下命令查看二进制日志是否开启，命令如下：

SHOW VARIABLES LIKE 'log_bin';

+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin       | OFF   |
+---------------+-------+
1 row in set, 1 warning (0.02 sec)

??显然，二进制日志是关闭的，那如何打开并配置呢？接着往下看。

??在 MySQL 中，可以通过在配置文件中添加 log-bin 选项来开启二进制日志，格式如下：

[mysqld]
log-bin=dir/[filename]

dir 参数指定二进制文件的存储路径
filename 参数指定二进制文件的文件名，其形式为 filename.number，number 的形式为 000001、000002 等

??从上面的配置看的出，其和错误日志很相似，不同在于每次重启 MySQL 服务后，二进制日志都会生成一个新的文件，这些日志文件的文件名中 filename 部分不会改变，number 会不断递增。而错误日志除非自己新建，不然只有一个。

2、二进制日志的查看和读取

??二进制日志的查看语法如下：

查看 MySQL 中有哪些二进制日志文件

SHOW binary logs;
??
±---------------------------±----------+
| Log_name | File_size |
±---------------------------±----------+
| LAPTOP-UHQ6V8KP-bin.000001 | 177 |
| LAPTOP-UHQ6V8KP-bin.000002 | 154 |
±---------------------------±----------+
2 rows in set (0.00 sec)

查看当前正在写入的二进制日志文件

SHOW master status;
??
±---------------------------±---------±-------------±-----------------±------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
±---------------------------±---------±-------------±-----------------±------------------+
| LAPTOP-UHQ6V8KP-bin.000002 | 154 | | | |
±---------------------------±---------±-------------±-----------------±------------------+
1 row in set (0.00 sec)

??二进制日志内容读取：

??二进制日志使用二进制格式存储，不能直接打开查看。如果需要查看二进制日志，必须使用 mysqlbinlog 工具来查看。

mysqlbinlog filename.number

??注意：mysqlbinlog 命令只在当前文件夹下查找指定的二进制日志，因此需要在二进制日志所在的目录下运行该命令，否则将会找不到指定的二进制日志文件。

??除了 filename.number 文件，MySQL 还会生成一个名为 filename.index 的文件，这个文件存储着所有二进制日志文件的列表，可以用记事本打开该文件。

tips:
实际工作中，二进制日志文件与数据库的数据文件放在不同的磁盘，这样即使数据文件所在的硬盘被破坏，也可以使用另一块硬盘上的二进制日志来恢复数据库文件。两块硬盘同时坏了的可能性要小得多，这样可以保证数据库中数据的安全。

3、二进制日志的删除

??对于比较繁忙的 OLTP（在线事务处理）系统，由于每天生成日志量大，这些日志如果长时间不清除，将会对磁盘空间带来很大的浪费。因此，定期删除日志是 DBA 维护 MySQL 数据库的一个重要工作内容。下面将介绍几种删除日志的常见方法。

1. 删除所有 BINLOG 日志

RESET MASTER;

??执行上述语句来删除所有二进制日志。删除所有二进制日志后，MySQL 将会重新创建新的二进制日志，新日志的编号从 000001 开始。

2. 删除指定编号之前的日志

PURGE MASTER LOGS TO ‘mysql-bin.******’

mysql-bin.******：具体的日志名称

purge master logs to 'localhost-bin.000006';

??运行上述语句，将删除000006之前的所有日志。

3. 删除指定日志之前的日志

PURGE MASTER LOGS BEFORE ‘yyyy-mm-dd hh24:mi:ss’

‘yyyy-mm-dd hh24:mi:ss’：具体的时间点

purge master logs before '2007-08-10 04:07:00';

??运行上述语句，将删除2007-08-10 04:07:00之前的所有日志。

4. 设置日志过期天数

??设置参数–expire_logs_days=#，此参数的含义是设置日志的过期天数，过了指定的天数后日志将会被自动删除，这样将将有利于减少 DBA 管理日志的工作量。

例子：
在 my.cnf 的[mysqld]中加入“expire_logs_day=3”，然后重新启动 MySQL 服务。

4、其他选项

??二进制日志由于记录了数据的变化过程，对于数据的完整性和安全性起着非常重要的作用。因此，MySQL 还提供了一些其他参数选项来进行更小粒度的管理，具体介绍如下。

• –binlog-do-db=db_name
  该选项告诉主服务器，如果当前的数据库(即 USE 选定的数据库)是 db_name，应将更新记录到二进制日志中。其他所有没有显式指定的数据库更新将被忽略，不记录在日志中。
• –binlog-ignore-db=db_name
  该选项告诉主服务器，如果当前的数据库(即 USE 选定的数据库)是 db_name，不应将更新保存到二进制日志中，其他没有显式忽略的数据库都将进行记录。如果想记录或忽略多个数据库，可以对上面两个选项分别使用多次，即对每个数据库指定相应的选项。例如，如果只想记录数据库 db1 和 db2 的日志，可以在参数文件中设置两行：
  –binlog-do-db=db1
  –binlog-do-db=db2
• –innodb-safe-binlog
  此选项经常和–sync-binlog＝N（每写 N 次日志同步磁盘）一起配合使用，使得事务在日志中的记录更加安全。
• SET SQL_LOG_BIN=0
  具有 SUPER 权限的客户端可以通过此语句禁止将自己的语句记入二进制记录。这个选项在某些环境下是有用的，但是使用时一定要小心，因为它很可能造成日志记录的不完整或者在复制环境中造成主从数据的不一致。

三、查询日志

??查询日志（General Query Log）用来记录用户的所有操作，包括启动和关闭 MySQL 服务、更新语句和查询语句等。默认情况下，通用查询日志功能是关闭的。
??在 MySQL 中，可以通过在 MySQL 配置文件添加 log 选项来开启通用查询日志。

开启查询日志：
[mysqld]
log=dir/filename
??
如果没有给定 file_name 的值，日志将写入参数 DATADIR（数据目录）指定的路径下，默认文件名是 host_name.log。

??可以通过以下命令查看通用查询日志是否开启以及日志文件存放路径，命令如下：

SHOW VARIABLES LIKE ‘%general%’;

??因为查询日志记录的格式是纯文本，因此可以直接进行读取。

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

四、慢查询日志

??慢查询日志记录了包含所有执行时间超过参数 long_query_time（单位：秒）所设置值的 SQL语句的日志。获得表锁定的时间不算作执行时间。通过慢查询日志，可以查找出哪些查询语句的执行效率低，以便进行优化。

1、启动、设置慢查询日志

??几种日志的开启方式基本都一样，都是在配置文件的 [mysqld] 组中添加日志的路径。慢日志不同在于，它有个 long_query_time 参数用来设置时间值，时间以秒为单位。如果查询时间超过了这个时间值，这个查询语句将被记录到慢查询日志。配置格式如下：

[mysqld]
log-slow-queries=dir\filename
long_query_time=n

dir：和之前一样，路径
filename：文件名（filename-slow.log）； 如果不指定文件名，默认文件名为hostname-slow.log，hostname 是 MySQL 服务器的主机名。
n：自定义的阈值时间，单位秒。

??除了上述常规的开启方法，还可以通过以下命令启动慢查询日志、设置指定时间：

SET GLOBAL slow_query_log=ON/OFF;
SET GLOBAL long_query_time=n;

2、查看慢查询日志

??慢日志的查看有两部分，一是路径和开启状态，二是设置的时间阈值。查询语法如下：

查看是否开启以及路径

SHOW VARIABLES LIKE ‘slow_query%’;

查看时间阈值

SHOW VARIABLES LIKE ‘long_query_time’;

??和错误日志、查询日志一样，慢查询日志记录的格式也是纯文本，可以被直接读取。所以通过上述语句查到文件路径，就可以自行打开查看了。

3、删除慢查询日志

??慢查询日志的删除方法与通用日志的删除方法是一样的。可以使用 mysqladmin 命令来删除。也可以使用手工方式来删除。mysqladmin 命令的语法如下：

mysqladmin -uroot -p flush-logs

??注意：查询日志和慢查询日志都是使用这个命令，使用时一定要注意，一旦执行这个命令，通用查询日志和慢查询日志都只存在新的日志文件中。如果需要备份旧的慢查询日志文件，必须先将旧的日志改名，然后重启MySQL 服务或执行 mysqladmin 命令。

??通俗的说，MySQL 慢查询日志是排查问题的 SQL 语句，以及检查当前 MySQL 性能的一个重要功能。正常情况下，一般启动该参数，虽然可能带来一些性能上的影响，但可以设置时间阈值大一点，当调优时，设置时间阈值小一点。

创作打卡挑战赛

赢取流量/现金/CSDN周边激励大奖