The MySQL error log is an essential part of database server performance monitoring. Whenever something goes wrong or performance degrades, the Error Logs are usually the first place we look to start troubleshooting.
The MySQL Error Log is one of three related log types:
You can enable error log monitoring to allow Monyog to keep an eye on your MySQL Error Log, and notify you when something goes awry. Moreover, Monyog combines the General Query, Slow Query and Error logs in a single view for both network and cloud servers. For example, in the case of Amazon RDS, Monyog utilizes the Amazon RDS Application Programming Interface (API).
In the Error Logging in MySQL 8 blog article, we were introduced to MySQL’s new component-based architecture. This new architectural model is more modular because components may only interact with each other through the services they provide. Services provided by components are available to the server as well as to other components.
These changes have added to error logging flexibility. Case in point, log messages may now be output with a particular format and output either to a file or the system log.
The improved flexibility correlates to an equal increase configuration complexity as a log component can be a filter or a sink:
For users of Monyog, these complexities are a non-issue, as Monyog makes error log monitoring a straight-forward process of entering log details on the Settings screen and then viewing output on the Monitors screen.
This blog will describe how to configure your MySQL/MariaDB Error Log settings, view output, and glean useful insights from error messages.
The MySQL Error Log may contain messages of varying severities. It collects messages that require persistence beyond that of a single connection or the server’s runtime, that have an intended audience of the DBA or system administrator, as opposed to the application developer. Though the latter may sometimes find the Error Log contents instructive as well. Examples of Error Log entries include exhaustion of resources on the host machine, certain errors reported by the host operating system, stack traces of crashes, messages about damaged databases and indexes, errors on start-up, failed or refused connections, etc.
The error log contains a record of mysqld startup and shutdown times. It also contains diagnostic messages such as errors, warnings, and notes that occur during server startup and shutdown, and while the server is running. For example, if mysqld comes across a table that needs to be checked or repaired, it writes a message to the error log.
On some operating systems, the error log contains a stack trace if mysqld exits abnormally. The trace can be used to determine where mysqld exited.
Monitoring the error log is crucial as changes to the log are indicative of disastrous outages. Monyog makes the task of monitoring the error log very simple. All one needs to do is configure Monyog, and it will take care of the rest! Monyog will alert you of changes in the error log, and in addition, if there is an entry of type [ERROR] in the log, Monyog will extract the corresponding message and send an email it to designated persons.
The Error Log is disabled in MySQL and MariaDB by default, so we have to enable it before Monyog can read and analyze it. To do that:
To configure error log monitoring:
Click the SAVE button to save your Server Settings and close the dialog.
MySQL Log data is combined on the MySQL Logs screen. Like all monitors, it can show log information for multiple servers. The MySQL Logs screen is accessible via the Monitors icon (on the left-hand toolbar) > MySQL Logs (in the MONITOR GROUP list).
The MySQL Log screen displays multiple servers side-by-side:
This MySQL Log screen shows the local Sakila server:
The MySQL Error Log occupies the first three rows of the MONITORS table:
The MySQL Log Screen does a lot more than display server errors. In addition to alerting you of changes in the error log, Monyog provides useful information about each monitor, as well as historical data. It can even extract the message from [ERROR] entries and send them to you via email.
Monitor names are actually clickable links that open the associated EDIT MONITOR screen at the right of the screen. It’s a screen where you can view and modify monitor parameters.
Here’s a description of the form fields. Mandatory fields are marked with an asterisk (*):
Monyog tells you that a monitor reading could be pointing to a potential issue to investigate by placing a Red or Yellow alert next to it. For example, here is a Critical alert next to a Log Error entry:
Alert settings are configured on the Alerts tab of the EDIT MONITOR screen.
The Email Recipients textbox accepts a comma-delimited list of email addresses to send notifications to when an alert is triggered. Clicking the Advanced link divides recipients into Critical and Warning lists.
You can turn Notifications on and off via the Notifications slider control. Note that you also have to configure SMTP or(and) SNMP for MONyog and Notification Settings for servers for this feature to work.
You can also override the notification settings via the Override notify when stable? and Override notify till stable? fields. Each includes radio buttons for “Yes”, “No”, and “Disable”. If set to “Disable”, this variable will not override the server-level setting.
When notifications are enabled for a monitor, the Notifications enabled icon appears beside the monitor name:
The term “Trend Analysis” refers to the concept of collecting information in an effort to spot a pattern, or trend, in the data. With respect to database administration, analyzing history reports gives a way to track trends and identify problem areas in your infrastructure. The idea is that, by using historical information to find recurring problems, you may be able to prevent future issues.
Each server column cell has a History trend value icon on the right-hand side. Clicking it displays historical trend data for the selected TIMEFRAME.
Clicking the SHOW ONLY CHANGED VALUES slider toggles between all log entries and only those where the value has changed.
An essential part of database server performance monitoring, the MySQL error log is usually the first place we look to start troubleshooting database issues. In today’s blog, we learned how Monyog facilitates error log monitoring on MySQL and MariaDB servers by combining all of the log output together on one screen. Moreover, Monyog offers additional value by providing features such as Alerts, Notifications, and History Trend Values.