mysql报警级别_MySQL 5.7定义日志级别新参数（log_error_verbosity）

log_warnings

在介绍这个参数前，我们先聊聊参数 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

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

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

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

Note

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

log_error_verbosity

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

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

用于处理错误日志的事件的详细程度，由错误日志过滤器组件 log_filter_internal 进行过滤。如果未启用 log_filter_internal，则 log_error_verbosity 不起作用。下表显示了允许的详细度值。

Desired Log Filtering

log_error_verbosity Value

Error messages

1

Error and warning messages

2

Error, warning, and note messages

3

从上面表格可以看出，从 MySQL 5.7->MySQL 8.0.3 此参数的默认值为 3，大于 MySQL 8.0.3 开始默认值调整为 2 了。所以，在 MySQL 5.7 版本中，你可能在错误日志信息中看到一大堆Note类型的日志。具体参考这篇文章“MySQL 5.7错误日志中常见的几种Note级别日志解释”。

log_error_verbosity 与 innodb_print_all_deadlocks

在 MySQL 中，发生死锁的时候，我们可以选择把死锁日志输出到 error log 中，通过开启 innodb_print_all_deadlocks 参数控制。这里有一个小问题，开启 innodb_print_all_deadlocks 后，输出到 error log 中的日志级别为 Note。也就是说，当 log_error_verbosity=3 的时候，Note 级别日志才会输出到 error log 中。具体日志信息格式如下：

2020-11-20T02:55:59.599990Z 1693135 [Note] InnoDB: Transactions deadlock detected, dumping detailed information.

2020-11-20T02:55:59.600008Z 1693135 [Note] InnoDB:

*** (1) TRANSACTION:

TRANSACTION 24688, ACTIVE 16 sec starting index read

mysql tables in use 1, locked 1

LOCK WAIT 3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1

MySQL thread id 1693133, OS thread handle 139675801577216, query id 153219328 localhost root updating

update t1 set name='c' where id=2

2020-11-20T02:55:59.600038Z 1693135 [Note] InnoDB: *** (1) WAITING FOR THIS LOCK TO BE GRANTED:

RECORD LOCKS space id 103 page no 3 n bits 80 index PRIMARY of table `sbtest`.`t1` trx id 24688 lock_mode X locks rec but not gap waiting

Record lock, heap no 3 PHYSICAL RECORD: n_fields 5; compact format; info bits 0

0: len 4; hex 80000002; asc ;;

1: len 6; hex 000000006071; asc `q;;

2: len 7; hex 560000014c09b8; asc V L ;;

3: len 1; hex 63; asc c;;

4: len 1; hex 62; asc b;;

2020-11-20T02:55:59.600173Z 1693135 [Note] InnoDB: *** (2) TRANSACTION:

TRANSACTION 24689, ACTIVE 6 sec starting index read

mysql tables in use 1, locked 1

3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1

MySQL thread id 1693135, OS thread handle 139675802642176, query id 153219329 localhost root updating

update t1 set name='c' where id=1

2020-11-20T02:55:59.600195Z 1693135 [Note] InnoDB: *** (2) HOLDS THE LOCK(S):

RECORD LOCKS space id 103 page no 3 n bits 80 index PRIMARY of table `sbtest`.`t1` trx id 24689 lock_mode X locks rec but not gap

Record lock, heap no 3 PHYSICAL RECORD: n_fields 5; compact format; info bits 0

0: len 4; hex 80000002; asc ;;

1: len 6; hex 000000006071; asc `q;;

2: len 7; hex 560000014c09b8; asc V L ;;

3: len 1; hex 63; asc c;;

4: len 1; hex 62; asc b;;

2020-11-20T02:55:59.600302Z 1693135 [Note] InnoDB: *** (2) WAITING FOR THIS LOCK TO BE GRANTED:

RECORD LOCKS space id 103 page no 3 n bits 80 index PRIMARY of table `sbtest`.`t1` trx id 24689 lock_mode X locks rec but not gap waiting

Record lock, heap no 8 PHYSICAL RECORD: n_fields 5; compact format; info bits 0

0: len 4; hex 80000001; asc ;;

1: len 6; hex 000000006070; asc `p;;

2: len 7; hex 550000019504ff; asc U ;;

3: len 1; hex 63; asc c;;

4: len 1; hex 61; asc a;;

2020-11-20T02:55:59.600410Z 1693135 [Note] InnoDB: *** WE ROLL BACK TRANSACTION (2)

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

47

2020-11-20T02:55:59.599990Z1693135[Note]InnoDB:Transactionsdeadlockdetected,dumpingdetailedinformation.

2020-11-20T02:55:59.600008Z1693135[Note]InnoDB:

***(1)TRANSACTION:

TRANSACTION24688,ACTIVE16secstartingindexread

mysqltablesinuse1,locked1

LOCKWAIT3lockstruct(s),heapsize1136,2rowlock(s),undologentries1

MySQLthreadid1693133,OSthreadhandle139675801577216,queryid153219328localhostrootupdating

updatet1setname='c'whereid=2

2020-11-20T02:55:59.600038Z1693135[Note]InnoDB:***(1)WAITINGFORTHISLOCKTOBEGRANTED:

RECORDLOCKSspaceid103pageno3nbits80indexPRIMARYoftable`sbtest`.`t1`trxid24688lock_modeXlocksrecbutnotgapwaiting

Recordlock,heapno3PHYSICALRECORD:n_fields5;compactformat;infobits0

0:len4;hex80000002;asc;;

1:len6;hex000000006071;asc`q;;

2:len7;hex560000014c09b8;ascVL;;

3:len1;hex63;ascc;;

4:len1;hex62;ascb;;

2020-11-20T02:55:59.600173Z1693135[Note]InnoDB:***(2)TRANSACTION:

TRANSACTION24689,ACTIVE6secstartingindexread

mysqltablesinuse1,locked1

3lockstruct(s),heapsize1136,2rowlock(s),undologentries1

MySQLthreadid1693135,OSthreadhandle139675802642176,queryid153219329localhostrootupdating

updatet1setname='c'whereid=1

2020-11-20T02:55:59.600195Z1693135[Note]InnoDB:***(2)HOLDSTHELOCK(S):

RECORDLOCKSspaceid103pageno3nbits80indexPRIMARYoftable`sbtest`.`t1`trxid24689lock_modeXlocksrecbutnotgap

Recordlock,heapno3PHYSICALRECORD:n_fields5;compactformat;infobits0

0:len4;hex80000002;asc;;

1:len6;hex000000006071;asc`q;;

2:len7;hex560000014c09b8;ascVL;;

3:len1;hex63;ascc;;

4:len1;hex62;ascb;;

2020-11-20T02:55:59.600302Z1693135[Note]InnoDB:***(2)WAITINGFORTHISLOCKTOBEGRANTED:

RECORDLOCKSspaceid103pageno3nbits80indexPRIMARYoftable`sbtest`.`t1`trxid24689lock_modeXlocksrecbutnotgapwaiting

Recordlock,heapno8PHYSICALRECORD:n_fields5;compactformat;infobits0

0:len4;hex80000001;asc;;

1:len6;hex000000006070;asc`p;;

2:len7;hex550000019504ff;ascU;;

3:len1;hex63;ascc;;

4:len1;hex61;asca;;

2020-11-20T02:55:59.600410Z1693135[Note]InnoDB:***WEROLLBACKTRANSACTION(2)

是不是意味着 log_error_verbosity<3 时，就算 innodb_print_all_deadlocks=ON，死锁日志也不会记录到 error log 中呢？

其实两者还是有点差别，当 log_error_verbosity<3 & innodb_print_all_deadlocks=ON 时，还是会记录死锁日志到 error log 中，不过日志简化了一些，格式如下：

TRANSACTION 24700, ACTIVE 12 sec starting index read

mysql tables in use 1, locked 1

LOCK WAIT 3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1

MySQL thread id 1693181, OS thread handle 139675801310976, query id 153223910 localhost root updating

update t1 set name='c' where id=2

RECORD LOCKS space id 103 page no 3 n bits 80 index PRIMARY of table `sbtest`.`t1` trx id 24700 lock_mode X locks rec but not gap waiting

Record lock, heap no 3 PHYSICAL RECORD: n_fields 5; compact format; info bits 0

0: len 4; hex 80000002; asc ;;

1: len 6; hex 00000000607d; asc `};;

2: len 7; hex 5c000005640f57; asc \ d W;;

3: len 1; hex 63; asc c;;

4: len 1; hex 62; asc b;;

TRANSACTION 24701, ACTIVE 5 sec starting index read

mysql tables in use 1, locked 1

3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1

MySQL thread id 1693182, OS thread handle 139675801843456, query id 153223911 localhost root updating

update t1 set name='c' where id=1

RECORD LOCKS space id 103 page no 3 n bits 80 index PRIMARY of table `sbtest`.`t1` trx id 24701 lock_mode X locks rec but not gap

Record lock, heap no 3 PHYSICAL RECORD: n_fields 5; compact format; info bits 0

0: len 4; hex 80000002; asc ;;

1: len 6; hex 00000000607d; asc `};;

2: len 7; hex 5c000005640f57; asc \ d W;;

3: len 1; hex 63; asc c;;

4: len 1; hex 62; asc b;;

RECORD LOCKS space id 103 page no 3 n bits 80 index PRIMARY of table `sbtest`.`t1` trx id 24701 lock_mode X locks rec but not gap waiting

Record lock, heap no 8 PHYSICAL RECORD: n_fields 5; compact format; info bits 0

0: len 4; hex 80000001; asc ;;

1: len 6; hex 00000000607c; asc `|;;

2: len 7; hex 5b00000562080c; asc [ b ;;

3: len 1; hex 63; asc c;;

4: len 1; hex 61; asc a;;

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

TRANSACTION24700,ACTIVE12secstartingindexread

mysqltablesinuse1,locked1

LOCKWAIT3lockstruct(s),heapsize1136,2rowlock(s),undologentries1

MySQLthreadid1693181,OSthreadhandle139675801310976,queryid153223910localhostrootupdating

updatet1setname='c'whereid=2

RECORDLOCKSspaceid103pageno3nbits80indexPRIMARYoftable`sbtest`.`t1`trxid24700lock_modeXlocksrecbutnotgapwaiting

Recordlock,heapno3PHYSICALRECORD:n_fields5;compactformat;infobits0

0:len4;hex80000002;asc;;

1:len6;hex00000000607d;asc`};;

2:len7;hex5c000005640f57;asc\dW;;

3:len1;hex63;ascc;;

4:len1;hex62;ascb;;

TRANSACTION24701,ACTIVE5secstartingindexread

mysqltablesinuse1,locked1

3lockstruct(s),heapsize1136,2rowlock(s),undologentries1

MySQLthreadid1693182,OSthreadhandle139675801843456,queryid153223911localhostrootupdating

updatet1setname='c'whereid=1

RECORDLOCKSspaceid103pageno3nbits80indexPRIMARYoftable`sbtest`.`t1`trxid24701lock_modeXlocksrecbutnotgap

Recordlock,heapno3PHYSICALRECORD:n_fields5;compactformat;infobits0

0:len4;hex80000002;asc;;

1:len6;hex00000000607d;asc`};;

2:len7;hex5c000005640f57;asc\dW;;

3:len1;hex63;ascc;;

4:len1;hex62;ascb;;

RECORDLOCKSspaceid103pageno3nbits80indexPRIMARYoftable`sbtest`.`t1`trxid24701lock_modeXlocksrecbutnotgapwaiting

Recordlock,heapno8PHYSICALRECORD:n_fields5;compactformat;infobits0

0:len4;hex80000001;asc;;

1:len6;hex00000000607c;asc`|;;

2:len7;hex5b00000562080c;asc[b;;

3:len1;hex63;ascc;;

4:len1;hex61;asca;;

可以看到具体的 Note 日志条目确实没有记录了，只有一些具体的死锁信息，友好度差了一些。

结论，两个参数并没有必然关系，开启了 innodb_print_all_deadlocks 参数必然会记录死锁日志，具体的死锁日志额外的 Note 信息靠 log_error_verbosity 参数来控制。

如果您觉得本站对你有帮助，那么可以支付宝扫码捐助以帮助本站更好地发展，在此谢过。