MySQL error log contains diagnostics messages such as errors, warnings and notes that occur during MySQL startup, shutdown and while the server is running. For example, a InnoDB table is corrupted and need to repaired, This will be recorded in the error log. MySQL 8.0 Error uses the MySQL component architecture for log event filtering and writing. The MySQL system variable log_error_services controls which log components to enable and the rules for filtering the log events. The component table in the mysql system database contains the information about currently loaded comments and shows which components have been registered with INSTALL COMPONENT. To confirm the components installed, you may use the SQL below:
SELECT * FROM mysql.component;
Currently the available log components are in lib/plugins:
- component_log_filter_dragnet.so
- component_log_sink_json.so
- component_log_sink_syseventlog.so
- component_log_sink_test.so
Table of Contents
Error Log configuration / system variables
The log_error_services system variable controls which log components to enable for error logging
mysql> select @@log_error_services; +----------------------------------------+ | @@log_error_services | +----------------------------------------+ | log_filter_internal; log_sink_internal | +----------------------------------------+ 1 row in set (0.00 sec)
The default value indicates that log evens first pass through the built-in filter controller, log_filter_interval and later through the built-in log writer component, log_sink_interval. Typically, a sink processes log events into log messages that have a particular format and writes these messages to its associated output, such as a file or the system log. The combination of log_filter_internal and log_sink_internal implements the default error log filtering and output behavior.
The output destination of error log can be collected from system variable log_error . You can configure the destination of error log either to the system log or JSON file.
You can make mysqld to write the error log to system log (Event Log on Windows and syslog on Linux and Unix systems):
INSTALL COMPONENT 'file://component_log_sink_syseventlog'; SET GLOBAL log_error_services = 'log_filter_internal; log_sink_syseventlog';
You can enable JSON writer to record the error log by first loading the writer component and then modifying log_error_services system variable:
INSTALL COMPONENT 'file://component_log_sink_json'; SET GLOBAL log_error_services = 'log_filter_internal; log_sink_json';
traditional error log:
2019-03-10T08:36:59.950769Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.15) starting as process 13222 2019-03-10T08:37:00.253523Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed. 2019-03-10T08:37:00.267812Z 0 [System] [MY-010931] [Server] /usr/sbin/mysqld: ready for connections. Version: '8.0.15' socket: '/var/run/mysqld/mysqld.sock' port: 3306 MySQL Community Server - GPL. 2019-03-10T08:37:00.429164Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Socket: '/var/run/mysqld/mysqlx.sock' bind-address: '::' port: 33060 2019-03-10T08:37:37.635761Z 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.15) MySQL Community Server - GPL. 2019-03-10T08:37:37.985380Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.15) starting as process 13410 2019-03-10T08:37:38.277912Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed. 2019-03-10T08:37:38.291494Z 0 [System] [MY-010931] [Server] /usr/sbin/mysqld: ready for connections. Version: '8.0.15' socket: '/var/run/mysqld/mysqld.sock' port: 3306 MySQL Community Server - GPL.
JSON error log:
{ "prio" : 0, "err_code" : 10910, "source_line" : 2191, "source_file" : "mysqld.cc", "function" : "clean_up", "msg" : "/usr/sbin/mysqld: Shutdown complete (mysqld 8.0.15) MySQL Community Server - GPL.", "time" : "2019-03-10T09:21:18.722864Z", "err_symbol" : "ER_SERVER_SHUTDOWN_COMPLETE", "SQL_state" : "HY000", "subsystem" : "Server", "label" : "System" }
- Filtering MySQL error with log_error_verbosity:
- Errors only – 1
- Errors and warnings – 2
- Errors, warnings and notes – 3
- If log_error_verbosity is configured to 2 or higher, the MySQL server even logs the statement that are unsafe for statement-based logging / replication. if the value is 3, the server logs aborted connections and access-denied errors for fresh connection attempts. It is recommended to configure log_error_verbosity with 2 or higher to record detailed information about what is happening to MySQL infrastructure.
- How MySQL 8.0 component based error logging filters are different ?
We are used to default built-in error log filters that are configured with MySQL system variable log_error_verbosity (default is 2). But, MySQL 8.0 has another component that allows you to filter on rules that you define: log_filter_dragnet. I have explained below step-by-step on how to setup Rule-Based Error Log Filtering using log_filter_dragnet :
INSTALL COMPONENT 'file://component_log_filter_dragnet'; SET GLOBAL log_error_services = 'log_filter_dragnet; log_sink_internal';
To limit information events to no more than one per 60 seconds:
mysql> SET GLOBAL dragnet.log_error_filter_rules =
-> 'IF prio>=INFORMATION THEN throttle 1/60.';
Query OK, 0 rows affected (0.00 sec)
To throttle plugin-shutdown messages to only 5 per 5 minutes (300 seconds):
IF err_code == ER_PLUGIN_SHUTTING_DOWN_PLUGIN THEN throttle 1.
To throttle errors and warnings to 1000 per hour and information messages to 100 per hour:
IF prio <= INFORMATION THEN throttle 1000/3600 ELSE throttle 100/3600.
and we can monitor the available dragnet rule:
mysql> select * from global_variables where VARIABLE_NAME like 'dragnet%'\G *************************** 1. row *************************** VARIABLE_NAME: dragnet.log_error_filter_rules VARIABLE_VALUE: IF prio>=INFORMATION THEN throttle 1/60. 1 row in set (0.00 sec)
Conclusion
MySQL 8.0 Error Logging Services are more powerful compared to the versions before and you can now filter error logging much better by creating your own components
