数据库原理及MySQL应用 | 日志管理

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

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

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文件夹)删除慢查询日志文件即可。