代码改变世界

MySQL新参数log_error_verbosity

2018-04-30 11:49 潇湘隐者 阅读(...) 评论(...) 编辑 收藏

 

 

在介绍这个参数前,我们先聊聊参数log_warnings。我们知道MySQL中,其中log_error定义是否启用错误日志的功能和错误日志的存储位置,log_warnings定义是否将告警信息(warning messages)也写入错误日志。此选项默认启用,具体来说:

 

 

log_warnings 为0, 表示不记录告警信息。

log_warnings 为1, 表示告警信息写入错误日志。

log_warnings 大于1, 表示各类告警信息,例如有关网络故障的信息和重新连接信息写入错误日志。

 

 

 

注意,此参数在不同版本略有差别,在MySQL 5.6中,log_warnings的默认值为1,如下所示:

 

 

Property

Value

Command-Line Format

--log-warnings[=#]

System Variable

log_warnings

Scope (>= 5.6.4)

Global

Scope (<= 5.6.3)

Global, Session

Dynamic

Yes

Type (64-bit platforms)

integer

Type (32-bit platforms)

integer

Default Value (64-bit platforms)

1

Default Value (32-bit platforms)

1

Minimum Value (64-bit platforms)

0

Minimum Value (32-bit platforms)

0

Maximum Value (64-bit platforms)

18446744073709551615

Maximum Value (32-bit platforms)

4294967295

 

 

 

在MySQL 5.7中,有些版本默认值为2,有些版本默认值为1, 具体参考官方文档信息,如下所示:

 

Property

Value

Command-Line Format

--log-warnings[=#]

Deprecated

5.7.2

System Variable

log_warnings

Scope

Global

Dynamic

Yes

Type (64-bit platforms)

integer

Type (32-bit platforms)

integer

Default Value (64-bit platforms, >= 5.7.2)

2

Default Value (64-bit platforms, <= 5.7.1)

1

Default Value (32-bit platforms, >= 5.7.2)

2

Default Value (32-bit platforms, <= 5.7.1)

1

Minimum Value (64-bit platforms)

0

Minimum Value (32-bit platforms)

0

Maximum Value (64-bit platforms)

18446744073709551615

Maximum Value (32-bit platforms)

4294967295

 

 

Print out warnings such as Aborted connection... to the error log. This option is enabled (1) by default. To disable it, use --log-warnings=0. Specifying the option without a level value increments the current value by 1. Enabling this option by setting it greater than 0 is recommended, for example, if you use replication (you get more information about what is happening, such as messages about network failures and reconnections). If the value is greater than 1, aborted connections are written to the error log, and access-denied errors for new connection attempts are written. See Section B.5.2.11, “Communication Errors and Aborted Connections”.

 

If a slave server was started with --log-warnings enabled, the slave prints messages to the error log to provide information about its status, such as the binary log and relay log coordinates where it starts its job, when it is switching to another relay log, when it reconnects after a disconnect, and so forth. The server logs messages about statements that are unsafe for statement-based logging if --log-warnings is greater than 0.

 

 

 

将告警信息,例如连接中断等告警信息输出到错误日志。该选项默认启用(默认值为1)。要禁用它,请使用--log-warnings = 0选项。指定没有级别值的选项时,将当前值递增1. 推荐将这个值设置为大于0启用告警日志信息写入错误日志。举个例子,如果你正在使用复制(你将会获取正在发生的事情的更多详细信息,例如有关网络故障的信息和重新连接信息)。如果该值大于1,连接中断将写入错误日志,新的连接尝试访问的拒绝访问信息。参见第B.5.2.11节通信错误和中止连接

 

如果从服务器(slave server)启动时启用了--log-warnings,则从设备将消息输出到错误日志中以提供有关其状态的信息,例如二进制日志和中继日志坐标,它在开始作业时切换到另一个中继日志,断开连接后重新连接等等。如果--log-warnings大于0,服务器将记录关于对基于语句的日志不安全的语句的消息。

 

 

注意:从MySQL 5.7.2开始,首选log_error_verbosity系统变量,而不是使用--log-warnings选项或log_warnings系统变量,这个参数从MySQL 8.0.3开始被移除了:

 

This system variable was removed in MySQL 8.0.3. Use the log_error_verbosity system variable instead.

 

 

而新参数log_error_verbosity更简单,它有三个可选值, 分别对应:1 错误信息;2  错误信息和告警信息; 3:错误信息、告警信息和通知信息。 具体参考官方文档,下面部分截取官方文档。

 

 

log_error_verbosity

 

Property

Value

Command-Line Format

--log-error-verbosity=#

System Variable

log_error_verbosity

Scope

Global

Dynamic

Yes

SET_VAR Hint Applies

No

Type

integer

Default Value (>= 8.0.4)

2

Default Value (<= 8.0.3)

3

Minimum Value

1

Maximum Value

3

 

 

The verbosity for handling events intended for the error log, as filtered by the log_filter_internal error log filter component. log_error_verbosity has no effect if log_filter_internal is not enabled.

The following table shows the permitted verbosity values.

Desired Log Filtering

log_error_verbosity Value

Error messages

1

Error and warning messages

2

Error, warning, and note messages

3

 

Selected important system messages about non-error situations, such as startup and shutdown messages, are printed to the error log when the variable value is 1.

For additional information, see Section 5.4.2.5, “Error Log Filtering”, and Section 5.5.1, “Error Log Components”.

 

 

参考资料:

 

https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_log_warnings

https://dev.mysql.com/doc/refman/5.7/en/server-options.html#option_mysqld_log-warnings