文章目录
- 错误日志
- 通用查询日志
- 慢查询日志
- 二进制日志
- 中继日志
- DDL 日志
大家好！我是只谈技术不剪发的 Tony 老师。今天我们来谈谈 MySQL 服务器中各种日志文件的作用以及相关配置选项。
MySQL 提供了一套完整的服务器日志体系，包含了多种日志类型，可以帮助我们查找、解决系统问题和优化数据库的性能。
日志类型 | 记录信息 |
错误日志 | 启动、运行、停止 mysqld 时出现的问题 |
通用查询日志 | 建立的客户端连接和客户端提交的语句 |
慢查询日志 | 执行时间超过 long_query_time（默认为 10 秒）的查询 |
二进制日志 | 修改数据的语句，也用于复制 |
中继日志 | 从复制主节点接收到的数据变更 |
DDL日志 | 元数据日志，DDL 语句执行的元数据操作 |
默认情况下，服务器的日志文件都位于数据目录（datadir）中。
错误日志
MySQL 错误日志（error log）记录了 mysqld 启动和停止的相关信息，同时还记录了服务器在启动、停止以及运行期间发生的诊断消息，例如错误、警告和通知等。例如，当 mysqld 发现某个表需要执行自动检查或修复时，它会向错误日志中写入一条消息。
默认安装时，错误日志通常在配置文件（my.cnf 或者 my.ini）中 mysqld 部分的 log-error 配置项进行设置；利用 MySQL 系统变量 log_error 可以查看错误日志的文件名：
mysql> show variables like 'log_error';
+---------------+---------------------+
| Variable_name | Value |
+---------------+---------------------+
| log_error | /var/log/mysqld.log |
+---------------+---------------------+
1 row in set (0.01 sec)如果查询结果为 stderr，表示标准错误输出，也就是终端或控制台窗口。
从 MySQL 8.0 开始，错误日志采用了 MySQL 组件体系结构，由执行日志过滤和写入的多个组件构成，系统变量 log_error_services 配置了用于生成错误日志的组件。当 log_error 系统变量指定了默认的错误日志文件之后，日志写入器组件可能基于该值设置自己的输出文件，或者使用单独的输出文件：
- 如果 log_error 设置为 stderr，默认的错误日志将会输出到控制台；日志写入器组件 log_sink_internal、log_sink_json 以及 log_sink_test 都会输出到控制台，但是 log_sink_syseventlog 的输出目标为系统日志文件，而不是 log_error。
- 如果 log_error 设置为系统文件，默认的错误日志将会输出到该文件；日志写入器组件 log_sink_internal 和 log_sink_test 都会输出到该文件；log_error_services 中配置的多个 log_sink_json 将会使用文件名加上带编号的 .NN.json 文件：file_name.00.json、file_name.01.json 等；log_sink_syseventlog 的输出目标为系统日志文件，而不是 log_error。
📝MySQL 错误日志是日常监控和服务器出现故障时首先应该查看的日志文件。
通用查询日志
MySQL 通用查询日志（general query log）记录了 mysqld 执行的各种操作，当客户端连接或断开连接、每次提交 SQL 语句时都会记录相关的信息。当我们怀疑客户端存在问题或者想要查看客户端发送给服务器的确切请求时，通用查询日志将会非常有用。
默认情况下，通用查询日志没有启用；我们可以通过系统变量 general_log 查看和控制通用查询日志的设置：
mysql> show variables like 'general_log%';
+------------------+----------------------------+
| Variable_name | Value |
+------------------+----------------------------+
| general_log | OFF |
| general_log_file | /var/lib/mysql/sqlhost.log |
+------------------+----------------------------+
2 rows in set (0.01 sec)查询结果中的 general_log_file 是通用查询日志的文件名，默认为数据目录下的 host_name.log。
通用查询日志和慢查询日志既可以写入日志文件，也可以写入系统数据库中的日志表中；通过系统变量 log_output 可以查看和设置：
mysql> show variables like 'log_output';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_output | FILE |
+---------------+-------+
1 row in set (0.01 sec)FILE 表示输出到日志文件，TABLE 表示输出到日志表（分别为 mysql.general_log 和 mysql.slow_log）中，也可以设置为 TABLE, FILE；NONE 表示禁用查询日志和慢查询日志。
另外，我们还可以通过系统变量 sql_log_off 设置当前会话的通用查询日志状态，当前前提是已经启用了系统的通用查询日志功能：
mysql> show variables like 'sql_log_off';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| sql_log_off | OFF |
+---------------+-------+
1 row in set (0.01 sec)OFF 表示启用日志功能；如果想要关闭当前会话的通用查询日志，可以将其设置为 ON。
📝MySQL 通用查询日志和慢查询日志支持运行时的动态设置，包括启用、禁用、修改日志文件名和输出目标等，具体可以参考官方文档。
慢查询日志
MySQL 慢查询日志（slow query log）记录了执行时间超过指定阈值的 SQL 语句，可以用于监控需要执行优化的查询语句。如果启用了慢查询日志，任何执行时间超过 long_query_time 并且执行次数达到 min_examined_row_limit 次的查询语句都会被记录到慢查询日志中：
mysql> show variables like 'long_query_time';
+-----------------+-----------+
| Variable_name | Value |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
1 row in set (0.00 sec)
mysql> show variables like 'min_examined_row_limit';
+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| min_examined_row_limit | 0 |
+------------------------+-------+
1 row in set (0.01 sec)默认情况下，执行时间超过 10 秒的任何查询都会被看作慢查询。
慢查询日志的启用和禁用由系统变量 slow_query_log 进行控制，默认禁用该功能：
mysql> show variables like 'slow_query_log%';
+---------------------+---------------------------------+
| Variable_name | Value |
+---------------------+---------------------------------+
| slow_query_log | OFF |
| slow_query_log_file | /var/lib/mysql/sqlhost-slow.log |
+---------------------+---------------------------------+
2 rows in set (0.01 sec)查询结果中的 slow_query_log_file 是慢查询日志的文件名，默认为数据目录下的 host_name-slow.log。
与通用查询日志类似，慢查询日志既可以写入日志文件，也可以写入系统数据库中的日志表（mysql.slow_log）中；通过系统变量 log_output 进行设置，参考上文中的通用查询日志介绍。
默认情况下，管理语句不会被记录到慢查询日志，包括 ALTER TABLE、ANALYZE TABLE、CHECK TABLE、CREATE INDEX、DROP INDEX、OPTIMIZE TABLE 以及 REPAIR TABLE。如果想要记录这些操作，可以将 log_slow_admin_statements 设置为 ON：
mysql> show variables like 'log_slow_admin_statements';
+---------------------------+-------+
| Variable_name | Value |
+---------------------------+-------+
| log_slow_admin_statements | OFF |
+---------------------------+-------+
1 row in set (0.00 sec)另外，不使用索引进行查找的查询也不会被记录到慢查询日志。如果想要记录这些查询，可以将 log_queries_not_using_indexes 设置为 ON：
mysql> show variables like 'log_queries_not_using_indexes';
+-------------------------------+-------+
| Variable_name | Value |
+-------------------------------+-------+
| log_queries_not_using_indexes | OFF |
+-------------------------------+-------+
1 row in set (0.00 sec)与此相关的另一个系统变量是 log_throttle_queries_not_using_indexes；如果启用了 log_queries_not_using_indexes，每分钟内执行次数超过 log_throttle_queries_not_using_indexes 的查询才会在慢查询日志中记录一条汇总信息；该参数的默认值为 0。
📝MySQL 通用查询日志和慢查询日志支持运行时的动态设置，包括启用、禁用、修改日志文件名和输出目标等，具体可以参考官方文档。
对于慢查询日志的分析，推荐使用 mysqldumpslow 或者 pt-query-digest 工具。
二进制日志
MySQL 二进制日志（binary log）存储了描述数据库变更的事件信息，例如创建表或者修改数据；即使该语句实际上可能并没有修改任何数据，例如没有匹配到任何数据行的 DELETE 语句。不过它不会记录查询语句，例如 SELECT 或者 SHOW，因为它们不会修改数据。如果想要记录所有的语句（例如为了找出存在问题的查询）可以使用上文中的通用查询日志。
二进制日志同时还包含了更新语句所消耗的时间。二进制日志为我们提供了两个重要的功能：
- 用于复制。主节点上的二进制日志存储了数据的变更记录并发送给从节点，然后在从节点上回放日志实现相同的操作。
- 用于数据恢复。通过备份文件执行还原之后，利用二进制日志可以将数据库恢复到指定时间点或者最新状态。
启用二进制日志会给性能稍微带来一些影响，但是它对复制的支持和数据的还原功能带来的优势更加明显。二进制日志不会受到系统故障的影响，因为它只记录和回读完整的事件和事务。
接下来我们介绍与二进制相关的几个主要系统变量，完整的参数可以参考官方文档：
mysql> show variables like '%log_bin%';
+---------------------------------+-----------------------------+
| Variable_name | Value |
+---------------------------------+-----------------------------+
| log_bin | ON |
| log_bin_basename | /var/lib/mysql/binlog |
| log_bin_index | /var/lib/mysql/binlog.index |
| log_bin_trust_function_creators | OFF |
| log_bin_use_v1_row_events | OFF |
| sql_log_bin | ON |
+---------------------------------+-----------------------------+
6 rows in set (0.01 sec)在 MySQL 5.7 以及之前的版本中，默认没有启用二进制日志；如果想要启用，可以指定 --log-bin 选项。
从 MySQL 8.0 开始，无论是否指定 --log-bin 选项，默认开启二进制日志（系统变量 log_bin 设置为 ON）。唯一的例外是手动执行带 --initialize 或者 --initialize-insecure 选项的 mysqld 命令初始化数据目录，此时默认不会启用二进制日志；当然也可以通过 --log-bin 选项启用二进制日志。
📝如果想要禁用二进制日志，可以指定 --skip-log-bin 或者 --disable-log-bin 启动选项。
系统变量 log_bin_basename 记录了二进制日志文件的路径和文件名，可以使用 --log-bin 选项进行设置。对于 MySQL 8.0，如果没有指定 --log-bin，默认的文件名为 binlog。对于 MySQL 5.7，默认的文件名为 host_name-bin。
[root@sqlhost ~]# ls /var/lib/mysql/binlog.* -1
/var/lib/mysql/binlog.000059
/var/lib/mysql/binlog.000060
/var/lib/mysql/binlog.000061
/var/lib/mysql/binlog.000062
/var/lib/mysql/binlog.000063
/var/lib/mysql/binlog.000064
/var/lib/mysql/binlog.000065
/var/lib/mysql/binlog.000066
/var/lib/mysql/binlog.000067
/var/lib/mysql/binlog.index从文件系统可以看出，MySQL 在创建二进制日志文件时增加了一个数字扩展名。每次创建一个新的日志文件时，扩展名中的数字编号都会加 1。服务器会在以下情况创建一个新的日志文件：
- 服务器进程启动或者重启时；
- 服务器刷新日志；
- 当前日志文件的大小到达 max_binlog_size 参数的限制。
为了追踪已经使用的二进制日志文件，MySQL 还创建了一个二进制日志索引文件。默认的索引文件和二进制日志文件名相同，后缀为“.index”。索引文件名可以通过 --log-bin-index 选项进行设置，但是千万不要在服务器运行时修改索引文件的内容。
如果客户端拥有设置 RESTRICTED SESSION 系统变量的权限，可以通过 SET sql_log_bin=OFF 语句禁用当前会话的二进制日志功能。系统变量 sql_log_bin 显示了当前的设置，默认为 ON。
日志文件中的事件格式取决于二进制日志的格式，可以通过 binlog_format 查看和设置 ：
- 基于语句的日志格式（statement-based logging）。以 SQL 语句为基础记录数据库的变更，MySQL 中的复制功能最初就是基于语句从主节点到从节点的传播。这种方式需要记录的日志量少，但是对于不确定性的函数（例如 now()）无法保证主从节点的一致性。通过 --binlog-format=STATEMENT 启动选项可以设置为该模式。
- 基于语句的日志格式（row-based logging），默认设置。日志中记录了每一行数据库的修改信息；如果一个 UPDATE 语句修改了 100 条记录，需要记录 100 条修改信息，所以可能产生大量的日志。通过 --binlog-format=ROW 启动选项可以设置为该模式。
- 混合日志格式（mixed logging）。默认使用基于语句的日志格式，对于不确定性的语句切换为基于行的日志格式。通过 --binlog-format=MIXED 启动选项可以设置为该模式。
关于二进制日志格式的详细介绍，可以参考 MySQL 官方文档。
MySQL 提供了一个实用工具 mysqlbinlog ，可以用于读取二进制日志文件中的内容。mysqlbinlog 可以通过重新执行日志文件中的语句实现恢复操作：
shell> mysqlbinlog log_file | mysql -h server_name默认情况下，每次提交事务都会将二进制日志同步到磁盘；该行为由系统参数 sync_binlog 控制，默认值为 1；此时不会丢失已经提交的事务，但是会对性能产生一定的影响。如果禁用 sync_binlog（设置为 0），操作系统或者服务器故障可能导致日志中丢失一些已经提交的语句；将 sync_binlog 设置为 N 意味着 N 次事务提交同步一次磁盘，这是一种性能和数据丢失风险的折衷。
对于 InnoDB 存储引擎，为了实现事务的持久性和故障恢复等功能，还使用了另外两个事务日志：重做日志（redo log）和回滚日志（undo log）。
📝MySQL 8.0.20 提供了二进制日志事务压缩功能，可以节省日志的存储空间，包括从节点中继日志以及日志备份的存储空间；而且可以节省主从节点之间发送日志时的网络带宽。
中继日志
MySQL 中继日志（relay log）只存在于主从复制结构中的从节点上，用于保存主节点传输过来的数据变更事件，然后将这些事件应用于从节点。
中继日志与二进制日志类似，也是由一组带编号的文件组成；同时还包含了一个索引文件，记录了所有已经使用过的中继日志文件。中继日志文件默认存放在数据目录中。
mysql> show variables like 'relay_log%';
+---------------------------+----------------------------------------+
| Variable_name | Value |
+---------------------------+----------------------------------------+
| relay_log | sqlhost-relay-bin |
| relay_log_basename | /var/lib/mysql/sqlhost-relay-bin |
| relay_log_index | /var/lib/mysql/sqlhost-relay-bin.index |
| relay_log_info_file | relay-log.info |
| relay_log_info_repository | TABLE |
| relay_log_purge | ON |
| relay_log_recovery | OFF |
| relay_log_space_limit | 0 |
+---------------------------+----------------------------------------+
8 rows in set (0.01 sec)其中，relay_log 和 relay_log_index 分别对应为中继日志的文件名和索引文件名。
中继日志文件的格式和二进制日志文件的格式相同，因此也可以使用 mysqlbinlog 工具进行读取。
📝关于主从复制中的中继日志信息，可以参考官方文档。
DDL 日志
MySQL DDL 日志，也称为元数据日志（metadata log）记录了数据定义语言执行的元数据操作，例如 DROP TABLE 或者 ALTER TABLE。
如果元数据操作的过程中发生故障，MySQL 使用该日志进行恢复。当我们执行 DROP TABLE t1, t2 命令时，需要确保 t1 和 t2 一起被删除 ，而且每个表都被完整地删除。另一个示例是 ALTER TABLE t3 DROP PARTITION p2 语句，必须确保分区被完整删除并且分区的定义从t3 的分区列表中删除。
对于 MySQL 5.7 以及之前的版本，元数据操作的记录日志位于数据目录下的 ddl_log.log 文件。该文件是一个二进制文件，不支持手动修改，也没有相关的配置选项和变量。
对于 MySQL 8.0，DDL 日志存储在 mysql.innodb_ddl_log 数据字典中。这是一个隐藏的数据字典表，不支持直接查询；可以通过设置系统变量 innodb_print_ddl_logs （默认为 OFF）将 DDL 日志打印到标准错误输出 strderr。
