Keeping track of the state of a database server is certainly one of the most important tasks of every DBA. As with most database products, this control is carried out via an error log. This is an ASCII log file that is located in a certain path (depending on the operating system or configuration) in the file system off the server.
With version 8.0.22 there is now a further possibility to access this relevant information.
Own your Errors...
From version 8.0.22. the current content of the error log can be accessed via a kind of "online view" with the table "performance_schema.error_log".
The table contains the same information as the entries in the known ASCII log file.
Convenient evaluations can of course now be carried out via the "SQL interface" in the error log world:
Let's take a look at the most recent three system error messages:
Someone still seems to want to log on to this system with an outdated password algorithm (error code "MY-013360). Suppose we are aware of this problem and would like to ignore it, we could use a corresponding system variable for this purpose. The following setting can be used to filter error messages depending on your needs:
From this moment on, the error "MY-013360" is no longer processed. Of course, further error codes can be taken into account, separated by commas.
Sink the Bismarck error...
The internal error process in the current version (8) now consists of a configurable (!) process, consisting of two steps:
- Error filtering
- Error writing
What happens within these two steps is determined by the "log_error_services" variable:
In our example, the "log_filter_internal" plug-in takes care of filtering the error messages based on "log_error_supression_list". "Log_sink_internal" is responsible for storing the error messages in the "error_log". The word "plugin" already indicates that changes are possible here.
For example, the "data sink" can be changed and even expanded. However, before other plugins can be used, they must be loaded.
This setting now causes the data to be written in three places:
- It is stored in the classical error-log,
- in JSON format and
- in the system-eventlogot the operating system (you might remember the following from older versions; mysqld_safe —syslog ;-) )
There is also an alternative extension (plug-in) for the "error filter": "log_filter_dragnet"
This is a rule-based filter (e.g., with IF-THEN-ELSE structures) that can be used to map more complex mechanisms to our error codes. The exact functionality including the syntax and some examples can be found at the following URL: https://dev.mysql.com/doc/refman/8.0/en/error-log-rule-based-filtering.html
Lessons learnt?
It is good to see that apart from the big features (e.g.,InnoDB cluster, MySQL Shell, ...), there are also optimizations for the small details. A "elaborated" error management is certainly not an "error" (!) and helps one or the other DBA to optimize the operating processes.
Do you have any questions about the operation of MySQL databases? Talk to us.