数据库系统管理维护阶段需要通过日志对数据库的性能进行监督、分析和改进。

日志是数据库系统的重要组成部分，记录了数据库的运行状态、数据的变更历史、错误信息及用户操作等信息。在日常管理中，数据库管理员可通过日志监控数据库的运行状态、优化数据库性能。在数据库出现问题时，可通过日志查询出错原因，并进行数据恢复。

MySQL 中有几种不同类型的日志，包括二进制日志、错误日志、通用查询日志、慢查询日志、中继日志等，功能如下。

• (1) 二进制日志：记录除查询语句以外所有的 DDL 和 DML 语句的操作，可用于数据库复制。

• (2) 错误日志：记录服务器启动、运行或停止时出现的问题，一般也会记录警告信息。

• (3) 通用查询日志：记录服务器接收到的所有操作，包括启动/关闭服务器、查询操作、更新操作等。

• (4) 慢查询日志：记录时长超过指定时间的查询，可用于优化查询。

• (5) 中继日志：记录从主服务器的二进制日志文件中复制而来的事件。

01、二进制日志

二进制日志，简称 BINLOG，对数据损坏后的恢复起着至关重要的作用，是 MySQL 中最重要的日志之一。它用二进制文件的形式记录了除查询语句以外所有的 DDL 和 DML 语句的操作，即记录对数据库对象进行的创建(CREATE)、修改(ALTER)、删除(DROP)操作和对数据表中记录的插入(INSERT)、更新(UPDATE)、删除(DELETE)等操作，但不记录 SELECT 或 SHOW 等不修改数据的操作。语句以“事件”形式存储，记录了语句的发生时间、执行时长、操作的数据等。

启用二进制日志会给服务器带来轻微的性能影响，但它能保证数据库出故障前的数据是可以恢复的。在进行数据恢复时，可以利用二进制日志，将数据恢复到指定的时间点。

另外，使用二进制日志，可以把对数据库所做的修改以“流”的方式传输到另一台服务器上，实现数据库复制功能。

1. 启用二进制日志

要启用二进制日志，必须修改数据库配置文件 my.ini，在［mysqld］组下加入以下变量声明，然后重新启动服务器。

语法说明如下。

path 是二进制日志文件的存储路径，默认位于 MySQL 安装目录下的 Data 文件夹中。

logfilename 是二进制日志的文件名，MySQL 会自动创建二进制日志文件，并将第一个二进制文件命名为 logfilename.000001，当这个文件的大小达到 max_binlog_size 设定的值(默认为 1GB)或 MySQL 重新启动时，会创建第二个二进制日志文件 logfilename.000002，以此类推。若没有指定 logfilename，则默认的格式为 hostname-bin.number，其中 hostname 为服务器的主机名。

可以用“SET @@global.max_binlog_size=10240;”命令设置全局系统变量 @@global.max_binlog_size 的值来更改 max_binlog_size 的大小。

也可以在配置文件 my.ini 的［mysqld］组下加入“max_binlog_size = 100M”这一变量声明，并重新启动服务器来设置二进制日志单个日志文件的大小。

提示/

在 MySQL 中，一个事务包含的所有操作必须记录在同一个二进制文件中，这可能会导致有些二进制文件的大小超出 max_binlog_size 设定的值。

2. 查看二进制日志

(1) 查看是否启用了二进制日志的基本语法格式如下所示。

执行结果如图 10-1 所示，log_bin 的值为 ON 表示启用。

■ 图 10-1 验证是否启用二进制日志

(2) 查看服务器上所有的二进制日志的基本语法格式如下所示。

3. 自动清除过期的二进制日志

可用以下方法设置二进制日志的到期时间，到期后系统会自动清除过期的二进制日志文件。

(1) 在配置文件 my.ini 的［mysqld］组下加入以下变量声明，并重新启动服务器来设置二进制日志的到期天数。

(2) 通过全局系统变量 expire_logs_days 设置日志的到期天数，例如：设置到期时间为 5 天，可用以下命令。

(3) 通过全局系统变量 binlog_expire_logs_seconds 设置日志的到期秒数，例如：同样想设置到期时间为 5 天，可用以下命令。

若想禁止到期自动清除二进制日志，可将这两个变量的值设置为 0。

4. 手动清除二进制日志

(1) 手动清除指定二进制日志文件之前的所有文件，基本语法格式如下所示。

(2) 手动清除所有的二进制日志文件，并重新创建新的二进制日志文件，基本语法格式如下所示。

5. 强制开启新的二进制日志文件

强制结束当前二进制日志文件，并开启新的二进制日志文件，基本语法格式如下所示。

6. 禁用当前会话的二进制日志

如果不想当前会话的 SQL 语句被记录到二进制日志中，可用 SET 命令禁用当前会话的二进制日志，基本语法格式如下所示。

语法说明如下。

• 值设置为 0 表示禁用当前会话的二进制日志。

• 值设置为 1 表示重新启用当前会话的二进制日志。

7. 查看二进制日志文件内容

不能直接打开二进制日志文件查看二进制日志，因为它是以二进制方式存储的，必须在 DOS 命令提示符窗口下使用 mysqlbinlog 命令进行查看，基本语法格式如下所示。

提示/

如果在配置文件 my.ini 中用"default-character-set=utf8mb4"语句设置了默认字符集，那么直接运行"mysqlbinlog path＼logfilename"命令，将会提示错误信息"mysqlbinlog: ［ERROR］ unknown variable 'default-character-set=UTF8MB4'.。"因为 mysqlbinlog 这个工具无法识别"default-character-set=utf8mb4"这个语句，以下两种方法可以解决这个问题。

在配置文件 my.ini 中将"default-character-set=utf8mb4"修改为"character-set-server=utf8mb4"，但是这需要重启 MySQL 服务，如果 MySQL 服务正在忙，代价会比较大。

用"mysqlbinlog --no-defaults path＼filename"命令。

8. 设置二进制日志格式

可以用 SET 命令更改系统变量 BINLOG_FORMAT 的值来改变二进制日志中的记录格式，基本语法格式如下所示。

语法说明如下。

SET @@SESSION.BINLOG_FORMAT 表示设置当前会话的 BINLOG_FORMAT 变量的值，无须断开会话重新连接。

SET @@GLOBAL.BINLOG_FORMAT 表示设置全局范围的 BINLOG_FORMAT 的值，必须断开会话重新连接，才能生效。

STATEMENT | ROW | MIXED，是二进制日志中的记录格式，包括以下 3 种选项。

◇ STATEMENT：表示在二进制日志中记录原始的 SQL 语句，优点是日志量小，缺点是执行一些不确定的函数(如 UUID()、NOW()等)可能会出现主从数据不一致问题。

◇ ROW：表示记录的不是 SQL 语句，而是表中记录的更改情况，优点是解决了 STATEMENT 格式下主从数据不一致的问题，所有数据都可以安全地复制，缺点是日志量大，会影响从库日志的复制时间，但可以通过设置参数"binlog_row_image=MINIMAL"来减少日志的生成量。

◇ MIXED：表示以 STATEMENT + ROW 的混合格式记录执行的语句。

◇ MySQL 默认采用 STATEMENT 格式进行二进制日志文件的记录，但出现以下情况会使用 ROW 格式。

◇ 使用了 UUID()、USER()、CURRENT_USER()、FOUND_ROWS()、ROW_COUNT()等不确定函数。

◇ 使用了 INSERT DELAY 语句。

◇ 使用了用户自定义函数。

◇ 使用了临时表。

设置完系统变量 BINLOG_FORMAT 的值后，可用以下命令查看当前日志格式。

【例 10-1】使用 mysqlbinlog 命令查看二进制日志。

(1) 登录 MySQL，新建一个会话窗口，手动清除所有的二进制日志文件后，将 BINLOG_FORMAT 变量的值设为 STATEMENT，并进行验证；然后对数据库的数据表执行修改操作。

(2) 用 cmd 命令进入 DOS 命令提示符窗口，切换到二进制日志文件所在的目录(Data 文件夹)，查看最新的二进制日志。

在输出的信息中可以看到刚才执行的完整的 SQL 语句，部分输出如下所示。

其中，“#at”后面的数字是二进制日志文件中事件的开始位置，即文件偏移量，下一行是语句在服务器上运行的时间戳，时间戳后面跟着 server id 表示 server id 的值、end_log_pos 表示下一个事件的开始位置。

(3) 返回 MySQL 会话窗口，将 BINLOG_FORMAT 变量的值设为 ROW，并进行验证；然后再对数据库的数据表执行修改操作。

(4) 返回 DOS 命令提示符窗口，查看最新的二进制日志。

在输出的信息中可以看到刚才执行的完整的 SQL 语句以二进制格式显示，对用户来说不可读。

通过二进制日志，可以恢复指定的时间点或位置的数据,关于数据恢复。

02、错误日志

错误日志是 MySQL 最重要的日志之一，服务器每次启动和停止的详细信息、事件调度器产生的信息以及服务器运行过程中出现的所有较为严重的警告和错误信息都会记录在其中。数据库服务器发生故障时，可以查看错误日志查找错误原因。

1. 查看错误日志

默认情况下，错误日志是开启的，错误日志文件位于 MySQL 安装目录下的 Data 文件夹中，文件名为主机名，扩展名为“.err”。可在配置文件 my.ini 的［mysqld］组下加入以下变量声明，并重新启动服务器来自定义错误日志文件的名称和存储位置。

MySQL 中的错误日志文件以文本文件形式存储，可直接用文本编辑器查看。

查询错误日志的存储路径，基本语法格式如下所示。

2. 删除错误日志

可以直接进入错误日志文件所在的目录(默认是 MySQL 安装目录下的 Data 文件夹)删除错误日志文件。如果在 MySQL 运行期间删除，MySQL 不会重新创建新的错误日志文件，MySQL 重新启动后才会自动创建。

03、通用查询日志

通用查询日志会记录服务器接收到的所有操作，包括启动和关闭服务器、查询操作、更新操作等，不管这些操作是否包含语法错误，是否返回结果，都会记录。

因此，开启通用查询日志会产生很大的系统开销，默认情况下，通用查询日志是关闭的，只有在需要进行采样分析或性能调优时才会开启。

1. 开启通用查询日志

查看通用查询日志开启状态和日志文件存储路径的基本语法格式如下所示。

执行结果中 general_log 的值为 OFF 表示关闭；general_log_file 的值为日志文件存储路径。

可以在配置文件“my.ini”的［mysqld］组下加入以下变量声明，并重新启动服务器来开启通用查询日志。

语法说明如下。

general_log 的值设置为 1 表示开启通用查询日志，为 0 表示关闭通用查询日志。

general_log_file 用于设置通用查询日志的存储位置和文件名。默认情况下，通用查询日志文件位于 MySQL 安装目录下的 Data 文件夹中，文件名为主机名，扩展名为“.log”。

如果不希望 MySQL 重新启动,也可以用 SET 命令来开启,基本语法格式

2. 查看通用查询日志

通用查询日志是以文本文件的形式存储在文件系统中的，可以使用文本编辑器直接打开进行查看。

3. 删除通用查询日志

直接进入通用查询日志文件所在的目录(默认是 MySQL 安装目录下的 Data 文件夹)删除通用查询日志文件即可。

04、慢查询日志

顾名思义，慢查询日志是用来记录时长超过指定时间的查询的。通过慢查询日志，可以找出哪些查询语句的执行时间较长、执行效率较低，以便进行优化。

1. 开启慢查询日志

默认情况下，慢查询日志是关闭的，它对服务器的性能影响不大，一般建议开启。

查看慢查询日志开启状态和日志文件存储路径的基本语法格式如下所示。

可以在配置文件 my.ini 的［mysqld］组下加入以下变量声明，并重新启动服务器来开启慢查询日志。

语法说明如下。

slow_query_log 的值设置为 1 表示开启慢查询日志，设置为 0 表示关闭慢查询日志。

long_query_time 用于指定记录阈值，可以省略，默认为 10 秒，以秒为单位，可以精确到微秒，可以用“SHOW VARIABLES LIKE '%long_query_time%';”查看该阈值。如果一个查询语句执行时间超过阈值，该查询语句将被记录到慢查询日志中。

slow_query_log_file 用于设置慢查询日志的位置和文件名。默认情况下，慢查询日志文件位于 MySQL 安装目录下的 Data 文件夹中，文件名是 hostname-slow.log，其中 hostname 是主机名。

如果不希望 MySQL 重新启动，也可以用 SET 命令来设置，基本语法格式如下所示。

2. 查看慢查询日志

慢查询日志是以文本文件的形式存储在文件系统中的，可以使用文本编辑器直接打开进行查看。

3. 删除慢查询日志

直接进入慢查询日志文件所在的目录(默认是 MySQL 安装目录下的 Data 文件夹)删除慢查询日志文件即可。