Monitoring MySQL – The error log

It is important that you monitor the MySQL error log. There are a few different options available for defining the details of the log. If not specified the default is [datadir]/[hostname].err. This is not an ideal location for 2 reasons.

First, a correctly configured MySQL datadir will have permissions only for the mysql user, and this is generally restrictive access to the user only and no group or world permissions. Other users/groups should have limited access to the mysql error log.

Second, the datadir is for data, not logs, especially logs that can potentially fill file systems. I am referring here to more then just the error log.

I would recommend you create a separate directory for MySQL logs such as the error, slow and general logs. An example I implement for single installation environments using Linux mysql packages is:

mkdir /var/log/mysql
chown mysql:mysql /var/log/mysql
chmod 750 /var/log/mysql

There does not seem to be a consensus over whether to include the hostname or not in the error log filename. My preference is to not include. I would rather the filename to be consistent across multiple servers. The argument is what about when consolidating logs from multiple servers. I discount this because you have to connect to the server to retrieve logs, create a sub directory of that hostname for consolidated logs.

With Linux distributions you may not find log files where you expect. Ubuntu packages for example has the log going to syslog. While the theory is to make system logging and monitoring easier, it makes MySQL specific monitoring more difficult. You also suffer a logrotate problem where you may only have 7 days of log. I prefer to have access to all historical MySQL log information.

The best choice is to define the error log with log-error, in both the [mysqld_safe] and [mysqld] section of your servers my.cnf

[mysqld_safe]
log-error=/var/log/mysql/error.log

[mysqld]
log-error=/var/log/mysql/error.log

In MySQL 5.1 you have the luxury of different output sources, FILE, TABLE or BOTH for the general log and the slow log with –log-output. No option exists for the error log.

Other my.cnf options to be aware of include:

  • log-warnings | skip-log-warnings
  • syslog | skip-syslog

There is generally also lacking in the standard monitoring products/plugins that present MySQL status information. In my monitoring MySQL solutions I provide a line count of the MySQL error log, so that a delta can be easily detected and then reviewed more proactively.

One issue with a recent client is the lack of access to the physical box by different parties and therefore the lack of access to the log. The identification that something needs to be viewed, then the ability to be able to view is an important problem to be solved.

References

Some other references for MySQL error log monitoring.

Tags: , , , , ,

11 Responses to “Monitoring MySQL – The error log”

  1. Great post. I agree 100%. Make it 200% :)

    Roland

  2. [...] I discuss in detail the options for the MySQL error log including recommendations for the MySQL error log file location in Monitoring MySQL – The error log [...]

  3. sbester says:

    you made a typo:

    [mysql] should be [mysqld] !!

  4. Mathayo says:

    InnoDB: stored checksum 4186899336, prior-to-4.0.14-form stored checksum 1330360371
    InnoDB: Page lsn 5 1523664719, low 4 bytes of lsn at page end 1523664719
    InnoDB: Page number (if stored to page already) 251,
    InnoDB: space id (if created with >= MySQL-4.1.1 and stored already) 0
    InnoDB: Page may be an index page where index id is 0 122
    InnoDB: Also the page in the doublewrite buffer is corrupt.
    InnoDB: Cannot continue operation.
    InnoDB: You can try to recover the database with the my.cnf
    InnoDB: option:
    InnoDB: set-variable=innodb_force_recovery=6

  5. Md. Mahbub says:

    I want to develop a slave server for replication which will replicate different databases from different hosts. How can I do this?

  6. [...] [EN] Configuration des logs d’erreur de MySQL [...]

  7. [...] Some more light reading at Have you checked your MySQL error log today? and Monitoring MySQL – The error log [...]

  8. [...] Some more light reading at Have you checked your MySQL error log today? and Monitoring MySQL – The error log [...]

  9. [...] Google nailed it as I quickly found “Ronald Bradford”. Ronald calls himself the MySQL Expert.  The specific blog posting I found is: Monitoring MySQL The Error Log http://ronaldbradford.com/blog/monitoring-mysql-the-error-log-2009-09-16/ [...]