MySQL 8.0 重新定義了錯誤日誌輸出和過濾，改善了原來臃腫並且可讀性很差的錯誤日誌。比如增加了 JSON 輸出，在原來的日誌後面以序號以及 JSON 字尾的方式展示。比如我機器上的 MySQL 以 JSON 儲存的錯誤日誌 mysqld.log.00.json：[root@centos-ytt80 mysql80]# jq . mysqld.log.00.json{ "log_type": 1, "prio": 1, "err_code": 12592, "subsystem": "InnoDB", "msg": "Operating system error number 2 in a file operation.", "time": "2019-09-03T08:16:12.111808Z", "thread": 8, "err_symbol": "ER_IB_MSG_767", "SQL_state": "HY000", "label": "Error"}{ "log_type": 1, "prio": 1, "err_code": 12593, "subsystem": "InnoDB", "msg": "The error means the system cannot find the path specified.", "time": "2019-09-03T08:16:12.111915Z", "thread": 8, "err_symbol": "ER_IB_MSG_768", "SQL_state": "HY000", "label": "Error"}{ "log_type": 1, "prio": 1, "err_code": 12216, "subsystem": "InnoDB", "msg": "Cannot open datafile for read-only: "./ytt2/a.ibd" OS error: 71", "time": "2019-09-03T08:16:12.111933Z", "thread": 8, "err_symbol": "ER_IB_MSG_391", "SQL_state": "HY000", "label": "Error"}以 JSON 輸出錯誤日誌後可讀性和可操作性增強了許多。這裡可以用 Linux 命令 jq 或者把這個字串 COPY 到其他解析 JSON 的工具方便處理。只想非常快速的拿出錯誤資訊，忽略其他資訊。[root@centos-ytt80 mysql80]# jq ".msg" mysqld.log.00.json"Operating system error number 2 in a file operation.""The error means the system cannot find the path specified.""Cannot open datafile for read-only: "./ytt2/a.ibd" OS error: 71""Cannot calculate statistics for table `ytt2`.`a` because the .ibd file is missing. Please refer to http://dev.mysql.com/doc/refman/8.0/en/innodb-troubleshooting.html for how to resolve the issue.""Cannot calculate statistics for table `ytt2`.`a` because the .ibd file is missing. Please refer to http://dev.mysql.com/doc/refman/8.0/en/innodb-troubleshooting.html for how to resolve the issue."使用 JSON 輸出的前提是安裝 JSON 輸出部件。
INSTALL COMPONENT "file://component_log_sink_json";完了在設定變數 SET GLOBAL log_error_services = "log_filter_internal; log_sink_json";格式為：過濾規則；日誌輸出；[過濾規則]日誌輸出；檢視安裝好的部件mysql> select * from mysql.component;+--------------+--------------------+---------------------------------------+| component_id | component_group_id | component_urn |+--------------+--------------------+---------------------------------------+| 2 | 1 | file://component_log_sink_json |+--------------+--------------------+---------------------------------------+3 rows in set (0.00 sec)現在設定 JSON 輸出，輸出到系統日誌的同時輸出到 JSON 格式日誌。mysql> SET persist log_error_services = "log_filter_internal; log_sink_internal; log_sink_json";Query OK, 0 rows affected (0.00 sec)來測試一把。我之前已經把表 a 物理檔案刪掉了。mysql> select * from a;ERROR 1812 (HY000): Tablespace is missing for table `ytt2`.`a`.現在錯誤日誌裡有 5 條記錄。[root@centos-ytt80 mysql80]# tailf mysqld.log2019-09-03T08:16:12.111808Z 8 [ERROR] [MY-012592] [InnoDB] Operating system error number 2 in a file operation.2019-09-03T08:16:12.111915Z 8 [ERROR] [MY-012593] [InnoDB] The error means the system cannot find the path specified.2019-09-03T08:16:12.111933Z 8 [ERROR] [MY-012216] [InnoDB] Cannot open datafile for read-only: "./ytt2/a.ibd" OS error: 712019-09-03T08:16:12.112227Z 8 [Warning] [MY-012049] [InnoDB] Cannot calculate statistics for table `ytt2`.`a` because the .ibd file is missing. Please refer to http://dev.mysql.com/doc/refman/8.0/en/innodb-troubleshooting.html for how to resolve the issue.2019-09-03T08:16:14.902617Z 8 [Warning] [MY-012049] [InnoDB] Cannot calculate statistics for table `ytt2`.`a` because the .ibd file is missing. Please refer to http://dev.mysql.com/doc/refman/8.0/en/innodb-troubleshooting.html for how to resolve the issue.JSON 日誌裡也有 5 條記錄。[root@centos-ytt80 mysql80]# tailf mysqld.log.00.json{ "log_type" : 1, "prio" : 1, "err_code" : 12592, "subsystem" : "InnoDB", "msg" : "Operating system error number 2 in a file operation.", "time" : "2019-09-03T08:16:12.111808Z", "thread" : 8, "err_symbol" : "ER_IB_MSG_767", "SQL_state" : "HY000", "label" : "Error" }{ "log_type" : 1, "prio" : 1, "err_code" : 12593, "subsystem" : "InnoDB", "msg" : "The error means the system cannot find the path specified.", "time" : "2019-09-03T08:16:12.111915Z", "thread" : 8, "err_symbol" : "ER_IB_MSG_768", "SQL_state" : "HY000", "label" : "Error" }{ "log_type" : 1, "prio" : 1, "err_code" : 12216, "subsystem" : "InnoDB", "msg" : "Cannot open datafile for read-only: "./ytt2/a.ibd" OS error: 71", "time" : "2019-09-03T08:16:12.111933Z", "thread" : 8, "err_symbol" : "ER_IB_MSG_391", "SQL_state" : "HY000", "label" : "Error" }{ "log_type" : 1, "prio" : 2, "err_code" : 12049, "subsystem" : "InnoDB", "msg" : "Cannot calculate statistics for table `ytt2`.`a` because the .ibd file is missing. Please refer to http://dev.mysql.com/doc/refman/8.0/en/innodb-troubleshooting.html for how to resolve the issue.", "time" : "2019-09-03T08:16:12.112227Z", "thread" : 8, "err_symbol" : "ER_IB_MSG_224", "SQL_state" : "HY000", "label" : "Warning" }{ "log_type" : 1, "prio" : 2, "err_code" : 12049, "subsystem" : "InnoDB", "msg" : "Cannot calculate statistics for table `ytt2`.`a` because the .ibd file is missing. Please refer to http://dev.mysql.com/doc/refman/8.0/en/innodb-troubleshooting.html for how to resolve the issue.", "time" : "2019-09-03T08:16:14.902617Z", "thread" : 8, "err_symbol" : "ER_IB_MSG_224", "SQL_state" : "HY000", "label" : "Warning" }那可能有人就問了，這有啥意義呢？只是把格式變了，過濾的規則我看還是沒變。那我們現在給第二條日誌輸出加過濾規則先把過濾日誌的部件安裝起來INSTALL COMPONENT "file://component_log_filter_dragnet";mysql> SET persist log_error_services = "log_filter_internal; log_sink_internal; log_filter_dragnet;log_sink_json";Query OK, 0 rows affected (0.00 sec)只保留 error，其餘的一律過濾掉。SET GLOBAL dragnet.log_error_filter_rules = "IF prio>=WARNING THEN drop.";檢索一張誤刪的表mysql> select * from a;ERROR 1812 (HY000): Tablespace is missing for table `ytt2`.`a`.檢視錯誤日誌和 JSON 錯誤日誌發現錯誤日誌裡有一條 Warning，JSON 錯誤日誌裡的被過濾掉了。2019-09-03T08:22:32.978728Z 8 [Warning] [MY-012049] [InnoDB] Cannot calculate statistics for table `ytt2`.`a` because the .ibd file is missing. Please refer to http://dev.mysql.com/doc/refman/8.0/en/innodb-troubleshooting.html for how to resolve the issue.再舉個例子，每 60 秒只允許記錄一個 Warning 事件mysql> SET GLOBAL dragnet.log_error_filter_rules = "IF prio==WARNING THEN throttle 1/60.";Query OK, 0 rows affected (0.00 sec)多次執行mysql> select * from b;ERROR 1812 (HY000): Tablespace is missing for table `ytt2`.`b`.mysql> select * from b;ERROR 1812 (HY000): Tablespace is missing for table `ytt2`.`b`.mysql> select * from b;ERROR 1812 (HY000): Tablespace is missing for table `ytt2`.`b`.現在錯誤日誌裡有三條 warning 資訊2019-09-03T08:49:06.820635Z 8 [Warning] [MY-012049] [InnoDB] Cannot calculate statistics for table `ytt2`.`b` because the .ibd file is missing. Please refer to http://dev.mysql.com/doc/refman/8.0/en/innodb-troubleshooting.html for how to resolve the issue.2019-09-03T08:49:31.455907Z 8 [Warning] [MY-012049] [InnoDB] Cannot calculate statistics for table `ytt2`.`b` because the .ibd file is missing. Please refer to http://dev.mysql.com/doc/refman/8.0/en/innodb-troubleshooting.html for how to resolve the issue.2019-09-03T08:50:00.430867Z 8 [Warning] [MY-012049] [InnoDB] Cannot calculate statistics for table `ytt2`.`b` because the .ibd file is missing. Please refer to http://dev.mysql.com/doc/refman/8.0/en/innodb-troubleshooting.html for how to resolve the issue.mysqld.log.00.json 只有一條{ "log_type" : 1, "prio" : 2, "err_code" : 12049, "subsystem" : "InnoDB", "msg" : "Cannot calculate statistics for table `ytt2`.`b` because the .ibd file is missing. Please refer to http://dev.mysql.com/doc/refman/8.0/en/innodb-troubleshooting.html for how to resolve the issue.", "time" : "2019-09-03T08:49:06.820635Z", "thread" : 8, "err_symbol" : "ER_IB_MSG_224", "SQL_state" : "HY000", "and_n_more" : 3, "label" : "Warning" }總結，我這裡簡單介紹了下 MySQL 8.0 的錯誤日誌過濾以及 JSON 輸出。MySQL 8.0 的component_log_filter_dragnet 部件過濾規則非常靈活，可以參考手冊，根據它提供的語法寫出自己的過濾掉的日誌輸出。
MySQL 8.0 重新定義了錯誤日誌輸出和過濾，改善了原來臃腫並且可讀性很差的錯誤日誌。比如增加了 JSON 輸出，在原來的日誌後面以序號以及 JSON 字尾的方式展示。比如我機器上的 MySQL 以 JSON 儲存的錯誤日誌 mysqld.log.00.json：[root@centos-ytt80 mysql80]# jq . mysqld.log.00.json{ "log_type": 1, "prio": 1, "err_code": 12592, "subsystem": "InnoDB", "msg": "Operating system error number 2 in a file operation.", "time": "2019-09-03T08:16:12.111808Z", "thread": 8, "err_symbol": "ER_IB_MSG_767", "SQL_state": "HY000", "label": "Error"}{ "log_type": 1, "prio": 1, "err_code": 12593, "subsystem": "InnoDB", "msg": "The error means the system cannot find the path specified.", "time": "2019-09-03T08:16:12.111915Z", "thread": 8, "err_symbol": "ER_IB_MSG_768", "SQL_state": "HY000", "label": "Error"}{ "log_type": 1, "prio": 1, "err_code": 12216, "subsystem": "InnoDB", "msg": "Cannot open datafile for read-only: "./ytt2/a.ibd" OS error: 71", "time": "2019-09-03T08:16:12.111933Z", "thread": 8, "err_symbol": "ER_IB_MSG_391", "SQL_state": "HY000", "label": "Error"}以 JSON 輸出錯誤日誌後可讀性和可操作性增強了許多。這裡可以用 Linux 命令 jq 或者把這個字串 COPY 到其他解析 JSON 的工具方便處理。只想非常快速的拿出錯誤資訊，忽略其他資訊。[root@centos-ytt80 mysql80]# jq ".msg" mysqld.log.00.json"Operating system error number 2 in a file operation.""The error means the system cannot find the path specified.""Cannot open datafile for read-only: "./ytt2/a.ibd" OS error: 71""Cannot calculate statistics for table `ytt2`.`a` because the .ibd file is missing. Please refer to http://dev.mysql.com/doc/refman/8.0/en/innodb-troubleshooting.html for how to resolve the issue.""Cannot calculate statistics for table `ytt2`.`a` because the .ibd file is missing. Please refer to http://dev.mysql.com/doc/refman/8.0/en/innodb-troubleshooting.html for how to resolve the issue."使用 JSON 輸出的前提是安裝 JSON 輸出部件。
INSTALL COMPONENT "file://component_log_sink_json";完了在設定變數 SET GLOBAL log_error_services = "log_filter_internal; log_sink_json";格式為：過濾規則；日誌輸出；[過濾規則]日誌輸出；檢視安裝好的部件mysql> select * from mysql.component;+--------------+--------------------+---------------------------------------+| component_id | component_group_id | component_urn |+--------------+--------------------+---------------------------------------+| 2 | 1 | file://component_log_sink_json |+--------------+--------------------+---------------------------------------+3 rows in set (0.00 sec)現在設定 JSON 輸出，輸出到系統日誌的同時輸出到 JSON 格式日誌。mysql> SET persist log_error_services = "log_filter_internal; log_sink_internal; log_sink_json";Query OK, 0 rows affected (0.00 sec)來測試一把。我之前已經把表 a 物理檔案刪掉了。mysql> select * from a;ERROR 1812 (HY000): Tablespace is missing for table `ytt2`.`a`.現在錯誤日誌裡有 5 條記錄。[root@centos-ytt80 mysql80]# tailf mysqld.log2019-09-03T08:16:12.111808Z 8 [ERROR] [MY-012592] [InnoDB] Operating system error number 2 in a file operation.2019-09-03T08:16:12.111915Z 8 [ERROR] [MY-012593] [InnoDB] The error means the system cannot find the path specified.2019-09-03T08:16:12.111933Z 8 [ERROR] [MY-012216] [InnoDB] Cannot open datafile for read-only: "./ytt2/a.ibd" OS error: 712019-09-03T08:16:12.112227Z 8 [Warning] [MY-012049] [InnoDB] Cannot calculate statistics for table `ytt2`.`a` because the .ibd file is missing. Please refer to http://dev.mysql.com/doc/refman/8.0/en/innodb-troubleshooting.html for how to resolve the issue.2019-09-03T08:16:14.902617Z 8 [Warning] [MY-012049] [InnoDB] Cannot calculate statistics for table `ytt2`.`a` because the .ibd file is missing. Please refer to http://dev.mysql.com/doc/refman/8.0/en/innodb-troubleshooting.html for how to resolve the issue.JSON 日誌裡也有 5 條記錄。[root@centos-ytt80 mysql80]# tailf mysqld.log.00.json{ "log_type" : 1, "prio" : 1, "err_code" : 12592, "subsystem" : "InnoDB", "msg" : "Operating system error number 2 in a file operation.", "time" : "2019-09-03T08:16:12.111808Z", "thread" : 8, "err_symbol" : "ER_IB_MSG_767", "SQL_state" : "HY000", "label" : "Error" }{ "log_type" : 1, "prio" : 1, "err_code" : 12593, "subsystem" : "InnoDB", "msg" : "The error means the system cannot find the path specified.", "time" : "2019-09-03T08:16:12.111915Z", "thread" : 8, "err_symbol" : "ER_IB_MSG_768", "SQL_state" : "HY000", "label" : "Error" }{ "log_type" : 1, "prio" : 1, "err_code" : 12216, "subsystem" : "InnoDB", "msg" : "Cannot open datafile for read-only: "./ytt2/a.ibd" OS error: 71", "time" : "2019-09-03T08:16:12.111933Z", "thread" : 8, "err_symbol" : "ER_IB_MSG_391", "SQL_state" : "HY000", "label" : "Error" }{ "log_type" : 1, "prio" : 2, "err_code" : 12049, "subsystem" : "InnoDB", "msg" : "Cannot calculate statistics for table `ytt2`.`a` because the .ibd file is missing. Please refer to http://dev.mysql.com/doc/refman/8.0/en/innodb-troubleshooting.html for how to resolve the issue.", "time" : "2019-09-03T08:16:12.112227Z", "thread" : 8, "err_symbol" : "ER_IB_MSG_224", "SQL_state" : "HY000", "label" : "Warning" }{ "log_type" : 1, "prio" : 2, "err_code" : 12049, "subsystem" : "InnoDB", "msg" : "Cannot calculate statistics for table `ytt2`.`a` because the .ibd file is missing. Please refer to http://dev.mysql.com/doc/refman/8.0/en/innodb-troubleshooting.html for how to resolve the issue.", "time" : "2019-09-03T08:16:14.902617Z", "thread" : 8, "err_symbol" : "ER_IB_MSG_224", "SQL_state" : "HY000", "label" : "Warning" }那可能有人就問了，這有啥意義呢？只是把格式變了，過濾的規則我看還是沒變。那我們現在給第二條日誌輸出加過濾規則先把過濾日誌的部件安裝起來INSTALL COMPONENT "file://component_log_filter_dragnet";mysql> SET persist log_error_services = "log_filter_internal; log_sink_internal; log_filter_dragnet;log_sink_json";Query OK, 0 rows affected (0.00 sec)只保留 error，其餘的一律過濾掉。SET GLOBAL dragnet.log_error_filter_rules = "IF prio>=WARNING THEN drop.";檢索一張誤刪的表mysql> select * from a;ERROR 1812 (HY000): Tablespace is missing for table `ytt2`.`a`.檢視錯誤日誌和 JSON 錯誤日誌發現錯誤日誌裡有一條 Warning，JSON 錯誤日誌裡的被過濾掉了。2019-09-03T08:22:32.978728Z 8 [Warning] [MY-012049] [InnoDB] Cannot calculate statistics for table `ytt2`.`a` because the .ibd file is missing. Please refer to http://dev.mysql.com/doc/refman/8.0/en/innodb-troubleshooting.html for how to resolve the issue.再舉個例子，每 60 秒只允許記錄一個 Warning 事件mysql> SET GLOBAL dragnet.log_error_filter_rules = "IF prio==WARNING THEN throttle 1/60.";Query OK, 0 rows affected (0.00 sec)多次執行mysql> select * from b;ERROR 1812 (HY000): Tablespace is missing for table `ytt2`.`b`.mysql> select * from b;ERROR 1812 (HY000): Tablespace is missing for table `ytt2`.`b`.mysql> select * from b;ERROR 1812 (HY000): Tablespace is missing for table `ytt2`.`b`.現在錯誤日誌裡有三條 warning 資訊2019-09-03T08:49:06.820635Z 8 [Warning] [MY-012049] [InnoDB] Cannot calculate statistics for table `ytt2`.`b` because the .ibd file is missing. Please refer to http://dev.mysql.com/doc/refman/8.0/en/innodb-troubleshooting.html for how to resolve the issue.2019-09-03T08:49:31.455907Z 8 [Warning] [MY-012049] [InnoDB] Cannot calculate statistics for table `ytt2`.`b` because the .ibd file is missing. Please refer to http://dev.mysql.com/doc/refman/8.0/en/innodb-troubleshooting.html for how to resolve the issue.2019-09-03T08:50:00.430867Z 8 [Warning] [MY-012049] [InnoDB] Cannot calculate statistics for table `ytt2`.`b` because the .ibd file is missing. Please refer to http://dev.mysql.com/doc/refman/8.0/en/innodb-troubleshooting.html for how to resolve the issue.mysqld.log.00.json 只有一條{ "log_type" : 1, "prio" : 2, "err_code" : 12049, "subsystem" : "InnoDB", "msg" : "Cannot calculate statistics for table `ytt2`.`b` because the .ibd file is missing. Please refer to http://dev.mysql.com/doc/refman/8.0/en/innodb-troubleshooting.html for how to resolve the issue.", "time" : "2019-09-03T08:49:06.820635Z", "thread" : 8, "err_symbol" : "ER_IB_MSG_224", "SQL_state" : "HY000", "and_n_more" : 3, "label" : "Warning" }總結，我這裡簡單介紹了下 MySQL 8.0 的錯誤日誌過濾以及 JSON 輸出。MySQL 8.0 的component_log_filter_dragnet 部件過濾規則非常靈活，可以參考手冊，根據它提供的語法寫出自己的過濾掉的日誌輸出。