MySQL 8.0 : Improved error logging

We extensively use MySQL error logging for proactive diagnostics, MySQL 8 has improved error logging which is capable of sending multiple format (like JSON) logs  to multiple destinations like Kibana for log processing and analysis. Let’s see how this works:

MySQL 8.0 default configuration

mysql> select * from global_variables where VARIABLE_NAME like 'log_error_%';
+---------------------+----------------------------------------+
| VARIABLE_NAME       | VARIABLE_VALUE                         |
+---------------------+----------------------------------------+
| log_error_services  | log_filter_internal; log_sink_internal |
| log_error_verbosity | 2                                      |
+---------------------+----------------------------------------+

Now that means log events will pass first through log_filter_interval (built-in filter component) and then through log_sink_interval (built-in log writer component). To configure and enable log_error_services you first have to install log component using INSTALL COMPONENT.

Currently available log components lib / plugins:

• JSON log writer – component_log_sink_json.so
• Built-in log filter and writer – log_filter_interval and log_sink_interval

Step 1 – Load JSON writer first if it’s not loaded:

INSTALL COMPONENT 'file://component_log_sink_json';

Step 2 – Set log_error_services to ‘ log_filter_interval ; log_sink_interval ; log_sink_json ‘  (we will set this with MySQL 8 very new feature SET PERSIST)

SET PERSIST log_error_services = 'log_filter_internal; log_sink_internal; log_sink_json';

You may also do same by setting my.cnf

[mysqld]
log_error_services='log_filter_internal; log_sink_internal; log_sink_json'

How do you order the components in log_error_services is very significant :

log_filter_internal; log_sink_1; log_sink_2

This makes log events to pass through built-in filter, then to first writer and then to second writer. Both writers receive filtered log events. Confirm that to log_error_services value / system variable:

log_sink_1; log_filter_internal; log_sink_2

So here, The log events pass through first writer, then to built-in filter, then to second writer. The second writer receives filtered events. You can as well configure error logging in way that one log contains messages for all log events and other containing messages of only the subset of log events.